Need help with Macro

jocote46

New Member
Joined
May 11, 2009
Messages
43
Hello ,

I'm hoping you can help me with this macro that i started but i got stuck. the part that i'm having problems is with copying the formula "=left(W2,10) all the way down on Column "W" to the lastRow. i also want to incorporate the vlookup formula in the macro. I want this macro to work on any active worksheet that i select. one more thing, i want Column "G" data to be formated as Number.

VBA Code:
Sub InsertColumnGP()

Dim ws As Worksheet
Set ws = ActiveSheet

'Insert Column to next to the Account number, to the right of Column W
ActiveSheet.Select
Range("AA1").Value = "BU"
Range("AB1").Value = "Amount"
Range("AC1").Value = "Portion"
Range("AD1").Value = "Balance"
Columns("X:X").Insert
Columns("X").NumberFormat = "General"
Columns("G").NumberFormat = "General"
Range("X1").Value = "Sub Account"
Range("X2").Formula = "= Left(W2, 10)"

With Sheets("ActiveSheet")
  .Range("X2").AutoFill .Range("X2:X" & .Cells(.Rows.Count, "W").End(xlUp).Row)
End With

'= "=VLOOKUP(X2,'[Adaptive Level Hierarchy by Geoff 8.21.20_WA.xlsx]Levels (2)'!$C$10:$F$344,4,0)


End Sub

Thank you in advance for all your help.

Walter
 
Last edited by a moderator:

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
3,998
VBA Code:
Sub InsertColumnGP()

Dim ws As Worksheet
Set ws = ActiveSheet

'Insert Column to next to the Account number, to the right of Column W
ActiveSheet.Select
Range("AA1").Value = "BU"
Range("AB1").Value = "Amount"
Range("AC1").Value = "Portion"
Range("AD1").Value = "Balance"
'Columns("X:X").Insert
Columns("X").NumberFormat = "General"
Columns("G").NumberFormat = "General"
Range("X1").Value = "Sub Account"
Range("X2").Formula = "=Left(W2, 10)"

'With ActiveSheet
'  .Range("X2").AutoFill .Range("X2:X" & .Cells(.Rows.Count, "W").End(xlUp).Row)
'End With

'= "=VLOOKUP(X2,'[Adaptive Level Hierarchy by Geoff 8.21.20_WA.xlsx]Levels (2)'!$C$10:$F$344,4,0)


End Sub

I'm somewhat confused what your are attempting as a goal. However, the above changes to your posted macro seem to do most of what your are seeking.

Please explain more about the VLOOKUP. Also, if the amended macro above isn't what you are seeking let me know.
 

excel_newbie86

New Member
Joined
Aug 1, 2020
Messages
17
Office Version
  1. 2016
  2. 2007
Platform
  1. Windows
Hi Joco, how about:
VBA Code:
Sub InsertColumnGP()
Dim ws As Worksheet, lr As Long, i As Long
Set ws = ActiveSheet

'Insert Column to next to the Account number, to the right of Column W
With ActiveSheet
    .Range("AA1:AD1").Value = Array("BU", "Amount", "Portion", "Balance")
'Columns("X:X").Insert
    .Columns("X").NumberFormat = "General"
    .Columns("G").NumberFormat = "General"
    .Range("X1").Value = "Sub Account"
    lr = .Range("X" & Rows.Count).End(3).Row
    For i = 2 To lr
        .Range("Xi").Value = Left(Wi, 10)
    Next
End With
End Sub
 

jocote46

New Member
Joined
May 11, 2009
Messages
43
VBA Code:
Sub InsertColumnGP()

Dim ws As Worksheet
Set ws = ActiveSheet

'Insert Column to next to the Account number, to the right of Column W
ActiveSheet.Select
Range("AA1").Value = "BU"
Range("AB1").Value = "Amount"
Range("AC1").Value = "Portion"
Range("AD1").Value = "Balance"
'Columns("X:X").Insert
Columns("X").NumberFormat = "General"
Columns("G").NumberFormat = "General"
Range("X1").Value = "Sub Account"
Range("X2").Formula = "=Left(W2, 10)"

'With ActiveSheet
'  .Range("X2").AutoFill .Range("X2:X" & .Cells(.Rows.Count, "W").End(xlUp).Row)
'End With

'= "=VLOOKUP(X2,'[Adaptive Level Hierarchy by Geoff 8.21.20_WA.xlsx]Levels (2)'!$C$10:$F$344,4,0)


End Sub

I'm somewhat confused what your are attempting as a goal. However, the above changes to your posted macro seem to do most of what your are seeking.

Please explain more about the VLOOKUP. Also, if the amended macro above isn't what you are seeking let me know.

Logit,

Thank you for replying, however is not doing what i need it to do. I need the Macro to create a new column which it would be Column "X" . then insert the the formula on cell "X2" =left(W2,10). then i want the macro to copy the formula all the way down to last row, using "W" as reference for last row.

Then I want to Vlookup formula to be place on Column"AA2" and copy the formula all the way down to last row, using "W" as reference.

your version of the macro was overriding the information already in place on column "X". if you have additional questions, please let me know and thank you once again.
 

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
3,998

ADVERTISEMENT

.
Try this :

VBA Code:
Sub InsertColumnGP()

Dim ws As Worksheet
Set ws = ActiveSheet

