Application.Match (Error 2042)

[grantlocke]

Hi, I would be extremely grateful for your help. I am trying to get match dates in two different arrays, both declared as variants and regardless of whether or not the dates actually match I get an (Error 2042). I have reproduced the code below. How the code works:tsdata() = loads an array of 3860 x 4 (first column are daily dates stored in date format)tsubdata()= a subset of tsdata 758x4 (first column are daily dates, rest are % changes)mDate() = 185 (a series of month end dates)Once tsubdata is loaded, the reference tsubdata(758,1) should be matched to mDate() the entire range of 185 dates, if the result is numeric, i.e. a positive integer then the code will carry on and call another macro, if not, it should just carry on until it does. In other words, I am creating an array and the last reference in column 1 should match a month end. Would be very grateful for your helpThanksGrantSub tsCalculator() 'time series calculatorDim tsData() As VariantDim tsubData() As VariantDim Mdate() As VariantDim a As IntegerDim b As IntegerDim c As IntegerDim d As IntegerDim tsTotal As IntegerDim tsAdvance As Integer 'number of periods between the initial period and end of time seriesDim tsheight As Integer'Redimension arrayReDim tsData(1 To 3861, 1 To 4)ReDim Mdate(1 To 185)ReDim tsubData(1 To 758, 1 To 4)'Load data into arraysData = Range("pData")Mdate = Range("mdates") 'Dimensionstsheight = 758 ' number of days in three yearstsTotal = 3861 ' Total number of rows in data set - daily datatsAdvance = tsTotal - tsheightc = 1'Create calculated timeseries based on portfolio weightsFor d = 1 To tsAdvance 'Create a sub array from tsData = fixed height of 3 years - 758 data points For a = c To tsheight For e = 1 To 758 For b = 1 To 4 tsubData(e, b) = tsData(a, b) Next b Next e, a If IsNumeric(application.match(tsubData(e-1,1),mdate,0)) Then 'THIS IS WHERE THE PROBLEM IS I GET A 2042 EVEN IF THE DATES SHOULD MATCH'DosomethingElseGoto Line1End IfLine1: tsheight = tsheight + 1c = c + 1End sub

 

[grantlocke]

Hi everyone, so sorry about the awful formatting, I tried twice to format the question and both times the formatting was lost when I clicked preview - it may be my browser. Simple terms: Application.Match(Array(1,1),Array2,0) was giving me an Error 2042. Both arrays where made up of subsets of the same dataset and both declared as Variant and when I knew both variables matched I still got a 2042. In the end i moved to a for next loop where array(1,1) = Array2(b,1) and this seems to work fine. I can only think that there is a quirk in Match function. Any ideas on how to format properly for when I next post?

 

[Jonmo1]

In the end i moved to a for next loop where array(1,1) = Array2(b,1) and this seems to work fine.

This indicates that Array2 is a 2 Dimentional Array (multiple columns and multiple rows)
Match only works on a 1 Dimentional Array (1 column OR 1 Row)

 

[grantlocke]

This indicates that Array2 is a 2 Dimentional Array (multiple columns and multiple rows)Match only works on a 1 Dimentional Array (1 column OR 1 Row)

Thanks Jonmo, This my lack of understanding of redim, below is the original Array2: ReDim Mdate(1 To 185)Is this a 1d or 2d?

 

[Jonmo1]

That looks like 1D.

But can you repost the code, wrapping it in code tags.
Click Go Advanced,
Paste your code
Highlight the code and press the # in the toolbar to put code tags around it.

 

[grantlocke]

Hi, here is the code below. The main line to focus on is the Match function. Ultimately, the function only ever returns a 2042 regardless of the variables and whether they match or not? Apologies again for the formatting, I found the BB code list and used some of the formatting but for some reason dont know how to preserve line breaks - even though HTML on convert linebreaks / off (tried both)

Sub tsCalculator() 'time series calculatorDim tsdata() As VariantDim Mdate() As VariantDim bdate As VariantDim a As IntegerDim tsTotal As IntegerDim tsAdvance As Integer 'number of periods between the initial period and end of time seriesDim tsheight As IntegerDim Currentw() As VariantDim Proposedw() As VariantDim Proposedr() As VariantDim RiskCheck As IntegerDim NumAssets As IntegerDim pvolatility As Long'Dimensionstsheight = 758 ' number of days in three yearstsTotal = 3861 ' Total number of rows in data set - daily datatsAdvance = tsTotal - tsheightNumAssets = 3'Redimension arrayReDim tsdata(1 To 758, 1 To 4)ReDim Mdate(1 To 185)ReDim Currentw(1 To 3, 1)ReDim Proposedw(1 To 3, 1)ReDim Proposedr(1 To 3, 1)'Load data into array / VariableMdate = Range("mdates")Currentw = Range("Currentw")Proposedw = Range("Proposedw")Proposedr = Range("Proposedr")RiskCheck = Range("RiskCheck")pvolatility = Range("pVolatility")'Create calculated timeseries based on portfolio weightsFor a = 1 To tsAdvance 'load the time series in array   tsdata = Range("tsdata").Offset(a, 0) If RiskCheck = 1 Then   'If monthly then rebalance risk weightsIf Isnumeric(Application.Match(tsdata(758),Mdates,0)) Then Else 'Dosomething End If  Next aEnd Sub

 

[Jonmo1]

Don't use any 'tools' to format your code.
Just copy it straight from VBA window and paste it into your post.
And put code tags around it using the # button (you don't need to go advanced as I previously thought..)