How can I stop my vba code from giving me an error 424

0 votes

I've created some code that can perform a lot of things (with a lot of help from you guys, thanks). In essence, it performs a loop through each worksheet beginning with MW, deleting some columns, performing some procedures, and changing some column names. The code is now working, but when I add the loop through the spreadsheets, I get an error 424 on the line that says, "If not Rng Is Nothing Then Rng.EntireColumn.Delete." How can I fix this?
My theory is that because my ws loop doesn't function properly, the code cannot run because the sheet has already been processed.

Here's my code

    Dim Cl As Range, Rng As Range
    Dim Cl2 As Range, Rng2 As Range
    Dim Cl3 As Range, Rng3 As Range
    Dim c As Range
    Dim Cl4 As Range, Rng4 As Range
    Dim Lastrow As Long
    Dim ws As Worksheet

    For Each ws In ActiveWorkbook.Worksheets
        If ws.Name Like "MW*" Then

            For Each Cl In Range("A1:J1")
                Select Case Cl.Value
                    Case "#", "Coupler Detached", "Coupler Attached", "Host Connected", "End Of File", "ms"
                        If Rng Is Nothing Then Set Rng = Cl Else Set Rng = Union(Rng, Cl)
                End Select
            Next Cl
            If Not Rng Is Nothing Then Rng.EntireColumn.Delete

            For Each Cl4 In Range("D1")
                Select Case Cl4.Value
                    Case "Abs Pres (kPa) c:1 2"
                        If Rng4 Is Nothing Then Set Rng4 = Cl4 Else Set Rng4 = Union(Rng4, Cl4)
                End Select
            Next Cl4
            If Not Rng4 Is Nothing Then
                Application.ScreenUpdating = False

                Lastrow = Cells(Rows.Count, "D").End(xlUp).Row
                For Each c In Range("D2:D" & Lastrow)
                    c.Value = c.Value * 0.101972
                Next
                Application.ScreenUpdating = True

            End If


            For Each Cl2 In Range("A1:J1")
                Select Case Cl2.Value
                    Case "Abs Pres (kPa) c:1 2"
                        If Rng2 Is Nothing Then Set Rng2 = Cl2 Else Set Rng = Union(Rng, Cl2)
                End Select
            Next Cl2
            If Not Rng2 Is Nothing Then Rng2.Value = ("LEVEL")


            For Each Cl3 In Range("A1:J1")
                Select Case Cl3.Value
                    Case "Temp (°C) c:2"
                        If Rng3 Is Nothing Then Set Rng3 = Cl3 Else Set Rng = Union(Rng, Cl3)
                End Select
            Next Cl3
            If Not Rng3 Is Nothing Then Rng3.Value = ("TEMPERATURE")

        End If
    Next ws
Feb 9, 2023 in Others by Kithuzzz
• 38,010 points
458 views

1 answer to this question.

0 votes

Object Variables in Loops

  • The main issue was that you cannot combine cells with an invalid range so you need to 'reset' the rng* variables i.e. explicitly set them to nothing. For example, in the first iteration, there were cells combined into rng. Now you delete rng and the variable's state is still Nothing although you have deleted the range making it invalid. In the next iteration, you try to combine this invalid range (which is Nothing) with a matching cell via Union so the error occurs. BTW, even if you didn't delete the range, again, an error would occur because you cannot combine ranges from different worksheets.
  • In the following code, pay attention to how this (Set rng = Nothing) is done after each stage per worksheet when only a single rng variable is used.
  • Also, note how ws and cell are 'safe', they don't need to be reset, since For Each... could be translated as something like Set ws = WhatEverWorksheet or Set cell = WhatEverCell.
