inputbox and vlookup - from a rank beginner

  • Thread starter Thread starter Peter
  • Start date Start date
P

Peter

hi......very much a beginner here...but I have managed to put together a sub
that gives me a small table from an output of telesales operators
calls....columns involve "calls made", "#sales", "total$"

Each operator has 2 "2opnumbers" (not always the same each day) so I am now
trying to put together a sub that allows me collate the info from the table
using an input box to allow me to assign a name to each "opnumber"..
from there lookup each op's credits, and sum the two credits for each op
(does that make sense?)

I've tried the following, which doesn't work.
Would someone like to help?
thanks
user1 = Val(InputBox("Maureen, First Op#", 1))
user2 = Val(InputBox("Maureen, Second Op#"))
mc1 = "=vlookup(user1,f8:j8,2)"
mc2 = "=vlookup(user2,F8:J8,2)"
Worksheets("opstats 154").Range("n4") = "=sum(mc1+mc2)"
 
I wasn't sure what sheet held the lookup array--so I used Sheet1.

I guessed that the input received from Maureen would be a whole number--so I
used clng(). There's a cdbl() if you need decimals. (And make the Dim's "as
Double" instead of "as Long" for both mc1 & mc2.)

And F8:J8 is only one row, so I used F8:J999.

And if you're asking for an operator number, it sounds like you want an exact
match. So I added ", False) to the =vlookup() formulas.

And I bet that you really wanted just the sum of the returned values:

So I used:

Worksheets("opstats 154").Range("n4").Value = mc1 + mc2

=Sum(mc1+mc2)
would be redundant (even if that syntax worked).

=====
You may want to make your next project into a small userform that accepts the
input for both variables--instead of using inputboxes. If you're interested:

http://www.microsoft.com/ExcelDev/Articles/sxs11pt1.htm

=====

Option Explicit
Sub testme01()

Dim user1 As Long
Dim user2 As Long
Dim mc1 As Variant
Dim mc2 As Variant
Dim myMsg As String
Dim myLookUpRng As Range

user1 = CLng(Application.InputBox(prompt:="Maureen, First Op#", _
Title:="First", Type:=1))
If user1 <= 0 Then
Exit Sub
End If

user2 = CLng(Application.InputBox(prompt:="Maureen, Second Op#", _
Title:="Second", Type:=1))
If user2 <= 0 Then
Exit Sub
End If

With Worksheets("sheet1")
Set myLookUpRng = .Range("F8:J999")
mc1 = Application.VLookup(user1, myLookUpRng, 2, False)
mc2 = Application.VLookup(user2, myLookUpRng, 2, False)
End With

myMsg = ""
If IsNumeric(mc1) Then
'do nothing
Else
myMsg = "Error with: " & user1 & vbLf
End If

If IsNumeric(mc2) Then
'do nothing
Else
myMsg = myMsg & "Error with: " & user2
End If

If myMsg = "" Then
Worksheets("opstats 154").Range("n4").Value = mc1 + mc2
Else
MsgBox myMsg
End If

End Sub
 
Back
Top