Excel macro question

[lareya]

Okay, I am using MS Excel 2002 w/Win2k.
I have a spreadsheet that looks like this

COUNT   OLD     NEW 	OLD PROCEDURES DESCRIPTION

1	20110	BC5 	ALVEOLECTOMY	
2	20100	BC5 	ALVEOLOPLASTY	
3	020120	BC5	BIOPSY MANDIBLE	
4	020420	BC5	DENTAL BRIDGE(S) INSTALLATION	
5	020400	BC5	DENTAL CROWN APPLICATION	
6	043400	BC5	DENTAL RESTORATION	
7	042100	BC5	DENTAL WIRING (TEETH)	
8	020200	CX	OTHER ORAL SURGERY MAJOR	
9	020210	CX	OTHER ORAL SURGERY MINOR	
10	041900	BC5	TOOTH EXTRACTION

now what I need to do is to take the column New and add a Z to the end of it, and then copy it to the begining of the OLD. It would look like this.

COUNT	OLD	        NEW 	OLD PROCEDURES DESCRIPTION
1	BC5Z20110	BC5Z	ALVEOLECTOMY
2	BC5Z20100	BC5Z	ALVEOLOPLASTY

SO, I recorded a macro to do this, but then I realized I need it to go on until the last cell is completed (or until the cell is empty which is the same thing). I have about 5 different sheets that need updated like that, and they number in the 100's. So how do I make this macro run until the end? I don't want to be pushing the ctrl + P for every cell.

I know this is probably rediculously easy, but I can't seem to find the right formula. Here are my tries, with horrible errors:
Sub procedures_update()
'
' procedures_update Macro
' Macro recorded 12/07/2004 by PierceM
'
' Keyboard Shortcut: Ctrl+p
'
' Sub Count()
' mycount = Range("C2") + 1
' Range("C2") = mycount
End Sub
Sub ContentChk()
If Application.IsText(ActiveCell) = True Then
procedure_update else
If ActiveCell = "" Then
goto End Sub

procedure_update
Range("C2").Select
ActiveCell.FormulaR1C1 = "BC5Z"
Range("B2").Select
ActiveCell.FormulaR1C1 = "BC5Z20110"
Range("C3").Select
ActiveCell.FormulaR1C1 = "BC5Z"
Range("B3").Select
ActiveCell.FormulaR1C1 = "BC5Z20100"
Range("C4").Select

End Sub
The part of the procedure_update is where I recorded, and the other stuff I tried to copy and paste from other formulas I found. Oh yeah, the numbers are just counting that i used the series to auto fill, the other columns are just text formatted. I don't need to add or do anything else with the data.

Any help will be appreciated!

Lareya


++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Crystal 8.5; ORSOS/One Call Hospital Scheduling System v9.3; SQL database; Huge Newbie to Crystal! Operating Room RN Analyst

 

[ETID]

'Assuming new is in col "C" and old is in col "B" ...

Sub old_to_new()
Dim r As Integer
r = 2

Do Until Cells(r, 3) = ""
new_ = Cells(r, 3) + "Z"
Cells(r, 3) = new_
old_ = CStr(Cells(r, 2))
Cells(r, 2) = new_ + old_
r = r + 1
Loop

End Sub

 

[ETID]

...This to leave "new" as is but update "old"

Sub old_to_new()
Dim r As Integer
r = 2

Do Until Cells(r, 3) = ""
new_ = Cells(r, 3) + "Z"
old_ = CStr(Cells(r, 2))
Cells(r, 2) = new_ + old_
r = r + 1
Loop

End Sub

 

[psarros]

Why don't you use the concatenate function?

 

[KenWright]

Assuming OLD is in Col B and NEW is in Col C starting in row 2, in two spare helper columns, (I'll assume J and K)

In K2 put =C2&"Z" and copy down
In J2 put =K2&B2 and copy down.

Copy Cols J&K together and then paste special as values into B & C

Regards
Ken.............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

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

 

[ETID]

The formula approach is great and probably the most efficient for a one and done scenerio,...but if you need to automate this then use the vba code.