Marco help
Posted by Paul on January 15, 2002 2:32 PM
I need a Marco to bring up the find window and when the user puts in a number have the macro find that cell, it will be in column A, then select the cell five columns over, column F, in the same row and put the current date in it and then open the find window again and repeat for the next number put in and repeat till the close button on the find window is clicked. I tried to use the Marco recorder but it want bring up the window. Thanks for your help
Posted by Jacob on January 15, 2002 2:38 PM
Hi
Sub MyMacro()
SearchValue
Do
searchvalue = inputbox("What number do you want to search for? Enter Stop to quit")
set c = .find(What:=searchvalue,lookat:=xlwhole,lookin:=xlvalues)
if c is nothing then
else
c.address.offset(0,5).formulaR1C1=date
end if
Loop while searchvalue<>"Stop"
End Sub
HTH
Jacob
Posted by Paul on January 15, 2002 3:02 PM
when I try to run it I get a compile error, invalid or unqualified reference for ".find" after set c, I am using Excel '97 if that helps
Posted by Jacob on January 15, 2002 6:34 PM
Hi
I forgot the With statement. Duh.
This one works I just tried it.
Sub MyMacro()
Do
searchvalue = InputBox("What number do you want to search for? Enter Stop to quit")
With Range("A1:A65536")
Set c = .Find(What:=searchvalue, lookat:=xlWhole, LookIn:=xlValues)
If c Is Nothing Then
Else
Range(c.Address).Offset(0, 5).FormulaR1C1 = Date
End If
End With
Loop While searchvalue <> "Stop"
End Sub
Jacob
Posted by Paul on January 15, 2002 7:26 PM
maybe I am doing something wrong, I put the code in a module but now when it runs I get compile error variable not defined and "SearchValue" is highlighted Hi I forgot the With statement. Duh.
Posted by Ivan F Moala on January 15, 2002 7:37 PM
You need to dim searchvalue eg
Dim searchvalue
Note: the code will stop @ the 1st occurance
of the searchvalue and will search no more.
If this is what you want the OK....otherwise
I'm sure Jacob will amend....if not repost
Ivan
Posted by Paul on January 15, 2002 7:55 PM
Thanks ,I also had to dim "c", whatever dim is
Dim searchvalue Note: the code will stop @ the 1st occurance
