Using VBA to clear contents in a cell, depending on the contents of another cell

[-Wayner-]

Hi all,

Wonder if someone might be able to give me a steer on a little problem i am having. I need to clear the contents of a cell, but which cell is emptied depends on the value selected by the user - or more specifically two cells.

As an example, i have three columns of numbers, C, E, & G. these are all unique numbers.

The user then has two drop down boxes in A1 and A2. The first lets the user select what type of numer they want to get rid of - falling into either the set in C, E or G.

The second dropdown then lists all of the numbers in that given column. Once the user selects what number they wish to remove there is a button which they can press and will go find that number and remove it (plus some other information relevant to that number.

The problem is i can't get VBA to specify which column to look in (based on the contents selected by the user in the first drop down.

I was trying to define the columns and rows as j & k - so for example this would give me the following peice of code:

For j = 4 To 205
If Cells(j, k + 1) = Range("$A$2") And Cells(j + 1, k) = 0 Then
    Cells(j, k).ClearContents

This should find out if it is the bottom value in the list and if so remove the value (the code has two parts - the second says if it's not the last value in the list then it moves the other numbers up - to maintain a list without gaps).

The problem i have is trying to get k to be variabel based on the contents of cell A1. I tried to use the following:

If Range("$A$1") = "Supergroup" Then k = 3
If Range("$A$1") = "Group" Then k = 5
If Range("$A$1") = "Upcs" Then k = 7

But i'm not sure that is doing what i want it to, or what the best way to get around this problem would be. If anyone could give me a steer in the right direction i would be very very appreciative.

Thanks in advance for any ideas.

Wayner

 

[thunder_anger]

Hi


iam new here
but that is what i tried and succeeded

 Sub removeme()
Dim A As Range
Dim b As Range
Set A = Selection
For Each b In A
If b.Value = 1 Then
b.Offset(0, 1).ClearContents
End If
Next b
End Sub

 

[MickG]

If the columns you are looking in are "C","E" & "G" and those three letters are in dropdown(1) in "A1", and the numbers you are looking for in each of the three columns, are in Dropdown(2) in "B1":- then try this:-

[COLOR="Navy"]Sub[/COLOR] MG30Mar57
[COLOR="Navy"]Dim[/COLOR] rng [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] dn [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] col [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Dim[/COLOR] num [COLOR="Navy"]As[/COLOR] Double
col = [a1] '[COLOR="Green"][B]Dropdown (1)[/B][/COLOR]
num = [b1] '[COLOR="Green"][B]Dropdown(2)[/B][/COLOR]
    [COLOR="Navy"]Set[/COLOR] rng = Range(Cells("1", col), Cells(rows.Count, col).End(xlUp))
        [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] dn [COLOR="Navy"]In[/COLOR] rng
            [COLOR="Navy"]If[/COLOR] dn = num [COLOR="Navy"]Then[/COLOR]
                dn.ClearContents
            [COLOR="Navy"]End[/COLOR] If
        [COLOR="Navy"]Next[/COLOR] dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[RichardMGreen]

You could also try something like this(untested)

Sub remover()
    Dim found As Object
    Select Case Range("A1").Value
        Case "Supergroup": col = "C"
        Case "Group": col = "E"
        Case "Upcs": col = "G"
    End Select
    Set found = Columns(col).Find(what:=Range("A2").Value, LookIn:=xlValue, lookat:=xlWhole)
    If Not found Is Nothing Then
        found.Delete shift:=xlUp
    Else
        msgbox "Unable to find value"
    End If
End Sub

 

[-Wayner-]

Thank you all very kindly for your suggestions. Richard and Mick, both solutions work and allow me to do what i need to with minimal adapting. Thanks for your insight.

Thunder, i am sure that a more talented VBA'er would be able to adapt your own solution to fit the problem i had, but sadly it's a little beyond my own skills!

Thanks again all, problem solved