Something missing from code

[jrhess003]

I must be missing a little bit from the code I'm trying to write. I am writing a Sub to assign to a button that will lookup certain values using Hlookup.

Here is the lookup table:


50
60
70
80
90


F
D
C
B
A



Here is the chart I want the button to auto-fill:


Final Score
Lettter Grade


68



75



82



88



95




Here is the code I have so far:

Sub Lookup()
Dim Score As String
Dim Grade As Variant
Score = InputBox("Enter Score", "Score Lookup")
Grade = Application.WorksheetFunction.HLookup(Score, Range("B3:F4"), 2)
End Sub

Thanks in advance for any answers.

[Paul_Hossler]

Your Sub doesn't return (do) anything with Grade

A Function returns an answer, so you could turn it into a function like any built in Excel functions




Say 'Final Score' is in A1




Sub Lookup()
Dim iRow as Long

For iRow = 2 to 1000
if Cells(iRow,1).Value < 0 then Exit Sub

Cells(iRow,2).Value =Application.WorksheetFunction.HLookup(Cells(iRow,1), Range("B3:F4"), 2)

Next iRow

End Sub


would most likely be a start


BTW, there's a lot of good books out there to get started with VBA macros. They use a more logical progression of teaching than just hit or miss questions

[jrhess003]

Your Sub doesn't return (do) anything with Grade

A Function returns an answer, so you could turn it into a function like any built in Excel functions




Say 'Final Score' is in A1




Sub Lookup()
Dim iRow as Long

For iRow = 2 to 1000
if Cells(iRow,1).Value < 0 then Exit Sub

Cells(iRow,2).Value =Application.WorksheetFunction.HLookup(Cells(iRow,1), Range("B3:F4"), 2)

Next iRow

End Sub


would most likely be a start


BTW, there's a lot of good books out there to get started with VBA macros. They use a more logical progression of teaching than just hit or miss questions


Thanks for the response. It was a homework question for an excel class I am taking. I figured it out though. I read the problem incorrectly. I ended up doing something similar to what you said though so thank you.