VLOOKUP error

[rollingzep]

I am trying to populate values on AK in ws TSS Trans, with S&P values from sheet, China by comparing AL with Ccode.

based on

This is my code, I am getting Subscript out of range error, at Chk line

<CODE>

LastRow = ws.Range("A" & Rows.Count).End(xlUp).Row
ws.Range("AK2:AK" & LastRow).Clear
With Sheets("TSS Trans").Cells(1).CurrentRegion.Resize(, 37)
Data = .Value
With Sheets("China").Cells(1).CurrentRegion
For i = 2 To UBound(Data)
Chk = Application.Match(Data(i, 38), .Columns(6), 0)
If Not IsError(Chk) Then Data(i, 37) = .Cells(Chk, 3)
Next i
End With
.Value = Data
End With
</CODE>

 

[jasonb75]

Perhaps because you've set Data as a 37 column array and you're trying to reference column 38.

 

[rollingzep]

Perhaps because you've set Data as a 37 column array and you're trying to reference column 38.

But I am trying to populate col 37 by matching the values of col 38 of the first ws to col 6 of the second ws.
Or maybe, I do not understand your reply.

 

[rollingzep]

But I am trying to populate col 37 by matching the values of col 38 of the first ws to col 6 of the second ws.
Or maybe, I do not understand your reply.

I think I understand what you meant. How to declare the array the right way?

 

[jasonb75]

In the third line, Change .Resize(, 37) to .Resize(, 38)

 

[rollingzep]

In the third line, Change .Resize(, 37) to .Resize(, 38)

Changed it to 38.
Although, I get no error but also the data is not getting populated in AK.

 

[jasonb75]

Try using .Cells(Chk, 3).Value
I don't see any reason for the code to fail now but sometimes it can error if you don't use .Value.

 

[rollingzep]

Try using .Cells(Chk, 3).Value
I don't see any reason for the code to fail now but sometimes it can error if you don't use .Value.

Not sure why it is not populating

I tried this
<CODE>
ws.Range("AK2:AK" & ws.Cells(Rows.Count, "A").End(xlUp).Row).FormulaR1C1 = "=VLOOKUP(""*""&RC[-38]&""*"",China!C1:C6,3,1)"
</CODE>

But I am getting #N/A. If I do, China!C1:C6,3,0. it populates the header, S&P. I need the values of S&P
What is the correct code?

 

[jasonb75]

I'm not sure either, you've only posted part of your code so the problem could be elsewhere. Please post the full code. When you do please click on the </> icon and paste the code into the popup to post it. When you type the tags, you are not entering them correctly so the formatting is getting lost.