Find cell contents in range
 

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

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

"Nigel" wrote in message
...
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

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

"Nigel" a écrit dans le message de news:
...
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

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

--
Kind Regards,
Will Riley


"Nigel" wrote:

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

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

--
HTH

Bob Phillips

"Bob Phillips" wrote in message
...
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

"Nigel" wrote in message
...
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: 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

"Bob Phillips" wrote in message
...
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

--
HTH

Bob Phillips

"Bob Phillips" wrote in message
...
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

"Nigel" wrote in message
...
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

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" wrote in message
news:qq%he.6951$It1.5267@lakeread02...
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

"Bob Phillips" wrote in message
...
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

--
HTH

Bob Phillips

"Bob Phillips" wrote in message
...
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

"Nigel" wrote in message
...
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

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!!

"Jim May" wrote in message
news:_V%he.6954$It1.3559@lakeread02...
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" wrote in message
news:qq%he.6951$It1.5267@lakeread02...
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

"Bob Phillips" wrote in message
...
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

--
HTH

Bob Phillips

"Bob Phillips" wrote in message
...
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

"Nigel" wrote in message
...
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