Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 |
#7
|
|||
|
|||
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 |
#8
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula to return ADDRESS of cell in range that meets criteria | Excel Worksheet Functions | |||
Find Max and Min based on cell reference | Excel Discussion (Misc queries) | |||
Adding contents of one cell to a range of cells. | Excel Worksheet Functions | |||
make cell contents equal to null value - not blank, but empty | Excel Worksheet Functions | |||
Returning a Value to a Cell Based on a Range of Uncertain Size | Excel Worksheet Functions |