VBA Run-time error "5"

abshaw

Board Regular
Joined
Feb 12, 2004
Messages
53
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.
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
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))
 
Upvote 0
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))
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,140
Members
448,551
Latest member
Sienna de Souza

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top