Subject: clicking on a button copies the choice *(VBA)

[Guest]

Hello folks.
In a sheet, I have a drop down list of Patient IDs. Say cell A1. next to
it, on cell B1, I have a pull-down menue of their specific Disease.
There are about 200 such Patient IDs that I need to assign their disease to.

On a separate sheet, I have a list of about 10,000 patient IDs. What I like
to be able to do is to set up a button that lunches a VBA.
What I want this VBA program to do is look through the 10,000 or so list of
Patient IDs on the second sheet, and find the one that matches the same
Patietn ID that I have selected from my pull down menu on the first sheet and
then copy the disease that I choose from cell B1 in the cell next to that
patient's ID on the second sheet.

I hope I made sense. Basiclly, I need a complete VBA that I can copy and
paste in my code window, assign that click-event button to it and use it.

Essentially, what I want is as I choose a patient ID from cell A1, sheet 1
and assign to this patient is a disease from pull-down menu in B1, sheet 1,
that this disease be copied in front of it correct patient Id (among many
thousands of patient id) on sheet2.

I hope someone can help us out

thanks

Thrava

 

[Guest]

VBA is not needed here, all you need to do is use the VLOOKUP function:
=VLOOKUP(A1,Sheet2!A1:B10000,2)

 

[Tom Ogilvy]

Private sub CommandButton1_Click()
Dim rng as Range, rng1 as Range
Dim res as Variant
with worksheets("Sheet2")
set rng = .Range(.Cells(2,1),.Cells(rows.count,1).End(xlup))
end with
res = Application.Match(worksheets("Sheet1").Range("A1"),rng,0)
if not iserror(res) then
set rng1 = rng(res)
rng1.offset(0,1).Value = worksheets("Sheet1").Range("B1").Value
else
msgbox worksheets("Sheet1").Range("A1").Value & " was not " &
vbNewLine & "on sheet2"
end if
End Sub

 

[Guest]

Thanks so much Tom,
I'll give this a shot

Private sub CommandButton1_Click()
Dim rng as Range, rng1 as Range
Dim res as Variant
with worksheets("Sheet2")
set rng = .Range(.Cells(2,1),.Cells(rows.count,1).End(xlup))
end with
res = Application.Match(worksheets("Sheet1").Range("A1"),rng,0)
if not iserror(res) then
set rng1 = rng(res)
rng1.offset(0,1).Value = worksheets("Sheet1").Range("B1").Value
else
msgbox worksheets("Sheet1").Range("A1").Value & " was not " &
vbNewLine & "on sheet2"
end if
End Sub

 

[Guest]

Hi Charles,
Thank you for taking the time to respond. In this case, unfortunately,
VLookup will not work. It works only for the specific Patient ID that I have
chosen from the pull-down menu. As I assign the disease and choose a second
Patient ID, the vlookup for the previous patient ID will not be retained.

 

[Guest]

Works like a charm
Thanks again

Private sub CommandButton1_Click()
Dim rng as Range, rng1 as Range
Dim res as Variant
with worksheets("Sheet2")
set rng = .Range(.Cells(2,1),.Cells(rows.count,1).End(xlup))
end with
res = Application.Match(worksheets("Sheet1").Range("A1"),rng,0)
if not iserror(res) then
set rng1 = rng(res)
rng1.offset(0,1).Value = worksheets("Sheet1").Range("B1").Value
else
msgbox worksheets("Sheet1").Range("A1").Value & " was not " &
vbNewLine & "on sheet2"
end if
End Sub