fix run time error mismatch" disable macro if cell is empty

abdelfattah

Well-known Member
Joined
May 3, 2019
Messages
982
Office Version
  1. 2010
hello
this code works but i need adding line to disable macro if col h="" and if any cell contain data in col h then run the macro i try adding it but not success my attempt colored by red i need advise from anybody can help me
VBA Code:
Sub MoveRows()
    Application.ScreenUpdating = False
    Dim LastRow As Long, srcWS As Worksheet, desWS As Worksheet
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Set srcWS = Sheets("Data")
    Set desWS = Sheets("Moved")

    With srcWS
 [B][COLOR=rgb(226, 80, 65)]If .Columns(8).Value = "" Then[/COLOR][/B]
    Exit Sub
    else
        .Cells(1, 1).CurrentRegion.AutoFilter 8, "moved"
        .AutoFilter.Range.Offset(1).Copy desWS.Cells(desWS.Rows.Count, "A").End(xlUp).Offset(1)
        .AutoFilter.Range.Offset(1).EntireRow.Delete
        .Range("A1").AutoFilter
        End If
    End With
    Application.ScreenUpdating = True
End Sub
thanks
 

Some videos you may like

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

abdelfattah

Well-known Member
Joined
May 3, 2019
Messages
982
Office Version
  1. 2010
yes it based on col h if is empty don't run macro if any cell in col h some data even some cells are empty then run the macro
 

davesexcel

Well-known Member
Joined
Feb 26, 2006
Messages
1,089
What does the macro do?

Come-on explain what is supposed to be happening.
 

abdelfattah

Well-known Member
Joined
May 3, 2019
Messages
982
Office Version
  1. 2010

ADVERTISEMENT

sorry daves i thought the code is clear that's why i don't put enough explain so actually the code filter data based on col h contains word "moved" and copy from sheet "data" to sheet "moved" so what i want is if col h doesn't contain "moved or empty then nothing copy to sheet "moved"
 

jolivanes

Well-known Member
Joined
Sep 5, 2004
Messages
1,708
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
What does this do?
Code:
If .Columns(8).Value = ""

I have not tried it but you can do that on a copy of your workbook (Change references, like Sheet4, as required.
Change this
Code:
    With srcWS
 [B][COLOR=rgb(226, 80, 65)]If .Columns(8).Value = "" Then[/COLOR][/B]
    Exit Sub
    else
        .Cells(1, 1).CurrentRegion.AutoFilter 8, "moved"
        .AutoFilter.Range.Offset(1).Copy desWS.Cells(desWS.Rows.Count, "A").End(xlUp).Offset(1)
        .AutoFilter.Range.Offset(1).EntireRow.Delete
        .Range("A1").AutoFilter
        End If
    End With
to this
Code:
If Application.CountIf(Sheet4.Columns(8), "moved") = 0 Then MsgBox "No data or no ""moved"" in Column ""H"" ":Exit Sub
    With srcWS
        .Cells(1, 1).CurrentRegion.AutoFilter 8, "moved"
        .AutoFilter.Range.Offset(1).Copy desWS.Cells(desWS.Rows.Count, "A").End(xlUp).Offset(1)
        .AutoFilter.Range.Offset(1).EntireRow.Delete
        .Range("A1").AutoFilter
        End If
    End With
 

abdelfattah

Well-known Member
Joined
May 3, 2019
Messages
982
Office Version
  1. 2010
about this
What does this do?
Code:
If .Columns(8).Value = ""
i was trying if col h= "" then nothing copy to sheet" moved" that's it
thanks for your updating it works
 

jolivanes

Well-known Member
Joined
Sep 5, 2004
Messages
1,708
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
Glad it worked.
Thanks for letting us know. Good Luck
 

Watch MrExcel Video

Forum statistics

Threads
1,127,316
Messages
5,623,968
Members
416,002
Latest member
Neshx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top