Find a number

  • Thread starter Thread starter Martin Wheeler
  • Start date Start date
M

Martin Wheeler

xl2000
I want to find the number 1 in range M7:M31 and then copy the corresponding
number in col A to the destination cell, R14.
So if 1 is in M14 and A14 has the number 36 it will be copied to R14.
Any assistance will be greatly appreciated.
Ta,
Martin
 
I think you're looking for something like this:

Option Explicit
Sub testme01()

Dim myRng As Range
Dim FoundCell As Range
Dim FindWhat As String

FindWhat = "1"

With Worksheets("sheet1")
Set myRng = .Range("M7:M31")
With myRng
Set FoundCell = .Cells.Find(what:=FindWhat, _
after:=.Cells(.Cells.Count), _
lookat:=xlWhole, searchorder:=xlByRows, _
searchdirection:=xlNext, MatchCase:=False)
End With
If FoundCell Is Nothing Then
MsgBox "not found"
Else
.Cells(FoundCell.Row, "R").Value = .Cells(FoundCell.Row, "A").Value
End If
End With

End Sub
 
Back
Top