Solved

2007 Excel Clear Content in Indirect Cells

Posted on 2010-09-01
8
318 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
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 
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 250 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

[Webinar] Code, Load, and Grow

Managing multiple websites, servers, applications, and security on a daily basis? Join us for a webinar on May 25th to learn how to simplify administration and management of virtual hosts for IT admins, create a secure environment, and deploy code more effectively and frequently.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
modify formula for range changes 8 30
INSERT value into first row in 75 workbooks 8 35
VBA error subscript out of range help 5 28
need help to look for 16 42
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

734 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