Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add a character to all cells in a column
Hi all
how would i go in VBA to do this: I have a single worksheet and i have a column with heading COLOR. I do not know what position color has in my worksheet (it could be column B, C or any other). I want to prepend a character "j" to all cells in the column that have a value. So if my column initially is like: COLOR ========== yellow pink orange after i run the VBA script it should be COLOR ========== jyellow jpink jorange thank you! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add a character to all cells in a column
one way
Sub putletterinfront() For Each c In Range("d2:d9") If Len(Application.Trim(c)) 1 Then c.Value = "j" & c Next End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "glenn" wrote in message ... Hi all how would i go in VBA to do this: I have a single worksheet and i have a column with heading COLOR. I do not know what position color has in my worksheet (it could be column B, C or any other). I want to prepend a character "j" to all cells in the column that have a value. So if my column initially is like: COLOR ========== yellow pink orange after i run the VBA script it should be COLOR ========== jyellow jpink jorange thank you! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add a character to all cells in a column
Dim res as Variant, rng as Range
res = Application.Match("Color",Range("A1:IV1"),0) if not iserror(res) then set rng = Range(.Cells(2,res),.Cells(rows.count,res)) for each cell in rng.SpecialCells(xlConstants) cell.Value = "j" & cell.Value Next End if -- regards, Tom Ogilvy "glenn" wrote: Hi all how would i go in VBA to do this: I have a single worksheet and i have a column with heading COLOR. I do not know what position color has in my worksheet (it could be column B, C or any other). I want to prepend a character "j" to all cells in the column that have a value. So if my column initially is like: COLOR ========== yellow pink orange after i run the VBA script it should be COLOR ========== jyellow jpink jorange thank you! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add a character to all cells in a column
Try this
_____________________________ Sub UpdateColorColumn() Dim colRng As Range Dim colRef As Integer Dim topRow As Integer For Each colRng In Rows(1).Cells If colRng.Value = "COLOR" Then colRef = colRng.Column topRow = Cells(65536, colRef).End(xlUp).Row For R = 1 To topRow If R 1 And Len(Cells(R, colRef).Value) 0 Then Cells(R, colRef).Value = "j" & Cells(R, colRef).Value End If Next R End If Next colRng End Sub ______________________________ Steve "glenn" wrote in message ... Hi all how would i go in VBA to do this: I have a single worksheet and i have a column with heading COLOR. I do not know what position color has in my worksheet (it could be column B, C or any other). I want to prepend a character "j" to all cells in the column that have a value. So if my column initially is like: COLOR ========== yellow pink orange after i run the VBA script it should be COLOR ========== jyellow jpink jorange thank you! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Add the same character(s) to multiple cells in a column (or row) . | Excel Discussion (Misc queries) | |||
search a column for a character.. | Excel Discussion (Misc queries) | |||
Can column character be added? | Excel Programming | |||
Add a character to each row in a column | Excel Discussion (Misc queries) | |||
Column Character | Excel Programming |