| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Newbie attempting VBA Scripting
Hey all,
I am new on the block trying to create a macro which creates 4 new lines with 21 columns from a set of source data. The macro will need to count how many rows are in the source data (as each row is a seperate entity) and store that count in a variable, X. Then it should create 4 new rows in a new sheet and populate each cell with various bits of information. The cells will be filled with simple vlookups and just links to the source sheet, or just plain numbers and text hardcoded (no links). I know this was vague, but can anyone at least tell me how to do some simple things like create a vlookup in a target cell, or a way to count the number of rows in the source data, store it in a variable, and then use that to create the appropriate number of 4-row entries in the resulting worksheet, there being 4 new entires per 1 row from the source. Im basically trying to generate journal entries based upon source data. I am trying to teach myself VBA through internet websites, but figured i would tap this forum for some knowledge! Thanks alot, in advance. -pogster |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Newbie attempting VBA Scripting
This should get you started.
Sub movedata() Worksheets.Add after:=Sheets(Sheets.Count) Set newsht = ActiveSheet OldRowCount = 1 NewRowCount = 1 With Worksheets("Old sheet") Do While .Range("A" & OldRowCount) < "" DataA = .Range("A" & OldRowCount) DataB = .Range("B" & OldRowCount) With newsht .Range("B" & (NewRowCount + 2)) = DataA .Range("C" & (NewRowCount + 3)) = DataB OldRowCount = OldRowCount + 4 End With OldRowCount = OldRowCount + 1 Loop End With End Sub " wrote: Hey all, I am new on the block trying to create a macro which creates 4 new lines with 21 columns from a set of source data. The macro will need to count how many rows are in the source data (as each row is a seperate entity) and store that count in a variable, X. Then it should create 4 new rows in a new sheet and populate each cell with various bits of information. The cells will be filled with simple vlookups and just links to the source sheet, or just plain numbers and text hardcoded (no links). I know this was vague, but can anyone at least tell me how to do some simple things like create a vlookup in a target cell, or a way to count the number of rows in the source data, store it in a variable, and then use that to create the appropriate number of 4-row entries in the resulting worksheet, there being 4 new entires per 1 row from the source. Im basically trying to generate journal entries based upon source data. I am trying to teach myself VBA through internet websites, but figured i would tap this forum for some knowledge! Thanks alot, in advance. -pogster |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Newbie attempting VBA Scripting
Thanks Joel,
I am having a little trouble understanding how the code works but only because i dont know what the line: "Do While .Range("A" & OldRowCount) < "" " says in english. Do the following while the value of the range A(OLDROWCOUNT) is what? My output is always the same, whatever is last in columns A and B, gets moved to B3 and C4 respectively. Gotta understand the code completely before i can modify it and use it. Thanks again Joel! |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Newbie attempting VBA Scripting
< - is not equal
the line is look for an empty cell - double quotes "" with noting between them VBA address cells using the Range statement like Range("A5"). I used the counter OldRowCount to indicate the row number. OldRowCount = 5 Range("A" & OldRowCount) The two statement above is equivalent to Range("A5") " wrote: Thanks Joel, I am having a little trouble understanding how the code works but only because i dont know what the line: "Do While .Range("A" & OldRowCount) < "" " says in english. Do the following while the value of the range A(OLDROWCOUNT) is what? My output is always the same, whatever is last in columns A and B, gets moved to B3 and C4 respectively. Gotta understand the code completely before i can modify it and use it. Thanks again Joel! |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Newbie attempting VBA Scripting
Ah okay joel,
I am used to C++ and Java where != is not equal to. VBA newbie indeed. I now understand how it works. Thanks for the demonstration, it is certainly a great building block. Ill let you know how far i get before i get dizzy. thanks. |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Newbie attempting VBA Scripting
Range function wants a string. If you are using C++ it make things more
difficult than using basic. You are going to have to do the combining. You probably want to use the function cells(row,column) instead of range. It will make programming in C++ easier. " wrote: Ah okay joel, I am used to C++ and Java where != is not equal to. VBA newbie indeed. I now understand how it works. Thanks for the demonstration, it is certainly a great building block. Ill let you know how far i get before i get dizzy. thanks. |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Newbie attempting VBA Scripting
One other pointer for somebody who doesn't know VBA BASIC. the "WITH"
statement is used to combine function that have a DOT infron of them With Worksheets("Old sheet") DataA = .Range("A" & OldRowCount) end with Is really DataA = Worksheets("Old sheet").Range("A" & OldRowCount) This feature of BASIC doesn't translate well to C++ " wrote: Ah okay joel, I am used to C++ and Java where != is not equal to. VBA newbie indeed. I now understand how it works. Thanks for the demonstration, it is certainly a great building block. Ill let you know how far i get before i get dizzy. thanks. |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Newbie attempting VBA Scripting
Yea, no worries about C++ and Java, just mentioned them in terms of
not knowing the VBA equivalent of things. I am coding this macro in VBA. Yea, i figured thats what the WITH statement did, after looking at your code. Thanks for the heads up though, ill put up what i come up with over the weekend, maybe you can help me clean up my code a bit!!! Also, quick question, if i want to insert a Vlookup into a cell, like so: ActiveCell.FormulaR1C1 = _ "=VLOOKUP('Premiums Commissions'!R[-31]C,'Dept Lookup'! R[-31]C[-5]:R[-23]C[-4],2,FALSE)" Instead of specifying a Cell location with R1C1, is there a way to make it a variable? Because as the macro goes down the entires, it will need to modify the source cell for the lookup as it will be changing. I.E., the first vlookup(located in the newsheet) will be referencing $F$2 in the source for column G of the first 4 journal rows it generates, whereas the next 4 generated journal rows will have a vlookup which will reference $F$3 in the source, etc..ad infinitum. Can i replace "R[-31]C" with a variable incremented by a counter somehow? thanks |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Newbie attempting VBA Scripting
Most functions in VBA gets string. So you are just creating sa string and
you can insert number by using the &. Row1 = -31 Row2 = -5 Row3 = -23 Row4 = -4 ActiveCell.FormulaR1C1 = _ "=VLOOKUP('Premiums Commissions'!R[" & Row1 & "]C,'Dept Lookup'! R[" & row1 & "]C[" & Row2 & "]:R[" & Row3 & "]C[" & Row4 & "],2,FALSE)" If your columns don't change I like using A1 addressing instead of R1C1 address ActiveCell.Formula = _ "=VLOOKUP('Premiums Commissions'!" & Row1 ":" & Row1 &"],'Dept Lookup'! C" & row1 & ":D" & Row2 & ",2,FALSE)" " wrote: Yea, no worries about C++ and Java, just mentioned them in terms of not knowing the VBA equivalent of things. I am coding this macro in VBA. Yea, i figured thats what the WITH statement did, after looking at your code. Thanks for the heads up though, ill put up what i come up with over the weekend, maybe you can help me clean up my code a bit!!! Also, quick question, if i want to insert a Vlookup into a cell, like so: ActiveCell.FormulaR1C1 = _ "=VLOOKUP('Premiums Commissions'!R[-31]C,'Dept Lookup'! R[-31]C[-5]:R[-23]C[-4],2,FALSE)" Instead of specifying a Cell location with R1C1, is there a way to make it a variable? Because as the macro goes down the entires, it will need to modify the source cell for the lookup as it will be changing. I.E., the first vlookup(located in the newsheet) will be referencing $F$2 in the source for column G of the first 4 journal rows it generates, whereas the next 4 generated journal rows will have a vlookup which will reference $F$3 in the source, etc..ad infinitum. Can i replace "R[-31]C" with a variable incremented by a counter somehow? thanks |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Newbie attempting VBA Scripting
Well heres the macro i coded, it works like a charm, but its really
really messy im sure. Any suggestions for cleaning it up/ making it less specific to THIS EXACT Layout and file etc. Like can i have it look for certain column names and then figure out their position, instead of just assigning their position and having the macro assume they are there and error if they arent. Anyway, here it is. Thanks for your help Joel. Sub AutoJV() '///Create New Sheet "Prem Comm JV" and Format It///////////////////////////////// Worksheets.Add after:=Sheets(Sheets.Count) Set NewSheet = ActiveSheet ActiveSheet.Name = "Prem Comm JV" Range("B1").FormulaR1C1 = "Unit" Range("C1").FormulaR1C1 = "Ledger" Range("D1").FormulaR1C1 = "Account" Range("E1").FormulaR1C1 = "Alt Account" Range("F1").FormulaR1C1 = "Oper Unit" Range("G1").FormulaR1C1 = "Dept ID" Range("H1").FormulaR1C1 = "Currency" Range("I1").FormulaR1C1 = "Amount" Range("J1").FormulaR1C1 = "N/R" Range("K1").FormulaR1C1 = "Rate Type" Range("L1").FormulaR1C1 = "Rate" Range("M1").FormulaR1C1 = "Base Amount" Range("N1").FormulaR1C1 = "Stat" Range("O1").FormulaR1C1 = "Stat Amount" Range("P1").FormulaR1C1 = "Product" Range("Q1").FormulaR1C1 = "Year" Range("R1").FormulaR1C1 = "Mngt Code" Range("S1").FormulaR1C1 = "Risk Loc" Range("T1").FormulaR1C1 = "Function" Range("U1").FormulaR1C1 = "Project" Range("V1").FormulaR1C1 = "Affiliate" Range("B1").Select Selection.Borders(xlDiagonalDown).LineStyle = xlNone Selection.Borders(xlDiagonalUp).LineStyle = xlNone Selection.Borders(xlEdgeLeft).LineStyle = xlNone Selection.Borders(xlEdgeTop).LineStyle = xlNone With Selection.Borders(xlEdgeBottom) .LineStyle = xlContinuous .Weight = xlThick .ColorIndex = xlAutomatic End With Selection.Borders(xlEdgeRight).LineStyle = xlNone Selection.Font.Bold = True Selection.Copy Range("C1:V1").Select Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False Application.CutCopyMode = False Columns("B:V").EntireColumn.AutoFit '///////////////////////////////////////////////////////// '///The Fun Stuff//////////////////////////////////////////////////// OldRowCounter = 2 NewRowCounter = 2 With Worksheets("Premiums Commissions") Do While .Range("A" & OldRowCounter) < "" UnitCol = .Range("D" & OldRowCounter) With NewSheet .Range("A" & NewRowCounter) = "Agents Bal - Affiliate Assumed" .Range("A" & (NewRowCounter + 1)) = "AWP - Affiliate" .Range("A" & (NewRowCounter + 2)) = "Asmd Cont Comm Res- Affil" .Range("A" & (NewRowCounter + 3)) = "Asmd Comm-Affiliate" .Range("B" & NewRowCounter) = UnitCol .Range("B" & (NewRowCounter + 1)) = UnitCol .Range("B" & (NewRowCounter + 2)) = UnitCol .Range("B" & (NewRowCounter + 3)) = UnitCol .Range("C" & NewRowCounter) = "CORE" .Range("C" & (NewRowCounter + 1)) = "CORE" .Range("C" & (NewRowCounter + 2)) = "CORE" .Range("C" & (NewRowCounter + 3)) = "CORE" .Range("D" & NewRowCounter) = "1270220076" .Range("D" & (NewRowCounter + 1)) = "3010620076" .Range("D" & (NewRowCounter + 2)) = "2144120076" .Range("D" & (NewRowCounter + 3)) = "6010620076" .Range("G" & NewRowCounter) = "=VLOOKUP('Premiums Commissions'!F" & OldRowCounter & ",'Dept Lookup'!A2:B65536,2,FALSE)" .Range("G" & (NewRowCounter + 1)) = "=VLOOKUP('Premiums Commissions'!F" & OldRowCounter & ",'Dept Lookup'!A2:B65536,2,FALSE)" .Range("G" & (NewRowCounter + 2)) = "=VLOOKUP('Premiums Commissions'!F" & OldRowCounter & ",'Dept Lookup'!A2:B65536,2,FALSE)" .Range("G" & (NewRowCounter + 3)) = "=VLOOKUP('Premiums Commissions'!F" & OldRowCounter & ",'Dept Lookup'!A2:B65536,2,FALSE)" .Range("H" & NewRowCounter) = "=+'Premiums Commissions'!I" & OldRowCounter & "" .Range("H" & (NewRowCounter + 1)) = "=+'Premiums Commissions'!G" & OldRowCounter & "" .Range("H" & (NewRowCounter + 2)) = "=+'Premiums Commissions'!I" & OldRowCounter & "" .Range("H" & (NewRowCounter + 3)) = "=+'Premiums Commissions'!G" & OldRowCounter & "" .Range("I" & NewRowCounter) = "=+'Premiums Commissions'!J" & OldRowCounter & "" .Range("I" & NewRowCounter).NumberFormat = "#,##0.00_); [Red](#,##0.00)" .Range("I" & (NewRowCounter + 1)) = "=-'Premiums Commissions'!H" & OldRowCounter & "" .Range("I" & (NewRowCounter + 1)).NumberFormat = "#,##0.00_);[Red](#,##0.00)" .Range("I" & (NewRowCounter + 2)) = "=+'Premiums Commissions'!N" & OldRowCounter & "" .Range("I" & (NewRowCounter + 2)).NumberFormat = "#,##0.00_);[Red](#,##0.00)" .Range("I" & (NewRowCounter + 3)) = "=-'Premiums Commissions'!L" & OldRowCounter & "" .Range("I" & (NewRowCounter + 3)).NumberFormat = "#,##0.00_);[Red](#,##0.00)" .Range("M" & NewRowCounter) = "=+'Premiums Commissions'!K" & OldRowCounter & "" .Range("M" & NewRowCounter).NumberFormat = "#,##0.00_); [Red](#,##0.00)" .Range("M" & (NewRowCounter + 1)) = "=-'Premiums Commissions'!K" & OldRowCounter & "" .Range("M" & (NewRowCounter + 1)).NumberFormat = "#,##0.00_);[Red](#,##0.00)" .Range("M" & (NewRowCounter + 2)) = "=+'Premiums Commissions'!M" & OldRowCounter & "" .Range("M" & (NewRowCounter + 2)).NumberFormat = "#,##0.00_);[Red](#,##0.00)" .Range("M" & (NewRowCounter + 3)) = "=-'Premiums Commissions'!M" & OldRowCounter & "" .Range("M" & (NewRowCounter + 2)).NumberFormat = "#,##0.00_);[Red](#,##0.00)" .Range("P" & NewRowCounter) = "=VLOOKUP('Premiums Commissions'!F" & OldRowCounter & ",'Product Lookup'! A2:B65536,2,FALSE)" .Range("P" & (NewRowCounter + 1)) = "=VLOOKUP('Premiums Commissions'!F" & OldRowCounter & ",'Product Lookup'! A2:B65536,2,FALSE)" .Range("P" & (NewRowCounter + 2)) = "=VLOOKUP('Premiums Commissions'!F" & OldRowCounter & ",'Product Lookup'! A2:B65536,2,FALSE)" .Range("P" & (NewRowCounter + 3)) = "=VLOOKUP('Premiums Commissions'!F" & OldRowCounter & ",'Product Lookup'! A2:B65536,2,FALSE)" .Range("R" & NewRowCounter) = "=VLOOKUP('Premiums Commissions'!F" & OldRowCounter & ",'MCC Lookup'!A2:B65536,2,FALSE)" .Range("R" & (NewRowCounter + 1)) = "=VLOOKUP('Premiums Commissions'!F" & OldRowCounter & ",'MCC Lookup'!A2:B65536,2,FALSE)" .Range("R" & (NewRowCounter + 2)) = "=VLOOKUP('Premiums Commissions'!F" & OldRowCounter & ",'MCC Lookup'!A2:B65536,2,FALSE)" .Range("R" & (NewRowCounter + 3)) = "=VLOOKUP('Premiums Commissions'!F" & OldRowCounter & ",'MCC Lookup'!A2:B65536,2,FALSE)" .Range("S" & NewRowCounter) = "=VLOOKUP('Premiums Commissions'!F" & OldRowCounter & ",'Risk Loc Lookup'! A2:B65536,2,FALSE)" .Range("S" & (NewRowCounter + 1)) = "=VLOOKUP('Premiums Commissions'!F" & OldRowCounter & ",'Risk Loc Lookup'! A2:B65536,2,FALSE)" .Range("S" & (NewRowCounter + 2)) = "=VLOOKUP('Premiums Commissions'!F" & OldRowCounter & ",'Risk Loc Lookup'! A2:B65536,2,FALSE)" .Range("S" & (NewRowCounter + 3)) = "=VLOOKUP('Premiums Commissions'!F" & OldRowCounter & ",'Risk Loc Lookup'! A2:B65536,2,FALSE)" .Range("V" & NewRowCounter) = "=+'Premiums Commissions'!B" & OldRowCounter & "" .Range("V" & (NewRowCounter + 1)) = "=+'Premiums Commissions'!B" & OldRowCounter & "" .Range("V" & (NewRowCounter + 2)) = "=+'Premiums Commissions'!B" & OldRowCounter & "" .Range("V" & (NewRowCounter + 3)) = "=+'Premiums Commissions'!B" & OldRowCounter & "" '=+'Premiums Commissions'!$B$2 NewRowCounter = NewRowCounter + 5 End With OldRowCounter = OldRowCounter + 1 Loop End With '///////////////////////////////////////////////////////// '////////////Grand Finale////////////////////////////////////////////// Columns("A:V").EntireColumn.AutoFit '///////////////////////////////////////////////////////// End Sub |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Newbie attempting VBA Scripting
The code looks code. Added some additional "WITH", cleaned up some - & ""
at ends of line, and added underscores which is a line continuation character. Posted code with long lines produce errors. Try taking your posted code and putting it back into VBA. You will see lots of problems because of the line wrapping. I also removed FormulaR1C1 from first lines of code. If these are named ranges then you can simply put the following: Range("B1") = Range("Unit") If you want the name range in the cell then your way is right. See my changes below. Sub AutoJV() '///Create New Sheet "Prem Comm JV" and Format 'It///////////////////////////////// Worksheets.Add after:=Sheets(Sheets.Count) Set NewSheet = ActiveSheet ActiveSheet.Name = "Prem Comm JV" Range("B1") = "Unit" Range("C1") = "Ledger" Range("D1") = "Account" Range("E1") = "Alt Account" Range("F1") = "Oper Unit" Range("G1") = "Dept ID" Range("H1") = "Currency" Range("I1") = "Amount" Range("J1") = "N/R" Range("K1") = "Rate Type" Range("L1") = "Rate" Range("M1") = "Base Amount" Range("N1") = "Stat" Range("O1") = "Stat Amount" Range("P1") = "Product" Range("Q1") = "Year" Range("R1") = "Mngt Code" Range("S1") = "Risk Loc" Range("T1") = "Function" Range("U1") = "Project" Range("V1") = "Affiliate" With Range("B1") .Borders(xlDiagonalDown).LineStyle = xlNone .Borders(xlDiagonalUp).LineStyle = xlNone .Borders(xlEdgeLeft).LineStyle = xlNone .Borders(xlEdgeTop).LineStyle = xlNone With .Borders(xlEdgeBottom) .LineStyle = xlContinuous .Weight = xlThick .ColorIndex = xlAutomatic End With .Borders(xlEdgeRight).LineStyle = xlNone .Font.Bold = True .Copy End With With Range("C1:V1") .PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False End With Application.CutCopyMode = False Columns("B:V").EntireColumn.AutoFit '///////////////////////////////////////////////////////// '///The Fun Stuff//////////////////////////////////////////////////// OldRowCounter = 2 NewRowCounter = 2 With Worksheets("Premiums Commissions") Do While .Range("A" & OldRowCounter) < "" UnitCol = .Range("D" & OldRowCounter) With NewSheet .Range("A" & NewRowCounter) = _ "Agents Bal - Affiliate Assumed" .Range("A" & (NewRowCounter + 1)) = _ "AWP - Affiliate" .Range("A" & (NewRowCounter + 2)) = _ "Asmd Cont Comm Res-Affil" .Range("A" & (NewRowCounter + 3)) = _ "Asmd Comm-Affiliate" .Range("B" & NewRowCounter) = UnitCol .Range("B" & (NewRowCounter + 1)) = UnitCol .Range("B" & (NewRowCounter + 2)) = UnitCol .Range("B" & (NewRowCounter + 3)) = UnitCol .Range("C" & NewRowCounter) = "CORE" .Range("C" & (NewRowCounter + 1)) = "CORE" .Range("C" & (NewRowCounter + 2)) = "CORE" .Range("C" & (NewRowCounter + 3)) = "CORE" .Range("D" & NewRowCounter) = "1270220076" .Range("D" & (NewRowCounter + 1)) = "3010620076" .Range("D" & (NewRowCounter + 2)) = "2144120076" .Range("D" & (NewRowCounter + 3)) = "6010620076" .Range("G" & NewRowCounter) = _ "=VLOOKUP('Premiums Commissions '!F" & _ OldRowCounter & ",'Dept Lookup'!A2:B65536,2,FALSE)" .Range("G" & (NewRowCounter + 1)) = _ "=VLOOKUP('Premiums Commissions '!F" & _ OldRowCounter & ",'Dept Lookup'!A2:B65536,2,FALSE)" .Range("G" & (NewRowCounter + 2)) = _ "=VLOOKUP('Premiums Commissions '!F" & _ OldRowCounter & ",'Dept Lookup'!A2:B65536,2,FALSE)" .Range("G" & (NewRowCounter + 3)) = _ "=VLOOKUP('Premiums Commissions '!F" & _ OldRowCounter & ",'Dept Lookup'!A2:B65536,2,FALSE)" .Range("H" & NewRowCounter) = _ "=+'Premiums Commissions'!I" & OldRowCounter .Range("H" & (NewRowCounter + 1)) = _ "=+'Premiums Commissions '!G" & OldRowCounter .Range("H" & (NewRowCounter + 2)) = _ "=+'Premiums Commissions '!I" & OldRowCounter .Range("H" & (NewRowCounter + 3)) = _ "=+'Premiums Commissions '!G" & OldRowCounter .Range("I" & NewRowCounter) = _ "=+'Premiums Commissions'!J" & OldRowCounter .Range("I" & NewRowCounter).NumberFormat = _ "#,##0.00_);[Red](#,##0.00)" .Range("I" & (NewRowCounter + 1)) = _ "=-'Premiums Commissions '!H" & OldRowCounter .Range("I" & (NewRowCounter + 1)).NumberFormat = _ "#,##0.00_);[Red](#,##0.00)" .Range("I" & (NewRowCounter + 2)) = _ "=+'Premiums Commissions '!N" & OldRowCounter .Range("I" & (NewRowCounter + 2)).NumberFormat = _ "#,##0.00_);[Red](#,##0.00)" .Range("I" & (NewRowCounter + 3)) = _ "=-'Premiums Commissions '!L" & OldRowCounter .Range("I" & (NewRowCounter + 3)).NumberFormat = _ "#,##0.00_);[Red](#,##0.00)" .Range("M" & NewRowCounter) = _ "=+'Premiums Commissions'!K" & OldRowCounter .Range("M" & NewRowCounter).NumberFormat = _ "#,##0.00_);[Red](#,##0.00)" .Range("M" & (NewRowCounter + 1)) = _ "=-'Premiums Commissions '!K" & OldRowCounter .Range("M" & (NewRowCounter + 1)).NumberFormat = _ "#,##0.00_);[Red](#,##0.00)" .Range("M" & (NewRowCounter + 2)) = _ "=+'Premiums Commissions '!M" & OldRowCounter .Range("M" & (NewRowCounter + 2)).NumberFormat = _ "#,##0.00_);[Red](#,##0.00)" .Range("M" & (NewRowCounter + 3)) = _ "=-'Premiums Commissions '!M" & OldRowCounter .Range("M" & (NewRowCounter + 2)).NumberFormat = _ "#,##0.00_);[Red](#,##0.00)" .Range("P" & NewRowCounter) = _ "=VLOOKUP('Premiums Commissions '!F" & _ OldRowCounter & ",'Product Lookup'!A2:B65536,2,FALSE)" .Range("P" & (NewRowCounter + 1)) = _ "=VLOOKUP('Premiums Commissions '!F" & _ OldRowCounter & ",'Product Lookup'!A2:B65536,2,FALSE)" .Range("P" & (NewRowCounter + 2)) = _ "=VLOOKUP('Premiums Commissions '!F" & _ OldRowCounter & ",'Product Lookup'!A2:B65536,2,FALSE)" .Range("P" & (NewRowCounter + 3)) = _ "=VLOOKUP('Premiums Commissions '!F" & _ OldRowCounter & ",'Product Lookup'!A2:B65536,2,FALSE)" .Range("R" & NewRowCounter) = _ "=VLOOKUP('Premiums Commissions '!F" & _ OldRowCounter & ",'MCC Lookup'!A2:B65536,2,FALSE)" .Range("R" & (NewRowCounter + 1)) = _ "=VLOOKUP('Premiums Commissions '!F" & _ OldRowCounter & ",'MCC Lookup'!A2:B65536,2,FALSE)" .Range("R" & (NewRowCounter + 2)) = _ "=VLOOKUP('Premiums Commissions '!F" & _ OldRowCounter & ",'MCC Lookup'!A2:B65536,2,FALSE)" .Range("R" & (NewRowCounter + 3)) = _ "=VLOOKUP('Premiums Commissions '!F" & _ OldRowCounter & ",'MCC Lookup'!A2:B65536,2,FALSE)" .Range("S" & NewRowCounter) = _ "=VLOOKUP('Premiums Commissions '!F" & _ OldRowCounter & ",'Risk Loc Lookup'!A2:B65536,2,FALSE)" .Range("S" & (NewRowCounter + 1)) = _ "=VLOOKUP('Premiums Commissions '!F" & _ OldRowCounter & ",'Risk Loc Lookup'!A2:B65536,2,FALSE)" .Range("S" & (NewRowCounter + 2)) = _ "=VLOOKUP('Premiums Commissions '!F" & _ OldRowCounter & ",'Risk Loc Lookup'!A2:B65536,2,FALSE)" .Range("S" & (NewRowCounter + 3)) = _ "=VLOOKUP('Premiums Commissions '!F" & _ OldRowCounter & ",'Risk Loc Lookup'!A2:B65536,2,FALSE)" .Range("V" & NewRowCounter) = _ "=+'Premiums Commissions'!B" & OldRowCounter .Range("V" & (NewRowCounter + 1)) = _ "=+'Premiums Commissions '!B" & OldRowCounter .Range("V" & (NewRowCounter + 2)) = _ "=+'Premiums Commissions '!B" & OldRowCounter .Range("V" & (NewRowCounter + 3)) = _ "=+'Premiums Commissions '!B" & _ OldRowCounter & "'=+'Premiums Commissions'!$B$2" NewRowCounter = NewRowCounter + 5 End With OldRowCounter = OldRowCounter + 1 Loop End With '///////////////////////////////////////////////////////// '////////////Grand 'Finale////////////////////////////////////////////// Columns("A:V").EntireColumn.AutoFit '///////////////////////////////////////////////////////// End Su " wrote: Well heres the macro i coded, it works like a charm, but its really really messy im sure. Any suggestions for cleaning it up/ making it less specific to THIS EXACT Layout and file etc. Like can i have it look for certain column names and then figure out their position, instead of just assigning their position and having the macro assume they are there and error if they arent. Anyway, here it is. Thanks for your help Joel. Sub AutoJV() '///Create New Sheet "Prem Comm JV" and Format It///////////////////////////////// Worksheets.Add after:=Sheets(Sheets.Count) Set NewSheet = ActiveSheet ActiveSheet.Name = "Prem Comm JV" Range("B1").FormulaR1C1 = "Unit" Range("C1").FormulaR1C1 = "Ledger" Range("D1").FormulaR1C1 = "Account" Range("E1").FormulaR1C1 = "Alt Account" Range("F1").FormulaR1C1 = "Oper Unit" Range("G1").FormulaR1C1 = "Dept ID" Range("H1").FormulaR1C1 = "Currency" Range("I1").FormulaR1C1 = "Amount" Range("J1").FormulaR1C1 = "N/R" Range("K1").FormulaR1C1 = "Rate Type" Range("L1").FormulaR1C1 = "Rate" Range("M1").FormulaR1C1 = "Base Amount" Range("N1").FormulaR1C1 = "Stat" Range("O1").FormulaR1C1 = "Stat Amount" Range("P1").FormulaR1C1 = "Product" Range("Q1").FormulaR1C1 = "Year" Range("R1").FormulaR1C1 = "Mngt Code" Range("S1").FormulaR1C1 = "Risk Loc" Range("T1").FormulaR1C1 = "Function" Range("U1").FormulaR1C1 = "Project" Range("V1").FormulaR1C1 = "Affiliate" Range("B1").Select Selection.Borders(xlDiagonalDown).LineStyle = xlNone Selection.Borders(xlDiagonalUp).LineStyle = xlNone Selection.Borders(xlEdgeLeft).LineStyle = xlNone Selection.Borders(xlEdgeTop).LineStyle = xlNone With Selection.Borders(xlEdgeBottom) .LineStyle = xlContinuous .Weight = xlThick .ColorIndex = xlAutomatic End With Selection.Borders(xlEdgeRight).LineStyle = xlNone Selection.Font.Bold = True Selection.Copy Range("C1:V1").Select Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False Application.CutCopyMode = False Columns("B:V").EntireColumn.AutoFit '///////////////////////////////////////////////////////// '///The Fun Stuff//////////////////////////////////////////////////// OldRowCounter = 2 NewRowCounter = 2 With Worksheets("Premiums Commissions") Do While .Range("A" & OldRowCounter) < "" UnitCol = .Range("D" & OldRowCounter) With NewSheet .Range("A" & NewRowCounter) = "Agents Bal - Affiliate Assumed" .Range("A" & (NewRowCounter + 1)) = "AWP - Affiliate" .Range("A" & (NewRowCounter + 2)) = "Asmd Cont Comm Res- Affil" .Range("A" & (NewRowCounter + 3)) = "Asmd Comm-Affiliate" .Range("B" & NewRowCounter) = UnitCol .Range("B" & (NewRowCounter + 1)) = UnitCol .Range("B" & (NewRowCounter + 2)) = UnitCol .Range("B" & (NewRowCounter + 3)) = UnitCol .Range("C" & NewRowCounter) = "CORE" .Range("C" & (NewRowCounter + 1)) = "CORE" .Range("C" & (NewRowCounter + 2)) = "CORE" .Range("C" & (NewRowCounter + 3)) = "CORE" .Range("D" & NewRowCounter) = "1270220076" .Range("D" & (NewRowCounter + 1)) = "3010620076" .Range("D" & (NewRowCounter + 2)) = "2144120076" .Range("D" & (NewRowCounter + 3)) = "6010620076" .Range("G" & NewRowCounter) = "=VLOOKUP('Premiums Commissions'!F" & OldRowCounter & ",'Dept Lookup'!A2:B65536,2,FALSE)" .Range("G" & (NewRowCounter + 1)) = "=VLOOKUP('Premiums Commissions'!F" & OldRowCounter & ",'Dept Lookup'!A2:B65536,2,FALSE)" .Range("G" & (NewRowCounter + 2)) = "=VLOOKUP('Premiums Commissions'!F" & OldRowCounter & ",'Dept Lookup'!A2:B65536,2,FALSE)" .Range("G" & (NewRowCounter + 3)) = "=VLOOKUP('Premiums Commissions'!F" & OldRowCounter & ",'Dept Lookup'!A2:B65536,2,FALSE)" .Range("H" & NewRowCounter) = "=+'Premiums Commissions'!I" & OldRowCounter & "" .Range("H" & (NewRowCounter + 1)) = "=+'Premiums Commissions'!G" & OldRowCounter & "" .Range("H" & (NewRowCounter + 2)) = "=+'Premiums Commissions'!I" & OldRowCounter & "" .Range("H" & (NewRowCounter + 3)) = "=+'Premiums Commissions'!G" & OldRowCounter & "" .Range("I" & NewRowCounter) = "=+'Premiums Commissions'!J" & OldRowCounter & "" .Range("I" & NewRowCounter).NumberFormat = "#,##0.00_); [Red](#,##0.00)" .Range("I" & (NewRowCounter + 1)) = "=-'Premiums Commissions'!H" & OldRowCounter & "" .Range("I" & (NewRowCounter + 1)).NumberFormat = "#,##0.00_);[Red](#,##0.00)" .Range("I" & (NewRowCounter + 2)) = "=+'Premiums Commissions'!N" & OldRowCounter & "" .Range("I" & (NewRowCounter + 2)).NumberFormat = "#,##0.00_);[Red](#,##0.00)" .Range("I" & (NewRowCounter + 3)) = "=-'Premiums Commissions'!L" & OldRowCounter & "" .Range("I" & (NewRowCounter + 3)).NumberFormat = "#,##0.00_);[Red](#,##0.00)" .Range("M" & NewRowCounter) = "=+'Premiums Commissions'!K" & OldRowCounter & "" .Range("M" & NewRowCounter).NumberFormat = "#,##0.00_); [Red](#,##0.00)" .Range("M" & (NewRowCounter + 1)) = "=-'Premiums Commissions'!K" & OldRowCounter & "" .Range("M" & (NewRowCounter + 1)).NumberFormat = "#,##0.00_);[Red](#,##0.00)" .Range("M" & (NewRowCounter + 2)) = "=+'Premiums Commissions'!M" & OldRowCounter & "" .Range("M" & (NewRowCounter + 2)).NumberFormat = "#,##0.00_);[Red](#,##0.00)" .Range("M" & (NewRowCounter + 3)) = "=-'Premiums Commissions'!M" & OldRowCounter & "" .Range("M" & (NewRowCounter + 2)).NumberFormat = "#,##0.00_);[Red](#,##0.00)" .Range("P" & NewRowCounter) = "=VLOOKUP('Premiums Commissions'!F" & OldRowCounter & ",'Product Lookup'! A2:B65536,2,FALSE)" .Range("P" & (NewRowCounter + 1)) = "=VLOOKUP('Premiums Commissions'!F" & OldRowCounter & ",'Product Lookup'! A2:B65536,2,FALSE)" .Range("P" & (NewRowCounter + 2)) = "=VLOOKUP('Premiums Commissions'!F" & OldRowCounter & ",'Product Lookup'! A2:B65536,2,FALSE)" .Range("P" & (NewRowCounter + 3)) = "=VLOOKUP('Premiums Commissions'!F" & OldRowCounter & ",'Product Lookup'! A2:B65536,2,FALSE)" .Range("R" & NewRowCounter) = "=VLOOKUP('Premiums Commissions'!F" & OldRowCounter & ",'MCC Lookup'!A2:B65536,2,FALSE)" .Range("R" & (NewRowCounter + 1)) = "=VLOOKUP('Premiums Commissions'!F" & OldRowCounter & ",'MCC Lookup'!A2:B65536,2,FALSE)" .Range("R" & (NewRowCounter + 2)) = "=VLOOKUP('Premiums Commissions'!F" & OldRowCounter & ",'MCC Lookup'!A2:B65536,2,FALSE)" .Range("R" & (NewRowCounter + 3)) = "=VLOOKUP('Premiums Commissions'!F" & OldRowCounter & ",'MCC Lookup'!A2:B65536,2,FALSE)" .Range("S" & NewRowCounter) = "=VLOOKUP('Premiums Commissions'!F" & OldRowCounter & ",'Risk Loc Lookup'! A2:B65536,2,FALSE)" .Range("S" & (NewRowCounter + 1)) = "=VLOOKUP('Premiums Commissions'!F" & OldRowCounter & ",'Risk Loc Lookup'! A2:B65536,2,FALSE)" .Range("S" & (NewRowCounter + 2)) = "=VLOOKUP('Premiums Commissions'!F" & OldRowCounter & ",'Risk Loc Lookup'! A2:B65536,2,FALSE)" .Range("S" & (NewRowCounter + 3)) = "=VLOOKUP('Premiums Commissions'!F" & OldRowCounter & ",'Risk Loc Lookup'! A2:B65536,2,FALSE)" .Range("V" & NewRowCounter) = "=+'Premiums Commissions'!B" & OldRowCounter & "" .Range("V" & (NewRowCounter + 1)) = "=+'Premiums Commissions'!B" & OldRowCounter & "" .Range("V" & (NewRowCounter + 2)) = "=+'Premiums Commissions'!B" & OldRowCounter & "" .Range("V" & (NewRowCounter + 3)) = "=+'Premiums Commissions'!B" & OldRowCounter & "" '=+'Premiums Commissions'!$B$2 NewRowCounter = NewRowCounter + 5 End With OldRowCounter = OldRowCounter + 1 Loop End With '///////////////////////////////////////////////////////// '////////////Grand Finale////////////////////////////////////////////// Columns("A:V").EntireColumn.AutoFit '///////////////////////////////////////////////////////// End Sub |
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Newbie attempting VBA Scripting
Joel,
Yep, i noted your changes. Thanks for those. Any ideas on what i mentioned before? About having the macro look for a certain column name, and adjust its understanding of the column location accordingly. This meaning that the macro would be smart enough (with a little help from me) to know what kind of numbers to expect in a column, and what to use them for, instead of just blindly working to column values that the macro assumes do not change. I may have columns added, or removed in the source file, anyway around that? Thanks. -Pogster |
|
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Newbie attempting VBA Scripting
There are a few ways of defining and search for columns names.
Method 1 Define a name on the worksheet by going into Insert-Names-Define You can label cell C1 as Total then in VBA column = Range("Total").column This method is proably the best becuase when you add and delete cells the define name will change Method 2 Set c = Rows(1).Find(what:="Total",lookin:=xlvalues) if not c is nothing then 'this take a while to really understand mycol = c.column end if Method 3 Define constants in you code for diffferent columns Const TotalCol = "C" Range(TotalCol & RowCount) = "abc" " wrote: Joel, Yep, i noted your changes. Thanks for those. Any ideas on what i mentioned before? About having the macro look for a certain column name, and adjust its understanding of the column location accordingly. This meaning that the macro would be smart enough (with a little help from me) to know what kind of numbers to expect in a column, and what to use them for, instead of just blindly working to column values that the macro assumes do not change. I may have columns added, or removed in the source file, anyway around that? Thanks. -Pogster |
|
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Newbie attempting VBA Scripting
Joel,
I like method 2 the best since it searches for the exact name of the column and adjusts its stuff from there. I wrote a quick script which uses your method, but when it finds the column location, it is stored as a number, instead of a letter. Is there a way to change that within the function, or will i have to adjust the rest of my previous macro to work with R1C1 naming? Here is my test macro: Sub Test1() Set Marco = Rows(1).Find(what:="Marco", LookIn:=xlValues) If Not Marco Is Nothing Then mycol = Marco.Column ActiveSheet.Range("B3") = mycol End If End Sub Obviously this just returns the location of "Marco" into column B3, in my example Marco was in Column I, which returned a 9. How do i get it to return a column Letter instead? So i can easily use this with my previous macro, which is based on the "A1" cell naming convention. Thanks again Joel. -Pogster |
|
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Newbie attempting VBA Scripting
If it returned a Letter, then i could use it for something like
With Newsheet .Range(mycol,OldRowCount) = "w/e" End With That is what i am trying to get at. Thanks again. -Pogster |
|
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Newbie attempting VBA Scripting
Sub Test1()
Set Marco = Rows(1).Find(what:="Marco", LookIn:=xlValues) If Not Marco Is Nothing Then myaddress = mid(Marco.address,2) mycol = left(myaddress,instr(myaddress,"$") - 1) ActiveSheet.Range("B3") = mycol End If End Sub " wrote: Joel, I like method 2 the best since it searches for the exact name of the column and adjusts its stuff from there. I wrote a quick script which uses your method, but when it finds the column location, it is stored as a number, instead of a letter. Is there a way to change that within the function, or will i have to adjust the rest of my previous macro to work with R1C1 naming? Here is my test macro: Sub Test1() Set Marco = Rows(1).Find(what:="Marco", LookIn:=xlValues) If Not Marco Is Nothing Then mycol = Marco.Column ActiveSheet.Range("B3") = mycol End If End Sub Obviously this just returns the location of "Marco" into column B3, in my example Marco was in Column I, which returned a 9. How do i get it to return a column Letter instead? So i can easily use this with my previous macro, which is based on the "A1" cell naming convention. Thanks again Joel. -Pogster |
|
#17
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Newbie attempting VBA Scripting
No! You have 2 choices. Most people use cells and work with column numbers
instead of letters. CELLS will except eith a letter or number for a column. 1) Range(mycol & OldRowCount) 2) Cells(OldRowCount,mycol) where mycol can either be a number of a column " wrote: If it returned a Letter, then i could use it for something like With Newsheet .Range(mycol,OldRowCount) = "w/e" End With That is what i am trying to get at. Thanks again. -Pogster |
|
#18
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Newbie attempting VBA Scripting
Ah, but the Range function only accepts Letters. So i could use the
simpler code and change all of my .Range functions to .Cell functions, since im not actually using them as ranges anyway. What is best do you think? To work with mycol as a number and use the .Cell function, or have lots of extra code just to use the .Range function. By the way, thanks for your fast response! -Pogster |
|
#19
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Newbie attempting VBA Scripting
Note: cells has an S at the end.
I recently switched from using CELLS to using RANGE. I thought for helping people on Postings that A1 formating was easier to understand and use. I still use CELLS when I need to move across columns such as total = 0 for ColCount = 2 to 10 total = total + cells(2,ColCount) next ColCount " wrote: Ah, but the Range function only accepts Letters. So i could use the simpler code and change all of my .Range functions to .Cell functions, since im not actually using them as ranges anyway. What is best do you think? To work with mycol as a number and use the .Cell function, or have lots of extra code just to use the .Range function. By the way, thanks for your fast response! -Pogster |
|
#20
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Newbie attempting VBA Scripting
One more tip. You can have a combination of Range and cells
set newrange = range(range("A1"), cells(5,10)) which is different from set newrange = range("A1", cells(5,10)) " wrote: Ah, but the Range function only accepts Letters. So i could use the simpler code and change all of my .Range functions to .Cell functions, since im not actually using them as ranges anyway. What is best do you think? To work with mycol as a number and use the .Cell function, or have lots of extra code just to use the .Range function. By the way, thanks for your fast response! -Pogster |
|
#21
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Newbie attempting VBA Scripting
Joel,
Im having some trouble implementing the code. I can't seem to get it to work with the following code, what am i doing wrong? Sub VariableColumns() Application.ScreenUpdating = False Worksheets.Add after:=Sheets(Sheets.Count) Set NewSheet = ActiveSheet ActiveSheet.Name = "Adjust Test" With Worksheets("Premiums Commissions") Set BU = Rows(1).Find(what:="Affiliate Code", LookIn:=xlValues) 'Finds the location of the column If Not BU Is Nothing Then 'If the value "BU" is found...then myaddress = Mid(BU.Address, 2) BUcol = Left(myaddress, InStr(myaddress, "$") - 1) 'sets the location to a variable BUcol NewSheet.Range("B3") = BUcol NewSheet.Range("B4") = "1" End If NewSheet.Range("B5") = "2" End With End Sub The other worksheet has that text in row1 formatted as either general, or as text (does it matter?) So what is wrong with my code? Thanks Joel. -Pogster |
|
#22
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Newbie attempting VBA Scripting
I think i figured it out. The With function is not enough for the Set
function to use the sheet specified by the With function. I.E., if i include the line: Sheets("Premiums Commissions").Select before the "Set BU = Rows(1)..." then it works like a charm. I guess it only works on the ActiveSheet? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Attempting to weight | Excel Discussion (Misc queries) | |||
| Attempting to use VBA to Ping within Excel... | Excel Programming | |||
| Attempting to reduce userforms | Excel Programming | |||
| Attempting Excel Template | New Users to Excel | |||
| Newbie VBE scripting question | Excel Programming | |||
Linear Mode
