Message box if number sequence matches

[outrider]

I have a small project which is currently driving me mad:

If the left four numbers in a cell on sheet 1 match the left four numbers in any cell in the range C5 to C250 on sheet 2, then display a message box requiring a yes/no answer.

The numbers in the cells are in blocks, usually separated by either "/" or "-" or a space but always start with numbers from 4 to 9 digits long before the separator.

As always, all help appreciated.

[andy_uk]

Message box stuff tends to do my head in. I usually lift the basics straight from Help, then embroider to suit. So the following's a bit of a shambles, but you could probably do worse.

HTH,
Andy


Sub Check_left_4()
For Each Cell In Range("Sheet2!C5:C250")
If Left(Cell, 4) = Left(Range("Sheet1!A1"), 4) Then
Msg = "Match found in Sheet2 C" & Cell.Row & " ; accept?"
Style = vbYesNo + vbCritical
Title = "Check left 4"
Response = MsgBox(Msg, Style, Title)
If Response = vbYes Then
MsgBox "Match accepted.", , "Check left 4"
Else
MsgBox "Match not accepted.", , "Check left 4"
End If
End If
Next Cell
End Sub

[Zack Barresse]

Hi,

Andy: check it out

Outrider: Do you want a message box for every iteration, like Andy's?? That would seem like it would get very annoying, imho. If you just want one message box if any match exists, I'd try something like this ...


Option Explicit

Sub Check4Left()
Dim rngCheck As Range, rngLook As Range, cel As Range
Dim valCheck As String, isFound As Boolean
Set rngLook = Sheets("Sheet2").Range("C5:C250")
Set rngCheck = Sheets("Sheet1").Range("B2")
valCheck = Left(rngCheck, 4)
For Each cel In rngLook.SpecialCells(xlCellTypeConstants, 23)
If Left(cel.Value, 4) = valCheck Then
isFound = True
Exit For
End If
Next cel
If isFound Then MsgBox "Match acquired.", vbInformation, "Match!"
End Sub

HTH

[andy_uk]

Hi Zack. :hi:

(i) I don't know what a VBIDE is,

(ii) VB tags worsen readability for me, since I only have a small screen.

Thanks for providing some real code tho'. :thumb

Rgds,
Andy

[Zack Barresse]

Andy: I'm sorry to hear that (about your screen). :( That stinks!

As for the VBIDE, check out this recent thread (http://www.vbaexpress.com/forum/showthread.php?t=2445). Basically the VBA Tags were created by Mark007 for readability. It adds color and formatting to our code.

(And thanks for the compliment. :o: )

[outrider]

The message box is simply a "This number already in use, proceed, cancel" sort of thing.

Thanks for the help folks.

really appreciated.