VBA runtime error 13 - Type Mismatch

[karan2807]

Hi Team ,

I need to get the whole row value from a 2d array but I am getting the runtime error 13 - Type Mismatch error.

I am using the following expression.

arr1 = Application.WorksheetFunction.Index(SparepartData, 1, 0)

sparepartdata = Array.



Please help

 

[Fluff]

Hi & welcome to MrExcel.
If SparepartData is a 2d array & arr1 is a variant, then this line should work

arr1 = Application.WorksheetFunction.Index(SparepartData, 1, 0)

If you are having problems, then please post the rest of the code using code tags

 

[karan2807]

Hi & welcome to MrExcel.
If SparepartData is a 2d array & arr1 is a variant, then this line should work

arr1 = Application.WorksheetFunction.Index(SparepartData, 1, 0)

If you are having problems, then please post the rest of the code using code tags

Hi , Yes they are the same as you mentioned still I am getting a same error dont know why? Can you help me in that

 

[Fluff]

Can you help me in that

Not unless you do what I asked.

 

[karan2807]

Not unless you do what I asked.

 

[karan2807]

s

View attachment 54512

Pls have a look on a screenshot , here sparepartdata is an array and contains in the data shown above, means I am using a 2d array. Can you help me with this because there is not much other code which is relevelant

 

[Fluff]

I asked you to post your code, not show an image of the array.

 

[karan2807]

I asked you to post your code, not show an image of the array.

Private function ProcessInvoice()

SparepartData = GetSparepartData(StrUniInvoiceNo(datarow))
 
 SparepartDatarow = Application.WorksheetFunction.Index(SparepartData, 1, 0)
 
End Function


Private Function GetSparepartData(StrInvoiceNumber)

Application.ScreenUpdating = False

Application.Calculation = xlCalculateManual

Application.EnableEvents = False

'Set objRecordSet = RunSQL("C:\Users\karora3\OneDrive - Deloitte (O365D)\Documents\BMW Germany\Docs\test1.xlsx", "Select [BUNO],[RECHNR],[RECHDAT],[AUF_ART],[SELBZAHLER],[TYP_KEY],[BAUREIHE],[ERSTZULASSUNG],[AW_NUMMER],[AW_MENGE],[TEILENUMMER],[TEILE_MENGE],[STORNO],[STORNO_TXT],0,[RABATT] from [CalculationSheet$] where [RECHNR] ='" & StrInvoiceNumber & "' AND [TEILENUMMER] Is Not Null")

Set objRecordSet = RunSQL("C:\Users\karora3\OneDrive - Deloitte (O365D)\Documents\BMW Germany\Docs\test1.xlsx", "Select [BUNO],[RECHNR],[AUF_ART]from [CalculationSheet$] where [RECHNR] ='" & StrInvoiceNumber & "' AND [TEILENUMMER] Is Not Null")


SparepartTableArr = RecordsetToArray(objRecordSet)

Set objRecordSet = Nothing

Application.ScreenUpdating = True

Application.Calculation = xlCalculateAutomatic

Application.EnableEvents = True

GetSparepartData = SparepartTableArr


End Function

In first function and I am calling the second function which gives me the array in the sparepartdata then I am using the expression
SparepartDatarow = Application.WorksheetFunction.Index(SparepartData, 1, 0)
, which is giving me the error

 

[Fluff]

Thanks for posting the code, unfortunately there is no way of testing it at this end so I cannot tell why you are getting the error.

 

[karan2807]

One reall

Private function ProcessInvoice()

SparepartData = GetSparepartData(StrUniInvoiceNo(datarow))
 
 SparepartDatarow = Application.WorksheetFunction.Index(SparepartData, 1, 0)
 
End Function


Private Function GetSparepartData(StrInvoiceNumber)

Application.ScreenUpdating = False

Application.Calculation = xlCalculateManual

Application.EnableEvents = False

'Set objRecordSet = RunSQL("C:\Users\karora3\OneDrive - Deloitte (O365D)\Documents\BMW Germany\Docs\test1.xlsx", "Select [BUNO],[RECHNR],[RECHDAT],[AUF_ART],[SELBZAHLER],[TYP_KEY],[BAUREIHE],[ERSTZULASSUNG],[AW_NUMMER],[AW_MENGE],[TEILENUMMER],[TEILE_MENGE],[STORNO],[STORNO_TXT],0,[RABATT] from [CalculationSheet$] where [RECHNR] ='" & StrInvoiceNumber & "' AND [TEILENUMMER] Is Not Null")

Set objRecordSet = RunSQL("C:\Users\karora3\OneDrive - Deloitte (O365D)\Documents\BMW Germany\Docs\test1.xlsx", "Select [BUNO],[RECHNR],[AUF_ART]from [CalculationSheet$] where [RECHNR] ='" & StrInvoiceNumber & "' AND [TEILENUMMER] Is Not Null")


SparepartTableArr = RecordsetToArray(objRecordSet)

Set objRecordSet = Nothing

Application.ScreenUpdating = True

Application.Calculation = xlCalculateAutomatic

Application.EnableEvents = True

GetSparepartData = SparepartTableArr


End Function

In first function and I am calling the second function which gives me the array in the sparepartdata then I am using the expression
SparepartDatarow = Application.WorksheetFunction.Index(SparepartData, 1, 0)
, which is giving me the error. Also one strange thing that I have noticed -my 2d array has 15 columns but If I am having only 3 columns in an array it is working fine.