Defailt size of Range/Array

[victorialj]

Hi there,

I am currently attempting to decipher a rather large macro that was written some time ago, which also has hardly any annotation and no documentation.

I think I have located one of the problems with it (well maybe?! as i think there are several.)

Basically the macro loops through a directory looking in worksheets determined by a date prompt the user enters. It then loops through each worksheet,column, row etc looking for non blank rows to copy.

It appears to me that when there are too many distinct ranges for it to copy to the summary sheet, that it just doesn't concatenate the final ranges to the overall range.

The range is called sRange, but I cannot see it defined at anywhere in the project. I am assuming it is being created on the fly - so what is the default size.

Does this sound like it could be the problem or am i barking up the wrong tree?!

Any help would be great!
Thanks
Victoria

 

[xlbo]

There is no "default size" for either a range or an array

Please specify the problem and post the line of code causing the problem, along with some lines to either side of it (to get an idea of what is happening in that part of the sub)

Rgds, Geoff

Three things are certain. Death, taxes and lost data. DPlank is to blame

Please read FAQ222-2244 before you ask a question

 

[victorialj]

Here is the function where it loops through each row and column.

On this occasion it returned an sRange of A11:N13,A15:N16,A20:N21. However it also looped through - correctly - the range A27:N46, but then didn't concatenate it at the end when combining the range.


Please let me know if you need anymore info.
Thanks
Victoria

Private Function GatherData(lStartRow As Long, _
lEndRow As Long) As String

Dim bEmpty As Boolean
Dim bLineEmpty As Boolean
Dim lRangeStart As Long
Dim lRangeEnd As Long

'here we need to build a range string to copy
lRangeStart = lStartRow
For lRow = lStartRow To lEndRow
'go along all columns and test
f InStr(1, Cells(lRow, 4), "ab") = 0 Then
For lCol = 1 To 14
If Len(Cells(lRow, lCol)) > 1 Then
bEmpty = False
lCol = 14
Else
bEmpty = True
End If
lCol = lCol + 1
Next lCol
If bEmpty Then
If lRangeStart = 0 Then
'here we carry on until a valid line
Do Until Not bEmpty
lRow = lRow + 1
If Not Rows(lRow).Hidden Then
For lCol = 1 To 14
If Len(Cells(lRow, lCol)) > 1 Then
bEmpty = False
lCol = 14
Else
bEmpty = True
End If
lCol = lCol + 1
Next lCol
End If
Loop
lRangeStart = lRow
Else
If lRangeStart <> lRow Then
lRangeEnd = lRow - 1 'end of range is row before empty one
If Len(sRange) > 0 Then 'concatenate if string exists
sRange = sRange & ",A" & lRangeStart & ":N" & lRangeEnd
Else
sRange = "A" & lRangeStart & ":N" & lRangeEnd
End If
lRangeStart = lRow + 1
Else
lRangeStart = lRow + 1
End If
End If


End If
End If
Next lRow

GatherData = sRange

End Function

 

[xlbo]

Have you tried stepping through the code with F8 ??

You can set a watch on the sRange variable and keep an eye on how / when it changes

To be honest, without seeing your data layout I am scrabbling in the dark a bit - you have numerous conditions that must be satisfied for it to add the range to "srange" - I would suggest that 1 of those tests is not being met....

Rgds, Geoff

Three things are certain. Death, taxes and lost data. DPlank is to blame

Please read FAQ222-2244 before you ask a question

 

[victorialj]

I've F8'd through the code which is how i know that it does search through the missing range correctly (i.e. If Len(Cells(lRow, lCol)) > 1 Then bEmpty = False lCol = 14)
- it just seems to forget to add them to the final array at the end.

will keep trying it - to be honest there are a few problems, where by it misses some ranges but finds others, with no obvious logic....

Thanks for your help anyway ;-)

Cheers
Victoria

 

[xlbo]

and this:

If lRangeStart <> lRow Then

???

Does lRow happen to be 27 at all ??

Rgds, Geoff

Three things are certain. Death, taxes and lost data. DPlank is to blame

Please read FAQ222-2244 before you ask a question

 

[victorialj]

Geoff,

At the end of the Function values are as follows: lRangeStart = 27, lRow = 47, and lRangeEnd = 21.

V

 

[xlbo]

ok - so that tells me that:

If lRangeStart <> lRow Then
  [b]lRangeEnd = lRow - 1]/b] 'end of range is row before empty one
    If Len(sRange) > 0 Then 'concatenate if string exists
      sRange = sRange & ",A" & lRangeStart & ":N" & lRangeEnd
    Else
      sRange = "A" & lRangeStart & ":N" & lRangeEnd
    End If
  lRangeStart = lRow + 1
Else
  [b]lRangeStart = lRow + 1[/b]
End If
End If

is not evaluated in this case as if it was then 1 or other of the bolded lines should be evaluated which would give you different numbers for lRangeStart or lRangeEnd depending on which was evaluated

Based on that, it must be something in:

If bEmpty Then
  If lRangeStart = 0 Then
  'here we carry on until a valid line
    Do Until Not bEmpty
      lRow = lRow + 1
      If Not Rows(lRow).Hidden Then
          For lCol = 1 To 14
             If Len(Cells(lRow, lCol)) > 1 Then
                bEmpty = False
                lCol = 14
             Else
                bEmpty = True
             End If
             lCol = lCol + 1
          Next lCol
       End If
     Loop

that is causing this....

Rgds, Geoff

Three things are certain. Death, taxes and lost data. DPlank is to blame

Please read FAQ222-2244 before you ask a question