| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select cell by value
Ok...
I'm writing a macro in which i know that none of the values created in a worksheet will be the same (except for blank cells). What code/command can I use to search for a specific value in the worksheet and then select the cell that contains that value? Thanks in advance. |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Select cell by value
You can use find something like this
dim rngFound as range set rngfound = sheets("sheet1").Columns("A").Find(What:="Find This", _ LookIn:=xlFormulas, _ LookAt:=xlPart, _ MatchCase:=False) if rngfound is nothing then msgbox "Sorry... Not found" else rngfound.select end if -- HTH... Jim Thomlinson " wrote: Ok... I'm writing a macro in which i know that none of the values created in a worksheet will be the same (except for blank cells). What code/command can I use to search for a specific value in the worksheet and then select the cell that contains that value? Thanks in advance. |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Select cell by value
I'm fairly new with this....
Could you explain what the code is saying, and also, I want it to search the whole sheet and not just one column. Jim Thomlinson wrote: You can use find something like this dim rngFound as range set rngfound = sheets("sheet1").Columns("A").Find(What:="Find This", _ LookIn:=xlFormulas, _ LookAt:=xlPart, _ MatchCase:=False) if rngfound is nothing then msgbox "Sorry... Not found" else rngfound.select end if -- HTH... Jim Thomlinson " wrote: Ok... I'm writing a macro in which i know that none of the values created in a worksheet will be the same (except for blank cells). What code/command can I use to search for a specific value in the worksheet and then select the cell that contains that value? Thanks in advance. |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Select cell by value
Using the Recorder to find 15:
Sub Macro1() Cells.Find(What:="15", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _ xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _ , SearchFormat:=False).Activate End Sub -- Gary's Student " wrote: Ok... I'm writing a macro in which i know that none of the values created in a worksheet will be the same (except for blank cells). What code/command can I use to search for a specific value in the worksheet and then select the cell that contains that value? Thanks in advance. |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Select cell by value
This code is using the find function (same find as you use in an Excel
worksheet). The issue with using find in code is that it crashes if it does not find something... For that reason I have a range object (rngfound) that I set to the cell that is found (or not found). If the cell is not found then the range object references nothing. So my if statement say if rngfound is nothing then nothing was found. Else it selects the cell that was found... Give this code a try. It looks at the entire active sheet. Sub findStuff() Dim rngFound As Range Set rngFound = Cells.Find(What:="Find This", _ LookIn:=xlFormulas, _ LookAt:=xlPart, _ MatchCase:=False) If rngFound Is Nothing Then MsgBox "Sorry... Not found" Else rngFound.Select End If End Sub -- HTH... Jim Thomlinson " wrote: I'm fairly new with this.... Could you explain what the code is saying, and also, I want it to search the whole sheet and not just one column. Jim Thomlinson wrote: You can use find something like this dim rngFound as range set rngfound = sheets("sheet1").Columns("A").Find(What:="Find This", _ LookIn:=xlFormulas, _ LookAt:=xlPart, _ MatchCase:=False) if rngfound is nothing then msgbox "Sorry... Not found" else rngfound.select end if -- HTH... Jim Thomlinson " wrote: Ok... I'm writing a macro in which i know that none of the values created in a worksheet will be the same (except for blank cells). What code/command can I use to search for a specific value in the worksheet and then select the cell that contains that value? Thanks in advance. |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Select cell by value
Great...thanks, one more thing, once I have a cell selected is there a
way to find out what cell is selected, as in "Sheet1.Cells(x, y)" and if i have for example "Cells(2, 4)" is there a way to have it assign those values to variables so that for example x = 2 and y = 4? Thank you so much for the find code...it's a great help. Jim Thomlinson wrote: This code is using the find function (same find as you use in an Excel worksheet). The issue with using find in code is that it crashes if it does not find something... For that reason I have a range object (rngfound) that I set to the cell that is found (or not found). If the cell is not found then the range object references nothing. So my if statement say if rngfound is nothing then nothing was found. Else it selects the cell that was found... Give this code a try. It looks at the entire active sheet. Sub findStuff() Dim rngFound As Range Set rngFound = Cells.Find(What:="Find This", _ LookIn:=xlFormulas, _ LookAt:=xlPart, _ MatchCase:=False) If rngFound Is Nothing Then MsgBox "Sorry... Not found" Else rngFound.Select End If End Sub -- HTH... Jim Thomlinson " wrote: I'm fairly new with this.... Could you explain what the code is saying, and also, I want it to search the whole sheet and not just one column. Jim Thomlinson wrote: You can use find something like this dim rngFound as range set rngfound = sheets("sheet1").Columns("A").Find(What:="Find This", _ LookIn:=xlFormulas, _ LookAt:=xlPart, _ MatchCase:=False) if rngfound is nothing then msgbox "Sorry... Not found" else rngfound.select end if -- HTH... Jim Thomlinson " wrote: Ok... I'm writing a macro in which i know that none of the values created in a worksheet will be the same (except for blank cells). What code/command can I use to search for a specific value in the worksheet and then select the cell that contains that value? Thanks in advance. |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Select cell by value
rngFound is the found Cell so there are any number of things you can do...
Give this a quick try to see what I mean... Sub findStuff() Dim rngFound As Range dim x as long dim y as integer Set rngFound = Cells.Find(What:="Find This", _ LookIn:=xlFormulas, _ LookAt:=xlPart, _ MatchCase:=False) If rngFound Is Nothing Then MsgBox "Sorry... Not found" Else rngFound.Select msgbox rngFound.address x= rngfound.row y = rngfound.column msgbox x & vbcrlf & y msgbox "Value of next cell over is " & rngFound.offset(0, 1).value set rngfound = rngfound.offset(1,0) rngfound.select End If End Sub -- HTH... Jim Thomlinson " wrote: Great...thanks, one more thing, once I have a cell selected is there a way to find out what cell is selected, as in "Sheet1.Cells(x, y)" and if i have for example "Cells(2, 4)" is there a way to have it assign those values to variables so that for example x = 2 and y = 4? Thank you so much for the find code...it's a great help. Jim Thomlinson wrote: This code is using the find function (same find as you use in an Excel worksheet). The issue with using find in code is that it crashes if it does not find something... For that reason I have a range object (rngfound) that I set to the cell that is found (or not found). If the cell is not found then the range object references nothing. So my if statement say if rngfound is nothing then nothing was found. Else it selects the cell that was found... Give this code a try. It looks at the entire active sheet. Sub findStuff() Dim rngFound As Range Set rngFound = Cells.Find(What:="Find This", _ LookIn:=xlFormulas, _ LookAt:=xlPart, _ MatchCase:=False) If rngFound Is Nothing Then MsgBox "Sorry... Not found" Else rngFound.Select End If End Sub -- HTH... Jim Thomlinson " wrote: I'm fairly new with this.... Could you explain what the code is saying, and also, I want it to search the whole sheet and not just one column. Jim Thomlinson wrote: You can use find something like this dim rngFound as range set rngfound = sheets("sheet1").Columns("A").Find(What:="Find This", _ LookIn:=xlFormulas, _ LookAt:=xlPart, _ MatchCase:=False) if rngfound is nothing then msgbox "Sorry... Not found" else rngfound.select end if -- HTH... Jim Thomlinson " wrote: Ok... I'm writing a macro in which i know that none of the values created in a worksheet will be the same (except for blank cells). What code/command can I use to search for a specific value in the worksheet and then select the cell that contains that value? Thanks in advance. |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Select cell by value
Great! This was a big help!
Jim Thomlinson wrote: rngFound is the found Cell so there are any number of things you can do... Give this a quick try to see what I mean... Sub findStuff() Dim rngFound As Range dim x as long dim y as integer Set rngFound = Cells.Find(What:="Find This", _ LookIn:=xlFormulas, _ LookAt:=xlPart, _ MatchCase:=False) If rngFound Is Nothing Then MsgBox "Sorry... Not found" Else rngFound.Select msgbox rngFound.address x= rngfound.row y = rngfound.column msgbox x & vbcrlf & y msgbox "Value of next cell over is " & rngFound.offset(0, 1).value set rngfound = rngfound.offset(1,0) rngfound.select End If End Sub -- HTH... Jim Thomlinson " wrote: Great...thanks, one more thing, once I have a cell selected is there a way to find out what cell is selected, as in "Sheet1.Cells(x, y)" and if i have for example "Cells(2, 4)" is there a way to have it assign those values to variables so that for example x = 2 and y = 4? Thank you so much for the find code...it's a great help. Jim Thomlinson wrote: This code is using the find function (same find as you use in an Excel worksheet). The issue with using find in code is that it crashes if it does not find something... For that reason I have a range object (rngfound) that I set to the cell that is found (or not found). If the cell is not found then the range object references nothing. So my if statement say if rngfound is nothing then nothing was found. Else it selects the cell that was found... Give this code a try. It looks at the entire active sheet. Sub findStuff() Dim rngFound As Range Set rngFound = Cells.Find(What:="Find This", _ LookIn:=xlFormulas, _ LookAt:=xlPart, _ MatchCase:=False) If rngFound Is Nothing Then MsgBox "Sorry... Not found" Else rngFound.Select End If End Sub -- HTH... Jim Thomlinson " wrote: I'm fairly new with this.... Could you explain what the code is saying, and also, I want it to search the whole sheet and not just one column. Jim Thomlinson wrote: You can use find something like this dim rngFound as range set rngfound = sheets("sheet1").Columns("A").Find(What:="Find This", _ LookIn:=xlFormulas, _ LookAt:=xlPart, _ MatchCase:=False) if rngfound is nothing then msgbox "Sorry... Not found" else rngfound.select end if -- HTH... Jim Thomlinson " wrote: Ok... I'm writing a macro in which i know that none of the values created in a worksheet will be the same (except for blank cells). What code/command can I use to search for a specific value in the worksheet and then select the cell that contains that value? Thanks in advance. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| VBA: Column Select then Data Select then return to cell A1 | Excel Discussion (Misc queries) | |||
| Using formulas to select cells (Ex: Select every nth cell in a col | Excel Discussion (Misc queries) | |||
| How to point to (select) a cell to the left from a cell where I enter the = equal sign? | Excel Discussion (Misc queries) | |||
| Select cell, Copy it, Paste it, Return to Previous cell | Excel Discussion (Misc queries) | |||
| Select First Active Cell if the cell is in a pivot table | Excel Programming | |||
Linear Mode
