G
Guest
I have a problem with checking the contents of an array in a loop. The array
looks originally like this:
Dim varWorksheetInfoArray(15) As Variant
varWorksheetInfoArray(0) = "Avkdata"
varWorksheetInfoArray(1) = "Date"
etc…
I then call another sub and send the array along:
Call chartMaker(varWorksheetInfoArray)
Private Sub chartMaker(ByRef varWorksheetInfoArray() As Variant)
Dim i, j, k As Integer
ReDim rng(1 To UBound(varWorksheetInfoArray)) As Range
I then put the info from the varWorksheetInfoArray and put it in a new
array. I do this because I need the addresses of the cells containing the
text the was defined in varWorksheetInfoArray.
Do While k < UBound(rng)
Set rng(k) =
Worksheets(varWorksheetInfoArray(0)).Cells.Find(varWorksheetInfoArray(k),
LookIn:=xlValues)
k = k + 1
Loop
I then get some values for the integers i, j etc. and make some other stuff
that is not important here. The problem occurs in the following piece of code:
k = 2 'startvärde
Do While k < UBound(rng) - 1
If Not rng(k) Is Nothing Then
ActiveChart.SeriesCollection(k - 1).Values =
Sheets(varWorksheetInfoArray(0)).Range(rng(k).Offset(i, 0).Address & ":" &
rng(k).Offset(j, 0).Address)
Else: End If
k = k + 1
Loop
The problem is that the varWorksheetInfoArray has to have a “generalâ€
dimension. I use this array many times but sometimes I just need three spaces
and sometimes ten spaces in the array. Thus sometime some spaces will be
empty, or undefined. When declaring the contents of the array I write:
varWorksheetInfoArray(0) = "Avkdata"
varWorksheetInfoArray(3) = "Price"
but some spaces will not be mentioned e.g.
varWorksheetInfoArray(2) is never mentioned. I don’t know what “value†that
space gets. The problem is that when loop through the rng array I need to
check if there are contents in the spaces in the array. But it just pops out
of the loop as it is now if there is some space that does not have any
contents. If you know how to solve this please help me out. Thank you very
much!!
looks originally like this:
Dim varWorksheetInfoArray(15) As Variant
varWorksheetInfoArray(0) = "Avkdata"
varWorksheetInfoArray(1) = "Date"
etc…
I then call another sub and send the array along:
Call chartMaker(varWorksheetInfoArray)
Private Sub chartMaker(ByRef varWorksheetInfoArray() As Variant)
Dim i, j, k As Integer
ReDim rng(1 To UBound(varWorksheetInfoArray)) As Range
I then put the info from the varWorksheetInfoArray and put it in a new
array. I do this because I need the addresses of the cells containing the
text the was defined in varWorksheetInfoArray.
Do While k < UBound(rng)
Set rng(k) =
Worksheets(varWorksheetInfoArray(0)).Cells.Find(varWorksheetInfoArray(k),
LookIn:=xlValues)
k = k + 1
Loop
I then get some values for the integers i, j etc. and make some other stuff
that is not important here. The problem occurs in the following piece of code:
k = 2 'startvärde
Do While k < UBound(rng) - 1
If Not rng(k) Is Nothing Then
ActiveChart.SeriesCollection(k - 1).Values =
Sheets(varWorksheetInfoArray(0)).Range(rng(k).Offset(i, 0).Address & ":" &
rng(k).Offset(j, 0).Address)
Else: End If
k = k + 1
Loop
The problem is that the varWorksheetInfoArray has to have a “generalâ€
dimension. I use this array many times but sometimes I just need three spaces
and sometimes ten spaces in the array. Thus sometime some spaces will be
empty, or undefined. When declaring the contents of the array I write:
varWorksheetInfoArray(0) = "Avkdata"
varWorksheetInfoArray(3) = "Price"
but some spaces will not be mentioned e.g.
varWorksheetInfoArray(2) is never mentioned. I don’t know what “value†that
space gets. The problem is that when loop through the rng array I need to
check if there are contents in the spaces in the array. But it just pops out
of the loop as it is now if there is some space that does not have any
contents. If you know how to solve this please help me out. Thank you very
much!!