initials + increment

[puba1967]

Hi,
Question from new macro writer but a non-programmer.

How can you increment in column A a reference number in the following
format?

ABC-0001
ABC-0002
ABC-0003
[...]
ABC-1000

Thanks

 

[Norman Jones]

Hi Puba,

Try:

'=============>>
Public Sub Tester()
Dim SH As Worksheet
Dim rng As Range

Set SH = ThisWorkbook.Sheets("Sheet1") '<<==== CHANGE
Set rng = SH.Range("A1:A100") '<<==== CHANGE

rng(1).Value = "ABC-0001"
rng(2).Value = "ABC-0002"
rng.Resize(2).AutoFill Destination:=Range("A1:A100"), _
Type:=xlFillDefault

End Sub
'<<=============

 

[Shaka215]

The best way of doing this is using the "&" symbol when you refrence
two cells...Please try to follow the instructions below and see if this
works, as it has for me in the past.

Column A (Row 1): =B1&C1&D1
Column B (Row 1): ABC
Column C (Row 1): -000
Column D (Row 1): 1
-------------------------------------------------
Column A (Row 2): =$B$1&$C$1&D1
Column B (Row 2): <----------------- EMPTY CELL
Column C (Row 2): <----------------- EMPTY CELL
Column D (Row 2): =SUM(D1+1)
--------------------------------------------------

You will realize how simple the process is and how functional the "&"
is, after you get the hang of how the formula works. If you need to
hide Column B, C, or D then do so however, you might find that you will
need to probally fill down to the bottom of the sheet with the formula
in Column A and D if you are trying to create some sorta of database.
Hope this helps...probally amature to some but simple to follow.

-Shaka215


====================

 

[Norman Jones]

Hi Puba,

Change:

rng.Resize(2).AutoFill Destination:=Range("A1:A100"), _
Type:=xlFillDefault

to

rng.Resize(2).AutoFill Destination:=rng, _
Type:=xlFillDefault

 

[Tim Marsh]

i think this is what you mean: -

'=== code starts
Sub test()
Dim x
For x = 1 To 1000
ActiveCell.Value = "ABC-" & Format(x, "0000")
ActiveCell.Offset(1, 0).Select
Next x
End Sub
'=== code ends

hth,

tim

 

[puba1967]

Thank you all three of you.
I'll try that now and will post back next week some feedback.

 

[Shaka215]

I tend to use methods that don't require so much VBA coding but more
analitical thinking...You might find my method is more gear towards
Excel versus VBA macro script.