PDA

View Full Version : VB ClearContents if add-on (Excel 2003)



VNouBA
05-25-2012, 11:27 AM
I have the following issue, please help

If Column B has information then Clear content of Column R in “Report” sheet.

Ex:

If B7 = 002 then .ClearContents of R7 in Sheet “Report”
If B12 = 005 Then .ClearContents of R12 in Sheet “Report”

I have the following code in my initial sheet:


If Target.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("B5:B1000")) Is Nothing Then
NumRows = Target.Value - 1
For R = 1 To NumRows
Target.Offset(1, 0).EntireRow.Insert
Next R
End If

Opv
05-25-2012, 03:23 PM
by "002" and "005" do you mean values, 0.002 and 0.005, or strings, "002" and "005"?

Opv
05-25-2012, 03:32 PM
Presuming you mean values, you might try the following in your Worksheet module:


Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("B7")) Is Nothing And _
Selection.Count = 1 Then

On Error Resume Next

If Target.Value = 0.002 Then

Target.Offset(, 16).Value = vbNullString

End If


ElseIf Not Intersect(Target, Range("B12")) Is Nothing And _
Selection.Count = 1 Then

If Target.Value = 0.005 Then

Target.Offset(, 16).Value = vbNullString

End If

On Error GoTo 0

End If

End Sub