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.

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.

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.

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.

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.

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.

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.

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.