CODEWITHSUNDEEP
vbaexcel
user2763151
user2763151

Reputation: 1

VBA 1004 Error when trying to parse scraped data

I got 2 questions:

I have 4000 rows of scraped data from a website, I am trying to clean up and get the relevant data (from columns B-G) from all non empty rows.

This code produces a 1004 runtime error, in the debugger the set ranges are highlighted.

Sub Schaltfläche1_Klicken()
Dim i As Integer
Dim j As Integer
Dim varRangeselect1 As Range
Dim varRangeSelect2 As Range

For i = 1 To 20
    j = 1
    If Worksheets("Input").Cells(i, 2).Value <> "" Then
        Set varRangeselect1 = Worksheets("Input").Range(Cells(i, 1), Cells(i, 7))
        Set varRangeSelect2 = Worksheets("Output").Range(Cells(j, 1), Cells(j, 7))
        varRangeselect1.Copy
        varRangeSelect2.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        j = j + 1
    End If
Next i

End Sub

In the output sheet I want the data to be posted in the first row that is empty, is the usage of j here suitable for that task?

thank you very much

Upvotes: 0

Views: 81

Answers (2)

Michiel van der Blonk
Michiel van der Blonk

Reputation: 720

You're not really cleaning up. All you seem to be doing is removing rows where the value in column B is empty. For that you could just use Data > Filter.

You can also use Data > Filter in a macro, and since you are using VBA anyway, no reason to loop.

Upvotes: 1

Siddharth Rout
Siddharth Rout

Reputation: 149325

You need to fully qualify the ranges. For example

Set varRangeselect1 = Worksheets("Input").Range( _
                                                 Worksheets("Input").Cells(i, 1), _
                                                 Worksheets("Input").Cells(i, 7) _
                                                )
Set varRangeSelect2 = Worksheets("Output").Range( _
                                                 Worksheets("Output").Cells(j, 1), _
                                                 Worksheets("Output").Cells(j, 7) _
                                                )

If the cells object is not fully qualified then you will get that 1004 Application Defined Error.

Upvotes: 2

Related Questions