Problem assigning my Array values to rows - Please
Greetings,
I'm writing a small VBA code to get some data from a worksheet (This is done by using an array) and then "paste" or "assign" my array to the cells in the second worksheet through a range. The problem is that when I execute the code, I just get "0", but when debugging the code, in each loop I can see the correct values. In other words, the correct values are translated to "0" when pasted into the second worksheet.
The following is my code:
Private Sub btnBringData_Click()
Dim rCell As Range
Dim X As Variant
Dim RN As Integer
Dim Page As Worksheet
Dim Answer As String
Dim MyNote As String
Dim MyNumber As Integer
Dim Total(150)
Set Page = WorkSheetExists("Main Payroll Template")
RN = 5
For Each rCell In Page.Range("D5150, E5:E150, F5:F150, G5:G150, H5:H150")
X = rCell.Value
If X = "" Then
MyNote = "Empty Space"
Answer = MsgBox(MyNote, vbQuestion + vbOKOnly, "???")
ElseIf X = "Total" Then
MyNumber = RN
Answer = MsgBox(MyNumber, vbQuestion + vbOKOnly, "???")
Exit For
Else
For i = 5 To 150
Report.MyArray(i).Acct = Page.Cells(i, 4)
Report.MyArray(i).Company = Page.Cells(i, 5)
Report.MyArray(i).Dept = Page.Cells(i, 6)
Report.MyArray(i).CredMIS = Page.Cells(i, 8)
Report.MyArray(i).DebMIS = Page.Cells(i, 7)
Total(i) = Report.MyArray(i).CredMIS - Report.MyArray(i).DebMIS
Next i
End If
RN = RN + 1
Next
'Set Atlas Template page
Set Page = WorkSheetExists("Atlas 3.5 Template")
For Each rCell In Page.Range("J6:J150")// Here I'm specifying the rage in the second worksheet
For i = 1 To 150
'rCell.Value = Report.MyArray(i).Acct
'rCell.Value = Report.MyArray(i).Company
'rCell.Value = Report.MyArray(i).Dept
rCell.Value = Total(i) // THIS IS WHERE I GET THE CORRECT VALUES, BUT IN (ATLAS 3.5 TEMPLATE) THIS IS JUST SHOWING A "0" in Each row.
Next i
Next
End Sub
Public Function WorkSheetExists(Sheetname As String) As Worksheet
Dim ws As Worksheet
For i = 1 To ThisWorkbook.Worksheets.Count
Set ws = ThisWorkbook.Worksheets(i)
If (UCase(ws.Name) = UCase(Sheetname)) Then
Set WorkSheetExists = ws
Exit Function
End If
Next i
End Function
Any ideas? What Am I doing wrong?
Thanks,
Eduardo
|
