PDA

View Full Version : Solved: VLOOKUP Code



mangisqa
07-19-2011, 10:36 AM
Hello,

I am trying to move my formulas into VBA so that my large spreadsheet runs faster:

On Sheet Paste Full Report Here in column G i have the following formula:
=IFERROR(VLOOKUP(A5,'Managers-Shifts'!$A$2:$D$999985,4,FALSE),"New Employee")
This continues down to Line 16000. How can I write this in VBA to calculate?
Note: A5 is a variable that changes depending on the row the formula is located.

I am very new to this and learn best by seeing an example and given labels.

Thanks!
Q

CatDaddy
07-19-2011, 10:39 AM
example of workbook would be useful

CatDaddy
07-19-2011, 10:45 AM
Maybe:

ActiveWorkbook.Sheets(1).Activate
Range("A1").Activate

Range("G2").Formula = "=IFERROR(VLOOKUP(A5,'Managers-Shifts'!$A$2:$D$999985,4,FALSE),"New Employee")"

Range("G2").Select
Selection.AutoFill Destination:=Range("G2:G" & UsedRange.Rows.Count), Type:=xlFillDefault

mangisqa
07-19-2011, 11:02 AM
I get an error with the code above, attached is a simple version of the worksheet
Match the ID numbers and autofill name of manager

CatDaddy
07-19-2011, 11:03 AM
Sub formulaToCells()
ActiveWorkbook.Sheets(2).Activate
Range("A1").Activate
Dim cell As Range
Dim rangeToFill As String
rangeToFill = "G2:G" & Range("G:G").Rows.Count
Application.ScreenUpdating = False
For Each cell In Range(rangeToFill)
cell.Formula = "=IFERROR(VLOOKUP(A5,'Managers-Shifts'!$A$2:$D$999985,4,FALSE),""New Employee"")"
Next cell
Application.ScreenUpdating = True
End Sub

mangisqa
07-19-2011, 11:12 AM
Ok it works, however is there a way for A5 to change to match the number for each row the formula is in?

CatDaddy
07-19-2011, 11:14 AM
"=IFERROR(VLOOKUP(A" & cell.Row & ",'Managers-Shifts'!$A$2:$D$999985,4,FALSE),""New Employee"")"

mangisqa
07-19-2011, 11:21 AM
THANKS! and I have already been able to adapt this to the other formulas I need!

CatDaddy
07-19-2011, 11:29 AM
glad to help :)