I have workbooks with datasets (of varying lengths) from measurements in different rooms. I need to sort each datasets. My code finds the start and end rows of each dataset and stores them as StartRowRoom1, EndRowRoom1, StartRowRoom2, EndRowRoom2 etc.
I want to go through each dataset in a while loop like this.
Dim StartRowRoom1 As Integer
Dim StartRowRoom2 As Integer
Dim EndRowRoom1 As Integer
Dim EndRowRoom2 As Integer
n = 1
While n < NumberOfRooms
startRow = "StartRowRoom" & n
endRow = "EndRowRoom" & n
With Range(Cells(startRow, 4), Cells(endRow, 4))
.FormulaR1C1 = "=RC[-2]+RC[-1]"
#sorting and graph creation
End With
n = n + 1
Wend
My problem is that the startRow and endRow variables are strings ("StartRowRoom1" and "EndRowRoom1", for n=1). so they cannot be used in Cells(). I want them to refer to the variables defined as integers. Does anyone have a solution?
This is what arrays are for.
You should declare your variables as
Dim StartRowRoom(1 to 2) As Integer
Dim EndRowRoom(1 to 2) As Integer
StartRowRoom(1) = [your value here]
StartRowRoom(2) = [your value here]
EndRowRoom(1) = [your value here]
EndRowRoom(2) = [your value here]
Then you can access them as
startRow = StartRowRoom(n)
endRow = EndRowRoom(n)
using n as the index
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments