Results 1 to 5 of 5

VBA Run-time error "5"

This is a discussion on VBA Run-time error "5" within the Excel Questions forums, part of the Question Forums category; Hey guys, i have this code running to export the data of my 2 sheets into a csv file but ...

  1. #1
    Board Regular
    Join Date
    Feb 2004
    Location
    Houston
    Posts
    51

    Default VBA Run-time error "5"

    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.
    Believe Everybody Trust Nobody

  2. #2
    MrExcel MVP TommyGun's Avatar
    Join Date
    Dec 2002
    Location
    Clear Lake, TX
    Posts
    4,202

    Default Re: VBA Run-time error "5"

    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

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Posts
    384

    Default Re: VBA Run-time error "5"

    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))

  4. #4
    Board Regular
    Join Date
    Feb 2004
    Location
    Houston
    Posts
    51

    Default Re: VBA Run-time error "5"

    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))
    Believe Everybody Trust Nobody

  5. #5
    Board Regular
    Join Date
    Feb 2002
    Posts
    384

    Default Re: VBA Run-time error "5"

    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.

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com