Specific VB Code for Select Case & Worksheet Event Change please

**mally1975** · 08-09-2011, 05:57 AM

Hi all,
What I'm trying to do is change the contents of a cell if that particular column is not relevant (based on Type).. I don't want to put a formula in the actual cell as I need it for possible data entry.. Is there a procedure or macro that would do this more effectively?..

My column headers are (the information is stored in rows):
Column A: Set
Cloumn B: Type
Column C: Unit

Columns: D through to H are only relevant to certain Types and those that are not, I have used conditional formatting so if the result is N/A the cell is 'blocked out' (which I have to enter manually).. I would like Excel to 'lookup'? Type and if A, then block out *cell reference* and *cell reference* as not applicable and if it's Type B then block out this *cell reference* and *cell reference* etc.. The main problem being there are 5 different types..

Please help!.. I'm useless at coding!!.. Please see the below, hence my change in my request for someone to please write the code for me as I'm struggling!!..

**Bob Phillips** · 08-09-2011, 06:16 AM

Something like this

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "B:B"      '<<<< change to suit

    On Error GoTo ws_exit
    
    Application.EnableEvents = False
    
    If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
    
        With Target
        
            Select Case .Value2
            
                Case "A":   .Offset(0, 2).Value2 = "" '<------ change to suit
                Case "B":   .Offset(0, 3).Value2 = "" '<------ change to suit
                'etc
        End With
    End If

ws_exit:
    Application.EnableEvents = True
End Sub

This is worksheet event code, which means that it needs to be
placed in the appropriate worksheet code module, not a standard
code module. To do this, right-click on the sheet tab, select
the View Code option from the menu, and paste the code in.

**mally1975** · 08-10-2011, 04:31 AM

[QUOTE=Bob Phillips;2577135]

Thank you for your help.. I keep getting compile errors with this, such as:

'End With without With'
'End If without If'

I think this looks like it will work but what am I doing wrong to get these errors?.. Your advice is most appreciated!!

.. Wish I blinking understood VBA code a little more!!

**tarquinious** · 08-10-2011, 06:32 AM

You'll receive errors just copying and pasting the above code - you need to complete it. The Select Case requires an End Select where the 'etc is for a start, hence the errors you're receiving above.

I think this is a 'starter for 10' to get you going. If you're after somebody to write the whole thing for you, please advise and this can be done too.

**Bob Phillips** · 08-10-2011, 06:50 AM

I omitted an 'End Select' statement after the line 'etc.

**mally1975** · 08-10-2011, 07:04 AM

Originally Posted by tarquinious

You'll receive errors just copying and pasting the above code - you need to complete it. The Select Case requires an End Select where the 'etc is for a start, hence the errors you're receiving above.

I think this is a 'starter for 10' to get you going. If you're after somebody to write the whole thing for you, please advise and this can be done too.

lol There is that, however, I don't wish to be lazy and how could I ever thank the person enough who took the time to do it!!.. Saying that, I think I might have to admit I can't do it and ask someone who can.. To add to my problems yet further, another column has been added so each Type now needs two separate columns showing "N/A" lol..

**mally1975** · 08-10-2011, 07:19 AM

OK lol I admit defeat.. Please may I have some help with the full code.. I admit I'm a bit useless with VBA and don't know what to do here.. I'm completely lost!!!