Macro help again 1

[lareya]

Okay, I am using MS Excel 2002 w/Win2k.

this is what the original LookS like:

A	B                C        	D
CT	GEN		OLD PROCEDURES	NEW PROCEDURES
1	BB1EZ093300	BB1EZ		ABDOMINAL PERINEAL 
2	BB1HZ095700	BB1HZ		ADRENALECTOMY	
4	DB2EZ091420	DB2EZ		ANAL FISTULECTOMY
5	DB2EZ096897	DB2EZ		ANOPLASTY	

this is what it should Change to:
A	B		C		D
CT	GEN		OLD PROCEDURES	NEW PROCEDURES
1	GENBB1EZ093300	BB1EZ		ABDOMINAL PERINEAL 
2	GENBB1HZ095700	BB1HZ		ADRENALECTOMY	
4	GENDB2EZ091420	DB2EZ		ANAL FISTULECTOMY
5	GENDB2EZ096897	DB2EZ		ANOPLASTY

As you can see the only thing that changes is that the 3 letter word GEN is added to the begining of column B. The GEN word will be different for each spreadsheet, but it will always be a all caps 3 letter word and in that same place.

I had excellent help before in the first part (see this thread

http://www.tek-tips.com/viewthread.cfm?qid=966276&page=1

) but I couldn't seem to adapt it to make this work. Again, I have lots of these to change.

Thank you again for your help,

Lareya

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

 

[kpetursson]

assuming that the three letters you want to add are in B2 (as shown on you table)

Sub CellValueAdd()
'
' Cell Value Add
' Macro recorded 2/14/2005 by Kevin Petursson
' This Macro will take the value of cell B2
' and add it to the beginning of the value of
' all of the cells in column B. It will stop
' processing the column when it encounters a
' blank cell.
' By changing the line noted you could set
' the macro to start at any given cell in any
' column. The macro will process untill it
' finds a blank cell in the column it started in.
' By removing the line noted you could set
' the macro to start in the currently selected
' cell. It will then process untill it finds
' a blank cell in the column in which it was started.
'
Dim ValueToAdd As String
ValueToAdd = Range("B2").Value
'Modify, or remove, the following line
Range("B3").Select
'Modify, or remove, the above line
Do While ActiveCell.Value <> ""
ActiveCell.Value = ValueToAdd + ActiveCell.Value
ActiveCell.Offset(1, 0).Select
Loop
End Sub

hope this helps.



Kevin Petursson

 

[lareya]

Thank you so much! This is exactly what I needed!
Lareya

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

 

[kpetursson]

no problem.



Kevin Petursson