I am still having a problem with my excel macro when I assign it to a button and I can't figure out how to make the sheet active again.
I originally posted a few days ago with
, but I have had no responses.
Below is the code that is causing the issue:
Originally I had a 1004 error, but I fixed that by adding .Activate to my workbook ("convert"), but now I get a 424 error at the point I highlighted in red.
I tried changing from an array, adding/removing Set but no luck.
Can someone please help, since I'm stumped at this point?
Thanks
I originally posted a few days ago with
HTML:
http://www.mrexcel.com/forum/excel-questions/804287-help-excel-macro-not-interpreting-emply-rows.html#post3932860
Below is the code that is causing the issue:
Code:
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 tried changing from an array, adding/removing Set but no luck.
Can someone please help, since I'm stumped at this point?
Thanks