VBA Run-time error "5"

[abshaw]

Hey guys,

i have this code running to export the data of my 2 sheets into a csv file but it get the Run-time error 5

Sub DumpSalesInfo()
Dim SalesPeople As New Collection
Dim Sales As New Collection

Dim SHEET_1 As Worksheet
Dim SHEET_2 As Worksheet

Dim SHEET_1_lastRow As Long
Dim SHEET_2_lastRow As Long

Dim csvDump As String

Dim errCount As Long
Dim i As Long, f As Long

Const DUMPFILE As String = "C:\Temp\dump.csv"

Set SHEET_1 = ActiveWorkbook.Sheets("FAIRWAY-EE") 'change to your sheet1
Set SHEET_2 = ActiveWorkbook.Sheets("CONSOLIDATEDDP") 'change to your sheet2

SHEET_1_lastRow = Sheet1.Cells(Sheet1.Rows.Count, 3).End(xlUp).Row
SHEET_2_lastRow = Sheet2.Cells(Sheet2.Rows.Count, 3).End(xlUp).Row

For i = 2 To SHEET_1_lastRow
With SHEET_1
On Error Resume Next
SalesPeople.Add Item:=Range2CSV(.Range(.Cells(i, 1), .Cells(i, 19))), _
key:=CStr(.Cells(i, 4))
If Err.Number <> 0 Then
MsgBox "Salesperson ID: " & .Cells(i, 4) & vbNewLine & vbNewLine & _
"Already exists in this collection.", vbOKOnly + vbInformation, "Error"
errCount = errCount + 1
End If
On Error GoTo 0
End With
Next

For i = 1 To SHEET_2_lastRow
With SHEET_2
On Error Resume Next
Sales.Add Item:=Range2CSV(.Range(.Cells(i, 3), .Cells(i, 9))), _
key:=CStr(.Cells(i, 3))
If Err.Number <> 0 Then
Dim tmp As String

tmp = Sales(CStr(.Cells(i, 3)))

Sales.Remove CStr(.Cells(i, 3))

Sales.Add Item:=tmp & "," & Range2CSV(.Range(.Cells(i, 1), .Cells(i, 19))), _
key:=CStr(.Cells(i, 3))
End If
On Error GoTo 0
End With
Next

csvDump = SalesPeople(2) & "," & Sales(Mid(SalesPeople(2), 2, 4))

For i = 4 To SalesPeople.Count
On Error Resume Next
csvDump = csvDump & vbNewLine & _
SalesPeople(i) & "," & Sales(Mid(SalesPeople(i), 2, 4))
If Err.Number <> 0 Then
csvDump = csvDump & vbNewLine & SalesPeople(i)
End If
On Error GoTo 0
Next

f = FreeFile

Open DUMPFILE For Output As #f
Print #f, csvDump
Close #f

MsgBox "Process Complete!" & vbNewLine & vbNewLine & _
"Errors: " & errCount, vbOKOnly + vbInformation, "CSV Dump"

Set SalesPeople = Nothing
Set Sales = Nothing

Set SHEET_1 = Nothing
Set SHEET_2 = Nothing
End Sub


Private Function Range2CSV(value As Range) As String
Dim tmp As String
Dim c As Range

For Each c In value.Cells
tmp = tmp & ",""" & c.value & """"
Next

Range2CSV = Mid(tmp, 2, Len(tmp) - 1)
End Function




can anyone please help, thankyou.

 

[TommyGun]

Where are you getting the runtime error?

Also, it would be good to keep any related replies to the original thread.

Thread #1: http://www.mrexcel.com/board2/viewtopic.php?p=390889
Thread #2: http://www.mrexcel.com/board2/viewtopic.php?p=392386

 

[Marc]

Looks like your getting hung up "Sales(Mid(SalesPeople(2), 2, 4))". Can Sales() take the string "Mid(SalesPeople(2), 2, 4)"?


MsgBox SalesPeople(2)
MsgBox Mid(SalesPeople(2), 2, 4)
csvDump0 = SalesPeople(2) '& "," & Sales(Mid(SalesPeople(2), 2, 4))
csvDump1 = Mid(SalesPeople(2), 2, 4)
csvDump2 = Sales(Mid(SalesPeople(2), 2, 4))
csvDump = SalesPeople(2) & "," & Sales(Mid(SalesPeople(2), 2, 4))

 

[abshaw]

i get the following error,

Run-time error "5":
Invalid procedure call or argument

on this line

csvDump = SalesPeople(1) & "," & Sales(Mid(SalesPeople(1), 2, 4))

 

[Marc]

abshaw, What is the layout of your sheets? What kind of data do you have in them? Can you post them?

I have loaded some bogus data and what I can see is happening is that you have placed the objects in the SALES() collection with the "key:=CStr(.Cells(i, 3))".

However, When you later try to access this data via "Sales(Mid(SalesPeople(2), 2, 4))" I don't believe the index is equal to your key.