I can't figure out why my For Next isn't working. I will post all of my
code but its kinda long. It does the first part when I run the macro
but then it doesn't fill in all the cells. It just stops at cell AE2
which is what makes me think that the For Next isn't working. Please
tell me what I'm doing wrong.....


Code:
--------------------
Public Sub surplus()
'
Dim LaRow As Long
Dim rng As Range

' START CREATE HEADERS
Range("AE1").Select
ActiveCell.FormulaR1C1 = "YR IN"
Range("AF1").Select
ActiveCell.FormulaR1C1 = "TOTAL AVL"
Range("AG1").Select
ActiveCell.FormulaR1C1 = "YR 1 DEM * 2"
Range("AH1").Select
ActiveCell.FormulaR1C1 = "DIFFERENCE"
Range("AI1").Select
ActiveCell.FormulaR1C1 = "770 AVL"
Range("AJ1").Select
ActiveCell.FormulaR1C1 = "772 AVL"
Range("AK1").Select
ActiveCell.FormulaR1C1 = "776 AVL"
Range("AL1").Select
ActiveCell.FormulaR1C1 = "777 AVL"
Range("AM1").Select
ActiveCell.FormulaR1C1 = "781 AVL"
Range("AN1").Select
ActiveCell.FormulaR1C1 = "970 AVL"
Range("AO1").Select
ActiveCell.FormulaR1C1 = "981 AVL"
Range("AE1:AO1").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Interior
.ColorIndex = 40
.Pattern = xlSolid
End With
' END CREATE HEADERS
' START INPUT VLOOKUPS
LaRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = LaRow To 2 Step -1
Set rng = Range("a" & i)
If rng.Value < "" Then
Range("AE" & i).Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-27],kickoutrange,6,FALSE)"
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
'########
Range("AF" & i).Select
ActiveCell.FormulaR1C1 = _
"=(VLOOKUP(D" & i & ",kickoutrange,206,FALSE))+(VLOOKUP(D" & i & ",kickoutrange,238,FALSE))"
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
'########
Range("AG" & i).Select
ActiveCell.FormulaR1C1 = "=(VLOOKUP(D" & i & ",kickoutrange,100,FALSE))*2"
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
'########
Range("AH" & i).Select
ActiveCell.FormulaR1C1 = "=AF" & i & "-AG" & i & ""
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
'########
Range("AI" & i).Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(D" & i & ",kickoutrange,142,FALSE)"
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
'########
Range("AJ" & i).Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(D" & i & ",kickoutrange,174,FALSE)"
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
'########
Range("AK" & i).Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(D" & i & ",kickoutrange,134,FALSE)"
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
'########
Range("AL" & i).Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(D" & i & ",kickoutrange,150,FALSE)"
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
'########
Range("AM" & i).Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(D" & i & ",kickoutrange,166,FALSE)"
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
'########
Range("AN" & i).Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(D" & i & ",kickoutrange,214,FALSE)"
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
'########
Range("AO" & i).Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(D" & i & ",kickoutrange,222,FALSE)"

Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
'########
End If
Next
' END INPUT VLOOKUPS
Range("AE1:AO2").Select
Application.CutCopyMode = False
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Columns("AF:AO").EntireColumn.AutoFit
Range("AE2").Select
End Sub

--------------------


--
DKY
------------------------------------------------------------------------
DKY's Profile: http://www.excelforum.com/member.php...o&userid=14515
View this thread: http://www.excelforum.com/showthread...hreadid=392640