VLookup in VBA to Paste Values

**StuartH** · 09-01-2011, 10:07 AM

Hi, I have a VBA problem which looks like it could be solved using a VLookup function but I can't seem to get the coding to work.

Suppose I have a list of colours on Sheet 1. I have a drop-down list of this data in another sheet (Sheet 2). I then have a button for a macro which runs a process to return some information on the chosen colour. I want to copy this data and paste it in Sheet 1 on the row with the chosen colour on.

Intuitively, I thought perhaps running a VLookup might work by using the drop-down list as the lookup value and the data set on Sheet 1 as the table array then offsetting the selection by one to the right before pasting. Alternatively, I've tried using the data-set and adjacent column as the table array and selecting the cell in row 2. Neither's worked which may be due to my coding being terrible or me approaching the problem in a silly way.

**JBeaucaire** · 09-01-2011, 10:16 AM

Assuming Sheet1 colors in column A, Sheet2 has a color chosen in A2 and a value in B2 you want to "transfer" to Sheet1, then this will work:

Option Explicit

Sub TransferValue()
Dim Rw As Long

With Sheets("Sheet1")
    Rw = .Range("A:A").Find([A2].Value, LookIn:=xlValues, LookAt:=xlWhole).Row
    .Range("B" & Rw).Value = [B2].Value       'always puts value in Sheet1 column B
End With

End Sub

If you want to transfer the value to the next empty cell in the same row so you never replace any values already transfered, but keep adding more and more, change this line of code:

.Cells(Rw, .Columns.Count).End(xlToLeft).Offset(, 1).Value = [B2].Value  'always add in empty cell

The button that activates this macro needs to reside on Sheet2.

**StuartH** · 09-01-2011, 10:26 AM

That's great, thanks!

Is it possible to extend this and suppose my macro returned a range of data in Sheet2 in cells B2:B5, copy and paste this column as a row into Sheet1 alongside the colour?

If not, I can always repeat your code for the other values

**JBeaucaire** · 09-01-2011, 10:35 AM

Anything is possible. Finish "designing" your sheets, then post the final layout here with all the desired goals and we can get it done in one shot.

Click GO ADVANCED and use the paperclip icon to post up a copy of your workbook.