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

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

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

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

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

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" <(E-Mail Removed)> wrote in message
news:38D2B2CE-8F5E-4F16-990D-(E-Mail Removed)...
> 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 ??