Find function in a macro - Excel VBA

[serhat]

I want to set up a macro that searches a column for a value 'X' and then
selects the cell right under the cell that contains 'X'.

I have been stumped on this for a few days now and would really really
appreciate any sort of help or guidance.


Thanks a lot!

SK

 

[kkknie]

Sub Findit()

Dim r as Range

For Each r in Range("A1:A1000") 'Or whatever yours is
If r.Value = "X" Then
Range("A" & r.Row+1).Select 'Change A to your column
Exit For
End If
Next

End Sub

K

P.S. You could probably use an array formula using match and offset
but I am no specialist (we fear what we do not understand)

 

[Frank Kabel]

Hi
try something like
sub foo()
dim row_index
row_index = application.match("X",Range("A:A"),0)
cells(row_index+1,"A").select
end sub

 

[Bill]

Try this. It finds the value A in column A, then selects the cell right
under it.

Dim Findnot As Object

Sub FindValue()
Columns("A:A").Select
Set Findnot = Selection.Find(What:="A", After:=ActiveCell,
LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:= _
False)
If Findnot Is Nothing Then
MsgBox "Can't find the value."
End
End If
Findnot.Offset(1, 0).Select

End Sub

Best Regards,

Bill McNeese
www.spcforexcel.com

 

[jeff]

Hi,'

the following assumes you want to search column A
(and it begins the search after "A3" - delete
"After:=Range("A3")," if you want to start at
upper left (A1))

Sub GETIT()
Columns("A:A").Find(What:="X", After:=Range("A3"),
LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByColumns,
SearchDirection:=xlNext, _
MatchCase:=False).Activate
Selection.Offset(1, 0).Select
End Sub

 

[Jason Morin]

Try:

Sub FindIt()
Dim lrow As Long
Dim rng As Range
Dim sfind As String

Set rng = Range("A:A")
sfind = "x"

With Application.WorksheetFunction
If .CountIf(rng, sfind) Then
lrow = .Match(sfind, rng, 0)
lrow = lrow + 1
Cells(lrow, rng.Column).Select
Else
MsgBox "Sorry, no """ & sfind & """ found."
End If
End With

End Sub

 

[serhat]

Thanks a lot for all of your help everyone. The code works like a charm
One small follow up question on the following code posted at the end b
KKKnie.

If I want to select from the cell under the value I found through cel
P1000 how would I code it. I tried using the following code but m
syntax was apparently wrong.

Range("A" & r.Row+11000).Select

Thanks for your help again!!

SK

KKNIES Code

Sub Findit()

Dim r as Range

For Each r in Range("A1:A1000") 'Or whatever yours is
If r.Value = "X" Then
Range("A" & r.Row+1).Select 'Change A to your column
Exit For
End If
Next

End Su

 

[Dave Peterson]

One way:

Option Explicit
Sub Findit()

Dim r As Range

For Each r In Range("A1:A1000") 'Or whatever yours is
If r.Value = "X" Then
Range("P1000", Range("A" & r.Row + 1)).Select 'Change A to your column
Exit For
End If
Next

End Sub

Although KKKnie's code works fine, you may want to take a look at one of the
alternatives. Find or Match is usually a whole lot faster than looping.

 

[Dave Peterson]

I think I'd use "Exit Sub" instead of plain old "End".

End does a lot of things that could mess up other stuff (resetting public
variables).