PDA

View Full Version : Solved: Issue storing cell value as integer



Pool Master
09-11-2008, 05:27 PM
Hi,

I think I might be going about the code all wrong, but some expert help on confirming this would be greatly appreciated.

The part that works thus far is the locating and storing of the start row and end row of integer values for x # of ranges in a column with a random value in each cell increasing as it goes on to the next row. I've attached a spreadsheet with an example of this data in column A.

However, when I try to utilize these variables (srTest1, lrTest1, etc.) via a For Next loop that strings the "i" with the name of the variable Excel doesn't return the value of the start row or end row it only returns the value of i. I've included a basic example below. Is there a way to do this?


srTest1 = Range("A10").Row
lrTest1 = Range("A30").Row

srTest2 = Range("A31").Row
lrTest2 = Range("A70").Row

For i = 1 to 2

'Returns 1 whereas I need the row number for Range("A10")

msgBox(srTest & i)

Next



Thank you!
Adam

MaximS
09-11-2008, 05:56 PM
try this instead:


srTest1 = Range("A10").Row
lrTest1 = Range("A30").Row

srTest2 = Range("A31").Row
lrTest2 = Range("A70").Row

For i = 1 To 2
'Returns 1 whereas I need the row number for Range("A10")
Select Case i
Case 1
MsgBox (srTest1)
Case 2
MsgBox (srTest2)
End Select
Next i

mikerickson
09-11-2008, 06:25 PM
Arrays are one way to do this.
Dim srTest (1 to 2) as Long
Dim lrTest (1 to 2) as Long
Dim i as Long

srTest(1) = 10
lrTest(1) = 30

srTest(2) = 31
lrTest(2) = 70

For i = 1 to 2
MsgBox srTest(i)
Next i
But if its to keep track of ranges, range variables might be prefered
Dim xRange (1 to 2) as Range, i as Long

Set xRange(1) = Range("A1:A30")
Set xRange(2) = Range("A31:A70")

For i = 1 to 2
MsgBox xRange(i).Row
Next i