VBA If/then. If cell is blank run macro, if not stop.

Status
Not open for further replies.
K

klafontaine

New Member
Joined
May 14, 2019
Messages
3
Hi... it's been 8 years but maybe someone will see this.
I also want to write in VBA, if cell is not blank, then do this.
And then stop. Acting only if the cell is not blank...
This is based on a column of numbers, updated monthly. So when the month is updated, I want the macro to run.
But I get the End If without block if error... over and over.
This is my code so far.

Private Sub Worksheet_Change(ByVal Target As Range)

If IsEmpty(Cells(F3).Value) = False Then Call Macro1
End If
If Not IsEmpty(Cells(F21).Value) Then Call Macro2
End If
If Not IsEmpty(Cells(F39).Value) Then Call Macro3
End If
If Not IsEmpty(Cells(F57).Value) Then Call Macro4
End If
If Not IsEmpty(Cells(F75).Value) Then Call Macro5
End If
If Not IsEmpty(Cells(F93).Value) Then Call Macro6
End If
If Not IsEmpty(Cells(F111).Value) Then Call Macro7
End If
If Not IsEmpty(Cells(F129).Value) Then Call Macro8
End If
If Not IsEmpty(Cells(F147).Value) Then Call Macro9
End If
If Not IsEmpty(Cells(F164).Value) Then Call Macro10
End If
If Not IsEmpty(Cells(F183).Value) Then Call Macro11
End If
If Not IsEmpty(Cells(F201).Value) Then Call Macro12
End If
End Sub
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
Sort by date Sort by votes
Hi all.
I need to write in VBA, "if cell is not blank, then run macro. And then stop." Activating only if the cell is not blank.
This is based on a column of numbers, updated monthly. So when the month is updated, I want the macro to run which updates a slicer on the dashboard for the new month.
But I get the "End If without block if" error... over and over.
This is my code so far.
I've also tried:
If IsEmpty(Cells(F3).Value) = False Then Call Macro1


Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
    
    If Not IsEmpty(Cells(F3).Value) Then Call Macro1
    End If
    If Not IsEmpty(Cells(F21).Value) Then Call Macro2
    End If
    If Not IsEmpty(Cells(F39).Value) Then Call Macro3
    End If
    If Not IsEmpty(Cells(F57).Value) Then Call Macro4
    End If
    If Not IsEmpty(Cells(F75).Value) Then Call Macro5
    End If
    If Not IsEmpty(Cells(F93).Value) Then Call Macro6
    End If
    If Not IsEmpty(Cells(F111).Value) Then Call Macro7
    End If
    If Not IsEmpty(Cells(F129).Value) Then Call Macro8
    End If
    If Not IsEmpty(Cells(F147).Value) Then Call Macro9
    End If
    If Not IsEmpty(Cells(F164).Value) Then Call Macro10
    End If
    If Not IsEmpty(Cells(F183).Value) Then Call Macro11
    End If
    If Not IsEmpty(Cells(F201).Value) Then Call Macro12
    End If
End Sub

Thank you!
 
Last edited by a moderator:
Upvote 0
Re: Want to write in VBA, If cell is not blank, do x

klafontaine said:
Hi... it's been 8 years but maybe someone will see this.
I also want to write in VBA, if cell is not blank, then do this.
And then stop. Acting only if the cell is not blank...
This is based on a column of numbers, updated monthly. So when the month is updated, I want the macro to run.
But I get the End If without block if error... over and over.
This is my code so far.

Private Sub Worksheet_Change(ByVal Target As Range)

