Contact US

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

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

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

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

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.


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

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:



 - good luck

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

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").CurrentRegion
   for each MyRow in MyRange.Rows
      your code here using MyRow


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

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).Selection.Insert Shift:=xlToRight

But, VB6 only likes the first line.  This must be a simple question, I hope someone knows the answer!!


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

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members! Already a Member? Login


Low-Volume Rapid Injection Molding With 3D Printed Molds
Learn methods and guidelines for using stereolithography (SLA) 3D printed molds in the injection molding process to lower costs and lead time. Discover how this hybrid manufacturing process enables on-demand mold fabrication to quickly produce small batches of thermoplastic parts. Download Now
Design for Additive Manufacturing (DfAM)
Examine how the principles of DfAM upend many of the long-standing rules around manufacturability - allowing engineers and designers to place a part’s function at the center of their design considerations. Download Now
Taking Control of Engineering Documents
This ebook covers tips for creating and managing workflows, security best practices and protection of intellectual property, Cloud vs. on-premise software solutions, CAD file management, compliance, and more. Download Now

Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close