CODEWITHSUNDEEP
excelvba
Niklas Toral
Niklas Toral

Reputation: 11

Error selecting all active cells in a sheet

Haven't done any programming in 10 years and are now trying to automate some administrative work. I need to create a CSV file from a certain data set. Running below code stand alone using this code to select the range:

Set rng = Sheets("HAOD").Range("A2", Cells(Range("a1000000").End(xlUp).row, Range("xfd1").End(xlToLeft).Column))

works fine but if I call the sub from my main program I get error 1004 and the debugger points to my range selection. I tried using this code for the range selection instead, I use this in a VBA vlookup function I found online and it works perfectly there:

rng = .Range("A2:" & tblEnd & .Cells(.Rows.Count, tblEnd).End(xlUp).row).Value

But when using this it first complains about the .-reference and when I add Sheets("HAOD") I get the 1004 error again. This time even when running the sub stand alone. What am I doing wrong?

Sub CreateCheckITfile()

    Call PopulateHAODtab
    Call SaveAsCSV

End Sub

Sub SaveAsCSV()

Dim myFile As String
Dim rng As Range
Dim cellValue As Variant
Dim i As Integer
Dim j As Integer

myFile = Application.DefaultFilePath & "\HAOD.csv"

Set rng = Sheets("HAOD").Range("A2", Cells(Range("a1000000").End(xlUp).row, Range("xfd1").End(xlToLeft).Column))

Open myFile For Output Lock Write As #1

For i = 1 To rng.Rows.Count
    Dim fileRow As String
    fileRow = ""
    For j = 1 To rng.Columns.Count
        cellValue = rng.Cells(i, j).Value

        If j = rng.Columns.Count Then
            fileRow = fileRow & cellValue
            Print #1, fileRow
        Else
            fileRow = fileRow & cellValue & ","
        End If
    Next j
Next i

Close #1
End Sub

Upvotes: 1

Views: 100

Answers (2)

Jchang43
Jchang43

Reputation: 891

Personally, I prefer to do it in little steps that way it lets me step through and see if everything is working as intended. This is kind of similar to the second version of what you tried, but I thought it would be more clear if the address property was used instead of tblend. Using a With block also helps keep it looking tidier.

Try replacing your set statement with this:

With Sheets("HAOD")
    lastrow = .Range("A" & .Rows.Count).End(xlUp).Row
    lastcol = .Cells.Find("*", searchorder:=xlByColumns, searchdirection:=xlPrevious).Column
    Set Rng = .Range("A2:" & .Cells(lastrow, lastcol).Address)
End With

Upvotes: 0

Harassed Dad
Harassed Dad

Reputation: 4704

All references need to specify the sheet, otherwise they default to the active sheet

 Set rng = Sheets("HAOD").Range("A2",  Sheets("HAOD").Cells( Sheets("HAOD").Range("a1000000").End(xlUp).row,  Sheets("HAOD").Range("xfd1").End(xlToLeft).Column))

Upvotes: 1

Related Questions