Make achange to this please

[Steved]

Hello From Steved
Sub test()
Dim x As Long
For x = 1 To 9
Cells(x, 1).Value = x & "-" & Cells(x, 1).Value
Next x
End Sub

Now that the above is working I would like to make a
change,as I have over 5000 rows.
Using the above code would it be possible to customize it.
Col A , Col B, Col C the columns I use
Col A is only the column that requires the code to work in.

What I require it to do is as laid out below please.
Col A

City Change this to 1-City
Roskill Change this to 2-Rosk
Papakura Change this to 3-Papa
Wiri Change this to 4-Wiri
North Shore Change this to 5-Shor
Orewa Change this to 6-Orew
Swanson Change this to 7-Swan
Panmure Change this to 8-Panm
Waiheke Change this to 9-Waih

The issue is that any of the above could be anywhere in
Col A

 

[mudraker]

steved

I am not sure of exactly what you are after so i have included
examples.

If the changes are only for the 10 cityies you listed then look a
macro test1 and add in more Case statements


Sub test()
Dim x As Long
For x = 1 To Range("a" & Rows.Count).End(xlUp).Row Step 1
Cells(x, 1).Value = x & "-" & Left(Cells(x, 1).Value, 4)
Next x
End Sub



Sub test1()
Dim x As Long
For x = 1 To Range("a" & Rows.Count).End(xlUp).Row Step 1
Select Case Left(Cells(x, 1).Value, 4)
Case "City "
Cells(x, 1).Value = "1-City"
Case "Rosk"
Cells(x, 1).Value = "2-Rosk"
Case "Papa"
Cells(x, 1).Value = "3-Papa"
End Select
Next x
End Su

 

[Steved]

Hello Mudraker from Steved

Yes test1 is perfect as it is what is required.

Question is it possible for test1 to be used in VBA,
My reasoning behind this is that I import from Paradox
and if I forget as I also use this information to make
a Pivot Table, hence instead of Papakura 3-Papa, the
numerals allows me to be put in the order I require,
if not no worries as you have done a lot more than what I
would have achieved.

Once again thankyou very much.

 

[mudraker]

Steved

I have only used VBA from within Excel and Access And unless yo
question is refering to one of those 2 programs I can not help you bu
I am sure others here can offer the advice you nee

 

[Steved]

Yes Mudraker I am refering to test1 (the Second you wrote).

Thankyou for taking the time sofar.

Cheers.

 

[mudraker]

Steved

Test1 is Excel VBA code and you can use it anywhere from within Exce
VBA.

It can be included as part of other Excel VBA code

eg

sub Macro1()
call test1
end sub

It can also be triggered by attaching a button to activate it

It can also be modified to include the ability to open other files an
work on those files

All you need to do is pose your query to cover your specifi
requirements and someone will usually reply with a suitable answe

 

[Steved]

Thankyou.