PDA

View Full Version : Repeat VBA code for each cell



j.stitt1
08-16-2018, 07:58 AM
Hello,

I have this code that works for the cell intended but i would like it available for a range of cell C4 thru C103.


Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("C4")) Is Nothing Then
If Range("C4") = "" Then Range("D4:V4").ClearContents
If Range("C4") <> "" Then Range("D4:V4").ClearContents
End If
End Sub

Any help would be grateful

Thanks
Jeffrey

Paul_Hossler
08-16-2018, 08:10 AM
1. You should use the[#] icon to insert CODE tags and then paste ypur macro between them

2. These don't make sense since D-V get cleared regardless of the C


If Range("C4") = "" Then Range("D4:V4").ClearContents
If Range("C4") <> "" Then Range("D4:V4").ClearContents



3. You should disable event handling when you're changing the WS from inside an event handler


4. GUESSING that you want D-V changed in the row where C is. This clears D-V regardless of the contents of the corresponding C



Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Range

If Intersect(Target, Range("C4:C103")) Is Nothing Then Exit Sub


Application.EnableEvents = False
For Each r In Intersect(Target, Range("C4:C103")).Cells
r.Offset(0, 1).Resize(1, 19).ClearContents
Next
Application.EnableEvents = True
End Sub

j.stitt1
08-16-2018, 08:23 AM
this is why.
it may be incorrect but it seems to works.


If Range("C4") = "" Then Range("D4:V4").ClearContents (With is one if i use the delete key it clears my range but if i change the value of C4 it won't clear the range.)
If Range("C4") <> "" Then Range("D4:V4").ClearContents (With is one if i use the if i change the value of C4 it clears the range but if i use delete key it won't clear my range.)

j.stitt1
08-16-2018, 08:35 AM
Thank You!

but i would like to add a cell to clear. I see you are clearing 1 thru 19 cells after column C i also need to skip 20, 21 and clear 22.

how would i add?

p45cal
08-16-2018, 10:30 AM
either add directly after the
r.Offset(0, 1).Resize(1, 19).ClearContents
line, the following:
r.Offset(, 22).ClearContents

or
replace it with:
Union(r.Offset(, 1).Resize(, 19), r.Offset(, 22)).ClearContents

j.stitt1
08-16-2018, 12:21 PM
Awesome!!
Thank You!