excelvbaevaluate

Excel VBA Evaluate function

I would like to use the column number of a cell as a variable in a for loop by using the Evaluate function. Here is the first part of the code:

Sub search()
Dim whe As Integer
 
lrow = ActiveSheet.Cells(Rows.Count, "C").End(xlUp).Row

For i = 2 To lrow
For j = 3 To 4

If Cells(i, j).Value = Range("G2").Value Then
whe = Evaluate("=COLUMN(" & Cells(i, j).Address(0, 0) & ")")

If whe = 3 Then
do something

However, I get a "Run-time error 13: Type mismatch" message at the If statement. I have tried to define the "whe" variable as Integer or Variant but neither of them worked. Could you please help me?

Solution

  • But the result of my evaluate function is a number not an array.

    Using VarType, the result of Evaluate("=COLUMN(" & Cells(i, j).Address(0, 0) & ")") is actually an array.

    It is a one-based array (verified using LBound), so

    whe = Evaluate("=COLUMN(" & Cells(i, j).Address(0, 0) & ")")(1)

    assigns its first element to whe.

    I have tried to define the "whe" variable as Integer or Variant but neither of them worked.

    Assigning to a Variant definitely works here, so I suspect you were referring to an unresolved error in the If statement, not the Evaluate.