VLOOKUP in a macro

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Barney - 10 Jan 2008 13:45 GMT

In Excel 2002 using and an InputBox I want to input an ID#, then find that
ID# in a VLOOKUP table, then using the InputBox, enter a score (Points) in
the cell next to (right of) the ID# in the table.

I am trying this to no avail:

Sub PointsInput()
'
' PointsInput Macro
' Macro recorded 1/10/2008 by Barney
'

'
Range("C2").Select
ActiveCell.FormulaR1C1 = _
InputBox("Enter ID#")
Range(VLOOKUP(C2,C4:C1000,2).Select
InputBox("Enter Points")

End Sub

Also, how do I make the Input Box remain active while I loop through 120
ID#'s and Points so that I don't need to continually select a macro button?

Thanks in advance

Barney

Reply to this Message

Don Guillett - 10 Jan 2008 14:22 GMT

It's not clear to me but it seems as if you have a list. Put your score in
the next column

Signature

Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com

> In Excel 2002 using and an InputBox I want to input an ID#, then find that
> ID# in a VLOOKUP table, then using the InputBox, enter a score (Points) in
[quoted text clipped - 24 lines]
>
> Barney

Reply to this Message

Don Guillett - 10 Jan 2008 14:24 GMT

Hit send too soon
a b c d
num score
1 a 3 c
2 b 2 b
3 c 1 a

Sub putscore()
For Each c In Range("a2:a4")
Columns(3).Find(c).Offset(, 1) = c.Offset(, 1)
Next c
End Sub

Signature

Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com

> In Excel 2002 using and an InputBox I want to input an ID#, then find that
> ID# in a VLOOKUP table, then using the InputBox, enter a score (Points) in
[quoted text clipped - 24 lines]
>
> Barney

Reply to this Message

Barney - 10 Jan 2008 14:47 GMT

Actually, I have the ID numbers (1 thru 1000) in a column. Only 120 of
those 1000 will get a score beside them and I am never sure which 120 that
will be. I don't want to have to scroll to each number as time is critical.
Hence the input box, VLOOKUP, and the macro.

Barney

> Hit send too soon
> a b c d
[quoted text clipped - 36 lines]
>>
>> Barney

Reply to this Message

Don Guillett - 10 Jan 2008 14:57 GMT

How do the scores get there?
On the macro presented if there was no score beside it then, of course, it
would not be assigned. Try it.
Send me a workbook to my address below, if desired.

Signature

Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com

> Actually, I have the ID numbers (1 thru 1000) in a column. Only 120 of
> those 1000 will get a score beside them and I am never sure which 120 that
[quoted text clipped - 43 lines]
>>>
>>> Barney

Reply to this Message