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.

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

Mike

"(E-Mail Removed)" wrote:

> 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.
>

On Apr 24, 12:21*pm, Mike H <Mi...@discussions.microsoft.com> wrote:
> 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.

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

"(E-Mail Removed)" wrote:

> On Apr 24, 12:21 pm, Mike H <Mi...@discussions.microsoft.com> wrote:
> > 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.
>

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.