Help Excel Macro not interpreting emply rows

[Rhodess]

I made a spreadsheet so I can extract data from a pdf and manipulate it into a qif file to import into Quicken.

All works well, but I have a bunch of blank rows where the macro places all the headers and fills the data with zeros. To stop this, if the row is empty I used

=IF(A1=""'"",Function)

So that it leaves the rows blank.

The problem is the macro to transpose the rows to column must still see that the cells have code to them and gives me a 1004 runtime error:
Method 'Range' of object '_Worksheet' failed

Set dataRange = Range(.Cells(12, 11), .Cells(.Rows.Count, 4).End(xlUp)) 'Data starts at 12th row and is 11 columns. First row starts with 4th column data'

Can someone please look at the spreadsheet at my dropbox link below, to see where the transpose to qif is breaking down, so that I can clear the output sheet and re transpose each time with variable data lengths.

https://www.dropbox.com/s/98d8la5laiirb8h/Fidelity-xls2qif Template.xlsm?dl=0

Thanks

 

[Rhodess]

I believe the issue is because I am running it from a macro button and the sheet does not have the focus. If I run the macro it works fine, but if I assign it to a button and run it, then I get the 1004 error:

Private Sub CommandButton1_Click()
' Transpose xls2qif
Dim dataRange As Range
Dim i As Long, j As Long, pointer As Long
Dim dataArray As Variant, outputArray As Variant, oneVal As Variant
 
With ThisWorkbook.Sheets("Convert")
    Set dataRange = Range(.Cells(12, 11), .Cells(.Rows.Count, 4).End(xlUp)) 'Data starts at 12th row and is 11 columns. First row starts with 4th column data'
End With
dataArray = dataRange.Value
ReDim outputArray(1 To dataRange.Rows.Count * dataRange.Columns.Count, 1 To 1)
For i = 1 To dataRange.Rows.Count
    For j = 1 To dataRange.Columns.Count
        pointer = pointer + 1
        outputArray(pointer, 1) = dataArray(i, j)
    Next j
Next i
 
ThisWorkbook.Sheets("Output").Range("A1").Resize(UBound(outputArray, 1), 1).Value = outputArray
Columns("A:A").ColumnWidth = 27
End Sub

Thanks

 

[Rhodess]

I have fixed the 1004 error by making the sheet active after the button press, but now I get a runtime error 424 object required.

Can someone please help?

Private Sub CommandButton1_Click()
' Transpose xls2qif
Dim dataRange As Range
Dim i As Long, j As Long, pointer As Long
Dim dataArray As Variant, outputArray As Variant, oneVal As Variant
 
With ThisWorkbook.Sheets("Convert").Activate
'Data starts at 12th row and is 11 columns. First row starts with 4th column data
    [COLOR=#ff0000]Set dataRange = Range(.Cells(12, 11), .Cells(.Rows.Count, 4).End(xlUp))[/COLOR]
End With
dataArray = dataRange.Value
ReDim outputArray(1 To dataRange.Rows.Count * dataRange.Columns.Count, 1 To 1)
For i = 1 To dataRange.Rows.Count
    For j = 1 To dataRange.Columns.Count
        pointer = pointer + 1
        outputArray(pointer, 1) = dataArray(i, j)
    Next j
Next i
 
ThisWorkbook.Sheets("Output").Range("A1").Resize(UBound(outputArray, 1), 1).Value = outputArray
Columns("A:A").ColumnWidth = 27
End Sub

I highlighted the offending row in red, but do not know how to fix it.

Thanks