Skip to content
TheChatArchiveThe Chat Archive
gpt-3.5-turbo-0301· openaiHugging Face Suzhen/CodeChat

I have an incremental formula in a column. The formula in row 74 is;…

  1. User

    I have an incremental formula in a column. The formula in row 74 is; =IF(A74<>"",VLOOKUP(A74<>"",VLOOKUP(A74,LIST!AA2:CC54,3,FALSE),"") . Using VBA to replicate this formula I have the code .Range("K2:K" & lastRow).FormulaR1C1 = _ "=IF(RC[-10]<>"",VLOOKUP(RC[-10],LIST!R2C1:R54C3,3,FALSE),"""")" . Comparing both, what is wrong with the VBA alternative since the formula is known to calculate correctly

  2. Assistant

    There doesn't seem to be anything inherently wrong with the VBA alternative. It appears to be essentially the same formula written using R1C1 notation instead of A1 notation, which is just a different way of referencing cells in Excel.

    However, there are a few small differences to note. In the original formula, the reference to cell A74 is absolute (i.e. $A74), which means it will always refer to column A regardless of where the formula is copied or dragged. In the VBA formula, the reference to the corresponding cell (i.e. RC[-10]) is relative, which means it will adjust based on where the formula is placed.

    Additionally, the VBA formula is using double quotes to signify an empty string ("") as the value to return if the condition is not met, while the original formula just uses two sets of double quotes with nothing in between (""), which is effectively the same thing.

    Overall, these differences are minor and shouldn't affect the calculation of the formula.

  3. User

    Is the reference to the range in the LIST sheet both the same

  4. Assistant

    Yes, the reference to the range in the LIST sheet is the same in both the original formula and the VBA alternative.

    In the original formula, the range reference is in A1 notation: LIST!AA2:CC54. This means the range starts in cell A2 and goes to cell C54 on the LIST sheet.

    In the VBA alternative, the range reference is in R1C1 notation: LIST!R2C1:R54C3. This means the range starts in the cell in the second row and first column of the LIST sheet (i.e. cell A2), and goes to the cell in the 54th row and third column of the LIST sheet (i.e. cell C54).

    Both notations are valid ways of referencing a range of cells in Excel, and as long as the references are the same, the formula should work the same way in both cases.

  5. User

    The code bellow is not updating Column 11 when I enter data in Column 1: Private Sub Worksheet_Change(ByVal Target As Range) If Not Application.CalculationState = xlDone Then Exit Sub If Target.Column = 1 Then Application.EnableEvents = False Call UpdateColumn11 Application.EnableEvents = True End If TimeStampSimple Target, "G2", "J", "mm/dd/yyyy hh:mm:ss", False End Sub

    Sub UpdateColumn11() Dim lastRow As Long With Worksheets("PREQUEST") ' Find the last row with data lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

    ' Fill in column 11 with the VLOOKUP results .Range("K2:K" & lastRow).FormulaR1C1 = _ "=IF(RC[-10]<>"",VLOOKUP(RC[-10],LIST!R2C1:R54C3,3,FALSE),"""")"

    End With End Sub

  6. Assistant

    The reason why Column 11 is not updating when data is entered in Column 1 might be because the UpdateColumn11 sub is not being called when the target range changes. The UpdateColumn11 sub contains the formula that updates Column 11, but is only called by the Worksheet_Change event when a change is made to Column 1.

    One potential issue could be the fact that the line Call UpdateColumn11 is in the Worksheet_Change event, which could cause an infinite loop if the event triggers another change in the worksheet. To fix this, try moving this line of code outside of the If statement so that it always runs when a change is made to the worksheet:

    Private Sub Worksheet_Change(ByVal Target As Range)
        If Not Application.CalculationState = xlDone Then Exit Sub
        If Target.Column = 1 Then
            Application.EnableEvents = False
            Application.ScreenUpdating = False
            UpdateColumn11
            Application.EnableEvents = True
            Application.ScreenUpdating = True
        End If
    
        TimeStampSimple Target, "G2", "J", "mm/dd/yyyy hh:mm:ss", False
    End Sub
    
    Sub UpdateColumn11()
        Dim lastRow As Long
        
        With Worksheets("PREQUEST")
            ' Find the last row with data
            lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
            
            ' Fill in column 11 with the VLOOKUP results
            .Range("K2:K" & lastRow).FormulaR1C1 = _
                "=IF(RC[-10]<>"",VLOOKUP(RC[-10],LIST!R2C1:R54C3,3,FALSE),"""")"
        End With
    End Sub
    

    This version of the code also disables screen updating while the UpdateColumn11 sub runs, which can speed up the process and avoid flickering on the screen.

Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.