Excel Macro Help?

robert_harper_2000

Hello,

I'm struggling with creating this Macro. We did some of this stuff at School but it seems to have forgotten me

I have been typing up my price lists and would like a button to the side where the member of staff would have one box where they could type in what they know about the product i.e. Code and/or partial description. Ideally the item would be found and perhaps lightly highlighted.

Any chances anyone has one of these Macros around??

Cheers for any help,
Robert:beer:

PaulK_3

Something like:
this?

robert_harper_2000

one hiccup is that it won't find anything that is linked to another page.

Example
Tab 1 - holds formulaes linking it to tab 3
Tab 3 - 11331

If I search for 11331 on Tab 1 it won't find it as it isn't on there only a formula (=Tab3 Cell 2)

robert_harper_2000

I need the spreadsheet to search for what the formula values equal not what literally they are ie the formula

FTD

robert_harper_2000 wrote: »

one hiccup is that it won't find anything that is linked to another page.

Example
Tab 1 - holds formulaes linking it to tab 3
Tab 3 - 11331

If I search for 11331 on Tab 1 it won't find it as it isn't on there only a formula (=Tab3 Cell 2)

Stick this line in the code before the cell.search
Sheets("Tab3").Activate
and activate the cell you want to begin searching from

Cells(1, 1).Activate

At the end of the macro you can return to tab1 the same way. (obviously replace Tab3 with the name of your worksheet.)

FTD

robert_harper_2000

Private Sub FindButton_Click()
Dim strFindWhat As String
strFindWhat = TextBox1.Text
On Error GoTo ErrorMessage

cell.Search
Sheets("Prices").Activate
Cells(1, 1).Activate

Cells.Find(What:=strFindWhat, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False).Select
Exit Sub
ErrorMessage:
MsgBox ("The information you have searched does not exist")
End Sub

robert_harper_2000

Doesn't seem to know the information is displayed on Trade but I want it to show the info on Prices

f1charlie

Perhaps you need "LookIn:=xlValues" rather than xlFormulas?

robert_harper_2000

This is a copy of what I have ended up with now... but again it doesn't see what I see?? I appreciate your help everyone!!

Any other ideaS?? Cheers!!!

Private Sub FindButton_Click()
Dim strFindWhat As String
strFindWhat = TextBox1.Text
On Error GoTo ErrorMessage

cell.Search
Sheets("Trade").Activate
Cells(1, 1).Activate

Cells.Find(What:=strFindWhat, After:=ActiveCell, LookIn:=xlValues, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False).Select
Exit Sub
ErrorMessage:
MsgBox ("The information you have searched does not exist")
End Sub

f1charlie

If you want to search on the prices sheet you need a "Sheets("Prices").Activate"

robert_harper_2000

Hmm... still no luck!
Sure we are getting closer-