Excel/VBA: Programming IF statements for a range of cells
Excel/VBA: Programming IF statements for a range of cells
(OP)
I'm trying to program IF statements for an unkown number of cells, that changes every time a new input file is used.
My problem is that the number of rows the IF statement needs to be
repeated in changes everytime. Every input file has a different number
of rows in it.
I have a column heading named Cost For Roads with an unknown number of
roads and costs associated with those roads. Then I have Cost for
Crossings in the same column with an unknown number of crossing and
costs associated with each crossing.
Could I somehow count the number of cells below Cost For Roads that
contain a number and stop the counter when I hit the cell that contains
the text Cost For Columns. That way I would know the number of rows that
I would need to copy the IF statement for. And then repeat the same for
columns, except stop the counter when I hit a blank cell.
How could I write a subroutine that counts the number of cells that are roads(that need to be a certain IF statement) and then create the IF statement and apply it to all those cells. Therefore, find x and apply the IF statement to x rows. The same would need to be done for the crossings, a different IF statement.
Any help on this would be greatly appreciated.
Carlos
My problem is that the number of rows the IF statement needs to be
repeated in changes everytime. Every input file has a different number
of rows in it.
I have a column heading named Cost For Roads with an unknown number of
roads and costs associated with those roads. Then I have Cost for
Crossings in the same column with an unknown number of crossing and
costs associated with each crossing.
Could I somehow count the number of cells below Cost For Roads that
contain a number and stop the counter when I hit the cell that contains
the text Cost For Columns. That way I would know the number of rows that
I would need to copy the IF statement for. And then repeat the same for
columns, except stop the counter when I hit a blank cell.
How could I write a subroutine that counts the number of cells that are roads(that need to be a certain IF statement) and then create the IF statement and apply it to all those cells. Therefore, find x and apply the IF statement to x rows. The same would need to be done for the crossings, a different IF statement.
Any help on this would be greatly appreciated.
Carlos





RE: Excel/VBA: Programming IF statements for a range of cells
http://j-walk.com/ss/excel/tips/index.htm
http://www.xl-logic.com/menu.html
- good luck
RE: Excel/VBA: Programming IF statements for a range of cells
Set MyRange=ActiveSheet.Range("A1").CurrentRegion
for each MyRow in MyRange.Rows
your code here using MyRow
next
Paul
RE: Excel/VBA: Programming IF statements for a range of cells
Worksheets(1).Columns("C:C").Select
Worksheets(1).Selection.Copy
Worksheets(1).Selection.Insert Shift:=xlToRight
But, VB6 only likes the first line. This must be a simple question, I hope someone knows the answer!!
Thanks!!
RE: Excel/VBA: Programming IF statements for a range of cells
'Get a handle to the column you want (3=column C)
Dim MyColumn as Range
Set MyColumn=MyExcel.ActiveSheet.Columns(3)
MyColumn.Insert xlShiftToRight