If IsEmpty(Cells(F3).Value) = False Then Call Macro1
End If
If Not IsEmpty(Cells(F21).Value) Then Call Macro2
End If
If Not IsEmpty(Cells(F39).Value) Then Call Macro3
End If
If Not IsEmpty(Cells(F57).Value) Then Call Macro4
End If
If Not IsEmpty(Cells(F75).Value) Then Call Macro5
End If
If Not IsEmpty(Cells(F93).Value) Then Call Macro6
End If
If Not IsEmpty(Cells(F111).Value) Then Call Macro7
End If
If Not IsEmpty(Cells(F129).Value) Then Call Macro8
End If
If Not IsEmpty(Cells(F147).Value) Then Call Macro9
End If
If Not IsEmpty(Cells(F164).Value) Then Call Macro10
End If
If Not IsEmpty(Cells(F183).Value) Then Call Macro11
End If
If Not IsEmpty(Cells(F201).Value) Then Call Macro12
End If
End Sub
Click to expand...

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)    
    If IsEmpty(Cells(F3).Value) = False Then Call macro1
    If Not IsEmpty(Cells(F21).Value) Then Call Macro2
    If Not IsEmpty(Cells(F39).Value) Then Call Macro3
    If Not IsEmpty(Cells(F57).Value) Then Call Macro4
    If Not IsEmpty(Cells(F75).Value) Then Call Macro5
    If Not IsEmpty(Cells(F93).Value) Then Call Macro6
    If Not IsEmpty(Cells(F111).Value) Then Call Macro7
    If Not IsEmpty(Cells(F129).Value) Then Call Macro8
    If Not IsEmpty(Cells(F147).Value) Then Call Macro9
    If Not IsEmpty(Cells(F164).Value) Then Call Macro10
    If Not IsEmpty(Cells(F183).Value) Then Call Macro11
    If Not IsEmpty(Cells(F201).Value) Then Call Macro12
End Sub
 
Last edited:
Upvote 0
Re: Want to write in VBA, If cell is not blank, do x

klafontaine said:
Hi... it's been 8 years but maybe someone will see this.
I also want to write in VBA, if cell is not blank, then do this.
And then stop. Acting only if the cell is not blank...
This is based on a column of numbers, updated monthly. So when the month is updated, I want the macro to run.
But I get the End If without block if error... over and over.
This is my code so far.

Private Sub Worksheet_Change(ByVal Target As Range)

If IsEmpty(Cells(F3).Value) = False Then Call Macro1
End If
If Not IsEmpty(Cells(F21).Value) Then Call Macro2
End If
If Not IsEmpty(Cells(F39).Value) Then Call Macro3
End If
If Not IsEmpty(Cells(F57).Value) Then Call Macro4
End If
If Not IsEmpty(Cells(F75).Value) Then Call Macro5
End If
If Not IsEmpty(Cells(F93).Value) Then Call Macro6
End If
If Not IsEmpty(Cells(F111).Value) Then Call Macro7
End If
If Not IsEmpty(Cells(F129).Value) Then Call Macro8
End If
If Not IsEmpty(Cells(F147).Value) Then Call Macro9
End If
If Not IsEmpty(Cells(F164).Value) Then Call Macro10
End If
If Not IsEmpty(Cells(F183).Value) Then Call Macro11
End If
If Not IsEmpty(Cells(F201).Value) Then Call Macro12
End If
End Sub
Click to expand...

Your "End If" is for "Block If" statements. That would like this..

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)    
    If IsEmpty(Cells(F3).Value) = False Then
        Call macro1
    End If
    If Not IsEmpty(Cells(F21).Value) Then
        Call Macro2
    End If
    If Not IsEmpty(Cells(F39).Value) Then
        Call Macro3
    End If
    If Not IsEmpty(Cells(F57).Value) Then
        Call Macro4
    End If
    If Not IsEmpty(Cells(F75).Value) Then
        Call Macro5
    End If
    If Not IsEmpty(Cells(F93).Value) Then
        Call Macro6
    End If
    If Not IsEmpty(Cells(F111).Value) Then
        Call Macro7
    End If
    If Not IsEmpty(Cells(F129).Value) Then
        Call Macro8
    End If
    If Not IsEmpty(Cells(F147).Value) Then
        Call Macro9
    End If
    If Not IsEmpty(Cells(F164).Value) Then
        Call Macro10
    End If
    If Not IsEmpty(Cells(F183).Value) Then
        Call Macro11
    End If
    If Not IsEmpty(Cells(F201).Value) Then
        Call Macro12
    End If
