VBA, error while using lookup

Question

I am having two Sheets BW and PSW.

With the BW the data is starting from row 4. the above 3 rows are merged and I have some command Buttons there. the sheet PSW starts from 1st row.

I am looking for the ID in sheet1,which starts from L5 and when the Id matches in sheet2, it pulls the date from sheet2 to sheet1. I am using the below code. The code, is not executing any Output. Could anyone tell where I am making mistake.

I suppose,in the below line, the range starts from A and since my data starts from row5, it does not Count. if this is the case, then how i should Change the range. I had this code running, if my data starts with row1 without any command buttons.

totalrows = Sheets("BW").Cells(Rows.Count, "A").End(xlUp).Row

Sub lookupePSR()
Dim totalrows As Long, totalrowsSht2 As Long
totalrows = Sheets("PSW").Cells(Rows.Count, "A").End(xlUp).Row
totalrowsSht2 = Sheets("PSW").Cells(Rows.Count, "A").End(xlUp).Row
Sheets("PSW").Range("AA5:AA" & totalrows).Formula = Application.WorksheetFunction.IfError(Application.VLookup(Sheets("PSW").Range("L5:L" & totalrowsSht2), Sheets("PSW").Range("$A:$L"), 7, 0), "")
End Sub

Do you want to put formulas in the cells, or the results of formulas in the cells ?? — Gary's Student
Sheets("PSW").Range("AA5:AA" & totalrows).Formula = "PUT ACTUAL FORMULA HERE" Put the formula in the worksheet and then put it in the code that I gave. Replace all ", if any, with "" — Siddharth Rout
Remember to put the formula in AA5 and then use that. If it doesn't work then post the formula that you manually put in AA5 — Siddharth Rout

Unknown Unknown · Accepted Answer · 2017-07-01T20:30:58

As mentioned in comments, put the actual formula into the worksheet; optionally revert to the values returned by the formulas.

with Sheets("PSW").Range("AA5:AA" & totalrows)
    .Formula = "=iferror(vlookup(l5, $a:$g, 7, false), text(,))"
    'optionally revert the formulas' returned values to values in cells
    .value = .value
end with

The TEXT(,) is the same as ""; i.e. a zero-length string. I use this because you have to double-up double-quotes (e.g. "" becomes """") when used in a quoted string like the formula above and that confuses matters.

I've cut your lookup range down to A:G since you are only looking for the seventh column in the range.

VBA, error while using lookup

1 Answers