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!

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
(E-Mail Removed)
"glenn" <(E-Mail Removed)> wrote in message
news:fdjk5n$ncj$(E-Mail Removed)...
> 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!

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!
>

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" <(E-Mail Removed)> wrote in message
news:fdjk5n$ncj$(E-Mail Removed)...
> 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!