lforster9999
New Member
- Joined
- Sep 16, 2020
- Messages
- 7
- Office Version
- 365
- Platform
- Windows
- MacOS
Hi All, new here.
I am doing some data organisations and munipulation in Excel. I have created serval arrays in VBA to hold different data. I thought i would create a Function to do this for efficiency and simplicity. The code runs well when not in a function but when in a function it doesnt work. Please see code below.
Any help would be appreciated, still trying to get my head round functions and arrays!
The error being shown is "Type mismatch" and errors on the Redim Preserve in function
I am doing some data organisations and munipulation in Excel. I have created serval arrays in VBA to hold different data. I thought i would create a Function to do this for efficiency and simplicity. The code runs well when not in a function but when in a function it doesnt work. Please see code below.
Any help would be appreciated, still trying to get my head round functions and arrays!
The error being shown is "Type mismatch" and errors on the Redim Preserve in function
VBA Code:
Sub AddProcessStageToArray(SourceWorksheet, RawDataArray, LastrowData, WhatStage, ArrayOutput)
Dim o As Long
For i = 2 To UBound(RawDataArray)
If RawDataArray(i, 13) = WhatStage And RawDataArray(i, 38) <> "NOK" Then
o = o + 1
'Dim ArrayName As Variant
ReDim Preserve ArrayOutput(o)
ArrayOutput(o) = Application.Index(SourceWorksheet.Range("A1:AO" & LastrowData), i, 0)
End If
Next
End Sub
VBA Code:
Sub AddITWToArray()
Dim DataWs As Worksheet: Set DataWs = ThisWorkbook.Sheets("DATA")
Dim PoolOfWeekWs As Worksheet: Set PoolOfWeekWs = ThisWorkbook.Sheets("Pool of the week")
Dim LastrowData As Long: LastrowData = DataWs.Range("A" & Rows.Count).End(xlUp).Row
Dim LastColData As Long: LastColData = DataWs.Cells(1 & DataWs.Columns.Count).End(xlToLeft).Column
Dim LastColDataString As String: LastColDataString = Split(Cells(1, LastColData).Address, "$")(1)
Dim DataRange As Range: Set DataRange = DataWs.Range("A1:" & LastColDataString & LastrowData)
Dim DataArr As Variant: DataArr = DataWs.Range("A1:AO" & LastrowData)
'Loop through Data array, if interview process = PQL, add to table. If interview proces = 1sTITW find postion and add data, if 2ndITW find postion and highlight, if 3rd find postion and highlight
Dim PoolofWeekTableLRow As Long: PoolofWeekTableLRow = PoolOfWeekWs.Range("A" & Rows.Count).End(xlUp).Row
'PoolOfWeekWs.Rows("3:" & PoolofWeekTableLRow).ClearContents
Dim i, o As Long
Dim RowNumberArr As Variant
i = 2
o = 0
'Create PQLArray
'Call AddProcessStageToArray(DataWs, DataArr, LastrowData, "Prequalification", PQLArray()
o = 0
'Create 1ITWArray
Call AddProcessStageToArray(DataWs, DataArr, LastrowData, "Candidate Interview 1", FirstITWArray)
o = 0
'Create 2ITWArray
Call AddProcessStageToArray(DataWs, DataArr, LastrowData, "Candidate Interview 2+", SecondITWArray)
o = 0
'Create PPLArray
Call AddProcessStageToArray(DataWs, DataArr, LastrowData, "Candidate Interview 2*", PPLArray)