Request for VB to do something simple (insert text in a column ofcells)

  • Thread starter Thread starter David Nebenzahl
  • Start date Start date
D

David Nebenzahl

I'm having a terrible time with methods, objects, properties, ranges ...
all making my head sorta spin.

What I want to do is pretty simple: turn the first column of cells into
the second one:

ceo --> XYZceo
abc --> XYZabc
123 --> XYZ123
vba --> XYZvba

and so on.

I know it might involve Insert("XYZ"), and I want it to be generalized
and operate on all the cells in a given column, so it would probably use
CurrentRegion.Cells, but I'm stumped.

(An extra twist is that the sheet would have a header row, so the text
should only be inserted starting with the 2nd row.)

Any light that could be shed on this would be much appreciated.
 
Hi David:

Withour VBA, in B2 enter:
="XYZ" & A2 and copy down (assumes A1 is a header cell)

With VBA, YOU select a part of ANY column and run:

Sub david1()
For Each r In Selection
r.Offset(0, 1).Value = "XYZ" & r.Value
Next
End Sub

david1 will fill the adjact cells


With VBA, run david2:

Sub david2()
n = Cells(Rows.Count, "A").End(xlUp).Row
For i = 2 To n
Cells(i, 2).Value = "XYZ" & Cells(i, 1).Value
Next
End Sub

david2 does not require you to Select the cells. It works on column A and
figures how far down to go.
 
With VBA, run david2:

Sub david2()
n = Cells(Rows.Count, "A").End(xlUp).Row
For i = 2 To n
Cells(i, 2).Value = "XYZ" & Cells(i, 1).Value
Next
End Sub
Click to expand...

Thanks for that. I changed it a little:

Sub Format4Upload
n = Cells(Rows.Count, "D").End(xlUp).Row
For i = 2 To n
Cells(i, 4).Characters(0, 0).Insert ("XYZ")
Next
End Sub

Works fine. But of course I need something more: how do I skip cells
that are blank? (In other words, don't insert anything if the cell is
blank).

Thanks again.
 
Sub Add_Text_Left()
Dim Cell As Range
Dim moretext As String
Dim thisrng As Range
On Error GoTo endit
Set thisrng = Range(ActiveCell.Address & "," & Selection.Address) _
.SpecialCells(xlCellTypeConstants, xlTextValues)
moretext = InputBox("Enter your Text")
For Each Cell In thisrng
Cell.Value = moretext & Cell.Value
Next
Exit Sub
endit:
MsgBox "only formulas in range"
End Sub

Select A2 then SHIFT + End + Downarrow.

The run the macro.


Gord Dibben MS Excel MVP
 
On 11/20/2007 1:22 PM Gord Dibben spake thus:

[reformatted for more logical bottom posting]
Sub Add_Text_Left()
Dim Cell As Range
Dim moretext As String
Dim thisrng As Range
On Error GoTo endit
Set thisrng = Range(ActiveCell.Address & "," & Selection.Address) _
.SpecialCells(xlCellTypeConstants, xlTextValues)
moretext = InputBox("Enter your Text")
For Each Cell In thisrng
Cell.Value = moretext & Cell.Value
Next
Exit Sub
endit:
MsgBox "only formulas in range"
End Sub

Select A2 then SHIFT + End + Downarrow.

The run the macro.
Click to expand...

Thanks; appreciated. Here's what I eventually came up with on my own
(mostly):


Sub Format4Upload()

Dim id_tag
'
' Format4Upload Macro
' Macro created 11/20/2007 by David Nebenzahl
'
' 1. Select "work" sheet:
Sheets("work").Select
' 2. Insert a new column at start of sheet, title it "path":
Range("A:A").Insert (xlShiftToRight)
Cells(1, 1).Value = "path"
' 3. Insert a new column after "id", title it "code":
Range("C:C").Insert (xlShiftToRight)
Cells(1, 3).Value = "code"
' copy "id" value to "code":
n = Cells(Rows.Count, "F").End(xlUp).Row
For i = 2 To n ' skip header row
' 4. Copy "id" value to "code":
Cells(i, 3).Value = Cells(i, 2).Value
' 5. Set "path" based on 1st 2 letters of id:
id_tag = Left(Cells(i, 2), 2)
Select Case id_tag
Case "FP"
Cells(i, 1).Value = "manmadebags"
Case "LP"
Cells(i, 1).Value = "leatherbags"
Case "EP"
Cells(i, 1).Value = "eveningbags"
End Select
' 6. Insert "Colors " before all (non-blank) options,
If Cells(i, 6) <> "" Then
Cells(i, 6).Characters(0, 0).Insert ("Colors ")
End If
Next

End Sub


Requires no interaction from the user, and can be run with any sheet of
the workbook open. I kind of prefer working in the iterative model,
where I can kill several birds w/one stone inside the loop.

Comments welcome.
 
Back
Top