Need To Create Row Reference Not Knowing The Row

EdAROC · #1 (**permalink**) 07-01-09, 16:54

Exporting from an Access query into an Excel spreadsheet in VBA. The statement is:
xlWSh.Range("A2").CopyFromRecordset rst
Everything works OK, except that I want a formula in column E:

E column needs to be a formula to compare the total of 4 other columns (F, H, J, L) in the row to a cell (D column) that contains the balance on hand. Example - In Cell E2:
=IF(D2 <> (F2 + H2 + J2 + L2),"*","")

So, I need to construct a formula in the query that's the equivalent to the above, without knowing the row.

I think I'm brain dead. I know ROW() and COLUMN() return the current row and column, and I can use OFFSET to reference the cells, but, I can't for the life of me construct the cell addresses, i.e. D2. I was building the formula piecemeal in E2, the 1st step was a reference to D2:
=offset(address(row(),column()),0,-1)
Got error, "The formula you typed contains an error."

MikeTheBike · #2 (**permalink**) 07-02-09, 08:03

Quote:

Originally Posted by EdAROC

Exporting from an Access query into an Excel spreadsheet in VBA. The statement is:
xlWSh.Range("A2").CopyFromRecordset rst
Everything works OK, except that I want a formula in column E:

E column needs to be a formula to compare the total of 4 other columns (F, H, J, L) in the row to a cell (D column) that contains the balance on hand. Example - In Cell E2:
=IF(D2 <> (F2 + H2 + J2 + L2),"*","")

So, I need to construct a formula in the query that's the equivalent to the above, without knowing the row.

I think I'm brain dead. I know ROW() and COLUMN() return the current row and column, and I can use OFFSET to reference the cells, but, I can't for the life of me construct the cell addresses, i.e. D2. I was building the formula piecemeal in E2, the 1st step was a reference to D2:
=offset(address(row(),column()),0,-1)
Got error, "The formula you typed contains an error."

Hi

You could try this

"=IF(RC[-1]<>(RC[1]+RC[3]+RC[5]+RC[7]),""*"","""")"

??

Hint: Try recording a macro in Excel !?

MTB

EdAROC · #3 (**permalink**) 07-02-09, 13:50

I haven't seen that format in decades! Didn't know it still existed.

It sort of works. For some reason the formula goes into the cells OK, but, is treated by Excel as text, as though there it was preceded by an apostrophe. If I edit the cell, simply adding a space at the end of the text string and deleting that space, Excel then treats it as a formula.

So, right now I'm Googling to see if this can be resolved.
- or -
The back of my mind is formulating to add a loop in the VBA to move through each cell in the column and insert the "=IF(D2 <> (F2 + H2 + J2 + L2),"*","")" formula.
- - - - - - -
For i = 1 to 747
strCellID = "E"&i+1
strCellD = "D"&i+1
strCellF = "F"&i+1
strCellH = "H"&i+1
strCellJ = "J"&i+1
strCellL = "L"&i+1
xlWSh.Range(strCellID) = "=IF(" & strCellD & " <> (" & strCellF & " + " & strCellH & " + " & strCellJ & " + " & strCellL & "),""*"","""")"
Next i
- - - - - - -

Will let you know how this turns out.
Suggestions are always welcome.

MikeTheBike · #4 (**permalink**) 07-03-09, 03:18

I must admit that is what I always do it in these circumstances (I have never tried, or though of, creating an Excel formula for inserting directly from a query), and if you are doing it within Excel then I would go for

"=IF(RC[-1]<>(RC[1]+RC[3]+RC[5]+RC[7]),""*"","""")"

as this definitly does work in Excel (I have used it numerous times) and, as previously noted, you do not need to know which cell it is being entered into, therefore the loop is very simple.

MTB

for example

Code:

Option Explicit

Sub Test()
    Dim i As Integer
    
    For i = 6 To 12
        Cells(i, "E") = "=IF(RC[-1]<>(RC[1]+RC[3]+RC[5]+RC[7]),""*"","""")"
    Next i

End Sub

Colin Legg · #5 (**permalink**) 07-05-09, 17:33

Hi,

You can insert the formulas without a loop, examples:

Code:

Sub a1_notation()
     
     Range("E2:E747").Formula = "=IF(D2 <> (F2 + H2 + J2 + L2),""*"","""")"
     
End Sub

Sub r1c1_notation()
     
     Range("E2:E747").FormulaR1C1 = "=IF(RC[-1] <> (RC[1] + RC[3] + RC[5] + RC[7]),""*"","""")"

End Sub

Internet Services

Web Development

Digital Marketing

Consumer Tech