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

[abdelfattah]

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

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

 

[davesexcel]

Are you attempting to loop through column H and do something?

 

[abdelfattah]

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]

What does the macro do?

Come-on explain what is supposed to be happening.

 

[abdelfattah]

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]

What does this do?

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

    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

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]

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]

Glad it worked.
Thanks for letting us know. Good Luck