Solved: VLOOKUP Code

[mangisqa]

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]

example of workbook would be useful

[CatDaddy]

Maybe:

[VBA]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[/VBA]

[mangisqa]

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]

[vba]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[/vba]

[mangisqa]

Ok it works, however is there a way for A5 to change to match the number for each row the formula is in?

[CatDaddy]

[VBA]"=IFERROR(VLOOKUP(A" & cell.Row & ",'Managers-Shifts'!$A$2:$D$999985,4,FALSE),""New Employee"")"
[/VBA]

[mangisqa]

THANKS! and I have already been able to adapt this to the other formulas I need!

[CatDaddy]

glad to help