Tech Support Guy banner
Status
Not open for further replies.
1 - 9 of 9 Posts

·
Registered
Joined
·
419 Posts
Discussion Starter · #1 ·
I can manage to create a macro to change the text of a cell to something else, but I was wondering if there is any way to add the same text to the beginning of every cell you run the macro in.

For Example I want to add the initials LSB to the beginning of several (long list) cells. Is this possible, and can anyone tell me how?
 

·
Registered
Joined
·
1,225 Posts
Hi :)
This below is a general code for what you asked:
Code:
Cell(RowIndex, ColumnIndex).Value = "String" & Cell(RowIndex, ColumnIndex).Value
To make it specific, you have to replace RowIndex, ColumnIndex and "String" with actual values.

Now, if you have lots of cells to modify, that should be done with a loop or something similar, not one by one. Maybe, if you tell some more details, or upload a sample workbook, we can find an easy solution.

Jimmy
 

·
Registered
Joined
·
5,458 Posts
Okay, seeing as how I just lost my entire post, I'll keep a short synopsis of what I already typed...

Get ASAP Utilities (www.asap-utilities.com) which can do this natively. If you do not want to download and install the add-in, these routines may help...

Code:
Sub AppendToExistingOnLeft()
    Dim c as range
    For each c in Selection
        If c.value <> "" Then c.value = "LSB " & c.value
    Next
End Sub
Code:
Sub AppendToExistingOnRight()
    Dim c as range
    For each c in Selection
        If c.value <> "" Then c.value = c.value & " LSB"
    Next
End Sub
I still recommend ASAP for this, especially as it will let you specify the text via an inputbox thus making it much more efficient.

HTH
 

·
Registered
Joined
·
419 Posts
Discussion Starter · #4 ·
Append to existing worked. Thank you, you are my hero. I have wanted to be able to do this for years.

Sorry Jimmy, I didn't check your solution, because I was trying to figure out what I would need to use as my replacement values.
 

·
Registered
Joined
·
419 Posts
Discussion Starter · #6 ·
I'm still not sure how I would use it. Can you help me with what I would use for Row Index, Column Index, and String. It seems to be more definite.
 

·
Registered
Joined
·
5,458 Posts
Each value from A1:C10 gets amended here...

Code:
Sub ShowExampleOfJimmysWork()
    Dim ws As Worksheet, iRow As Long, iCol As Long, i As Long
    Set ws = Sheets("Sheet5")
    For iRow = 1 To 10
        For iCol = 1 To 3
            With ws.Cells(iRow, iCol)
                If .Value <> "" Then .Value = "String " & .Value
            End With
        Next
    Next
End Sub
This is faster than looping through the ranges. Although with such a small range you won't hardly notice any difference. Still, ASAP Utilities is HIGHLY RECOMMENDED. There is no need to reinvent the wheel. Plus it is more dynamic as it lets you choose right there on the spot, before or after the value, etc.
 

·
Registered
Joined
·
419 Posts
Discussion Starter · #8 ·
OK, I tried it. I kind of like it. I still liked the append for what I asked for. I plan on taking a closer look at the code to see if that would haved worked better, if I understood it better. Anyway, Thank you for your help. I went to the ASAP site, and started checking it out, it looks great. I will go back and look into it a little more when I get some more time.
 
1 - 9 of 9 Posts
Status
Not open for further replies.
Top