Continue formula until reaching a blank cell.

[Alberta Rose]

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

 

[Jacob Skaria]

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

 

[Alberta Rose]

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]

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

 

[Alberta Rose]

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

 

[Patrick Molloy]

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?