Thread: Newbie attempting VBA Scripting
View Single Post
  #11   Report Post  
Posted to microsoft.public.excel.programming
joel joel is offline
external usenet poster
  
Posts: 9,101
Default 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

Reply With QuoteReply With Quote