For Next help? I can't figure this out.

D

DKY

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
 
D

DKY

I did that and I also changed the value that the LaRow looked at fro
"A" to "D" because A was not a good value. I love when I look a
something for like an hour and then as soon as I post I figure ou
where I messed up. Thanks for the hel
 
T

Tom Ogilvy

You are using A1 referencing in a formula string and assigning it using
FormulaR1C1.

In my test, this produces a formula like:

=(VLOOKUP('D2',kickoutrange,206,FALSE))+(VLOOKUP('D2',kickoutrange,238,FALSE
))
which produces #Name

you would then copy this and paste special values, so it would hold an error
constant.

I don't know if that is your only problem or not.

--
Regards,
Tom Ogilvy



DKY said:
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
Click to expand...
 
J

Jef Gorbach

DKY said:
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
Click to expand...

Simplified your code to make it easier to read (and perchance a little
faster) then changed how LaRow is found and "next" to "next i"; neither of
which should make any meaningful difference because a first glance the loop
seems valid.

Public Sub surplus()
Dim LaRow As Long
'START CREATE HEADERS
'btw, you dont need to .select -- just do the action.
Range("AE1").FormulaR1C1 = "YR IN"
Range("AF1").FormulaR1C1 = "TOTAL AVL"
Range("AG1").FormulaR1C1 = "YR 1 DEM * 2"
Range("AH1").FormulaR1C1 = "DIFFERENCE"
Range("AI1").FormulaR1C1 = "770 AVL"
Range("AJ1").FormulaR1C1 = "772 AVL"
Range("AK1").FormulaR1C1 = "776 AVL"
Range("AL1").FormulaR1C1 = "777 AVL"
Range("AM1").FormulaR1C1 = "781 AVL"
Range("AN1").FormulaR1C1 = "970 AVL"
Range("AO1").FormulaR1C1 = "981 AVL"
With Range("AE1:AO1")
.Borders.LineStyle = xlContinuous
.Borders.Weight = xlThin
.Borders.ColorIndex = xlAutomatic
.Interior.ColorIndex = 40
.Interior.Pattern = xlSolid
End With
'END CREATE HEADERS

'START INPUT VLOOKUPS
'LaRow = Cells(Rows.Count, "A").End(xlUp).Row
LaRow = range("A65536").end(xlup).row 'should not make a difference but
worth a shot
For i = LaRow To 2 Step -1
If Range("a" & i).Value < "" Then
Range("AE" & i).FormulaR1C1 =
"=VLOOKUP(RC[-27],kickoutrange,6,FALSE)"
Range("AF" & i).FormulaR1C1 = "=(VLOOKUP(D" & i &
",kickoutrange,206,FALSE))+(VLOOKUP(D" & i & ",kickoutrange,238,FALSE))"
Range("AG" & i).FormulaR1C1 = "=(VLOOKUP(D" & i &
",kickoutrange,100,FALSE))*2"
Range("AH" & i).FormulaR1C1 = "=AF" & i & "-AG" & i & ""
Range("AI" & i).FormulaR1C1 = "=VLOOKUP(D" & i &
",kickoutrange,142,FALSE)"
Range("AJ" & i).FormulaR1C1 = "=VLOOKUP(D" & i &
",kickoutrange,174,FALSE)"
Range("AK" & i).FormulaR1C1 = "=VLOOKUP(D" & i &
",kickoutrange,134,FALSE)"
Range("AL" & i).FormulaR1C1 = "=VLOOKUP(D" & i &
",kickoutrange,150,FALSE)"
Range("AM" & i).FormulaR1C1 = "=VLOOKUP(D" & i &
",kickoutrange,166,FALSE)"
Range("AN" & i).FormulaR1C1 = "=VLOOKUP(D" & i &
",kickoutrange,214,FALSE)"
Range("AO" & i).FormulaR1C1 = "=VLOOKUP(D" & i &
",kickoutrange,222,FALSE)"
'########
With Range("AE" & i & ":AO" & i).Select
Selection.Copy
Selection.PasteSpecial _
Paste:=xlPasteValues, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=False
End With
End If
Next i
'END INPUT VLOOKUPS

With Range("AE1:AO2")
.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
--------------------
 
D

DKY

already solved, thanks but I was wondering. How do I get rid of those
single quotes around both D2's in the output?
=(VLOOKUP('D2',kickoutrange,206,FALSE))+(VLOOKUP('D2',kickoutrange,238,FALSE))
 
