Issue with VBA Vlookup with a Loop Function

[Jdogg2022]

Hi Guys,

Having an issue trying to get my VBA Vlookup to work as it keeps bringing up #NAME? in the box, and the VBA code is putting a @"E2"..
After every row, i'd like it to go down one step and increase the variable. Hence the need for my Integer. Screenshot below

export (4).xlsx
	J	K	L	M	N
1	Port Connect Vessel	Port Connect Voyage	Port Connect ETA	Port Connect Activated	Port Connect Location
2	#N/A	#N/A	#N/A	#N/A	#NAME?
3	OOCL BUSAN	522S	22/06/2022 5:00	4/06/2022 10:19	Vessel
4	TORONTO TRADER	125W	13/06/2022 5:00	30/05/2022 7:40	Vessel
5	TORONTO TRADER	125W	13/06/2022 5:00	30/05/2022 7:41	Vessel
ag-grid

Cell Formulas
Range	Formula
J2:J5	J2	=VLOOKUP($H2&$E2,Sheet1!$A:$BC,7,FALSE)
K2:K5	K2	=VLOOKUP($H2&$E2,Sheet1!$A:$BC,8,FALSE)
L2:L5	L2	=VLOOKUP($H2&$E2,Sheet1!$A:$BC,49,FALSE)
M2:M5	M2	=VLOOKUP($H2&$E2,Sheet1!$A:$BC,55,FALSE)
N2	N2	=VLOOKUP('E2',Sheet1!B:BC,5,FALSE)
N3:N5	N3	=VLOOKUP($H3&$E3,Sheet1!$A:$BC,9,FALSE)

Below is my loop

Range("N2").Select
    
    For i2 = 2 To 1000
        
        'If Range("N" & i2).Value = "#N/A" Then
        If IsError(Range("N" & i2).Value) = True Then
            Range("N" & i2).Value = "=VLOOKUP(E" & i2 & ",Sheet1!C[-12]:C[41],5,FALSE)"
                                 
        End If
        
        If IsError(Range("N" & i2).Value) = False Then
            If Range("N" & i2).Value = "" Then
               Exit For
            End If
        End If
    Next i2

Any help would be appreciated! I've been racking my brains!

Cheers

 

[Fluff]

Try it like

            Range("N" & i2).Formula2R1C1 = "=VLOOKUP(r" & i2 & "c5,Sheet1!C[-12]:C[-8],5,FALSE)"

 

[Jdogg2022]

Try it like

            Range("N" & i2).Formula2R1C1 = "=VLOOKUP(r" & i2 & "c5,Sheet1!C[-12]:C[-8],5,FALSE)"

Hi Fluff,

Thanks for your response, its greatly appreciated. You are not far off the money! But now when I run the code it comes up with the below so the fomula doesnt calculate.. Got any other ideas how to solve? (its got the apostrophies around the E2)

Let me know your thoughts.

 

[Jdogg2022]

Hi Fluff,

Thanks for your response, its greatly appreciated. You are not far off the money! But now when I run the code it comes up with the below so the fomula doesnt calculate.. Got any other ideas how to solve? (its got the apostrophies around the E2)

Let me know your thoughts.

View attachment 66631

HI Fluff,

I figured it out! It was the [ ] that was screwing everything up. Thanks for your kind support!

For i2 = 2 To 2000
        If IsError(Range("N" & i2).Value) = True Then
            Range("N" & i2).Value = "=VLOOKUP(E" & i2 & ",Sheet1!B:F,4,FALSE)"
        End If
        
        If IsError(Range("N" & i2).Value) = False Then
            If Range("N" & i2).Value = "" Then
               Exit For
            End If
        End If
    Next i2

 

[Fluff]

You obviously did not use the suggestion I made as your image still shows you using columns B:BC which is pointless as you are only trying to get the value from the 5th column.

 

[Jdogg2022]

You obviously did not use the suggestion I made as your image still shows you using columns B:BC which is pointless as you are only trying to get the value from the 5th column.

Yes you are correct, I change the lookup in the end.. But managed to get it working which was the main thing!

 

[Fluff]

Glad you sorted it & thanks for the feedback.