Invoice Number Problem

[Sue]

Hi

I have a textbox on a Userform and using the code below
when I enter for example 100 and then in the Listbox select another row the
textbox updates to 101 with no problem - however if I enter L100 the code
errors
is there a way round this error as sometimes I have to use a letter as it
denotes the department that is issuing the Invoice.

Private Sub Lb1_Change()
Application.ScreenUpdating = False
Dim invnum As String
Dim iRow As Long
Dim ws As Worksheet
Sheets("Invoice").Select
Tb36A.Text = Range("C14").Text + 1'<<<< errors on this line
Application.ScreenUpdating = True
End Sub

 

[Mike H]

Sue,

Prefixing a number with a letter turns it into text so adding 1 to a text
string causes an error. Do it like this by just having the number in C14 and
add the L at runtime

Tb36A.Text = "L" & Range("C14").Text + 1

Mike

 

[Mike H]

I just noticed that you say you 'sometimes add a letter. Same principle but
add the letter from another range address. It wouldn't matter if that address
was empty

Tb36A.Text = Range("Z1").Text & Range("C14").Text + 1

Mike

 

[Sue]

Hi Mike

Thanks for quick reply however I forgot to mention I may have 4 or 5
invoices following one another with a single letter prefix for the same
department -- after entering letter L in ("Z1") first time works OK when
changing to next line in the ListBox ready to enter next invoice it gives a
runtime error'13' - Type Mismatch -- can you help.

 

[Rick Rothstein]

**IF** your values in C14 are **always** two digits long or more, and if
your letter prefix is **always** a single character, you can use this line
in place of the one that caused the error originally...

Tb36A.Text = Left(range("C14", 1) & (Mid(range("C14", 2) + 1)

 

[Sue]

Hi Rick

Thanks for your reply

Working late tonight most of the UK snowed up probably stay at a local hotel
so I can get to work tomorrow.

everything meets the If conditions that you asked about and I'm receiving an
error message highlighted in debug at Mid - compile error -- argument not
optional.

Tb36A.Text = Left(range("C14", 1) & (Mid(range("C14", 2) + 1)

 

[Rick Rothstein]

Sorry, I somehow lost two of the closing parentheses. Try this statement
instead...

Tb36A.Text = Left(Range("C14"), 1) & (Mid(Range("C14"), 2) + 1)

--
Rick (MVP - Excel)

Hi Rick

Thanks for your reply

Working late tonight most of the UK snowed up probably stay at a local
hotel
so I can get to work tomorrow.

everything meets the If conditions that you asked about and I'm receiving
an
error message highlighted in debug at Mid - compile error -- argument not
optional.

Tb36A.Text = Left(range("C14", 1) & (Mid(range("C14", 2) + 1)

 

[Sue]

Hi Rick

Works a treat thanks for your patience and help