lookup,copy, paste macro??

[Hugh Askew]

I have searched Google and the groups for this, but unable to find.

Need a macro to take a entered value in any cell on Sheet 1, column A,
look up that value on Sheet 2, column A, go one row over to col. B,
copy the cell, then paste it into Sheet 1, column B, next to the
entered value.
Just like Vlookup, only copy>paste .
Need to paste the cell contents, not just values.


any and all help GREATLY appreciated.

 

[Guest]

Hi Hugh

If I've understood you correctly then this should do it ...

Sub doIt()

' first get your sheet1 column A lookup value from whichever rowNumber .. let's make it 4
rownumber = 4
lookupValueA = Sheets("sheet1").Cells(rownumber, 1)

'now iterate through your list on sheet2 (assume list ends with first blank cell encountered)
rowPointer = 1
While Sheets("sheet2").Cells(rowPointer, 1) <> ""
If Sheets("sheet2").Cells(rowPointer, 1) = lookupValueA Then
lookupValueB = Sheets("sheet2").Cells(rowPointer, 2) ' get adjacent value in column B
End If
rowPointer = rowPointer + 1
Wend

'now put that value in column B in sheet1
Sheets("sheet1").Cells(rownumber, 2) = lookupValueB

' bingo ... it's inefficient in that it looks through the whole of Sheet2's values ... but what the heck!
' btw it assumes no duplication of values in your list sheet2

End Sub

 

[Dave Peterson]

I would have thought that contents meant values, but if you mean formatting,
too, how about something like this:

Option Explicit
Sub testme()

Dim res As Variant
Dim wks As Worksheet
Dim lookupWks As Worksheet
Dim lookupRng As Range
Dim myCell As Range
Dim myRng As Range

Set wks = Worksheets("sheet1")
Set lookupWks = Worksheets("sheet2")

With lookupWks
Set lookupRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp))
End With

With wks
Set myRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp))
For Each myCell In myRng.Cells
If IsEmpty(myCell) Then
'do nothing
Else
res = Application.Match(myCell.Value, lookupRng, 0)
If IsError(res) Then
'no match-what should be done?
Else
lookupRng(res).Offset(0, 1).Copy _
myCell.Offset(0, 1)
End If
End If
Next myCell
End With

End Sub


I used .offset(0,1) in both spots, but you could change each of them to what you
want.

 

[Hugh Askew]

Dave, many,many gracious thanks! EXACTLY what was needed!!!