Quick Excel Question - Data Validation 2

[westma]

Greetings,
I have a spreadsheet with a cells in a column (say column B) that use a VLOOKUP formula to return a value that depends on what the corresponding cell in column A says. Is there some way to say that IF(A#="",take user input,use VLOOKUP formula)?

I have tried doing this using data validation, but no luck so far. Any ideas?

Thanks

 

[ThomasLafferty]

I take it that your VLOOKUP is in the B column? Or do you want to reject values in the B column based on validation criteria and have a VLOOKUP in some other column based on B columns input?
tl

 

[westma]

I know my question was a bit vague. . .sorry.

VLOOKUP is in column B cells. Cells in column A are actually populated by a drop down list in each cell using data validation. The user can either pick a value from the list or leave blank.

Goal: If cell in column A is blank, the user can input the column B cell. If cell in column A has a selection, column B returns a value based on the VLOOKUP formula that references the column A input.

Hopefully this makes sense. Data validation may not be the way to go, but I am not sure if this is even possible.

 

[ThomasLafferty]

If you don't mind VBA, try this:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim strLookupCellAddress As String
    If ActiveCell.Column = 1 Then   'user has made a change in A column data
        Select Case ActiveCell.Value
        Case Is = ""    'user is in the A column and has deleted the entry
            strLookupCellAddress = ActiveCell.Address  'address of cell for LOOKUP
            'move to column B and input LOOKUP formula
            ActiveCell.Offset(rowOffset:=0, columnOffset:=-1).Activate
            ActiveCell.Formula = "=VLOOKUP(" & strLookupCellAddress & ", Database, 2, False)"
         Case Else
         End Select
    End If
End Sub

Hope this is close...

Tom

 

[ThomasLafferty]

Incidentally, this assumes that you have a named range called Database somewhere in your workbook and that you are pulling values from column 2 based on the value in the A column. This will generate an error because the user has deleted the value in the A column. You may want to change to:

Case <>""

If nothing else, I may have you on the right track now.

Regards
Tom

 

[firefytr]

Change out ..

ActiveCell

.. for ..

Target

Also, add this line at the start of the routine to limit to only one cell change at a time ...

    If Target.Cells.Count > 1 Then Exit Sub

And one more, enclose all code in these lines (start directly after the above line) ...

    Application.EnableEvents = False

    '.. code here

    Application.EnableEvents = True
End Sub

-----------
Regards,
Zack Barresse

 

[firefytr]

Oh, and you can change this ..

ActiveCell.Offset(rowOffset:=0, columnOffset:=-1).Activate
            ActiveCell.Formula = "=VLOOKUP(" & strLookupCellAddress & ", Database, 2, False)"

.. for this ..

Target.Offset(0, -1).Formula = "=VLOOKUP(" & Target.Address & ",Database,2,0)"

No activation that way, which is really not needed.

HTH

-----------
Regards,
Zack Barresse

 

[ThomasLafferty]

Hey Zack:
I ended up with this as my code:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim strLookupCellAddress As String
    If Target.Cells.Count > 1 Then Exit Sub
    Application.EnableEvents = False
    If Target.Column = 1 Then   'user has made a change in A column data
        Select Case Target.Value
        Case Is <> ""    'user is in the A column and has selected an entry
            strLookupCellAddress = Target.Address  'address of cell for LOOKUP
            'move to column B and input LOOKUP formula
            Target.Offset(0, -1).Formula = "=VLOOKUP(" & Target.Address & ",Database,2,0)"
         Case Else
         End Select
    End If
    Application.EnableEvents = True
End Sub

and it no workee...
Did I misread you?
I get an application defined error...

Regards,

Tom

 

[firefytr]

Oh! That's because it's a negative one. Sorry. Should be a positive one, drop the minus sign. Since we're in column A, a negative column offset would put us left of column A, which is obviously invalid. Positive 1 will be column B, where the formula should go.

Also, this does assume the data range to lookup is named 'Database'.

-----------
Regards,
Zack Barresse

 

[ThomasLafferty]

Not sure what's wrong...
I removed the negative sign now the code does nothing. When I was using ActiveCell, it was working except for the backwards logic. What's the benefit of using Target instead of ActiveCell?

Just curious...this wasn't even my post to start with, but I'm learning too.

Tom

 

[westma]

Thanks for the reply.

I am not able to get this to do anything for some reason. Does the macro run automatically? Also, it appears that the code places the VLOOKUP formula in column B when the user deletes what is in column A. I need it to do just the opposite: Use the formula when A is NOT blank. Could I just replace

Case Is = ""

With

Case Is <> "" ?

Finally, I only want this to work on Sheet1!, so how do code that in. I tried

dim w as worksheet
Set w=Worksheets("Sheet1")

If w.ActiveCell.Column . . .Does not work!

Thanks again for all your help.

 

[ThomasLafferty]

Since the sub routine is private, placing it Sheet 1 should lock it down to just sheet 1. It should run automatically any time there is a value change on sheet 1. Yes just try replacing Case Is ="" with Case<>"" and it should work. I was unable to use Zack's suggestion of Target, so try try this code:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim strLookupCellAddress As String
    If ActiveCell.Column = 1 Then   'user has made a change in A column data
        Select Case ActiveCell.Value
        Case Is <> ""    'user is in the A column and has deleted the entry
            strLookupCellAddress = ActiveCell.Address  'address of cell for LOOKUP
            'move to column B and input LOOKUP formula
            ActiveCell.Offset(rowOffset:=0, columnOffset:=-1).Activate
            ActiveCell.Formula = "=VLOOKUP(" & strLookupCellAddress & ", Database, 2, False)"
         Case Else
         End Select
    End If
End Sub

Let me know if this does it.

Tom

 

[firefytr]

This goes into the Sheet1 module, not a Standard Module. The easiest way to get there is to right click Sheet1 tab and select View Code, past it on the right (code pane). This is what I ended up with...

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim strLookupCellAddress As String
    If Target.Cells.Count > 1 Then Exit Sub
    Application.EnableEvents = False
    If Target.Column = 1 Then   'user has made a change in A column data
        Select Case Target.Value
        Case Is <> ""    'user is in the A column and has selected an entry
            strLookupCellAddress = Target.Address  'address of cell for LOOKUP
            'move to column B and input LOOKUP formula
            Target.Offset(0, 1).Formula = "=VLOOKUP(" & Target.Address & ",Database,2,0)"
        Case Else
            'to clear column B formula if blank
            Target.Offset(0, 1).ClearContents
        End Select
    End If
    Application.EnableEvents = True
End Sub

I generally prefer Target when using an event that passes it natively (look at the sub line .. Private Sub .. (ByVal Target As Range)). ActiveCell can be a dangerous habit to get into, and Target will cover the entire range as well. But the largest drawback is if you select A1, press F2 and enter, the "ActiveCell" is A2, where the "Target" is A1, or the cell being changed. That's because the routine fires after the change when the selection has already been moved, thus having the distinct possibility of giving erroneous results.

Hope that explains things a little better.

-----------
Regards,
Zack Barresse

 

[ThomasLafferty]

Incidentally, the comment should read:

        Case Is <> ""    'user is in the A column and has selected an entry

Sorry for the slop.

Tom

 

[firefytr]

Oh, and my code assumes the user is changing a cell in column A to get the event to fire. Is that right? Hope I'm still following this all ok..

-----------
Regards,
Zack Barresse

 

[ThomasLafferty]

westma:
This works:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim strLookupCellAddress As String
    If ActiveCell.Column = 1 Then   'user has made a change in A column data
        Select Case ActiveCell.Value
        Case Is <> ""    'user is in the A column and has selected an entry
            strLookupCellAddress = ActiveCell.Address  'address of cell for LOOKUP
            'move to column B and input LOOKUP formula
            ActiveCell.Offset(rowOffset:=0, columnOffset:=1).Activate
            ActiveCell.Formula = "=VLOOKUP(" & strLookupCellAddress & ", Database, 2, False)"
         Case Else
         End Select
    End If
End Sub

I am unable to get any result from using Target, and I'm not sure why... I agree that this would be better since it is called natively, but I'm not able to get it to work. Zack, could you show me how to modify the above code in full so I get it? I thought I understood, but it didn't work, and I suspect that it has something to do with enabling and disabling events. After running your code, which I couldn't get to work, my original code wouldn't work. I had to shut down Excel and start a new workbook and insert my original code to get it to work.

Tom

Tom

 

[firefytr]

Hi Tom,

I'm thinking maybe the reason it didn't work for you is that EnableEvents were toggled to False with you, which you have to manually reset it. I open the Immediate window (Ctrl + G) in the VBEIDE and type "Application.EnableEvents = True" and press enter.

Here is some commented code ...

Private Sub Worksheet_Change(ByVal Target As Range)
    
    'Check if only one cell is selected
    If Target.Cells.Count > 1 Then Exit Sub
    
    'Check if cell changed is in column A
    If Target.Column = 1 Then
        
        'Force a quit on error prior to enabling events again
        On Error GoTo ExitHere
        
        'shut off events as to not get caught in loop with change event
        Application.EnableEvents = False
        
        Select Case Target.Value
            
            'Check for not equal to blank
        Case Is <> ""
            
            'Enter VLOOKUP formula upon condition
            Target.Offset(0, 1).Formula = "=VLOOKUP(" & Target.Address & ",Database,2,0)"
            
        Case Else
            
            'Clear cell if column A cell is blank
            'to clear column B formula if column A cell is blank
            Target.Offset(0, 1).ClearContents
            
        End Select
        
    End If
    
ExitHere:
    'Turn events back on
    Application.EnableEvents = True
    
End Sub

Does this help a little bit? I get skewed results if I use ActiveCell as it will put the VLOOKUP in the cell one column to the right (B, as desired) but one row below where I just entered. I have my cell selection going down after edit. Note that the offset will be different for Target as opposed to Activecell, especially if the "Move Selection After Edit" in Tools | Options is set to something other than down.

-----------
Regards,
Zack Barresse

 

[ThomasLafferty]

That was it. Hope we have understood westma properly. Zack's Code works fine now and is probably safer than what I was using. Hope it helps.

Tom

 

[westma]

Thanks you guys, sorry for the late reply.

This is awesome. The last bit of code from Zack is just what I needed. Thank you very much for the extensive time you have both put in. Enjoy the stars.

Matt

 

[firefytr]

I'm glad it worked for you Matt, and I'm glad the explanation was enough for you Tom. Sometimes I'm not too great at putting things into words.. Take care!

-----------
Regards,
Zack Barresse