CODEWITHSUNDEEP
excelvbacsv
B Hall
B Hall

Reputation: 1

Why do I get this error when exporting an excel file to a CSV with Visual Basics?

When the error message appears and I click debug it brings up the code in visual basics which I also have attached.

Error Message

To fix the problem we have to:
1. Backspace the yellow highlighted line to the previous line
2. Then return it to the original position. Doing so “moves” the yellow/error highlight to the next line.
3. Continue “pushing” the yellow/error highlight to line 1If CICheck <> “CI” And Cost > 0 Then1
4. Re-run program in VBE.
5. Close VBE
6. Exported CSV should exist in the CSV tab of the original Excel file.

 AltContract = Cells(j, 1)
 CICheck = Cells(j, 2)


If Len(AltContract) > 0 Then 'picking CI num between CI group or Alt CI
    SetContract = AltContract

Else 'no alt contract

    For i = 1 To 1000 'loop to set CI from above
    If Cells(j - i, 2) = "CI" Then '<~~~ ERRROR HERE
    BillDsc = Cells(j - i, 7)
    BillGrp = Cells(j - i, 6)
        Exit For
    End If
    Next
End If


If CICheck <> "CI" And Cost > 0 Then


Do Until Cells(j + i, 2) <> "CI"

    If Len(Cells(j + i, 1)) > 0 Then GoTo NextIteration

   i = i + 1

Upvotes: 0

Views: 24

Answers (1)

urdearboy
urdearboy

Reputation: 14580

You need to qualify your Cells object.

You can do this directly by using Thisworkbook.Sheets("Sheet1").Cells(j-1, 2)
(Hopefully j here is an integer that is properly defined elsewhere in your code)

A faster way to do this would be to create a shortcut to your qualification by using: 

Dim ws as WorkSheet
Set ws = Thisworkbook.Sheets("Sheet1")

and then reffering to your qualification by using ws.Cells(j-1, 2)

Another way to do this is use the With block. 

Dim ws as WorkSheet
Set ws = Thisworkbook.Sheets("Sheet1")

    With ws
        .Cells(j-1, 2)
    End With

or

With Thisworkbook.Sheets("Sheet1")
    .Cells(j-1, 2)
End With

Upvotes: 1

Related Questions