Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

2007 Excel Clear Content in Indirect Cells

Posted on 2010-09-01
8
Medium Priority
?
321 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

688 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