Help with renaming a value in a cell with a letter

[UMAKEMESIK]

Hello to all,

We have a WB1 that upon opening evaluates cell d37
if D37 is blank then VB will go out to the network, search
another wb2, and retrieve a number and apply that number
to D37 of the current WB1.

Next, if d37 of wb1 is populated we use this:

If Range("D37").Value <> "" Then Exit Sub

Then we submit wb1 to send out via email, save to the network and log onto a master log, then close everything.

---------------------------------


I have this idea but have not idea on how to implement.

When we open a legacy document D37 wil be populated so the initial VB code will end
and D37 will stay as is.

but now when click on the Submit button I would like VB to re-evaluate D37 again
and if the cell is populated, which it will be, append the number in the Cell with a Letter

i.e. - 2534 will become 2534A
Where 2534 was the original number in D237

and again, if we use the from again
evaluate D37 upon submittal and if
the number is

2534A

Change it to 2534B.


if this or any form of it is possible with the help of VB I would appreciate the the assistance.

thanks

 

[GR00007]

Do you think you'll need more than 26 iterations? This will only work for A thru Z unless you don't mind '[\]^_abc.....':

Sub EditD37()
    vD37 = ActiveSheet.Range("D37").Value
    If vD37 = "" Then Exit Sub
    If IsNumeric(vD37) Then
        vD37 = vD37 & "A"
    Else
        vOrig = Left(vD37, Len(vD37) - 1)
        vSfx = Right(vD37, 1)
        vSfx = Chr(Asc(vSfx) + 1)
        vD37 = vOrig & vSfx
    End If
    ActiveSheet.Range("D37").Value = vD37
End Sub

 

[Snakehips]

Not sure where you would wish to incorporate this but maybe like...

Sub IncD37()Dim RgD37 As Range
Dim Num As String
Set RgD37 = Range("D37")
If RgD37.Value = "" Then Exit Sub 'Not a legacy workbook
If IsNumeric(RgD37) Then  'Currently no suffix
RgD37.Value = RgD37.Value & "A"  'Suffix A
Else
Num = Left(RgD37, Len(RgD37) - 1)
RgD37.Value = Num & Chr(Asc(Right(RgD37, 1)) + 1) 'increment suffix
End If
End Sub

Hope that helps.

 

[UMAKEMESIK]

thanks for taking the time, worked exactly how I envisioned it to work.

really appreciate it.

have a good one

 

[UMAKEMESIK]

thanks for all the help.

 

[UMAKEMESIK]

Not sure where you would wish to incorporate this but maybe like...

Sub IncD37()Dim RgD37 As Range
Dim Num As String
Set RgD37 = Range("D37")
If RgD37.Value = "" Then Exit Sub 'Not a legacy workbook
If IsNumeric(RgD37) Then  'Currently no suffix
RgD37.Value = RgD37.Value & "A"  'Suffix A
Else
Num = Left(RgD37, Len(RgD37) - 1)
RgD37.Value = Num & Chr(Asc(Right(RgD37, 1)) + 1) 'increment suffix
End If
End Sub

Hope that helps.

hello,

when running this I got an error. the first time the code ran, it ran perfectly.

But then we will open up an existing form and submit again.
so we open #184a
in the hopes that when we submit again

it will change the new one to 184 b but I get an error here

RgD37.Value = RgD37.Value & "A"  'Suffix A

any help would be much appreciated.

 

[UMAKEMESIK]

Do you think you'll need more than 26 iterations? This will only work for A thru Z unless you don't mind '[\]^_abc.....':

Sub EditD37()
    vD37 = ActiveSheet.Range("D37").Value
    If vD37 = "" Then Exit Sub
    If IsNumeric(vD37) Then
        vD37 = vD37 & "A"
    Else
        vOrig = Left(vD37, Len(vD37) - 1)
        vSfx = Right(vD37, 1)
        vSfx = Chr(Asc(vSfx) + 1)
        vD37 = vOrig & vSfx
    End If
    ActiveSheet.Range("D37").Value = vD37
End Sub

Hello,

this one works but.....
we don't get any errors and it does change the next form to the next letter but.

Say we create a new form - the code appends the letter A to the value in D37
then saves everything to our log and archives a copy on the network.

so now the next user goes to the log.
they click on the 187A to open up the completed sheet.
187A is displayed on the sheet ( 187 is the value in cell d37)
so now we run the code again.

the form saves to the network
creates a new line item on our log
and is appended with the letter b.

All good, but if I open up the original 187A - the number on the original sheet
is also changed to 187b.

is there a way to somehow lock the letter
and when the form saves to a new sheet then the next letter is created
thus leaving the original form as is.

I know this is convoluted, sorry.

 

[GR00007]

Could it be a timing issue? That sub only runs when invoked - should the original be closed right after a copy is made?