Sub ALot()
    
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    ' If it's not, change back to 'ActiveWorkbook'.
   
    Application.ScreenUpdating = False
    
    Dim ws As Worksheet, rng As Range, cell As Range

    For Each ws In wb.Worksheets
        
        If ws.Name Like "MW*" Then
            
            ' Delete columns.
            For Each cell In ws.Range("A1:J1").Cells
                Select Case CStr(cell.Value)
                    Case "#", "Coupler Detached", "Coupler Attached", _
                            "Host Connected", "End Of File", "ms"
                        If rng Is Nothing Then Set rng = cell Else Set rng = Union(rng, cell)
                End Select
            Next cell
            If Not rng Is Nothing Then
                rng.EntireColumn.Delete
                Set rng = Nothing ' reset
            End If
            
            ' Check 4th column.
            Set cell = ws.Range("D1")
            If CStr(cell.Value) = "Abs Pres (kPa) c:1 2" Then
                Set rng = ws.Range("D2", ws.Cells(ws.Rows.Count, "D").End(xlUp))
                rng.Value = ws.Evaluate(rng.Address & "*0.101972")
                Set rng = Nothing ' reset
            End If

            ' Check "Abs Pres (kPa) c:1 2".
            For Each cell In ws.Range("A1:J1").Cells
                Select Case CStr(cell.Value)
                    Case "Abs Pres (kPa) c:1 2"
                        If rng Is Nothing Then Set rng = cell Else Set rng = Union(rng, cell)
                End Select
            Next cell
            If Not rng Is Nothing Then
                rng.Value = "LEVEL"
                Set rng = Nothing ' reset
            End If
             
            ' Check "Temp (°C) c:2".
            For Each cell In ws.Range("A1:J1").Cells
                Select Case cell.Value
                    Case "Temp (°C) c:2"
                        If rng Is Nothing Then Set rng = cell Else Set rng = Union(rng, cell)
                End Select
            Next cell
            If Not rng Is Nothing Then
                rng.Value = "TEMPERATURE"
                Set rng = Nothing ' reset
            End If

        'Else ' is not like "MW*"; do nothing
        End If
    
    Next ws

    Application.ScreenUpdating = True

    MsgBox "Worksheets processed.", vbInformation

End Sub
answered Feb 9, 2023 by narikkadan
• 63,420 points

Related Questions In Others

0 votes
1 answer

How can I open a URL in Android's web browser from my application?

ry this: Intent browserIntent = new Intent(Intent.ACTION_VIEW, Uri ...READ MORE

answered Jun 14, 2022 in Others by polo
• 1,480 points
4,049 views
0 votes
0 answers

How can i solve NoClassdeffoundError on my mobile?

Oct 15, 2019 in Others by SolomonTS
• 120 points
369 views
0 votes
1 answer

Lucky patcher, how can I protect from it?

public void checkSignature(final Context context) { try ...READ MORE

answered Feb 8, 2022 in Others by Rahul
• 9,670 points
683 views
0 votes
1 answer

Runtime Error when trying to Loop Worksheets

Copy To Multiple Worksheets A Quick Fix Option Explicit Sub ...READ MORE

answered Mar 19, 2023 in Others by Kithuzzz
• 38,010 points
164 views
0 votes
1 answer

Sort Excel worksheets based on name, which is a date

Sorting sheets of a workbook are rather ...READ MORE

answered Oct 17, 2022 in Others by narikkadan
• 63,420 points
336 views
0 votes
1 answer

VBA Loop To Import Changing File Names

You can use a FOR loop and ...READ MORE

answered Jan 12, 2023 in Others by narikkadan
• 63,420 points
504 views
0 votes
1 answer

Trying to run different macros based on value of cell in a range

This demonstrates one approach to loop through ...READ MORE

answered Jan 19, 2023 in Others by narikkadan
• 63,420 points
251 views
0 votes
1 answer

How can I add a wildcard to my if formula (that also contains an or condition)

3 Try: The formula in B1: =IF(SUM(COUNTIF(A1,{"* IDE","* IDE-?"})),"Y","N") Or, a little ...READ MORE

answered Feb 2, 2023 in Others by narikkadan
• 63,420 points
292 views
0 votes
1 answer

How can I convert excel to PDF by Libreoffice and keep all format from excel file?

"Times New Roman" typeface does not have ...READ MORE

answered Oct 3, 2022 in Others by narikkadan
• 63,420 points
1,220 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP