CODEWITHSUNDEEP
excelvba
mgunia
mgunia

Reputation: 147

VBA code with if function (values taken from the cell)

I have VBA code which uses countif function and checks if the value "United States" is populated in the Test column (see the code below).

I would like to make this more flexible and I want it to get the data from the specific column (e.g. A2) instead of static value "United States".

I know that this piece of code should be amended

Debug.Print "=IF(RC[" & CStr(FormulaCol - LookupCol - 1) & "]=""United States"",1,0)"
ActiveCell.Offset(0, FormulaCol - 1).FormulaR1C1 = "=IF(RC[" & CStr(LookupCol -     FormulaCol) & "]=""United States"",1,0)"

I tried to use the simple function code =IF(B2=A2,1,0) and put A2 in the code. All my tries returned the syntax error.

My Macro

Sub bbb()
Dim FormulaCol As Long
Dim LookupCol As Long
Dim TotalRows As Long
Dim TotalCols As Long
Dim i As Long

Sheets("Sheet1").Select
TotalRows = ActiveSheet.UsedRange.Rows.Count
TotalCols = ActiveSheet.UsedRange.Columns.Count

For i = 1 To TotalCols
If Cells(1, i).Value = "Test" Then
    LookupCol = i
    Exit For
End If
Next

For i = 1 To TotalCols
If Cells(1, i).Value = "Values" Then
    FormulaCol = i
    Range("A2").Activate
    Debug.Print "=IF(RC[" & CStr(FormulaCol - LookupCol - 1) & "]=""United States"",1,0)"
    ActiveCell.Offset(0, FormulaCol - 1).FormulaR1C1 = "=IF(RC[" & CStr(LookupCol - FormulaCol) & "]=""United States"",1,0)"
    Cells(2, FormulaCol).AutoFill Destination:=Range(Cells(2, FormulaCol), Cells(TotalRows, FormulaCol))
    With Range(Cells(2, FormulaCol), Cells(TotalRows, FormulaCol))
    .Value = .Value
    End With
End If
Next
End Sub

Upvotes: 1

Views: 8118

Answers (2)

ikh
ikh

Reputation: 2436

Try using R2C1 instead of A2 in the function. The reason for this is that you are using Range.FormulaR1C1, which accepts only RC-style references.

The VBA line which assigns the formula to the cell will then look like this:

ActiveCell.Offset(0, FormulaCol - 1).FormulaR1C1 = _
    "=IF(RC[" & CStr(LookupCol - FormulaCol) & "]=R2C1,1,0)"

Upvotes: 1

John Bustos
John Bustos

Reputation: 19544

... Replace "]=""United States"",1,0)" with "]=" & Range("A2").Value & ",1,0)"

That should do the trick...

Upvotes: 0

Related Questions