'Insert Column to next to the Account number, to the right of Column W
ActiveSheet.Select
Range("AA1").Value = "BU"
Range("AB1").Value = "Amount"
Range("AC1").Value = "Portion"
Range("AD1").Value = "Balance"
Columns("X:X").Insert
Columns("X").NumberFormat = "General"
Columns("G").NumberFormat = "General"
Range("X1").Value = "Sub Account"
Range("X2").Formula = "=Left(W2, 10)"
Range("AA2").Formula = "=VLOOKUP(X2,'[Adaptive Level Hierarchy by Geoff 8.21.20_WA.xlsx]Levels (2)'!$C$10:$F$344,4,0)"

    With ActiveSheet
            .Range("X2").AutoFill .Range("X2:X" & .Cells(.Rows.Count, "W").End(xlUp).Row)
            .Range("AA2").AutoFill .Range("AA2:AA" & .Cells(.Rows.Count, "W").End(xlUp).Row)
            'add additional columns here
    End With

End Sub

Two points :

When inserting a new X column it will automatically insert to the left of the existing X column. Trying to insert to the right of the existing X column will
make the newly inserted column the new Y column.

Secondly, without your existing workbook, I can't fulfill the rest of the VLOOKUP formula process. I don't have the workbook in question. However, the
insertion of the formula seems to work but I am not certain it is accurate.
 

jocote46

New Member
Joined
May 11, 2009
Messages
43
.
Try this :

VBA Code:
Sub InsertColumnGP()

Dim ws As Worksheet
Set ws = ActiveSheet

'Insert Column to next to the Account number, to the right of Column W
ActiveSheet.Select
Range("AA1").Value = "BU"
Range("AB1").Value = "Amount"
Range("AC1").Value = "Portion"
Range("AD1").Value = "Balance"
Columns("X:X").Insert
Columns("X").NumberFormat = "General"
Columns("G").NumberFormat = "General"
Range("X1").Value = "Sub Account"
Range("X2").Formula = "=Left(W2, 10)"
Range("AA2").Formula = "=VLOOKUP(X2,'[Adaptive Level Hierarchy by Geoff 8.21.20_WA.xlsx]Levels (2)'!$C$10:$F$344,4,0)"

    With ActiveSheet
            .Range("X2").AutoFill .Range("X2:X" & .Cells(.Rows.Count, "W").End(xlUp).Row)
            .Range("AA2").AutoFill .Range("AA2:AA" & .Cells(.Rows.Count, "W").End(xlUp).Row)
            'add additional columns here
    End With

End Sub

Two points :

When inserting a new X column it will automatically insert to the left of the existing X column. Trying to insert to the right of the existing X column will
make the newly inserted column the new Y column.

Secondly, without your existing workbook, I can't fulfill the rest of the VLOOKUP formula process. I don't have the workbook in question. However, the
insertion of the formula seems to work but I am not certain it is accurate.

Thank you so much, the macro works perfect. i made a few minor changes for the data to copy down on the correct column. here is the final macro , i added the highlighting and bold of the Cells that were created. this was my first attempt in creating a macro, now i will try to create a few more that i need. thank you so much for your help. you're my hero.

VBA Code:
Sub InsertColumnGP()

Dim ws As Worksheet
Set ws = ActiveSheet

'Insert Column to next to the Account number, to the right of Column W
ActiveSheet.Select
Range("AA1").Value = "BU"
Range("AB1").Value = "Amount"
Range("AC1").Value = "Portion"
Range("AD1").Value = "Balance"
Range("AA1:AD1").Interior.Color = ColorConstants.vbYellow
Range("AA1:AD1").Font.Bold = True
Columns("X:X").Insert
Columns("X").NumberFormat = "General"
Columns("G").NumberFormat = "General"
Range("X1").Value = "Sub Account"
Range("X2").Formula = "=Left(W2, 10)"
Range("AB2").Formula = "=VLOOKUP(X2,'[Adaptive Level Hierarchy by Geoff 8.21.20_WA.xlsx]Levels (2)'!$C$10:$F$344,4,0)"

With ActiveSheet
.Range("X2").AutoFill .Range("X2:X" & .Cells(.Rows.Count, "W").End(xlUp).Row)
.Range("AB2").AutoFill .Range("AB2:AB" & .Cells(.Rows.Count, "W").End(xlUp).Row)
'add additional columns here
End With

End Sub
 

jocote46

New Member
Joined
May 11, 2009
Messages
43
Hi Joco, how about:
VBA Code:
Sub InsertColumnGP()
Dim ws As Worksheet, lr As Long, i As Long
Set ws = ActiveSheet

'Insert Column to next to the Account number, to the right of Column W
With ActiveSheet
    .Range("AA1:AD1").Value = Array("BU", "Amount", "Portion", "Balance")
'Columns("X:X").Insert
    .Columns("X").NumberFormat = "General"
    .Columns("G").NumberFormat = "General"
    .Range("X1").Value = "Sub Account"
    lr = .Range("X" & Rows.Count).End(3).Row
    For i = 2 To lr
        .Range("Xi").Value = Left(Wi, 10)
    Next
End With
End Sub

Excel_newbie86, Thank you for your help.
 

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
3,998
You are welcome. Glad it works for your needs.

Please wrap all of your posted code using the menu selection at the top of the reply box. " < / > > _ "
 
Solution

Watch MrExcel Video

Forum statistics

Threads
1,129,486
Messages
5,636,611
Members
416,929
Latest member
Nitil

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top