CODEWITHSUNDEEP
vbaexcel
Venoox
Venoox

Reputation: 66

Copy the cell value to another cell if

I'm writing VBA in Excel and I have a cell with some numbers in it and I have a "shopping cart" range and I when I click on the button I want to copy the cell value to the "cart" but if the first row in the cart is already full it should move on the next row and do this until it finds a row that it empty and paste it in there.

I tried to do it but got a problem

Sub Gumb1_Klikni()

  Range("B1").Select
  Selection.Copy
  Range("J2").Select
  If IsEmpty(ActiveCell) Then
  Selection.PasteSpecial xlPasteAll
  Else
  Set nextcell = ActiveCell.Offset(1, 0)
  Range(nextcell).Select
  ActiveSheet.Paste
  End If

End Sub

It gives me Error 1004 "Method 'Range' of object '_Global' failed" at

Range(nextcell).Select

Upvotes: 0

Views: 69

Answers (1)

Shai Rado
Shai Rado

Reputation: 33662

If you would define Dim nextcell as Range at the beginning of your Sub , all you need to do is:

nextcell.Select

However, you could use the "Cleaner" version below, without the need to use Select or Selection :

Option Explicit

Sub Gumb1_Klikni()

Dim nextCell As Range

Range("B1").Copy
If IsEmpty(Range("J2")) Then
    Range("J2").PasteSpecial xlPasteAll
Else
    Set nextCell = Range("J2").Offset(1, 0)
    nextCell.PasteSpecial
End If

End Sub

Edit 1: after PO clarification:

Sub Gumb1_Klikni()

Dim LastRow As Long

' get last row with data in column "J"
LastRow = Cells(Rows.Count, "J").End(xlUp).Row
If LastRow < 1 Then LastRow = 1

Range("B1").Copy
Range("J" & LastRow + 1).PasteSpecial xlPasteAll

End Sub

Upvotes: 2

Related Questions