VBA - Vlookup

[RoseChapman]

Hi, could anyone help me, please?
I have to copy from the Test2 spreadsheet from column AG to AJ and from column AL to BB and paste them in test 1 spreadsheet. The table array in Test 2 is E:BB. The formula below copy all columns, but I do not need to paste all columns I just need the ones I've mentioned above. What shall I include in the formula to be able to get what I need? many thanks for your help.

Sub Test()


For i = 8 To 100
For j = 1 To 22


On Error Resume Next
Sheets("Test1").Cells(i, j + 5).Value = Application.WorksheetFunction.VLookup(Sheets("Test1").Cells(i, 1).Value, Sheets("Test2").Range("E:BB"), j + 28, False)


On Error GoTo 0


Next j
Next i




End Sub

 

[JackDanIce]

Hi, it's not clear what you're asking and without being able to see your Pc monitor, can only guess what you might mean.

Can you provide an explicit example, e.g.

Cell A8 contains value <xxx> in Sheet Test1
Match it to column ? in sheet Test2 (e.g. match is found in cell Z4)If match is in row ?, then copy AG?:AJ?, AL?:BB? from Test2 to columns ?:? and ?:? in Test1
(e.g. match is in row 4, then copy AG4:AJ4 and AL4:BB4 from Test 2 to columns D:L in Test 1)

Use actual values for ? from your spreadsheet

Pretend you're explaining your issue to a blind person who can't see your PC screen. How much explicit information will help them suggest working code for you?

 

[offthelip]

Doing a double loop through two different worksheets using vlookup is a very inefficient way of doing what you want to do. You want to end up with just values, so you can very easily do this with variant arrays. see this thread for an example on how to use variant arrays insted of vlookup in VBa. also note my signature
https://www.mrexcel.com/forum/excel-questions/1043185-vlookup-vba-alternative.html

 

[RoseChapman]

Hello Offthelip, many thanks for your message, I have seen what you said in the link you provided but i am getting lost, sorry!

I am trying to use the code below, but I am getting a bit confused, i hope you can help me.

I want to paste the values from sheet1 in sheet2, for that i want to see if the reference numbers in sheet 1 (column E) are in sheet 2 (column A), then if the reference number in sheet 1 (column E) matches the reference number in sheet 2 (column A), then take the values from sheet 1 columns AG to AJ and also from sheet 1 columns AL to BB and Paste all these columns in sheet 2 columns F to I (the ones which are AG to AJ in sheet 1) and columns K to AA (the ones which are AL to BB in Sheet 1). I know this is a bit of the hassle, sorry!. But many thanks for your help in advance.




Dim lastrow, lastrow2, i As Long
Dim Searchfor, j, inarr As Variant




'Data Dump Sheet
With Sheets("Sheet 2")
lastrow = .Cells(Rows.Count, "A").End(xlUp).Row
inarr = Range(.Cells(1, 1), .Cells(lastrow, 20))




End With




'Values to look up & paste Sheet
With Sheets("Sheet 1")
lastrow2 = .Cells(Rows.Count, "B").End(xlUp).Row
' load variant array with sercha variables
searcharr = Range(.Cells(1, 2), .Cells(lastrow2, 2))
' define an output aray
outarr = Range(.Cells(2, 3), .Cells(lastrow2, 19))
End With




On Error Resume Next
For i = 2 To lastrow2
For j = 2 To lastrow
Searchfor = searcharr(i, 1)




If inarr(j, 1) = Searchfor Then
For kk = 2 To 18
outarr(i, kk - 1) = inarr(j, kk)
Next kk


Exit For


End If
Next j
Next i
' writeout the output array
With Sheets("Sheet 1")
Range(.Cells(2, 3), .Cells(lastrow2, 19)) = outarr
End With


End Sub

 

[offthelip]

You are on the right track, try this, I am assuming that you want the dat copied across to the row on sheet 2 where the refernce number is found.

Sub test()
'Data Dump Sheet
With Sheets("Sheet2")
lastrow2 = .Cells(Rows.Count, "A").End(xlUp).Row
refno2 = Range(.Cells(1, 1), .Cells(lastrow2, 1))
outarr = Range(.Cells(1, 1), .Cells(lastrow2, 27))
End With
With Sheets("Sheet1")
lastrow1 = .Cells(Rows.Count, "E").End(xlUp).Row
inarr = Range(.Cells(1, 1), .Cells(lastrow1, 54))
End With


For i = 2 To lastrow1
  For j = 2 To lastrow2
   ' check refnumbers to find the row to copy to
   If inarr(i, 5) = refno2(j, 1) Then
     ' now copy sheet1 data to ouptput array
      For k = 6 To 27
       ' check if column j, if so don't copy
       If k <> 10 Then
        outarr(j, k) = inarr(i, k + 27)
       End If
      Next k
      Exit For
   End If
 Next j
Next i
 
With Sheets("Sheet2")
 Range(.Cells(1, 1), .Cells(lastrow2, 27)) = outarr
End With
 
End Sub

 

[RoseChapman]

Hello offthelip,
many thanks for trying to help, much appreciated, but i don't get what i need, I have tried to fix it but it gets worse.

I want to paste the values from sheet1 in sheet2, for that i want to see if the reference numbers in sheet 1 (column E) are in sheet 2 (column A), then if the reference number in sheet 1 matches the reference number in sheet 2 then take the values from sheet 1 only the columns AG:AJ and AL:BB and Paste these columns in sheet 2 columns F:I and K:AA.

I do not want to paste Column AK from sheet 1 to column J in sheet 2. Many thanks

 

[offthelip]

I believe that is exactly what the code I have written does,
what is going wrong? Does the code not run, is it copying data from the wrong place or to the wrong palce or over writing stuff which is already there. Give me a clue as to where the error is. I believe I have done it exactly to your description howver your requirements are not totallly clear, I had to make an assumption as to which rows you wanted it written and what to do about the other columns.

 

[RoseChapman]

Hi Offthelip,
many thanks for your help. Well what it does is, it takes the reference numbers which are in column A, sheet1 and it paste it in horizontal all across in sheet 2.
It does not copy the values from columns AG:AJ and AL:BB from sheet 2 and paste then in sheet 1 columns F:I and and K:AA. I don't know what to do. Many thanks for your help

 

[offthelip]

You quite clearly state in your post 4

I want to paste the values from sheet1 in sheet2,

That is exactly what i have done. No you seem to be asking to paste from sheet 2 to sheet 1

What about defining exactly what you do want. Define exactly which columns on which sheet to which columns on which sheet

I will help you if you give me a clear unambiguous definition.

I want to paste the values from sheet1 in sheet2, for that i want to see if the reference numbers in sheet 1 (column E) are in sheet 2 (column A), then if the reference number in sheet 1 matches the reference number in sheet 2 then take the values from sheet 1 only the columns AG:AJ and AL:BB and Paste these columns in sheet 2 columns F:I and K:AA.

I thought this was fairly clear, so hat is what you got

 

[RoseChapman]

Sorry for not being clear, it confuses me too. The code does not do the job, it copies all the reference numbers in column E ( Sheet 1) onto rows from B to AA in sheet 2.
Let's see if this explanation is clearer.
I need to run a vlookup comparing column E in sheet 1 with column A in sheet 2. Then copy the values on columns AG to AJ of sheet 1 for the matching rows found in the lookup onto columns F to I on sheet 2, and columns AL to BB of sheet 1 onto columns K to AA on sheet 2.