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.....
Please Login or Register to view this content.
Try Next i instead of only Next
May this help
Regards
Jose Luis
I did that and I also changed the value that the LaRow looked at from "A" to "D" because A was not a good value. I love when I look at something for like an hour and then as soon as I post I figure out where I messed up. Thanks for the help
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" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>
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))
"DKY" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>
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
--------------------
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" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>
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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>>
>> 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
>>
>
>
Thank you Patti, I'm going to have to really look these last few posts over, you gave me a lot of great information.
use Formula instead of FormulaR1C1 when you use A1 style referencing.
Sorry if that wasn't clear in my first answer.
--
Regards,
Tom Ogilvy
"DKY" <[email protected]> wrote in message
news:[email protected]...
>
> 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
))
>
>
> --
> DKY
> ------------------------------------------------------------------------
> DKY's Profile:
http://www.excelforum.com/member.php...o&userid=14515
> View this thread: http://www.excelforum.com/showthread...hreadid=392640
>
Tom, thanks. That makes sense. It works now, that helped. LOL
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks