Using VB to clear Cells in MS excel

[tech_boy]

Hello all,

This is a relatively complex problem to explain, but i'll give it my best shot!

OK... I have the following 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

This basically runs a macro that looks through a hidden datasheet within an excel document which looks to see if any given customer has anything written in their "Comment" column; it it does, then it is displayed.

My spreadsheet has a "Trailer Number" column and if someone enters a customer number adjacent to a particular set of trailer numbers, I need an error message to be displayed telling the user that they cannot use this kind of trailer and when they click "OK" the Customer ID field needs to be automatically wiped.

I'm sorry if this explaination is terrible, please ask me for any extra information you may need!

Thanks in advance,

- Jon

Edited July 22, 2011 by tech_boy