Excel/VBA: Programming IF statements for a range of cells

[CarlosDiaz]

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

 

[DaytonEE]

You could try the WorkSheets.UsedRange method to find the range of cells to search for the last cell containing data. This method will return the smallest rectangle around the used cells in a worksheet. Becareful with this method, though. Sometimes it returns the ranges for cells that once contained values but no longer do. If you're not familiar with how to search cells in a worksheet, there are good resources on the web. Here are two:

http://j-walk.com/ss/excel/tips/index.htm

http://www.xl-logic.com/menu.html

- good luck

 

[PaulWeal]

If the cell in the upper left corner of a range of contiguous cells is known, say "A1", then you could use:
Set MyRange=ActiveSheet.Range("A1&quot.CurrentRegion
for each MyRow in MyRange.Rows
your code here using MyRow
next

Paul

 

[IFRs]

I'm writing a Visual Basic program that accesses existing Excell files. I need to select an entire column and then insert the entire column, shifting all columns to the right. Excell's macro recorder shows:

Worksheets(1).Columns("C:C&quot.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!!

 

[PaulWeal]

If you are using VB6 then I assume you have an variable set to the Excel.Application object. In my example below this is assigned to variable MyExcel. The following inserts a column in the active sheet at column C and shifts the remaining columns to the right.

'Get a handle to the column you want (3=column C)
Dim MyColumn as Range
Set MyColumn=MyExcel.ActiveSheet.Columns(3)
MyColumn.Insert xlShiftToRight