PDA

View Full Version : Solved: Search and Find



ddh
05-15-2006, 05:48 PM
I thought this would work but something is wrong. I type in "Cat" (C1) or any other name Cow, Dog, Rat and click the macro button and the curser would goto A4 for "Cat".

Thank you.

-----A-----B-----C------D1----------
--Cat2---Dog3---Cow4---Cat5---Rat




Sub SearchandFind()

Range("C1").Select
Columns("A:A").Select
Application.ActiveCell.Select

End Sub

Jacob Hilderbrand
05-15-2006, 06:29 PM
Can you post just what you would like to have the macro do?

Thanks

ddh
05-15-2006, 07:16 PM
Thank you for your help.
I have a VLookup in cell C1 that brings up the word "Cat"
and I would like to click on a macro that would take me to that
cell that "cat" is in.
What ever word that is in C1 is the cell that I want to go to.

ddh

mdmackillop
05-16-2006, 12:10 AM
Assuming the lookup range is on the same worksheet.
Sub GoToFnd()
Cells.Find(what:=[C1], after:=[C1]).Activate
End Sub

ddh
05-16-2006, 03:19 AM
Thank you very much for your help.
Is it possible for it to have it always look in row A.
The word for it to find will always be in Cell C1.
And the data will always be in Row A
ddh



Sub GoToFnd()
Range("A:A").Select
Cells.Find(what:=[C1], after:=[C1]).Activate
End Sub

mdmackillop
05-16-2006, 12:31 PM
Hi DDH,
Please note that "A" is a column, not a row. If you don't get this straight, you'll soon run into problems with your coding.
Regards
MD

Sub GoToFnd()
Range("A:A").Find(what:=[C1], after:=[A1]).Activate


End Sub

ddh
05-16-2006, 01:06 PM
Thank you very much, I messed up.
I get nervous typing to explain what I was trying to do
and I typed in Row instead of column in my question.
I tried the code and it worked perfect.
I agree that mistaking row for column would be a very
large problem.
If you are interested I could explain to you what I am doing.
Thank you very much for your help.
ddh

ddh
05-16-2006, 01:31 PM
I have a new question.
What if I typed in on cell CI "Dog" and it went
to the first "Dog" on Column A, Cell 2.
And I had another "Dog" on Column A, Cell 10.
How would I get it to go to the next one?
Thank you for your help.

Sub GoToFnd()
Range("A:A").Find(what:=[C1], after:=[A1]).Activate


End Sub

Zack Barresse
05-16-2006, 02:27 PM
Do you want to get it to the last "Dog", or a specified number of "Dog"'s later?

ddh
05-16-2006, 05:13 PM
Thank you for your help.
I am trying to get it go to the word shown in cell C1 "Dog" (always in C1)
to the first "Dog" in Column A (always column A) and then ask me if
I want to go to the next "Dog" and if I click Yes then it will take me there
and so on and so until it says there are no more "Dog" words in Column A.
I tried to do it on my own with the code below but so far I can not get it to work.






Sub GoToFnd2()
Dim rCell As Range
Dim lReply As Long

Set rCell = Range("A:A").Find(Range("C1").Value, after:=Range("A1"), LookIn:=xlValues)
If Not rCell Is Nothing Then
firstaddress = rCell.Address
Do

Loop While Not rCell Is Nothing And rCell.Address <> firstaddress And rCell.Row > 8

Else
lReply = MsgBox("Data cannot be found. Try Again", vbYesNo)
If lReply = vbYes Then Run "Find_Data":
End If
On Error GoTo 0

End Sub

mdmackillop
05-17-2006, 12:21 AM
A good attempt along the right lines!

Option Explicit
Sub GoToFnd()
Dim c As Range, FirstFound As String
Set c = Range("A:A").Find(what:=[C1], after:=[A1])
FirstFound = c.Address
c.Activate
Do Until MsgBox("Look for next", vbYesNo) = vbNo
Set c = Range("A:A").Find(what:=[C1], after:=c)
If c.Address = FirstFound Then
MsgBox "No more " & [C1]
Exit Sub
End If
c.Activate
Loop
End Sub

ddh
05-17-2006, 03:24 AM
Thank you vey much for all of your help.
The code works perfect.
I try to understand how to write VBA code but I
have such a hard time with it.
Thank you for all of the help.
ddh