×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

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!
  • Students Click Here

*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

Jobs

looping ranges

looping ranges

looping ranges

(OP)
I am new to VBA and Excel and can't figure out how to loop through ranges.  Basically, I have a calculation that takes two values (actually alot more but for example...) and returns one.  I want to input the two values from two ranges and output to another range.


'input variable values
rangeA = Range(RefEdit1.Text)
rangeB = Range(RefEdit1.Text)

'output location
rangeC = Range(RefEdit1.Text)

'then I need to start a loop

For Each cell In ???

        RangeC.Cell.Value = Function(rangeA.Cell.Value,rangeB.Cell.Value)

Next cell


Please help me with this syntax!!!  I think that I could figure it out with some example code but I have been having a hard time finding example code for this.

Thanks,

Rob

RE: looping ranges

Rob,

Not to sure what you mean. Why a loop if you only have 2 input values? If you want to create a table, it is much easier to call the function multiple times from the worksheet.

However, to come back to your question: you can loop through cells in basically two ways:
1. the "For each" construction:

CODE

Dim C As Range
For Each C in InputRange
   x = MyFunc(C.Value)
Next C
2. by using an index:

CODE

Dim i As Integer
For i = 1 to 10
   x = MyFunc(Cells(i,1).Value)
Next i

Cheers,
Joerd

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.

RE: looping ranges

(OP)
Joerd,

Thanks for the reply.  To clarify I am working with an equation of state.  I want to input Temperature and Volume and get back Pressure.  I want to do this for an entire range of temperatures, volumes and output Pressure to a range in the spreadsheet.

You showed a case where there is a single variable and I understand that.  However,  I need to learn how to work with  mulitple ranges.

So I have something like the following:

Dim temperatureRange As Range
Dim volumeRange As Range
Dim outputRange As Range

'input variable values
temperatureRange = Range(RefEdit1.Text)
volumeRange = Range(RefEdit1.Text)

'output location
outputRange = Range(RefEdit1.Text)

'then I need to start a loop
For each ?temperatureRange and volumeRange and outputRange?

outputRange.Value = Myfunction(temperatureRange.Value,volumeRange.Value)

next ?temperatureRange and volumeRange and outputRange?


How do I tell VBA to use the cooresponding value of temperature, volume and output.  This should be simple but I can't find it anywhere I look.

Thanks,

Rob

RE: looping ranges

Ok, further to my previous statements, let's assume you have 3 columns: TemperatureRange, VolumeRange and OutputRange. You can use the .Offset(row, column) method, or directly index the range (which is a little easier to understand). So, for example:

For i = 1 to TemperatureRange.Rows.Count
   OutputRange(i,1)=EOSFunc(TemperatureRange(i,1) , VolumeRange(i,1) )
Next i

By the way, I still fail to see why you wouldn't just fill up your OutputRange cells with the formula =EOSFunc(TemperatureRange, VolumeRange). As mogens taught me in an earlier thread (Thread770-75114), you could even use named ranges in this case (so name your range with temperatures TemperatureRange, etc.)

Cheers,
Joerd

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.

RE: looping ranges

(OP)
That is just what I needed.  Thanks for the information!

Rob

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!


Resources