Macro

  steviegee 13:14 26 Aug 2010
Locked

On sheet 1 I have numbers all over the spreadsheet and in the adjacent cell to its right a description. On sheet 2 I have a list of the same numbers in column A. What I need to do is run a macro which will find the numbers on sheet 1 and put the descriptions in column B.

e.g on sheet 1 in cell r17 the number 001 and in cell s17 abc. On sheet 2 in cell a1 is 001 so I want it to look for 001 on sheet 1 and put in cell b1 the abc which is cell s17 and so on.

I hope you understand what I mean and that this can be done. Thanks.

  VoG II 18:28 26 Aug 2010

Sub btest()
Dim LR As Long, i As Long, Found As Range
With Sheets("Sheet2")
LR = .Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To LR
With .Range("A" & i)
Set Found = Sheets("Sheet1").Find(what:=.Value, LookIn:=xlValues, lookat:=xlWhole)
If Not Found Is Nothing Then .Offset(, 1).Value = Found.Offset(, 1).Value
End With
Next i
End With
End Sub

  VoG II 19:20 26 Aug 2010

Sub btest()
Dim LR As Long, i As Long, Found As Range
With Sheets("Sheet2")
LR = .Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To LR
With .Range("A" & i)
Set Found = Sheets("Sheet1").UsedRange.Find(what:=.Value, LookIn:=xlValues, Lookat:=xlWhole)
If Not Found Is Nothing Then .Offset(, 1).Value = Found.Offset(, 1).Value
End With
Next i
End With
End Sub

  steviegee 08:34 02 Sep 2010

Thanks VoG worked a treat.

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

OnePlus 5 review

Alice Saey's mesmerising animation for Dutch singer Mark Lotterman

iPad Pro 10.5in (2017) review

Comment booster votre iPhone ?