C
CompleteNewb
I have a loop that fills an array with values found in ranges:
I look for string "Start," then set the beginning of my range at the cell
that's down 2 cells and to the left 1 cell, then set the END of my range as
the last value before a blank in the column after the beginning of my range:
Set FoundIt = .Find("Start", LookIn:=xlValues, LookAt:=xlPart)
If Not FoundIt Is Nothing Then
FirstAddress = FoundIt.Address
Do
StartRange =
SheetName.Range(FoundIt.Address).Offset(2, -1).Address
EndRange =
SheetName.Range(StartRange).End(xlDown).Address
For Each ACell In SheetName.Range(StartRange,
EndRange)
ReDim Preserve ArrayList(0 To i)
ArrayList(i) = ACell.Value
i = i + 1
Next ACell
Set FoundIt = .FindNext(FoundIt)
Loop While Not FoundIt Is Nothing And FoundIt.Address <>
FirstAddress
i = 0
End If
This works great EXCEPT when there is only one value in the StartRange, and
there are a couple blanks after that, then some other value that's not
really supposed to be in my range. So it seems that when I use End(xlDown)
from a StartRange that's the only value (ie. the cell under StartRange is
blank, so really my StartRange and EndRange should be the same cell), Excel
looks for a blank that's after the NEXT value found after the StartRange.
Example: Let's say I found "Start", and I'm now at 2 cells down, 1 to the
left (in the example, Blank means empty cell, not the value "Blank":
A3: val1
A4: val2
A5: val3
A6: Blank
A7: Blank
This sets my StartRange at A3, EndRange at A5, which is correct. Then,
however, (and again, I found "Start" and am now at 2 cells down, 1 over):
A16: val1
A17: Blank
A18: Blank
A19: val2
A20: Blank
This sets my StartRange at A16, but my EndRange at A19 (even though there
are blanks under A16). So this is populating my array with blanks and with
that val2 I don't want.
How can I get this to work so that when my StartRange has the only value,
then EndRange will be the same as StartRange? I can't use offsets on my
current method of setting StartRange and EndRange, because those actually
work perfectly when there ARE more than one value, so an offset would lose
me my first or last value.
Any help on this would be appreciated, and thanks for reading.
I look for string "Start," then set the beginning of my range at the cell
that's down 2 cells and to the left 1 cell, then set the END of my range as
the last value before a blank in the column after the beginning of my range:
Set FoundIt = .Find("Start", LookIn:=xlValues, LookAt:=xlPart)
If Not FoundIt Is Nothing Then
FirstAddress = FoundIt.Address
Do
StartRange =
SheetName.Range(FoundIt.Address).Offset(2, -1).Address
EndRange =
SheetName.Range(StartRange).End(xlDown).Address
For Each ACell In SheetName.Range(StartRange,
EndRange)
ReDim Preserve ArrayList(0 To i)
ArrayList(i) = ACell.Value
i = i + 1
Next ACell
Set FoundIt = .FindNext(FoundIt)
Loop While Not FoundIt Is Nothing And FoundIt.Address <>
FirstAddress
i = 0
End If
This works great EXCEPT when there is only one value in the StartRange, and
there are a couple blanks after that, then some other value that's not
really supposed to be in my range. So it seems that when I use End(xlDown)
from a StartRange that's the only value (ie. the cell under StartRange is
blank, so really my StartRange and EndRange should be the same cell), Excel
looks for a blank that's after the NEXT value found after the StartRange.
Example: Let's say I found "Start", and I'm now at 2 cells down, 1 to the
left (in the example, Blank means empty cell, not the value "Blank":
A3: val1
A4: val2
A5: val3
A6: Blank
A7: Blank
This sets my StartRange at A3, EndRange at A5, which is correct. Then,
however, (and again, I found "Start" and am now at 2 cells down, 1 over):
A16: val1
A17: Blank
A18: Blank
A19: val2
A20: Blank
This sets my StartRange at A16, but my EndRange at A19 (even though there
are blanks under A16). So this is populating my array with blanks and with
that val2 I don't want.
How can I get this to work so that when my StartRange has the only value,
then EndRange will be the same as StartRange? I can't use offsets on my
current method of setting StartRange and EndRange, because those actually
work perfectly when there ARE more than one value, so an offset would lose
me my first or last value.
Any help on this would be appreciated, and thanks for reading.