vlookup query using VBA

[cmccabe1]

The code below places a dropdown box in A2. I am having trouble modifying it so that the rows in the data validation list associated with the case populate the cells in yellow. I have put the workbook on box.net as it is too large to attach.

https://app.box.com/s/7rcgdelvsqj9xbmnlxw7

So in the example case 2 is selected in A2 and in B2-F2 the information associated with that case is populated (2,2,2,2,2). Thanks.

 '  Step 3 Select CaseWith Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=$CA$5:$CA$"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
    Selection.Copy
    Range("A2").Select
    ActiveSheet.Paste

[ashleyuk1984]

For instance, by looking at your spreadsheet, the correct answer for B2 would be

=VLOOKUP(A2,$CA$3:$CF$23,2,0)

Are you saying that you want to know how to do this ... but in VBA ?

[cmccabe1]

Yes, I would like the user to select the case # from A2 and have the other cells populate with the information associated with that case. Thank you very much.

[Bob Phillips]

Just use VLOOKUP as Ashley suggested, VBA is pointless here.

[cmccabe1]

Since there will be a new worksheet each time I can not use vlookup as the formulas would need to be re-done each time (this is what I am trying to avoid). Thank you very much.

[OG Loc]

Since there will be a new worksheet each time I can not use vlookup as the formulas would need to be re-done each time (this is what I am trying to avoid). Thank you very much.

Don't have time to look a the spreadsheet, but just thought I would quickly say you might be able to use the VBA line:

Application.VLookup()

Where in the brackets you put the usual VLOOKUP arguments.

[cmccabe1]

If I wanted cell B2 to do a vlookup based on the case selected in A2, would:
Sheets("annovar")B2.value = Application.vlookup(A2,$CA$3:$CF$,2,0) work? Thanks.

[Bob Phillips]

No, it woud be

WorkSheets("annovar").Range("B2").Value = Application.vlookup(Range("A2").Value,Range("CA3:CFnn"),2,0)

You would need to replace that nn with the lastrow number, and you might need to qualify the extra two range references with their sheet names.

[cmccabe1]

 Worksheets("annovar").Range("B2").Value = Application.VLookup(Range("A2").Value, Range("CA5:CA75"), 2, 0)

I have two questions. The first is in A2 a dropdown with each case # results (their are 66), but when a particular case is selected the B2 cell is #REF.
Also, is there a way to make Range("CA5:CA75") something like Range("CA5:CA") as the # of cases or rows in that column changes daily (today it is 66, but tomorrow it may be 71). Thank you for your help.

[Bob Phillips]

 Worksheets("annovar").Range("B2").Value = Application.VLookup(Range("A2").Value, Range("CA5:CA75"), 2, 0)

I have two questions. The first is in A2 a dropdown with each case # results (their are 66), but when a particular case is selected the B2 cell is #REF.

Well, if you try and access the 2nd column of va single column range, you will have problems.

Also, is there a way to make Range("CA5:CA75") something like Range("CA5:CA") as the # of cases or rows in that column changes daily (today it is 66, but tomorrow it may be 71). Thank you for your help.

You need to take the row count of those 66 lines wherever they are and inject that into your formula

Worksheets("annovar").Range("B2").Value = Application.VLookup(Range("A2").Value, Range("CA5").Resize(numRows), 2, 0)

[OG Loc]

 Worksheets("annovar").Range("B2").Value = Application.VLookup(Range("A2").Value, Range("CA5:CA75"), 2, 0)

I have two questions. The first is in A2 a dropdown with each case # results (their are 66), but when a particular case is selected the B2 cell is #REF.
Also, is there a way to make Range("CA5:CA75") something like Range("CA5:CA") as the # of cases or rows in that column changes daily (today it is 66, but tomorrow it may be 71). Thank you for your help.

You can do something to find out how many rows there are in code,then use that to change the vlookup. You can do it with:

Dim LastRowNo As Long
LastRowNo = Cells(Rows.Count, "CA").End(xlUp).Row

That gives you the last row number used in column CA. Then have:

ApplicationVLookup(Range("A2").Value, Range("CA5:CB" & LastRowNo), 2, 0)

In your lookup line. Note you need to change the vlookup range to include the next column, since it appears you are looking the thing up from column CB based on the value of CA. Should do the trick! The problem might be that the last row code will count formatting changes to cells in the column as 'something', so might end up giving a longer range than you want if you have set up prior formatting that goes beyond the maximum data range. Probably won't be a problem in this case though, as you just need your lookup range to contain the data somewhere within it. Just something to keep in mind really.

[cmccabe1]

VB:

 Dim LastRowNo As Long 
LastRowNo = Cells(Rows.Count, "CA").End(xlUp).Row 

Then
VB:

 Worksheets("annovar").Range("B2").Value = Application.VLookup(Range("A2").Value, Range("CA5:CB" & LastRowNo), 2, 0)

The code runs, but when a case is selected B2 doesen't change.

For example, if the case in A2 is 0 B2 is says Empty (which is correct)
if the case in A2 is 4 B2 says Empty (does not change). Thank you very much.

[OG Loc]

Assuming you are rerunning the code each time you select a new value of A2, then I think it must be something to do with the data range for the lookup, maybe the looked-up value for 4 really was Empty!

Otherwise I'm not sure what's wrong, your spreadsheet download doesn't work for me so still not completely sure what context this is all happening in.

[Bob Phillips]

You need to add a worksheet_change event to monitor a change to A2, and then run the code that sets B2. Or just use a VLOOKUP cell formula, which is far simpler.

[cmccabe1]

I added a worksheet_change event in the annovar sheet module:

VB:

 Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$2" Then Classify
End Sub

Did I do something incorrect? Thanks.

[cmccabe1]

I put the workbook here on Box.net:
https://app.box.com/s/clj9nrbhqfsqmxmuql5e

I used some dummy data, but it illustrates the point that B2 stays as xxxx despite changing the value in the dropdown of A2. Thanks.

[Bob Phillips]

I thought you were just doing a VLOOKUP, Classify does a lot more than that (but oddly, no VLOOKUP). Also, I cannot see any drop-down, so what is going to get changed, on which sheet?

The Worksheet_Change event should be in the worksheet code module, and Classify probably shouldn't.

[cmccabe1]

annovar is the sheet that the code runs in, so I placed a module in that sheet:

VB:

 Private Sub Worksheet_Change(ByVal Target As Range) 
    Target.Address = "$A$2"
End Sub 

Thanks.

[cmccabe1]

If you click on A2 a dropdown box should appear is it not?

[Bob Phillips]

Don't think so.