Getting Excel Vba Runtime Error 1004

Question

Private Sub CommandButton3_Click()
    Dim rownum, startcol, endcol, holder As Integer
    rownum = InputBox("Enter the row that swapping process going to happen")
    startcol = InputBox("Enter the column of the cell you want to swap")
    endcol = InputBox("Enter the column of the cell that you wanted swap with")
    holder = Cells(rownum, startcol).Value
    Cells(rownum, startcol).Value = Cells(rownum, endcol).Value
    Cells(rownum, endcol).Value = holder
End Sub

Gives

Runtime Error 1004 - "Run-time error '1004': Application-defined or object-defined error"

Can't seem to understand.

First thing I see is that your DIM statements need to be cleaned up...only holder is set as an Integer, everything else is a Variant. Second, you probably don't want rownum to be an integer as you could easily hit the limit on it in a big spreadsheet. — Rdster
– Rdster, Commented Mar 15, 2018 at 13:14
You will also want to assign the parent sheet to every range object: Worksheets("Sheet1").Cells(... — Scott Craner
– Scott Craner, Commented Mar 15, 2018 at 13:16
Since you didn't Dim the variables correctly, I'm guessing the problem is rownum etc get populated with String instead. Fix the variables and the rest will be OK — Vegard
– Vegard, Commented Mar 15, 2018 at 13:16
Thank you so much, such a simple mistake. I did not realize that they were variant. — Ahmet Taha Aymeir
– Ahmet Taha Aymeir, Commented Mar 15, 2018 at 13:17
As per your last question also, I recommend asking questions in the form "How do I ..." or "How to ...". The form of "please help me" is often understood here to mean "do my work for me", even if that's not what you intended. — halfer
– halfer, Commented Mar 16, 2018 at 13:22

user4039065 · Accepted Answer · 2018-03-15 13:17:40Z

Dim your vars properly and use application.inputbox for more options.

'using column numbers
Private Sub CommandButton3_Click()
    Dim rownum as long, startcol as long, endcol as long, holder As variant
    rownum = application.InputBox("Enter the row that swapping process going to happen", type:=1)
    startcol = application.InputBox("Enter the column number of the cell you want to swap", type:=1)
    endcol = application.InputBox("Enter the column numbedr of the cell that you wanted swap with", type:=1)
    holder = Cells(rownum, startcol).Value
    Cells(rownum, startcol).Value = Cells(rownum, endcol).Value
    Cells(rownum, endcol).Value = holder
End Sub

'using column letters
Private Sub CommandButton3_Click()
    Dim rownum as long, startcol as string, endcol as string, holder As variant
    rownum = application.InputBox("Enter the row that swapping process going to happen", type:=1)
    startcol = application.InputBox("Enter the column letter of the cell you want to swap", type:=2)
    endcol = application.InputBox("Enter the column letter of the cell that you wanted swap with", type:=2)
    holder = Cells(rownum, startcol).Value
    Cells(rownum, startcol).Value = Cells(rownum, endcol).Value
    Cells(rownum, endcol).Value = holder
End Sub

The type:=1 tells application.inputbox to expect a number; type:=2 tells application.inputbox to expect a string.

Collectives™ on Stack Overflow

Getting Excel Vba Runtime Error 1004

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related