Macro for search and go to specific cell

Microsoft Office 2007 professional (aca...
May 23, 2010 at 23:45:40
Specs: Excel 2007

Hi every one
i think it's simple but i failed to do it because i'm still a beginner in Macro

I have 5 sheet for projects with the project no. in cell A4,
I also have 1 sheet with a summary of the 5 projects labeled "summary"

i need command button with assigned macro that takes me from the project sheet to the cell that contains the project no. in the summary sheet

Can any one support?

See More: Macro for search and go to specific cell

May 24, 2010 at 05:35:24

Attach this macro to a custom button on a toolbar.
Clicking the button from any project worksheet should take you back to cell A4 on the "summary" worksheet.

Public Sub ProjBack()
End Sub


Report •

May 24, 2010 at 06:20:24
Hello Humer

I need to go to the cell in the summary sheet that contains the project no.
the Project no. is not in cell A4 in the summary sheet
It's in cell A4 of each of the 5 project sheets
i want to search for it using the value in A4 and go to it's cell in summary sheet

thanks for your help

Report •

May 24, 2010 at 10:07:35
How 'bout this?

Option Explicit
Sub ProjNumCell()
Dim projNum As Variant
 With Sheets("Summary").Cells
  Set projNum = .Find(ActiveSheet.Range("A4"), lookat:=xlWhole)
   If Not projNum Is Nothing Then
    Else: MsgBox "Project Not Found On Summary Sheet"
   End If
 End With
End Sub

You didn't give a range on the Summary sheet to look for the Project Number, so I used With Sheets("Summary").Cells which will search the entire sheet. You can narrow the search range to something like:

With Sheets("Summary").Range("A1:A100")

Report •

Related Solutions

May 25, 2010 at 01:06:48
Thanks DerbyDad03

it works effectivly

Report •

June 22, 2010 at 01:58:08
Dear Derbydad03

I'm wondering why each time i run the above code msgbox " Project not found on summary sheet" appears

this is because the find options are changed

each time i open the workbook i must first change the find option as the below so macro can work effectively

1- clear selection from the box "Match entire cell content"
2- choose "loon in" to be values

What i must do with this?

Report •

June 22, 2010 at 04:38:38
The options within the Find dialog box in Excel impact the way .Find works within VBA and vice-versus.

To be completely safe, you should set all off the options within VBA so that the code does exactly what you want it to do. This may change what shows in the Find dialog box the next time you use it within Excel, but the code should have priority since you don't want it to fail. You can actually reset the dialog box values by adding some code to "Find" something else with different options set and just don't do anything with whatever it Finds.

Open the VBA editor and place your cursor within the word .Find.

Press F1 to open the VBA Help file.

If you have to, expand the section entitled:

Find method as it applies to the Range object.

This will show you all of the settings you can use with the .Find method. Set them as required to have it work properly for you.

Let me know if you have any other questions.

Report •

June 22, 2010 at 07:42:03

i was recording another code and i noticed an intruction and iI tried to add it (LookIn:=xlValues, _) and it works

so this part of the code is now like this

Set projNum = .Find(ActiveSheet.Range("B4"), LookIn:=xlValues, _

thanks derbydad03


Report •

Ask Question