Using data collected in a module/ macro

[tech_boy]

Hi All,

I have recently inherited a spreadsheet from someone leaving the office and I need to make some modifications to it. The problem is that I can't fully understand what he's done!

Here is the code:

[VBA]
Public Sub LookUp(s)

Dim vOurResult
Dim lookFor
lookFor = s

With Sheets("DATA").Range("comment")
vOurResult = .Find(What:=lookFor, After:=.Cells(1, 1), LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False).Offset(0, 9)
End With

If vOurResult = 0 Then
Exit Sub
' Ends program if no comment present for this customer
Else
MsgBox vOurResult
' Displays messagebox with corresponding comment

End If

End Sub
[/VBA]

From what I can see, this macro looks through a sheet within the workbook called "Data" and looks to see if there is anything written within the "comment" field; if there is something there then it is displayed, if there is nothing there then the macro finishes.

Now; the challenge!

If the returned message is "Do not route on cobo trailer" i need it to check against a different field (perhaps using the .offset(x , x) command?) and if the fields match up, i.e. if the message is against a cobo trailer for example, i need the field to be wiped.

I am aware that i might not have explained this particularly well... but any advice at all would be greatly appreciated.

thanks in advance,


- Jon

[Bob Phillips]

Comment is not a field, it is a range of cells.

Where does "Do not route on cobo trailer" come into things, and what is meant by ...if the fields match up, i.e. if the message is against a cobo trailer for example?

[tech_boy]

The "Do not route on cobo trailer" is a comment within the "Data" sheet - sorry, should have explained that.

What I mean is that if the "cobo trailer" comment is displayed then I need it to look at a different field to see if that field contains specific information (i.e. any of the following trailer numbers: 43401F,43402F, 43403, 43404, 43405, 43406, 43407E, 43408E, 43409E, 43410E), if it does I need the field that the user is currently in to be automatically cleared of any data.

Thanks for your extremely quick reply!!

[Bob Phillips]

I think it would help to see your data. Can you post the workbook?