auto number

[smullens]

hi
we have a spreadsheet where we have a tracking number that we enter
then in the cell to the right we enter a product code (which varies)
the tracking number is now up to 3455 we have to enter this each time
eg 3455, 56, 57 etc
is it possible to enter the product code and auto increment the tracking number (continuing on from where we are already at)
thanks

 

[Smitty]

Welcome to the Board!

This might work for you:

<font face=Tahoma><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)
    <SPAN style="color:#00007F">Dim</SPAN> rng <SPAN style="color:#00007F">As</SPAN> Range
    <SPAN style="color:#007F00">'   Only look at single cell changes</SPAN>
    <SPAN style="color:#00007F">If</SPAN> Target.Count > 1 <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
    <SPAN style="color:#007F00">'   Assumes you select the Product Code in Cell A2</SPAN>
    <SPAN style="color:#00007F">Set</SPAN> rng = Range("A2")
    <SPAN style="color:#007F00">'   Only look at that range</SPAN>
    <SPAN style="color:#00007F">If</SPAN> Intersect(Target, rng) <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
    <SPAN style="color:#007F00">'   Increment Cell B2's current value by 1</SPAN>
    Range("B2").Value = Range("B2").Value + 1
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>

The code goes in the worksheet specific module. Change the ranges as necessary.

Hope that helps,

Smitty

 

[Juan Pablo González]

Altough in this case it doesn't create an endless loop, it's a good idea to disable events to prevent infinite recursion, i.e, change this:

Range("B2").Value = Range("B2").Value + 1

to

Application.EnableEvents = False
Range("B2").Value = Range("B2").Value + 1
Application.EnableEvents = True

 

[Smitty]

Good catch Juan! Thanks!

And congratulations on 9000! Quite impressive.

Smitty

 

[2rrs]

A formula approach might do:

=IF(H182<>"",ROW()+3500,"")

adjust "3500" until it matches your tracking numb requirements

 

[jcvoth]

Am i being overly simplistic by saying put

=A32+1 in cell A33? Then the formula could simply be extended as needed.

 

[smullens]

hi
thanks for the quick replys i don't think i've explainned this right
new to this!
i know this code does'nt work on the line in red
but maybe you can see what i'm trying to do
when i enter data in col3 it's puts a date stamp in col1 and increments
the number by 1 from the previous cell in col 2
can u help me with the code
thanks

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 3 Then
Intcolumn = 1
introw = Target.Row
Cells(introw, Intcolumn) = Now()

End If

If Target.Column = 1 Then
Intcolumn = 2
introw = Target.Row
Cells(introw, Intcolumn) = "=IF(H182<>"",ROW()+3455,"")"

End If

End Sub

 

[Scott Huish]

Did you want something like this?

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 3 Then
Cells(Target.Row, 1) = Now()
Cells(Target.Row, 2) = Target.Row + 3455
End If
End Sub

 

[smullens]

great
thanks everyone