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! |
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! |
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! |
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! |
All times are GMT +1. The time now is 10:06 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com