Error 1004 - Application defined or object defined error.

[MeaclH]

Hi all,

I am getting this error when I run my macro. The weird thing is, when I go to run it again the error doesnt occur.
I select 2 different cells, the msg box pops up, then the error code. The note gets added to the cell but the rest of the code doesn't run.
I then click the button for the macro to run and it executes without fail.
Any ideas?

Cheers,

Hayden

Sub ActionSwap()
Dim sCmt As String
Dim i As Long
Dim rCell As Range
Dim area1 As Variant, area2 As Variant, swapval As Variant
    
sCmt = InputBox( _
  Prompt:="Enter details of the swap. Including when it was actioned and by who." & vbCrLf & _
  "Comment will be added to all cells in Selection", _
  Title:="DAO Swap Details")
If sCmt = "" Then
    MsgBox "No comment added"
Else
    For Each rCell In Selection
    With rCell
        If .Comment Is Nothing Then
        .AddComment.Text sCmt
    Else
        .Comment.Text sCmt & vbLf & .Comment.Text
    End If
End With
Next
End If
Set rCell = Nothing
If Selection.Areas.Count <> 2 Then Exit Sub
If Selection.Areas(1).Columns.Count <> Selection.Areas(2).Columns.Count Then
    MsgBox ("Selection areas must have the same number of columns")
    Exit Sub
End If
area1 = Selection.Areas(1)
area2 = Selection.Areas(2)
If Selection.Areas(1).Columns.Count = 1 Then
    swapval = area1
    area1 = area2
    area2 = swapval
Else
    For i = LBound(area1, 2) To UBound(area1, 2)
        swapval = area1(1, i)
        area1(1, i) = area2(1, i)
        area2(1, i) = swapval
    Next
End If
Selection.Areas(1) = area1
Selection.Areas(2) = area2
swapval = True
End Sub

 

[MeaclH]

Hi all,

I am getting this error when I run my macro. The weird thing is, when I go to run it again the error doesnt occur.
I select 2 different cells, the msg box pops up, then the error code. The note gets added to the cell but the rest of the code doesn't run.
I then click the button for the macro to run and it executes without fail.
Any ideas?

Cheers,

Hayden

Sub ActionSwap()
Dim sCmt As String
Dim i As Long
Dim rCell As Range
Dim area1 As Variant, area2 As Variant, swapval As Variant
   
sCmt = InputBox( _
  Prompt:="Enter details of the swap. Including when it was actioned and by who." & vbCrLf & _
  "Comment will be added to all cells in Selection", _
  Title:="DAO Swap Details")
If sCmt = "" Then
    MsgBox "No comment added"
Else
    For Each rCell In Selection
    With rCell
        If .Comment Is Nothing Then
        .AddComment.Text sCmt
    Else
        .Comment.Text sCmt & vbLf & .Comment.Text
    End If
End With
Next
End If
Set rCell = Nothing
If Selection.Areas.Count <> 2 Then Exit Sub
If Selection.Areas(1).Columns.Count <> Selection.Areas(2).Columns.Count Then
    MsgBox ("Selection areas must have the same number of columns")
    Exit Sub
End If
area1 = Selection.Areas(1)
area2 = Selection.Areas(2)
If Selection.Areas(1).Columns.Count = 1 Then
    swapval = area1
    area1 = area2
    area2 = swapval
Else
    For i = LBound(area1, 2) To UBound(area1, 2)
        swapval = area1(1, i)
        area1(1, i) = area2(1, i)
        area2(1, i) = swapval
    Next
End If
Selection.Areas(1) = area1
Selection.Areas(2) = area2
swapval = True
End Sub

Actually, I've realised even the comments aren't being added correctly either. I am not sure what I have done to the code, or if adding other sheet codes has had an impact......help...

 

[MeaclH]

Hi all, I managed to figure it out. Thanks!

 

[Peter_SSs]

Hi all, I managed to figure it out. Thanks!

Good to hear you found a solution.
If you would like to post the solution then it is perfectly fine to mark your post as the solution to help future readers. Otherwise, please do not mark a post that doesn't contain a solution.

 

[MeaclH]

Good to hear you found a solution.
If you would like to post the solution then it is perfectly fine to mark your post as the solution to help future readers. Otherwise, please do not mark a post that doesn't contain a solution.

Thanks Peter. No problem, my mistake. The solution was me realising I had doubled up on a line of code
Cheers