End Sub
 
Last edited:
Upvote 0
Re: Want to write in VBA, If cell is not blank, do x

Thank you, Steve! I pasted your reply (with one change, I made the first line match the rest, like this: If Not IsEmpty(Cells(F3).Value instead of if = false as I wrote it before.)
I get this error when I go to update my data sheet to make the macro run:
"Run-time error '1004' application-defined or object-defined error." When I click Debug, it highlights the top row.

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
        If Not IsEmpty(Cells(F3).Value) Then
            Call Macro1
        End If
        If Not IsEmpty(Cells(F21).Value) Then
            Call Macro2
        End If
        If Not IsEmpty(Cells(F39).Value) Then
            Call Macro3
        End If
        If Not IsEmpty(Cells(F57).Value) Then
            Call Macro4
        End If
        If Not IsEmpty(Cells(F75).Value) Then
            Call Macro5
        End If
        If Not IsEmpty(Cells(F93).Value) Then
            Call Macro6
        End If
        If Not IsEmpty(Cells(F111).Value) Then
            Call Macro7
        End If
        If Not IsEmpty(Cells(F129).Value) Then
            Call Macro8
        End If
        If Not IsEmpty(Cells(F147).Value) Then
            Call Macro9
        End If
        If Not IsEmpty(Cells(F164).Value) Then
            Call Macro10
        End If
        If Not IsEmpty(Cells(F183).Value) Then
            Call Macro11
        End If
        If Not IsEmpty(Cells(F201).Value) Then
            Call Macro12
        End If
    End Sub
 
Last edited by a moderator:
Upvote 0
Re: Want to write in VBA, If cell is not blank, do x

The syntax for cells is incorrect, if you are using cells the syntax is Cells(row number, column number or letter (in quotes)) i.e Cells(3, 6) or Cells(3, "F").
Try the below using Range but I am sure you don't really want it triggering on a change in any cell on the sheet :eeek:

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
    If Not IsEmpty(Range("F3")) Then Call macro1
    If Not IsEmpty(Range("F21")) Then Call Macro2
    If Not IsEmpty(Range("F39")) Then Call Macro3
    If Not IsEmpty(Range("F57")) Then Call Macro4
    If Not IsEmpty(Range("F75")) Then Call Macro5
    If Not IsEmpty(Range("F93")) Then Call Macro6
    If Not IsEmpty(Range("F111")) Then Call Macro7
    If Not IsEmpty(Range("F129")) Then Call Macro8
    If Not IsEmpty(Range("F147")) Then Call Macro9
    If Not IsEmpty(Range("F164")) Then Call Macro10
    If Not IsEmpty(Range("F183")) Then Call Macro11
    If Not IsEmpty(Range("F201")) Then Call Macro12
End Sub
 
Last edited:
Upvote 0
Re: Want to write in VBA, If cell is not blank, do x

@klafontaine
Please do not post the same question multiple times. All clarifications, follow-ups, and bumps should be posted back to the original thread. (rule 12 here: Forum Rules).
I have merged two of your threads & deleted the others.
 
Upvote 0
Status
Not open for further replies.

Similar threads

T
  • Solved
VBA - Seek user confirmation when changing non blank cells
Replies
6
Views
487
kevin9999
K
S
  • Solved
VBA code to fill in blank cells running really slowly
Replies
6
Views
409
Joe4
Joe4
G
  • Solved
Stop excel macro from running if there is a blank cell in a column
Replies
5
Views
477
RobP
R
B
  • Question Question
VBA Code to Send Mail to skip blank cells
Replies
1
Views
274
Micron
M
W
  • Question Question
Trying to learn how to loop code
Replies
1
Views
233
mumps
mumps

Forum statistics

Threads
1,223,886
Messages
6,175,198
Members
452,617
Latest member
Narendra Babu D

Share this page

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
Back
Top