Need help with Macro

J

jocote46

Board Regular
Joined
May 11, 2009
Messages
57
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

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
Sort by date Sort by votes
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.
 
Upvote 0
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
 
Upvote 0
Logit said:
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.
Click to expand...

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.
 
Upvote 0
.
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.
 
Upvote 0
Logit said:
.
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.
Click to expand...

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
 
Upvote 0
excel_newbie86 said:
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
Click to expand...

Excel_newbie86, Thank you for your help.
 
Upvote 0
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. " < / > > _ "
 
Upvote 0
Solution
You must log in or register to reply here.

Similar threads

J
  • Solved
Need help in finishing a macro..
Replies
4
Views
321
jocote46
J
L
  • Solved
VBA remove leading zeros
2
Replies
14
Views
915
Joe4
Joe4
Guna13
  • Question
Help to Optimize Macro Code for Faster Performance
Replies
0
Views
740
Guna13
Guna13
U
  • Solved
VBA question involving 2 different workbooks
Replies
2
Views
401
ummjay
U
G
  • Question
VBA help - copy cells from specific table to specific table on another sheet.
Replies
0
Views
227
GardianAngelUK
G

Forum statistics

Threads
1,214,522
Messages
6,120,020
Members
448,939
Latest member
Leon Leenders

Share this page

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
Back
Top