ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   For Next help? I can't figure this out. (https://www.excelbanter.com/excel-programming/336291-next-help-i-cant-figure-out.html)

DKY[_47_]
For Next help? I can't figure this out.
 

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

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

Try Next i instead of only Next

May this help

Regards

Jose Luis


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

DKY[_48_]
For Next help? I can't figure this out.
 

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

--
DK
-----------------------------------------------------------------------
DKY's Profile: http://www.excelforum.com/member.php...fo&userid=1451
View this thread: http://www.excelforum.com/showthread.php?threadid=39264

Tom Ogilvy
For Next help? I can't figure this out.
 
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" wrote in message
...

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

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

"DKY" wrote in message
...

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
--------------------

DKY[_49_]
For Next help? I can't figure this out.
 

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

Patti[_2_]
For Next help? I can't figure this out.
 
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" wrote in message
...

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

Patti[_2_]
For Next help? I can't figure this out.
 
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" wrote in message
...
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" wrote in message
...

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

DKY[_50_]
For Next help? I can't figure this out.
 

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

--
DK
-----------------------------------------------------------------------
DKY's Profile: http://www.excelforum.com/member.php...fo&userid=1451
View this thread: http://www.excelforum.com/showthread.php?threadid=39264

Tom Ogilvy
For Next help? I can't figure this out.
 
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" wrote in message
...

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

DKY[_59_]
For Next help? I can't figure this out.
 

Tom, thanks. That makes sense. It works now, that helped. LO

--
DK
-----------------------------------------------------------------------
DKY's Profile: http://www.excelforum.com/member.php...fo&userid=1451
View this thread: http://www.excelforum.com/showthread.php?threadid=39264


All times are GMT +1. The time now is 05:52 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com