PDA

View Full Version : vlookup query using VBA



cmccabe1
05-27-2014, 08:23 AM
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
05-27-2014, 09:21 AM
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
05-27-2014, 09:36 AM
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
05-27-2014, 01:35 PM
Just use VLOOKUP as Ashley suggested, VBA is pointless here.

cmccabe1
05-27-2014, 01:41 PM
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
05-28-2014, 09:23 AM
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
05-28-2014, 02:56 PM
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
05-28-2014, 03:41 PM
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
05-29-2014, 07:57 AM
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
05-29-2014, 08:08 AM
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
05-29-2014, 08:17 AM
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
05-29-2014, 08:32 AM
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
05-29-2014, 08:59 AM
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
05-29-2014, 08:59 AM
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
05-29-2014, 09:15 AM
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
05-29-2014, 09:22 AM
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
05-29-2014, 09:33 AM
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
05-29-2014, 09:54 AM
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
05-29-2014, 11:00 AM
If you click on A2 a dropdown box should appear is it not?

Bob Phillips
05-29-2014, 03:58 PM
Don't think so.