Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

2007 Excel Clear Content in Indirect Cells

Posted on 2010-09-01
8
Medium Priority
?
323 Views
Last Modified: 2012-05-10
How do I apply this code (from the original question,which works perfectly on a single selection) to a range of cells? If B2 changes clear C2, if B3 change clear C3 etc

Private Sub Worksheet_Change(ByVal Target As Range)

   If Target.Address = "$A$1" Then
      [B1] = ""
   End If

End Sub
0
Comment
Question by:notpandora
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 11

Expert Comment

by:Runrigger
ID: 33576596
Private Sub Worksheet_Change(ByVal Target As Range)

  Activecell.offset(0,1).value = ""

End Sub
0
 
LVL 13

Expert Comment

by:MWGainesJR
ID: 33576604
Try this
Private Sub Worksheet_Change(ByVal Target As Range)

   If Target.column = 2 Then
     target.offset(0,1).clear
   End If

End Sub

0
 
LVL 13

Expert Comment

by:MWGainesJR
ID: 33576633
runrigger's solution will work on all columns, not just B, not sure if it is intended for all columns....If it is, then runrigger's solution would be more efficient.....
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 11

Expert Comment

by:Runrigger
ID: 33576699
I did intend it for all columns (question almost points to this, but that's my interpretation!), otherwise yours is best.
0
 

Author Comment

by:notpandora
ID: 33576930
I suspect I didn't add enough detail to the question :(
Column B is a List, Column C is another list (Indirect), For example a user can enter FT to Column B and NORM to Column C (OK) and then change Column B to Cas, Cas and NORM is not an allowable selection and NORM does not appear on the indirect list. if Cas is selected first.

MWGainesJr solution causes some sort of endless loop that crashes Excel
Runrigger solution wipes out the validation list in Column C
I have attached the file ith one line working on the enter sheet
Cheers, fc
cond-lists.xlsm
0
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 1000 total points
ID: 33577031
Try this:



Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rngcell As Range
    On Error GoTo err_handle
    If Not Intersect(Target, Range("B:B")) Is Nothing Then
        Application.EnableEvents = False
        For Each rngcell In Intersect(Target, Range("B:B"))
            rngcell.Offset(, 1).ClearContents
        Next rngcell
   End If

clean_up:
    Application.EnableEvents = True
    Exit Sub
err_handle:
    MsgBox Err.Description
    Resume clean_up
End Sub

Open in new window

0
 

Author Closing Comment

by:notpandora
ID: 33577092
Worked a treat thanks Rorya
0
 
LVL 11

Expert Comment

by:Runrigger
ID: 33577112
this should get around the loop/crash issue!

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Column = 2 Then
        ActiveCell.Offset(0, 1).ClearContents
    End If

End Sub
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

926 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question