If the cell two columns to the left is empty

B

BenRichards

New Member
Joined
Apr 7, 2014
Messages
41
Hi guys!

I want this bit of code to keep the cells from C3 to C6 empty if there is "-" in C1 or if the cell in the column A on the same row is empty. If I do it like this:

Private Sub Worksheet_Change(ByVal Target As Range)
Call Macro1
End Sub

Sub Macro1()
For Each cell In Range("C3:C6")

If ActiveSheet.Range("C1").Value = "-" Or Active.cell.Offset(0, -2).Value = "" Then
cell.Value = ""
End If
Next cell
End Sub

I get an error "Object required." I think there's something wrong with the part I've bolded. I've also tested it this way:

If ActiveSheet.Range("C1").Value = "-" Or RC - 2 = "" Then

The VBA-editor accepts that bit of code, but it doesn't seem to work. (I obviously don't know how to use those R[3]C[2]-types of references.)

Does someone know a better alternative for the bolded part? Thank you!
 
Last edited:

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
Sort by date Sort by votes
Okay, thanks steve the fish and AlphaFrog! :) Now it works as if it should, that if something is entered when at least one of those two conditions is met, it is erased.

After I've been able to enter something into C3, it is erased if C1 becomes "-"

However, if I let C1 be filled and delete the contents of A3, I get the error "Runtime error '1004' Application-defined or object-defined error.

I'd like it to work so that if entries in A-column are deleted, the approriate cells in C are emptyed as well.
 
Last edited:
Upvote 0
And debug highlights the row:

If ActiveSheet.Range("C1").Value = "-" Or Activecell.Offset(0, -2).Value = "" Then

More information. If I delete what's in A3, then go to C3, C3 becomes empty. However, if I delete what is in A3 and hit enter, going to A4, that triggers the error.
 
Upvote 0
If you are in column A then Activecell.Offset(0, -2) refers to a nonexistent range.
 
Upvote 0
From your description, I'm not sure I follow what you want to do but try something like this...

Code: 
[color=darkblue]Private[/color] [color=darkblue]Sub[/color] Worksheet_Change([color=darkblue]ByVal[/color] Target [color=darkblue]As[/color] Range)
    [color=darkblue]If[/color] Target.Count = 1 [color=darkblue]Then[/color]
        [color=green]'Test if C1 or A3:A6 was the cell that changed[/color]
        [color=darkblue]If[/color] [color=darkblue]Not[/color] Intersect(Range("C1,A3:A6"), Target) [color=darkblue]Is[/color] [color=darkblue]Nothing[/color] [color=darkblue]Then[/color]
            [color=darkblue]If[/color] Target.Address(0, 0) = "C1" And Target.Value = "-" [color=darkblue]Then[/color]
                Range("C3:C6").ClearContents
            [color=darkblue]ElseIf[/color] Target.Value = "" [color=darkblue]Then[/color] [color=green]'If A3:A6 was changed[/color]
                Target.Offset(, 2).ClearContents
            [color=darkblue]End[/color] [color=darkblue]If[/color]
        [color=darkblue]End[/color] [color=darkblue]If[/color]
    [color=darkblue]End[/color] [color=darkblue]If[/color]
End [color=darkblue]Sub[/color]
 
Upvote 0
VoG said:
If you are in column A then Activecell.Offset(0, -2) refers to a nonexistent range.
Click to expand...

I know, but I was under the impression that the bit of code where that was included applies only to C3:C6 as there is the line:

For Each cell In Range("C3:C6")

AlphaFrog, thanks. I want the cells in C3:C6 to be empty if there is "-" in C1 or if there is "" in the A-column on the same row (C3->A3). If one of those conditions is true, whatever the user fills in C3 is erased. And when C3 is filled, it is emptied if either A1 has "-" or A3 becomes "".

If I do it like this it works:

If ActiveSheet.Range("C1").Value = "-" Or ActiveSheet.Range("A3") = "" Then

But it only works for C3. And there are a few hundred rows and columns, so I really need to be able to use "For each cell in range" and Activecell.Offset so I can go a column at a time and fill in different IF-conditions.
 
Last edited:
Upvote 0
Are there formulas in A3:A6, C3:C6, or C1? The Worksheet_Change event procedure doesn't trigger when a formula changes a cell value.

BenRichards said:
so I really need to be able to use "For each cell in range" and Activecell.Offset so I can go a column at a time and fill in different IF-conditions.
Click to expand...

You don't have to loop through the range every time one cell changes. If C3 changes, you only would test conditions for row 3 and not every cell in column C when only C3 changed.
 
Last edited:
Upvote 0
I agree. I tried the code you posted, but it doesn't seem to do both of what I want to check. And I do not wish to clear the entire contents of C3:C6, but, if C3 if A3 becomes "". If A4 becomes "", then C4 should be "".

I'll return to this tomorrow.
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Solved
userform command button, list box, and check box macro
Replies
7
Views
316
dmt32
D
H
  • Solved
Quickly select the 10th non-empty cell in column "A"
Replies
3
Views
168
Peter_SSs
Peter_SSs
D
  • Solved
Excel VBA for Changing Tab Name & Return Blank Cell to Formula
Replies
5
Views
137
DjTRex2002
D
L
  • Solved
VBA remove leading zeros
2
Replies
14
Views
958
Joe4
Joe4
G
  • Solved
Loop with column header match and two conditions paste
Replies
3
Views
424
Legacy 143009
L

Forum statistics

Threads
1,214,839
Messages
6,121,892
Members
449,058
Latest member
Guy Boot

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