P

Patti

Not exactly related to your question, but... you don't always need to use
Select. You could use:

Range("AE1").FormulaR1C1 = "YR IN"
Range("AF1").FormulaR1C1 = "TOTAL AVL"
Range("AG1").FormulaR1C1 = "YR 1 DEM * 2"

or

Range("AE1").Value = "YR IN"
Range("AF1").Value = "TOTAL AVL"
Range("AG1").Value = "YR 1 DEM * 2"

After you get the results from your formulas, instead of using this on each
line:

Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks
_
:=False, Transpose:=False

You use may be able to use something like this at the end of your sub:

columns("AE:AO").Value = columns("AE:AO").Value

Ron de Bruin has info on changing formulas to values at
http://www.rondebruin.nl/values.htm .


For setting your borders, this seems to do it:

Sub Borders()
With Range("AE1:AO1").Cells.Borders
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 1
End With

With Range("AE1:AO1").Cells.Interior
.ColorIndex = 40
.Pattern = xlSolid
End With
End Sub

Rather than loop through your range, you could insert the formula in this
type of manner:

LaRow = Cells(Rows.Count, "A").End(xlUp).Row

Range("AF2").Formula =
"=(VLOOKUP($D2,kickoutrange,206,FALSE))+(VLOOKUP($D2,kickoutrange,238,False))
Set sourceRange = Worksheets("YourWorksheet").Range("AF2")
Set fillRange = Worksheets("YourWorksheet").Range("AF:AF" & LaRow)
sourceRange.AutoFill Destination:=fillRange
Columns("AF:AF").Value = Columns("AF:AF").Value

Regards,

Patti

DKY said:
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
Click to expand...
 
P

Patti

I should have mentioned that if you fill the formula to the used range as I
mentioned, you can nest it in IF(ISERR... to account for the instances where
column A doesn't have a value.

I also noticed that I forgot the " at the end of the formula.



Patti said:
Not exactly related to your question, but... you don't always need to use
Select. You could use:

Range("AE1").FormulaR1C1 = "YR IN"
Range("AF1").FormulaR1C1 = "TOTAL AVL"
Range("AG1").FormulaR1C1 = "YR 1 DEM * 2"

or

Range("AE1").Value = "YR IN"
Range("AF1").Value = "TOTAL AVL"
Range("AG1").Value = "YR 1 DEM * 2"

After you get the results from your formulas, instead of using this on
each line:

Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks
_
:=False, Transpose:=False

You use may be able to use something like this at the end of your sub:

columns("AE:AO").Value = columns("AE:AO").Value

Ron de Bruin has info on changing formulas to values at
http://www.rondebruin.nl/values.htm .


For setting your borders, this seems to do it:

Sub Borders()
With Range("AE1:AO1").Cells.Borders
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 1
End With

With Range("AE1:AO1").Cells.Interior
.ColorIndex = 40
.Pattern = xlSolid
End With
End Sub

Rather than loop through your range, you could insert the formula in this
type of manner:

LaRow = Cells(Rows.Count, "A").End(xlUp).Row

Range("AF2").Formula =
"=(VLOOKUP($D2,kickoutrange,206,FALSE))+(VLOOKUP($D2,kickoutrange,238,False))
Set sourceRange = Worksheets("YourWorksheet").Range("AF2")
Set fillRange = Worksheets("YourWorksheet").Range("AF:AF" & LaRow)
sourceRange.AutoFill Destination:=fillRange
Columns("AF:AF").Value = Columns("AF:AF").Value

Regards,

Patti

DKY said:
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?action=getinfo&userid=14515
View this thread:
http://www.excelforum.com/showthread.php?threadid=392640
Click to expand...
Click to expand...
 
D

DKY

Thank you Patti, I'm going to have to really look these last few post
over, you gave me a lot of great information
 
T

Tom Ogilvy

use Formula instead of FormulaR1C1 when you use A1 style referencing.

Sorry if that wasn't clear in my first answer.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Run-time error 1004 paste method of worksheet class failed 3
Simplify Code for Copy/Paste Special 11
= Today()+1 into Sheet1, Sheet2, and Sheet3 2
Advanced Filter works in recorder but not while running macro 5
Variable not defined for "number of trials" ? 3
File Name changes with dates and Deleting Rows of Zeros 0
Totals An Easier Way?? 5
wrong code 1

Top