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

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.

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

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.
--
Gary''s Student - gsnu2007a


"David Nebenzahl" wrote:

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.

Request for VB to do something simple (insert text in a columnof cells)
 

On 11/20/2007 1:56 AM Gary''s Student spake thus:

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

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 mo how do I skip cells
that are blank? (In other words, don't insert anything if the cell is
blank).

Thanks again.

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

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 Tue, 20 Nov 2007 09:18:45 -0800, David Nebenzahl
wrote:

On 11/20/2007 1:56 AM Gary''s Student spake thus:

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

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 mo how do I skip cells
that are blank? (In other words, don't insert anything if the cell is
blank).

Thanks again.

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

Also fills blank cells which OP did not want.


Gord Dibben MS Excel MVP

On Tue, 20 Nov 2007 01:56:00 -0800, Gary''s Student
wrote:

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.

Request for VB to do something simple (insert text in a columnof cells)
 

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

[reformatted for more logical bottom posting]

On Tue, 20 Nov 2007 09:18:45 -0800, David Nebenzahl
wrote:

On 11/20/2007 1:56 AM Gary''s Student spake thus:

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

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 mo 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.

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.