General discussion

Locked

Excel 97 VBA

By spp ·
I am trying to create a macro in Excel 97 that will search all my worksheets that saved inside my one workspace. I want to be able to fidn a specific job and get the corresponding number to right of it. If anyone could help me start a macro in VBA, I would greatly appreciate it.

This conversation is currently closed to new comments.

8 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

Excel 97 VBA

by Glen_McLeod In reply to Excel 97 VBA

Dim wkSheet as Worksheet

For Each wkSheet In Workspace.Worksheets
'Search for your job on each sheet
'If Found then
'Get the value of the cell offset by 1
'Retrieve the number into a local variable
Exit for
'End if
Next

This should get you started, it'll loop through all of the sheets in the workspace, I'll leave it to you to search for your specific cell value. The "Exit For" will exit the loop immidiately after finding your search target, so you don't wast time searching after you've already found it.

(This reply box usually doesn't preserve the formating, so there's 1 indent for the lines starting with "Search", "If" and "End If" and 2 indents for the 2 lines between the "If" and "End If")

Glen

Collapse -

Excel 97 VBA

by Glen_McLeod In reply to Excel 97 VBA

Oops, "2 indents for the *3* lines between the "If" and "End If".

Glen

Collapse -

Excel 97 VBA

by spp In reply to Excel 97 VBA

Poster rated this answer

Collapse -

Excel 97 VBA

by spp In reply to Excel 97 VBA

hi Glenn, could you be a little bit moer specfic on the actual code in the macro. I am fairly new to VB so any more help would be appreciated. Thanks

Collapse -

Excel 97 VBA

by spp In reply to Excel 97 VBA

Poster rated this answer

Collapse -

Excel 97 VBA

by Glen_McLeod In reply to Excel 97 VBA

The search function looks like this:

Cells.Find(What:="February 4, 2001", _
After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=False)

Of course, your "What:=" will differ, and you can even pass a parameter in to replace it.

To get the value of an offestting cell, reference it like this:

ActiveCell.Offset(Rows, Columns).Value

Where 'Rows is the number of offset rows you want to reference and 'Columns' is the number of offset columns you want to reference, both can be positive or negative.

So your whole function would look like this:

Public Function WorkSheetsFind(ByVal sWhat As String) As String
Dim wskSheet As Worksheet
Dim rngTarget As Range
Dim sRetVal As String

For Each wskSheet In Workbooks(1).Worksheets
Set rngTarget = Cells.Find(What:=sWhat, After:=ActiveCell, LookIn:=xlValues _
, LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
MatchCase:=False)
If Not rngTarget Is Nothing Then 'Found it
rngTarget.Activate
sRetVal = ActiveCell.Offset(0, 1).Value
Exit For
End If
Next
WorkSheetsFind = sRetVal
Set rngTarget = Nothing
End Function

I formatted it correctly (indents) in this reply box, but it'll be lost, We'll have to talk to TR about this...

Glen

Collapse -

Excel 97 VBA

by spp In reply to Excel 97 VBA

Poster rated this answer

Collapse -

Excel 97 VBA

by spp In reply to Excel 97 VBA

This question was closed by the author

Back to Software Forum
8 total posts (Page 1 of 1)  

Related Discussions

Related Forums