Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 ?? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 ?? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 ?? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 ?? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 ?? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 ?? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to return a blank formula cell if the reference is blank? | Excel Worksheet Functions | |||
Average Formula to display blank cell if named range is blank | Excel Worksheet Functions | |||
leave cell blank and continue with formula | Excel Worksheet Functions | |||
If blank cell continue macro | Excel Programming | |||
Formula that will leave cell blank if cell is blank | Excel Programming |