CODEWITHSUNDEEP
vbaexcelformulavlookup
Ang Yiwei
Ang Yiwei

Reputation: 91

Application-defined or object-defined error with formula VBA

I'm new to VBA and encounter the

application-defined or object-defined error

while doing it.

Set twb = ThisWorkbook
Set extwbk = Workbooks.Open("abc.xlsx")
Set x = extwbk.Worksheets("FILTERED").Range("$A:$A")

With twb.Sheets("FILTERED")

    For rw = 9 To .Cells(Rows.Count, 1).End(xlUp).Row
        .Cells(rw, 34).Formula = "=IF(ISERROR(VLOOKUP(.Cells(rw, 1).Value2, x, 1, False))=TRUE,""New"",""Old"")"
    Next rw

End With

This can be done manually but I'm asked to do it by programming. It is simply apply the formula from AH9 to the end of the data. May I know is there any coding error in the for loop?

Thanks in advance.

Upvotes: 1

Views: 1674

Answers (1)

A.S.H
A.S.H

Reputation: 29332

Dont put VBA variables inside your string formula. Resolve them in VBA and inject the result into the formula:

 .Formula = "=IF(ISERROR(VLOOKUP(" & .Cells(rw, 1).Value2 & "," & _
   x.Address(External:=True) & ", 1, False))=TRUE,""New"",""Old"")"

p.s. dont use =TRUE to check for a boolean, things like If ISERROR(Something) = TRUE don't make sense. Just If ISERROR(Something).

.Formula = "=IF(ISERROR(VLOOKUP(" & .Cells(rw, 1).Value2 & "," & _
  x.Address(External:=True) & ", 1, False)),""New"",""Old"")"

If you want the formula to be dynamic and recalculate accordingly when the cell at (rw, 1) changes in the future, use .Address instead of .Value2:

'                                                vvvvvvvv
.Formula = "=IF(ISERROR(VLOOKUP(" & .Cells(rw, 1).Address & "," & _
  x.Address(External:=True) & ", 1, False)),""New"",""Old"")"

Upvotes: 3

Related Questions