VLookup with for loop

[nosense]

I have a probelm with vlookup function used with for loop.

In the file attached you can see what i am trying to achive. I need values from e7:e11 that are corresponding to the letters in range c7:c11 to be allocated to the correspodning entries in another table. I need to use the function vlookup and for each cell.

This i swhat i managed to workout s far but I am stuck now

Sub newmacroo()



 On Error Resume Next


Set batchrange = Range("i7:i11")

Set myrange = Range("c7:e11")



For Each cl In batchrange
Cells(11, asd) = Application.WorksheetFunction.VLookup(cl, myrange1, 3, False)

asd = asd + 1
Next cl





End Sub

[simcode]

try

Sub newmacroo()
   On Error Resume Next

   Set batchrange = Range("i7:i11")
   Set myrange = Range("c7:e11")

   For Each cl In batchrange
      asd = asd + 1
      Cells(11, asd) = Application.WorksheetFunction.VLookup(cl, myrange, 3, False)
   Next cl
End Sub

[nosense]

try

Sub newmacroo()
   On Error Resume Next

   Set batchrange = Range("i7:i11")
   Set myrange = Range("c7:e11")

   For Each cl In batchrange
      asd = asd + 1
      Cells(11, asd) = Application.WorksheetFunction.VLookup(cl, myrange, 3, False)
   Next cl
End Sub

This is not pasting where it should.
I need the values to be pasted using vlookup i.e. to corresponding entries in the right hand side table...

[nosense]

I was also thinking that i will need to use match function to find appropriate row number to paste a value but not sure

Sub newmacroo()
   On Error Resume Next

   Set batchrange = Range("i7:i11")
   Set myrange = Range("c7:e11")
   
For Each cell In batchrange

asd = Application.WorksheetFunction.Match(celll, myrange, 0)
      
   asd1 = asd.Row

Next cell


   For Each cl In batchrange
      Cells(asd1, 11) = Application.WorksheetFunction.VLookup(cl, batchrange, 3, False)
      
   
      
   
  
   
   Next cl
End Sub

[Richard Buttrey]

..Why do you need to use VBA at all? why not just enter the VLOOKUP in K7:K11 directly?

[nosense]

..Why do you need to use VBA at all? why not just enter the VLOOKUP in K7:K11 directly?

My actual spreadsheet is much more complex and I have not pasted it here just created an example instead. The work I do needs to be done in a way I am asking .

Thanks

[Richard Buttrey]

My actual spreadsheet is much more complex and I have not pasted it here just created an example instead. The work I do needs to be done in a way I am asking .

Thanks

Examples often don't reflect the production data which is why we more often than not prefer to see the real data, if only a subset.

Since you are hardcoding the range i7:i11 it's not at all apparent why you NEED a macro. It would be nice to understand why you think that since that often helps us understand the overall process. Even if you do want a macro why not just write the VLOOKUP into the range

i.e.

Range("I7:I11") = "=Vlookup(lookup_value,lookup_range,lookup_column,False)"

[Kaper]

Try (based on your original one):

Sub newmacroo()
dim batchrange as range, myrange as range, cl as range
Set batchrange = Range("i7:i11")
Set myrange = Range("c7:e11")
For Each cl In batchrange
  On Error Resume Next
  cl.Offset(0, 2) = Application.WorksheetFunction.VLookup(cl, myrange, 3, False)
  On Error GoTo 0
Next cl
End Sub

note that Richard's comments are still valid, I just corrected, what I seen as obviously wrong approach in your macro - using asd variable, mismatched rows and columns in Cells(11, asd), using myrange1 in vlookup...