Hello all,
I am trying to use arrays for the first time and I am flummoxed! Although the array seems to be populating when I write the values to it, any attempt to read those values fail as nothing is returned. Now I KNOW that this is my stupidity and I have checked and rechecked the code that I cobbled together but I simply cannot see where I am going wrong. Any help would be hugely appreciated.
I would like to write the array to a range in Sheet1 but no values are returned and when I test with debug.print it seems that the array contains only 3 or 4 values instead of the dozens that were written to it. Please forgive my messy code!
I am trying to use arrays for the first time and I am flummoxed! Although the array seems to be populating when I write the values to it, any attempt to read those values fail as nothing is returned. Now I KNOW that this is my stupidity and I have checked and rechecked the code that I cobbled together but I simply cannot see where I am going wrong. Any help would be hugely appreciated.
I would like to write the array to a range in Sheet1 but no values are returned and when I test with debug.print it seems that the array contains only 3 or 4 values instead of the dozens that were written to it. Please forgive my messy code!
Code:
'====DECLARE VARIABLES:'Declare Counter variables
'Dim x As Integer
Dim SheetCounter As Integer 'Used to count sheets
Dim MasterCount As Long 'Used to store number of unique keys
Dim RowCounter As Long 'Used to count rows in ranges
Dim TxnCounter As Integer 'Used to count transactions
'Declare Database variables
Dim UniqueKey As String 'Stores unique customer/household id
Dim MonthAdded As String 'Stores the month in which a household becomes a customer
'Declare Range variables
Dim MasterList As Range 'Holds the master list of unique keys
Dim DataRange As Range 'Holds the active range being worked on
Dim RefCell As Range 'Hold the address of the cell being referenced in a data range
'Declare Calculation variables
Dim CalcNew As Integer 'Stored calculation of new customers for a month
Dim CalcRetained As Integer 'Stores calculation of retained customers for a month
Dim KeyResult As Variant
'Declare Arrays:
Dim MasterArray As Variant 'Stores MasterArray list of unique keys
Dim TxnArray As Variant 'Stores TxnArray month's list of transactions
Dim CalcArray As Variant 'Stores results of calculations
'SET APPLICATION STATE:
'Application.ScreenUpdating = False 'To stop screen flicker
'Application.Calculation = xlCalculationManual 'Stop automatic calculation
'MsgBox Sheets.Count & "sheets in workbook" 'For testing purposes only
'INITIALISE CALCULATION ARRAY:
CalcArray = Array("Sep 2018", "Oct 2018", "Nov 2018", "Dec 2018", "Jan 2019", "Feb 2019", "Mar 2019", "Apr 2019", "May 2019", "Jun 2019", "Jul 2019", "Aug 2019")
ReDim CalcArray(Sheets.Count, 2)
'READ MASTER LIST INTO ARRAY:
Sheets("Master List").Activate
Set DataRange = ActiveSheet.Range("A1:B1", Range("B1").End(xlDown))
DataRange.Select
MasterCount = DataRange.Rows.Count
MasterArray = DataRange.Value2
'COMPARE UNIQUE IDs ON MONTHLY TRANSACTION SHEETS TO MASTER LIST:
SheetCounter = Sheets.Count
For Each Sheet In Sheets
CalcNew = 0
CalcRetained = 0
If Sheets(SheetCounter).Name = "Report" Or Sheets(SheetCounter).Name = "LTV" Or Sheets(SheetCounter).Name = "Master List" Or Sheets(SheetCounter).Name = "Sheet1" Then
'Do nothing for utility sheets
Else
'MsgBox "Now processing sheet " & Sheets(SheetCounter).Name
Sheets(SheetCounter).Activate
MonthAdded = ActiveSheet.Name
Set DataRange = ActiveSheet.Range("A2", Range("A2").End(xlDown))
RowCounter = DataRange.Rows.Count
TxnCounter = 0
On Error Resume Next
For Each RefCell In DataRange
If RefCell.Offset(0, 1) = "Settled Successfully" Then 'If the txn was settled successfully, generate the client key and check if exists
UniqueKey = RefCell.Offset(0, 27) & RefCell.Offset(0, 30) 'Generate the unique customer key from billing address and zipcode
'UniqueKey = "TEST DATA"
KeyResult = IsInArray2DIndex(UniqueKey, MasterArray)
If KeyResult(0) >= 0 And KeyResult(1) >= 0 Then
Debug.Print Chr(34) & MasterArray(KeyResult(0), KeyResult(1)) & Chr(34) & " exists in array at row: " & KeyResult(0) & ", col: " & KeyResult(1)
CalcRetained = CalcRetained + 1
Else
'IF THE GENERATED KEY DOES NOT EXIST IN THE MASTER LIST:
Debug.Print UniqueKey & " does not exist in array"
'ReDim Preserve MasterArray(MasterCount + 1, 2) 'Expand the existing Master key array to add in the new keys
MasterArray(MasterCount, 1) = UniqueKey 'Write unique key to master list
MasterArray(MasterCount, 2) = MonthAdded 'Write month added to master list
CalcNew = CalcNew + 1 'Count as new customer/household
Debug.Print MasterArray(MasterCount, 1) & " WAS WRITTEN TO ARRAY FOR " & MasterArray(MasterCount, 2) & " AT POSITION " & MasterCount
End If
End If
MasterCount = MasterCount + 1
TxnCounter = TxnCounter + 1 'Increment counter
'ReDim MasterArray(MasterCount, 2)
'[ Debug.Print MasterArray(MasterCount, 1) & " | " & MasterArray(MasterCount, 2)
Next
On Error GoTo 0
End If
'STORE THE CALCULATED VALUES FOR REPORTS:
CalcArray(SheetCounter, 0) = MonthAdded
CalcArray(SheetCounter, 1) = CalcNew
CalcArray(SheetCounter, 2) = CalcRetained
Next Sheet
'====WRITE THE UPDATED MASTER LIST====
Worksheets("Sheet1").Activate
'OPTION A
'Set Summary = Worksheets("Sheet1").Range("A1").Resize(UBound(MasterArray, 1) + 1)
'Summary = MasterArray
For MasterCount = 1 To UBound(MasterArray, 1)
'MasterCount = RowCounter
'MsgBox MasterArray(RowCounter, 1)
'DEBUG TEST
Debug.Print "Writing to array:" & MasterArray(MasterCount, 1) & MasterArray(MasterCount, 2)
' Summary.Cells(RowCounter, 1).Value = MasterArray(RowCounter, 1) & "dddd"
' Summary.Cells(RowCounter, 2).Value = MasterArray(RowCounter, 2) & "FFFF"
' RowCounter = RowCounter + 1
Next
'OPTION B:
'Worksheets("Sheet1").Range("A1").Resize(UBound(MasterArray, 1) + 1, UBound(MasterArray, 2) + 1).Value = MasterArray
'MsgBox TxnCounter & " RECORDS PROCESSED!" 'Pause during testing to check values
'====WRITE CALCULATED AMOUNTS TO CELLS====
'For x = 1 To MasterCount
' Debug.Print MasterArray(x, 1)
'Next
Sheets("Report").Range("c7").Value = CalcArray(11, 1)
Sheets("Report").Range("c8").Value = CalcArray(10, 1)
Sheets("Report").Range("c9").Value = CalcArray(9, 1)
Sheets("Report").Range("c10").Value = CalcArray(8, 1)
Sheets("Report").Range("c11").Value = CalcArray(7, 1)
Sheets("Report").Range("c12").Value = CalcArray(6, 1)
Sheets("Report").Range("c13").Value = CalcArray(5, 1)
Sheets("Report").Range("c14").Value = CalcArray(4, 1)
Sheets("Report").Range("c15").Value = CalcArray(3, 1)
Sheets("Report").Range("c16").Value = CalcArray(2, 1)
'====CLEAN UP====
'RESTORE APPLICATION STATE
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
'====CLEAN UP COMPLETED====
Worksheets("Sheet1").Activate
End Sub
Public Function IsInArray(MasterArray As Variant, UniqueKey As String) As Boolean
Dim i As Integer, found As Boolean
found = False
If Not Len(Join(MasterArray)) > 0 Then
found = False
Else
For i = 0 To UBound(MasterArray)
If MasterArray(i) = UniqueKey Then
found = True
End If
Next i
End If
IsInArray = found
End Function
