Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2009
    Location
    Rochester NY
    Posts
    11

    Unanswered: Need To Create Row Reference Not Knowing The Row

    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."

  2. #2
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    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

  3. #3
    Join Date
    Jun 2009
    Location
    Rochester NY
    Posts
    11
    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.

  4. #4
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    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
    Last edited by MikeTheBike; 07-03-09 at 04:21.

  5. #5
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •