asked on

VBA : add text to a selected column.

Hello experts,

I have a file with the following information
Column A
ID;
8589;
589636;
8968756;

I need a VBA macro which could perform the following:

1-Based on the selected column, insert a column to the left and display the following information :

Result

ID;
(ID="8589") or
(ID="589636") or
(ID="8968756")

Warning the last used row don't need to have an or statement and also the first row is not concerned by the action.

Thank you again for your help.

Subodh Tiwari (Neeraj)

A sample workbook with two sheets would be helpful. Where the first sheet should contain what you already have and on another sheet, show the desired output manually

Ryan Chong

try customize this accordingly:

Sub test()
    Columns("A:A").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    lastRow = Cells(Rows.Count, "B").End(xlUp).Row
    For i = 1 To lastRow
        v = Cells(i, "B")
        If v = "ID;" Then
            Cells(i, "A") = "ID;"
        Else
            If i = lastRow Then
                Cells(i, "A") = "(ID=""" & Left(v, Len(v) - 1) & """)"
            Else
                Cells(i, "A") = "(ID=""" & Left(v, Len(v) - 1) & """) or"
            End If
        End If
    Next
End Sub

Open in new window

28698492.xlsm

Luis Diaz

ASKER

Please find attached my template.

Regards,
40889103.xlsm

Luis Diaz

ASKER

In my template I put the revised column at the right side but it doesn't matter it can be at the right or at the left.

Ryan Chong

ok, tried my uploaded example at above? (ID: 40889110) ? it will work (once) if executed (click the button).

Luis Diaz

ASKER

Thank you your example works. However I would like to apply this for a selected column your macro can just be run if the action is performed in Column A.

Ryan Chong

ok, I have amended the script accordingly:

In sheet1, add the SelectionChange event accordingly.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Column = 1 Then
        Call test
    End If
End Sub

Sub test()
    'Columns("A:A").Select
    'Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    lastRow = Cells(Rows.Count, "A").End(xlUp).Row
    For i = 1 To lastRow
        v = Cells(i, "A")
        If v = "ID;" Then
            Cells(i, "B") = "ID;"
        Else
            If i = lastRow Then
                Cells(i, "B") = "(ID=""" & Left(v, Len(v) - 1) & """)"
            Else
                Cells(i, "B") = "(ID=""" & Left(v, Len(v) - 1) & """) or"
            End If
        End If
    Next
End Sub

Open in new window

28698492-b.xlsm

Luis Diaz

ASKER

Thank you for this, however it doesn't works as I put all the information in Column E, I select the column and I don't have the information that I want in Column F.

The reference column and destination column shouldn't be always column A or B but the selected column the same for the destination column it should be +1 the selected column.
Ex: If I select column E I run the result in Column F. If I select the column B i run the result in Column C

Subodh Tiwari (Neeraj)

Okay try this to see if you get the desired output.

Just select the whole column or any cell in the target column and run the code.
In the example workbook column F is already selected, if not select a cell in col. F and run the code to get the desired output.

Sub InsertText()
Dim col As Long, lr As Long, i As Long

Application.ScreenUpdating = False
col = Selection.Column
lr = Cells(Rows.Count, col).End(xlUp).Row
If lr > 1 Then
Columns(col).Insert
col = col + 1
For i = 1 To lr
    If i = 1 Then
        Cells(i, col - 1) = Cells(i, col)
    ElseIf i < lr Then
        Cells(i, col - 1) = "(ID="" & cells(i,col) & "") or"
    Else
        Cells(i, col - 1) = "(ID="" & cells(i,col) & "")"
    End If
Next i
End If
Columns(col - 1).AutoFit
Application.ScreenUpdating = True
End Sub

Open in new window

InsertText.xlsm

Ryan Chong

OK, understand. so it's better to keep the output into another worksheet so it didn't mass up ur data.

I'm out of work now, so other experts Wil provide suggestions on similar solutions. or I Wil reply u with example in due course.

Luis Diaz

ASKER

@sktneer:

I test your code and this is the result that I got:

i and col variables and not properly replaced.

I tried to select the hole column and also a cell of my active column but it doesn't work.

Thank you again for your help.

ASKER CERTIFIED SOLUTION

Subodh Tiwari (Neeraj)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Luis Diaz

ASKER

Yes it works the one "" was missing in line 14 and 16.

Thank you very much for your help!!

Subodh Tiwari (Neeraj)

Thanks LD! Glad I could help. :)