Continue formula until reaching a blank cell.
 

I have 3 formulas that need to be repeated in column U (dependant on the cost
type) as long as there is information in column E. One of the formulas I
have in Column U is: =IF(V" & RowCount & "<AA" & RowCount & _
",AA" & RowCount & "*AG" & RowCount & ",MAX(K" & RowCount & _
",N" & RowCount & ",AA" & RowCount & "*AG" & RowCount & "))".

Would I need to put another =IF in front of this code to have it proceed and
run this formula until it encounters a blank cell in column E?

It's my last little bit on this MONSTER! Help anyone ??

Continue formula until reaching a blank cell.
 

Dear Alberta

Try the below.

RowCount = 1
'I am not sure what row count is at the beginning. change to suit
'Loop Until E is empty
Do While Range("E" & RowCount) < ""

'put your formulas 1,2,3 inside the loop
'Range("U" & RowCount) = "=IF(V" & RowCount & "<AA" & RowCount & _
",AA" & RowCount & "*AG" & RowCount & ",MAX(K" & RowCount & _
",N" & RowCount & ",AA" & RowCount & "*AG" & RowCount & "))"

RowCount = RowCount + 1
Loop

If this post helps click Yes
---------------
Jacob Skaria


"Alberta Rose" wrote:

I have 3 formulas that need to be repeated in column U (dependant on the cost
type) as long as there is information in column E. One of the formulas I
have in Column U is: =IF(V" & RowCount & "<AA" & RowCount & _
",AA" & RowCount & "*AG" & RowCount & ",MAX(K" & RowCount & _
",N" & RowCount & ",AA" & RowCount & "*AG" & RowCount & "))".

Would I need to put another =IF in front of this code to have it proceed and
run this formula until it encounters a blank cell in column E?

It's my last little bit on this MONSTER! Help anyone ??

Continue formula until reaching a blank cell.
 

The first row the formulas are in is row 6. I also have some formatting in
other cells that I want to stop when column E is empty. My question is do I
put the first part of the formula at the top of my macro and the second part
at the end of my macro?

Thanks...Laurie

"Jacob Skaria" wrote:

Dear Alberta

Try the below.

RowCount = 1
'I am not sure what row count is at the beginning. change to suit
'Loop Until E is empty
Do While Range("E" & RowCount) < ""

'put your formulas 1,2,3 inside the loop
'Range("U" & RowCount) = "=IF(V" & RowCount & "<AA" & RowCount & _
",AA" & RowCount & "*AG" & RowCount & ",MAX(K" & RowCount & _
",N" & RowCount & ",AA" & RowCount & "*AG" & RowCount & "))"

RowCount = RowCount + 1
Loop

If this post helps click Yes
---------------
Jacob Skaria


"Alberta Rose" wrote:

I have 3 formulas that need to be repeated in column U (dependant on the cost
type) as long as there is information in column E. One of the formulas I
have in Column U is: =IF(V" & RowCount & "<AA" & RowCount & _
",AA" & RowCount & "*AG" & RowCount & ",MAX(K" & RowCount & _
",N" & RowCount & ",AA" & RowCount & "*AG" & RowCount & "))".

Would I need to put another =IF in front of this code to have it proceed and
run this formula until it encounters a blank cell in column E?

It's my last little bit on this MONSTER! Help anyone ??

Continue formula until reaching a blank cell.
 

Alberta, if I understand correctly you want to have the formulas until Col E
is blank; then place the formulas within the loop; no matter in which order
you keep it.

If this post helps click Yes
---------------
Jacob Skaria


"Alberta Rose" wrote:

The first row the formulas are in is row 6. I also have some formatting in
other cells that I want to stop when column E is empty. My question is do I
put the first part of the formula at the top of my macro and the second part
at the end of my macro?

Thanks...Laurie

"Jacob Skaria" wrote:

Dear Alberta

Try the below.

RowCount = 1
'I am not sure what row count is at the beginning. change to suit
'Loop Until E is empty
Do While Range("E" & RowCount) < ""

'put your formulas 1,2,3 inside the loop
'Range("U" & RowCount) = "=IF(V" & RowCount & "<AA" & RowCount & _
",AA" & RowCount & "*AG" & RowCount & ",MAX(K" & RowCount & _
",N" & RowCount & ",AA" & RowCount & "*AG" & RowCount & "))"

RowCount = RowCount + 1
Loop

If this post helps click Yes
---------------
Jacob Skaria


"Alberta Rose" wrote:

I have 3 formulas that need to be repeated in column U (dependant on the cost
type) as long as there is information in column E. One of the formulas I
have in Column U is: =IF(V" & RowCount & "<AA" & RowCount & _
",AA" & RowCount & "*AG" & RowCount & ",MAX(K" & RowCount & _
",N" & RowCount & ",AA" & RowCount & "*AG" & RowCount & "))".

Would I need to put another =IF in front of this code to have it proceed and
run this formula until it encounters a blank cell in column E?

It's my last little bit on this MONSTER! Help anyone ??

Continue formula until reaching a blank cell.
 

I'm still having issues with this project. The coding below will not format
to row 450, it stops at row 82. Any ideas?

'add column for Default
Columns("u:u").Select
Selection.Insert Shift:=xlToRight
Selection.ColumnWidth = 7
Columns("u:u").Select
Selection.Interior.ColorIndex = xlNone
Range("u5").Select
ActiveCell.FormulaR1C1 = "Default"
With ActiveCell.Characters(Start:=1, Length:=7).Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 8
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Range("u5").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlEdgeTop).LineStyle = xlNone
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlHairline
.ColorIndex = xlAutomatic
End With
Range("u5:u450").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlHairline
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlHairline
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlHairline
.ColorIndex = xlAutomatic
End With
Range("v450").Select

Also, where would I insert the coding to have the formatting stop when the
cell in Column E is blank? Say if there were 90 rows on one sheet and the
next had 300, I want the formatting to stop when there is nothing in Column E.

Thanks :0) Laurie



"Alberta Rose" wrote:

I have 3 formulas that need to be repeated in column U (dependant on the cost
type) as long as there is information in column E. One of the formulas I
have in Column U is: =IF(V" & RowCount & "<AA" & RowCount & _
",AA" & RowCount & "*AG" & RowCount & ",MAX(K" & RowCount & _
",N" & RowCount & ",AA" & RowCount & "*AG" & RowCount & "))".

Would I need to put another =IF in front of this code to have it proceed and
run this formula until it encounters a blank cell in column E?

It's my last little bit on this MONSTER! Help anyone ??

Continue formula until reaching a blank cell.
 

your code specifies borders for this range

Range("u5:u450").Select

and that's what i see in Excel
this isn't a loop, so the code could 'stop' as such where there's no data in
E

why don't you just use Conditional Formatting?



"Alberta Rose" wrote in message
...
I'm still having issues with this project. The coding below will not
format
to row 450, it stops at row 82. Any ideas?

'add column for Default
Columns("u:u").Select
Selection.Insert Shift:=xlToRight
Selection.ColumnWidth = 7
Columns("u:u").Select
Selection.Interior.ColorIndex = xlNone
Range("u5").Select
ActiveCell.FormulaR1C1 = "Default"
With ActiveCell.Characters(Start:=1, Length:=7).Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 8
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Range("u5").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlEdgeTop).LineStyle = xlNone
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlHairline
.ColorIndex = xlAutomatic
End With
Range("u5:u450").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlHairline
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlHairline
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlHairline
.ColorIndex = xlAutomatic
End With
Range("v450").Select

Also, where would I insert the coding to have the formatting stop when the
cell in Column E is blank? Say if there were 90 rows on one sheet and the
next had 300, I want the formatting to stop when there is nothing in
Column E.

Thanks :0) Laurie



"Alberta Rose" wrote:

I have 3 formulas that need to be repeated in column U (dependant on the
cost
type) as long as there is information in column E. One of the formulas I
have in Column U is: =IF(V" & RowCount & "<AA" & RowCount & _
",AA" & RowCount & "*AG" & RowCount & ",MAX(K" & RowCount & _
",N" & RowCount & ",AA" & RowCount & "*AG" & RowCount & "))".

Would I need to put another =IF in front of this code to have it proceed
and
run this formula until it encounters a blank cell in column E?

It's my last little bit on this MONSTER! Help anyone ??