I have a workbook that is doing something I don't understand. In several places I am using arrays to transfer/modify information, then placing the array back onto the worksheet, and this works perfectly. The following code... doesn't.
(I've cut out unnecessary code like variable declarations - all my integer items are declared as Long)
This seems to work perfectly. All my debugging shows that varB, with my current data, ends up with 245 entries that conform to the Like "*_*" criteria. I can access every individual item by either varB(x) or varB(x,1) depending on whether I declare varB as one or two dimensional. I can do a simple .cells(1,1) = varB(1,1) with no problem. My debug at the end produces the line:
10000 - 245 - 1
10k cells UBound of varB, single column wide, with 245 data points in a single column.
However, every time I try to transfer varB back to the worksheet in one go, it fails. I've tried:
and several other ways. What happens is just null output, a full column of nothing. However, when I expand my worksheet range to two columns, all the data from varB appears - it is shifted down one column and row. So my data starts at .cells(2,2) instead of .cells(1,1). The data is all perfect, I've verified it left and right.... I just can't figure out why it's getting shifted like this. Any help would be greatly appreciated, I feel like I've taken crazy pills.
(I've cut out unnecessary code like variable declarations - all my integer items are declared as Long)
Code:
Dim varA As Variant, varB(10000, 1) As Variant
Set wkDest = Sheets("a")
erB = 0
For lpTerm = 1 To 4
Set wkTarg = Sheets(lpTerm)
With wkTarg
For lpYear = 1 To 3
colTarg = Range("cal_Col_Y" & lpYear & "Ofst") + 8 ' just results in a column number, eg. 10
erTarg = .Cells(.Rows.Count, colTarg).End(xlUp).Row ' last row of data in above column
varA = .Range(.Cells(Range("cal_Row_TermDataStart"), colTarg), _
.Cells(erTarg, colTarg))
' For each year within each term, varA is set to an array of cells starting at a pre-defined constant row, and going to erTarg.
For i = 1 To UBound(varA, 1)
If varA(i, 1) Like "*_*" Then
erB = erB + 1
varB(erB, 1) = varA(i, 1)
End If
Next i
Next lpYear
End With
Next lpTerm
Debug.Print UBound(varB, 1) & " - " & erB & " - " & UBound(varB, 2)This seems to work perfectly. All my debugging shows that varB, with my current data, ends up with 245 entries that conform to the Like "*_*" criteria. I can access every individual item by either varB(x) or varB(x,1) depending on whether I declare varB as one or two dimensional. I can do a simple .cells(1,1) = varB(1,1) with no problem. My debug at the end produces the line:
10000 - 245 - 1
10k cells UBound of varB, single column wide, with 245 data points in a single column.
However, every time I try to transfer varB back to the worksheet in one go, it fails. I've tried:
Code:
.range(.cells(1,1),.cells(UBound(varB,1),1)) = varB
.Range("A1").Resize(UBound(varB, 1), 1) = varBand several other ways. What happens is just null output, a full column of nothing. However, when I expand my worksheet range to two columns, all the data from varB appears - it is shifted down one column and row. So my data starts at .cells(2,2) instead of .cells(1,1). The data is all perfect, I've verified it left and right.... I just can't figure out why it's getting shifted like this. Any help would be greatly appreciated, I feel like I've taken crazy pills.
