tech_boy
07-25-2011, 05:07 AM
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:
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
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
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:
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
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