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
'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
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
For Each C in InputRange
x = MyFunc(C.Value)
Next C
CODE
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
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
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
Rob