looping ranges 1

[felkin]

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

 

[joerd]

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:

Dim C As Range
For Each C in InputRange
   x = MyFunc(C.Value)
Next C

2. by using an index:

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.

 

[felkin]

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

 

[joerd]

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.

 

[felkin]

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

Rob