CODEWITHSUNDEEP
vbaexcel-formula
Conor
Conor

Reputation: 125

VBA IF AND Formula with a referencing RC Range

I have the below code but I'm getting an error when I try to run the macro.
Based on my macro there is a moving column depending on criteria. Therefore I have added code to get the column number. See ColRef1.
The issue I'm having is when I try to add this into my IF AND formula. The issue is with the first piece of the formula and perhaps I'm writing it incorrectly. IF(AND(RC&ColRef1&>0,U2>0)

Sub Test()
    Dim GetRow1 As Integer
    Dim GetRow2 As Integer
    Dim GetRow3 As Integer
    Dim GetCol1 As Range
    Dim GetCol2 As Range
    Dim ColRef1 As Integer

    'finds last row on Client Options tab
    Sheets("Client Options").Select
    GetRow1 = ActiveSheet.Cells(Rows.Count, "G").End(xlUp).Row

    'finds last row on Client Response tab
    Sheets("Client Response").Select
    GetRow2 = ActiveSheet.Cells(Rows.Count, "E").End(xlUp).Row

    'finds last row on Recon tab
    Sheets("Recon").Select
    GetRow3 = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row

    'finds Column that contains Client Options since this is a moving column depending on number of brokers on recon
    Sheets("Recon").Select
    Set GetCol1 = ActiveSheet.UsedRange.Find("Client Options", , xlValues, xlWhole)
    ColRef1 = GetCol1.Column

    Sheets("Recon").Select
    Range("B2").Value = "=IF(AND(RC&ColRef1&>0,U2>0),""FALSE"",IF(T2>0,(VLOOKUP(A2,'Client Options'!G$2:L$" & GetRow1 & ",6,FALSE)),IF(U2>0,(VLOOKUP(A2,'Client Response'!E$2:G$" & GetRow2 & ",3,FALSE)))))"


End Sub

Upvotes: 0

Views: 649

Answers (1)

Darren Bartrup-Cook
Darren Bartrup-Cook

Reputation: 19837

  • You're combining R1C1 styles with A1 styles in your formula.
  • ColRef1 is within the string, so it's trying to reference RC&ColRef1 rather than RC5 (for example).
  • Side note - you don't need to select the sheet to work with it.

Try this code (note - addresses such as U2 should be R2C21).

Sub Test()

    Dim GetRow1 As Long
    Dim GetRow2 As Long
    Dim GetRow3 As Long
    Dim GetCol1 As Range
    Dim ColRef1 As Long

    'finds last row on Client Options tab
    GetRow1 = Sheets("Client Options").Cells(Rows.Count, "G").End(xlUp).Row

    'finds last row on Client Response tab
    GetRow2 = Sheets("Client Response").Cells(Rows.Count, "E").End(xlUp).Row

    'finds last row on Recon tab
    GetRow3 = Sheets("Recon").Cells(Rows.Count, "A").End(xlUp).Row

    'finds Column that contains Client Options since this is a moving column depending on number of brokers on recon
    Set GetCol1 = Sheets("Recon").UsedRange.Find("Client Options", , xlValues, xlWhole)
    If Not GetCol1 Is Nothing Then
        ColRef1 = GetCol1.Column

        Sheets("Recon").Range("B2").FormulaR1C1 = "=IF(AND(RC" & ColRef1 & " >0,R2C21>0),FALSE,IF(R2C20>0,(VLOOKUP(R2C1,'Client Options'!R2C7:R" & GetRow1 & "C12,6,FALSE)),IF(R2C21>0,(VLOOKUP(R2C1,'Client Response'!R2C5:R" & GetRow2 & "C7,3,FALSE)))))"
    End If

End Sub

Upvotes: 1

Related Questions