Solved: Clear The contents of Adjacent Cell

[rahul_r79]

Hi
I just need a help to clear the contents of the cells if the value in the adjacent cell is empty
For ex
J1= 0 then L1 should be zero(means -clear the contents)
till the end of the row.
I had tried to write a macro but its giving an Error Mismatch
My VBA Code is as follows:-

[VBA]
Dim rng1 As Range
Dim rng2 As Range
Set rng1 = ActiveSheet.Range("J3:j100")
Set rng2 = ActiveSheet.Range("L3:L100")
'Set rng2 = Worksheets("sheet2").Range("a1:c5")
Do While rng1.Value = ""
rng2.ClearContents
Loop
End Sub


[/VBA]

[mbarron]

Try this one:
[VBA]Sub J_blank_L_as_well()
Dim rng1 As Range
Dim rng2 As Range
Set rng1 = ActiveSheet.Range("J3:j100")

For Each rng2 In rng1
If rng2 = "" Then rng2.Offset(, 2).ClearContents
Next

End Sub[/VBA]

.... but its giving an Error Mismatch

The Mismatch is because you are trying to get the ".Value" of more than one cell. If rng1 was set to just J3, then that line would not error out.

[rahul_r79]

You are AWESOMEEEEEE...Thanks a million