Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Insert cell text every 5 rows in a column | Excel Discussion (Misc queries) | |||
when the column text is different, how to insert one row below eac | Excel Worksheet Functions | |||
Forgotten a Simple Forumla - Is Text present in another column | Excel Discussion (Misc queries) | |||
Here is a simple formula request from a newb | Excel Worksheet Functions | |||
DATEDIF Simple request | Excel Worksheet Functions |