VBA Clear Range on Cell Change?

**Batman** · Sep 24th 2004

Paul,

I've never worked with the Range collection of a Range before, so I don't know how looping through that collection works. Do you have any other ranges that intersect with column A of sheet Database? If so, try declaring rngData as a Variant and adding .Cells to the end of your For Each ... statement so that you loop through the cells in a specified range, rather than through the ranges.

Hope this helps.

Regards,
Batman.

twowheeltoad · Sep 24th 2004

Thanks Batman, I'll keep plugging away.

I'll keep you posted.

twowheeltoad · Oct 4th 2004

Guess what . . . FURTHER PROBLEMS!

I haven't touched this file for about a week and now I do I find that it's not doing what it should be doing again.

I'm pretty sure that this bit of code . . .

Code

Option Explicit
 
Private Sub Worksheet_Calculate()
    On Error Resume Next
    Application.EnableEvents = False
    If Not Application.Intersect(ActiveCell, Range("Client_Name")) Is Nothing Then
        Range("A12:H150").ClearContents
    End If
    Application.EnableEvents = True
    On Error GoTo 0
    Call RetrieveAllRecords
End Sub

Display More

. . . is where the problem lies. For some reason it does not seem to be recognising the fact that the Client_Name cell is changing and consequently it's not clearing down the specified range or calling the Retrieve Records procedure.

Has anyone got any further ideas that may help crack this one before I go completely insane . . . PLEEEAAASSSEEE?

Thanks all,

Paul.

VBA Clear Range on Cell Change?

Participate now!

Share

Users Viewing This Thread