Excel Macro Help runtime error

[peterperkins]

I have a fairly simple VBA macro to sort/transpose a massive column of data into rows whenever it finds a key value. 0xB2 in this case

This has worked for years under Excel 2003 and 2010 but now it gives me runtime error 1004 which I don't understand .
'Application defined or object defined error.' Help made no sense as I am no Excel expert.

Why has it suddenly stopped working???

Sub transpose()
    Dim sourceRow, targetRow, targetColumn As Integer

    ' find first empty row in target sheet
    targetRow = 1
    While (Sheets(2).Cells(targetRow, 1) <> "")
        targetRow = targetRow + 1
    Wend

    sourceRow = 1
    targetColumn = 1
    targetRow = 0
    Do
        If (Sheets(1).Cells(sourceRow, 1) = "0xB2") Then

                'if it is, then start a new line by incrementing th erow and resetting column to 1
                targetRow = targetRow + 1
                targetColumn = 1

        End If
        'transpose the cells in increment sourcerow and target column
        Sheets(2).Cells(targetRow, targetColumn) = Sheets(1).Cells(sourceRow, 1)
        sourceRow = sourceRow + 1
        targetColumn = targetColumn + 1

    Loop Until (Sheets(1).Cells(sourceRow, 1) = "") 'stop when input cell is empty

End Sub

[snuffy]

Have you removed a sheet or reordered them ?

Sheets(1) is the first sheet, and so on.

Try replacing the index with the actual sheet name :

Sheets(1) would become Sheets("<Sheet Name>"). So, for example Sheets("Sheet1") or whatever yours in called.

And of course, do the same for Sheets(2).

[peterperkins]

Names look ok to me..

File is here..

https://docs.google.com/spreadsheets/d/1iVD6aCOYYt...

[snuffy]

Yep, that will crash every time because after it's worked out the first empty row (targetRow) on Sheet2, it then resets it to 0.

Because your first entry in Sheet1 is not "0xB2", it does not execute targetRow = targetRow + 1

So, therefore, when it then executes Sheets(2).Cells(targetRow, targetColumn) = Sheets(1).Cells(sourceRow, 1), targetrow is still 0, so it crashes (because row 0 does not exits in Excel).

However, on sheet1, if the very first entry was "0xB2", then it will not crash, because targetRow = targetRow + 1 will have executed for the first time, so targetRow will now be 1.

Therefore, if previously, the first value on sheet1 was "0xB2" then it would run, but now it's not, so it crashes.

The answer is to just remove targetRow = 0 (i.e. the line before the "Do").

[peterperkins]

Brilliant that fixed it thanks. Have a virtual pint.

[snuffy]

peterperkins said:

Brilliant that fixed it thanks. Have a virtual pint.

Thank you !!