VBA Offset Function giving Synthaxis error

Question

Currently doing the last bits of VBA on a macro. This macro is intented to via an Inputbox using Findstring, finding a row, copying it to a different worksheet, paste this row on the first empty row, and delete the row on the original worksheet.

If Not Rng Is Nothing Then
    Rng.EntireRow.Copy
    Worksheets("ScrapLogboek").Cells(Rows.Count, "A").End(xlUp).Offset(1, -1)
    Worksheets("ScrapLogboek").Range("A2").PasteSpecial
    Rng.EntireRow.Delete
Else
    MsgBox ("Niks gescrapt. Check of het B-nummer correct is.")
End If

End With
    MsgBox ("Logboek gescrapt.")
End If
End Sub

Now i keep getting a synthaxis-error on the Offset-function. What am i doing wrong? As said in the comments; Worksheets("ScrapLogboek").Cells(Rows.Count, "A").End(xlUp).Offset(1, -1). This line is giving me shit, for some reason.

Please add to your code example or tidy it up. You've got End With and End If with no starting condition. Which line is it failing on? What is wrong? — Tom
Excuse me Tom. Total code was too long, giving an error. I approved your edit. Line that it is failing on is; Worksheets("ScrapLogboek").Cells(Rows.Count, "A").End(xlUp).Offset(1, -1). It works perfectly without this, but keeps overwriting the data found, and copied to another worksheet. To prevent this from happening i wanted to try it with the Offset function. — RobExcel
You aren't doing anything with the offset. Don't you want to select it or assign it a value? — Vulthil
You're telling the offset to go 1 down on the Row and 1 to the left on the Column. As you're doing this in Column A there is no where for it to go giving you the error. — Tom
Tom, that was pretty stupid. Thank you for that correction. @Alex4336, i want the Pasting function below it to look for the first empty row and paste there, instead of pasting in A2 everytime, overwriting everything. — RobExcel

R3uK R3uK · Accepted Answer · 2015-12-02T13:18:50

You are in column A, so you can't Offset minus one column because there is none other on the left!

Try your code with Worksheets("ScrapLogboek").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0) and it'll work! ;)

And your code would always paste on A2 which is probably not the purpose, so here is the corrected version :

If Not Rng Is Nothing Then
    Rng.EntireRow.Copy
    Worksheets("ScrapLogboek").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial
    Rng.EntireRow.Delete
Else
    MsgBox ("Niks gescrapt. Check of het B-nummer correct is.")
End If

End With
    MsgBox ("Logboek gescrapt.")
End If
End Sub

VBA Offset Function giving Synthaxis error

1 Answers