check lettered ticket number

[karaisland]

my sheet1 had ticket book log numbers like

salesmanname,beginno,endno
janet 1 100
weber 101 200

I had no difficulty finding the salesman name when I enter number 78
ticket by VBA.
I mean till they numbered ticket books differently this year like

janet 1 100
janet 1b 100b
weber 101 200
weber 101b 200b

Now I can't reach janet when I enter ticket 78a. Any help ?

Thank in advance.

 

[Mike H]

Can we see how your doing it now in code and perhaps someone will be able to
modify it

Mike

 

[karaisland]

Can we see how your doing it now in code and perhaps someone will be able to
modify it
Mike

Just looping through ticket log sheet. Concerning part is like below:

With Worksheets("TicketLog")
Set rng1 = .Range("B2", .Range("B" & Rows.Count).End(xlUp))
For Each i1 In rng1
If ticknum.Value >= i1.Value And _
ticknum.Value <= i1.Offset(, 1).Value Then
ticknum.Offset(, 9).Value = i1.Offset(, -1).Value
End If
Next i1
End With

My problem here since ticket numbers are with a letter now
and I can't make comparison:
I can search for instance ticket 141 in 100-200 logged book
but I can't search ticket 141b in 100b-200b book.

Thank you.

 

[Mike H]

Hi,

I wasn't sure where you were getting you values from but you should be able
to adaopt this to search for tickts ending in a letter and use your existin
routine fro those that don't

Sub tickets()
ticknum = "105b"
ticknumval = Val(Left(ticknum, Len(ticknum) - 1))
ticknumletter = Right(ticknum, 1)
If IsNumeric(ticknumletter) Then
MsgBox ("Ticket doesn't end in a letter")
Exit Sub
End If
With Worksheets("TicketLog")
Set rng1 = .Range("B2", .Range("B" & Rows.Count).End(xlUp))
For Each i1 In rng1
If ticknumval >= Val(Left(i1, Len(i1) - 1)) And ticknumletter =
Right(i1, 1) And _
ticknumval <= Val(Left(i1.Offset(, 1), Len(i1.Offset(, 1)) - 1))
And ticknumletter = Right(i1, 1) Then
i1.Offset(, 9).Value = i1.Offset(, -1).Value
End If
Next i1
End With
End Sub

Mike

 

[karaisland]

Thank you very much Mike. I got the idea now.
Already started adopting your code to mine.
I think I can manage the rest.Funny bit of my problem was
ticket books are logged bot numerical and lettered so I will have to
check both ways.

Thank you again.