0

Issues with code executing when the empty cell is a part of a table.

Everything.

Private Sub Data()

Dim Answer As String
Dim Box As String

'Question to confirm

    Box = "Have you pasted the data into the tab?"

'Display MsgBox

    Answer = MsgBox(Box, vbQuestion + vbYesNo, "Data")

'Code for No press

    If Answer = vbNo Then
    MsgBox "Please paste the data in the tab before running this code"

    Else

    If Answer = vbYes Then
    MsgBox "Running code."

    Sheets("Data").Activate
    Range("D2:AO2").Copy

    Dim LR4 As Long, cell4 As Range, rng4 As Range
    LR4 = Range("D" & Rows.Count).End(xlUp).Row
    For Each cell4 In Range("D" & LR4)
        If cell4.Value <> "" Then
                Set rng4 = cell4
            Else
                Set rng4 = Union(rng4, cell4)
            End If

                Next cell4
    rng4.Offset(1, 0).PasteSpecial

    End If
    End If

End Sub

The code runs through properly if it is run and the cell is not part of a table. If the cell is part of a table however the run time error pops up.

Tom
  • 1
  • 2
  • `MsgBox` returns an integer rather than a string (though I don't think that this is your problem). In any event -- what line throws the error, and exactly how can it be triggered? – John Coleman Jul 10 '19 at 12:07
  • Best way to re-organise to avoid the error? Basically, the error is caused at "rng4.Offset(1, 0).PasteSpecial. The code runs through perfectly fine if it attempts to paste in an empty cell that is not a part of an excel table. If it is a part of a table it breaks. – Tom Jul 10 '19 at 12:09
  • What's the point of this loop which goes through one cell `For Each cell4 In Range("D" & LR4)`? And I don't think this is valid syntax `Big = Range("D2:AO2").Copy`. – SJR Jul 10 '19 at 12:35
  • The "Big" was a mistake. Wasn't intended to be included in the question, I'll delete it out. I seem to get a 1004 error when I don't include the loop. – Tom Jul 10 '19 at 12:40
  • You should read up on indenting your code to make it easier to read. Try inserting `if not rng4 is nothing then rng4.Offset(1, 0).PasteSpecial` to see if the error stops. – SJR Jul 10 '19 at 12:46
  • @AAA Essentially there is a huge table of data. The first empty row is not a part of this huge table until data is manually pasted into the first 3 columns of the table. When the code is run after this is done, the run time error occurs. SJR noted. I am fairly new to this so am still learning about best practices. I've pasted that piece of code in and this time I received a run time 13 error. – Tom Jul 10 '19 at 12:54
  • What are you trying to achieve actually? Describe the logic behind the code. – AAA Jul 10 '19 at 12:56
  • It's a 5 step code 1) Display msg box to identify user intentions. 2) Copy the first row of data (Range("D2:AO2").Copy) 3) Look for the first empty row of data, or rather the first empty cell in column D 4) Paste the copied data in this row 5)* This is the step I was going to implement afterwards - paste the copied data in every empty row where there is a value in Column A. – Tom Jul 10 '19 at 13:01
  • So I've found what I need with "Cells(Rows.Count, "D").End(xlUp).Offset(1)". The issue is, since I have a table, Excel finds the first cell below the table as it considers the table to hold a value. Is there any way to bypass this – Tom Jul 10 '19 at 13:53
  • See [this question](https://stackoverflow.com/questions/43631926/lastrow-and-excel-table) perhaps. – BigBen Jul 10 '19 at 14:30

0 Answers0