Find cell contents in range

[Guest]

Hi,
i am really struggling to create a macro to carry out the following.
( i am quite new to this and presently using a book but things are explained
fully!)

lets say cell K12 = 2245
i have a range of A2:A50 containing a list between 2220 & 2270

i need my macro to look at the value of K12. Find that value in range, and
make the cell with that value active
so in my list between 2220 & 2270 is an activecell where the value is 2245

i hope this can be done and hope that someone can help me.

Regards,

Nigel

 

[Bob Phillips]

With Worksheets(1).Range("A2:A50") Set cell = .Find(Range("K12").Value,
LookIn:=xlValues) If Not cell Is Nothing Then cell.Activate End
IfEnd With-- HTH
Bob Phillips

 

[papou]

Hello Nigel
Try this:
On Error Resume Next
Range("A1", Range("A65536").End(xlUp)).Find(Range("K12") _
..Value, , xlValues, xlWhole, xlByRows).Activate
If Err <> 0 Then MsgBox "value not found"

HTH
Cordially
Pascal

 

[Guest]

Something like this would do it...

Sub findit()
Dim rngFound As Range
Dim rngFind As Range
Dim rngStart As Range
Dim strFind As String

strFind = Range("K12").Value
'set the range to search
Set rngFind = Range("A2:A50")
'set where to start the search
Set rngStart = rngFind.Cells(1)
'try & find it
Set rngFound = rngFind.Find(What:=strFind, After:=rngStart,
LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows,
SearchDirection:=xlNext, _
MatchCase:=False)
'found it
If Not rngFound Is Nothing Then
MsgBox "Value Found at: " & rngFound.Address
rngFound.Activate
Else
'didn't find it
MsgBox "Sorry, couldn't find " & strFind & " in that range."
End If
'clean up
Set rngFind = Nothing
Set rngStart = Nothing
Set rngFound = Nothing
strFind = vbNullString
End Sub

 

[Bob Phillips]

Don't know what happened with that post

With Worksheets(1).Range("A2:A50")
Set cell = .Find(Range("K12").Value, LookIn:=xlValues)
If Not cell Is Nothing Then
cell.Activate
End If
End With

 

[Jim May]

Bob: On a smaller scale I've set up some data, as follows and run:

Private Sub CommandButton1_Click()
Dim cell As Range
With Worksheets(1).Range("A2:A16")
Set cell = .Find(Range("D3").Value, LookIn:=xlValues)
If Not cell Is Nothing Then
cell.Activate
End If
End With
End Sub

As I step thru the code After Line 4 Set Cell -- Cell = Nothing...??
What am I missing?
Jim

 

[Jim May]

Further in Cell A12 I have 2245 and in D3 I have 2245.
In Cell A5 I have 456, si If I can D3 to 456,,, It works
In stepping thru Cell=456,
Strange...
TIA,

 

[Jim May]

OK, after Formatting all my number as General - Everything works;
I didn't figure the formatting (Comma, 2 decimals) 2,245.00 mattered,
but it must!!