VBA Function for Match and Offset

[Jaybird]

Hello, VBA moron here... I have two formulas (Match and Offset) in
Excel which allow me to reproduce cells containing information in one
worksheet on another. Basically, we have a long list of
identification numbers (4500+) with information about that product in
one worksheet called: Prealert. Our customer sends us this huge list
(a database table, really) with all of the stuff they want to send us
all month long. We are supposed to verify the product that comes in
is on this list and reproduce the relevant information on another
worksheet I call: Match. The formulas I am currently using are:

=MATCH($A2,Prealert!$H$2:$H$31,0) to find the row number from Column H
containing the ID number we are looking for
And,
=OFFSET(Prealert!$H$1,$B2-1,0) to reproduce the cells of that row
containing important information

This works pretty well if you understand the formula. The other users
and my lead do not understand it.

I figured that I might get an 'Attaboy' at work if I could figure out
an easy way for them to use these formulas. My first thought was to
come up with a VBA program. The problem is that I don't know what I'm
doing.

The questions I've managed to come up with so far:

What sort of function will I need?
How do I declare my variables?
Should I use an input box to input the ID number?
How do I call up the function once it works?
What sort of error trapping will I need?

Important facts that will likely affect the answers:

The users will most likely open the spreadsheet, activate the function
and begin to scan the ID numbers in. It would help if there were an
audible for missed scans and for invalid inputs, and for items that
aren't on the prealert so that the user can keep scanning until they
detect a problem.

Thanks to anyone who can steer me in the right direction.

 

[Don Guillett]

Try this idea where your list to find is in col g starting at row 5 and you
are looking in col H.

Sub findit()
'On Error Resume Next
For i = 5 To Cells(Rows.Count, "g").End(xlUp).Row
myvalue = Columns("h").Find(Cells(i, "g")).Offset(, 3)
MsgBox myvalue
Next i
End Sub

 

[Jaybird]

Wow! Much shorter than I anticipated... I will try it as soon asI
can. Thank you so much!

 

[Jaybird]

Okay, I am getting this error:

Object variable or With block variable not set (Error 91)

Don't know enough about declaring the variable, apparently.

 

[Don Guillett]

try

Dim i As Long
Dim mr As String

 

[Jaybird]

Dim i As Long

Dim mr As String

Do you mean Dim myvalue as String?

In any case, I'm still getting the error. But I have a couple of
other questions while you are kind enough to help me:
-Does the offset function work as you have written it? I am
unfamiliar with the way in which these functions are interpreted by
VBA, but I understood that it needed to be something like:
APPLICATION.OFFSET(J2,0,3)
-Doesn't the message box need to be something like MsgBox(myvalue)
-My version is Excel 2003. Could there be any missing references that
would cause my code not to behave as yours does?

Okay, that's three question... At least I can count.

 

[Don Guillett]

yes

 

[Jaybird]

I'm sorry. "Yes" to what?

As near as I can tell, the "myvalue" statement is being rejected
because it hasn't been declared properly. I've attempted to declare
it as a string, an integer, and a variant without success. I'm not
sure what type of data type it is in this context.

Thanks for the help, by the way!

 

[Don Guillett]

You should have been able to figure out a typo and correct but you asked
=====

Dim i As Long
Dim mr As String

Do you mean Dim myvalue as String?
======
I answered "YES"
=======
Is there some part of this that is difficult to understand?

 

[Jaybird]

Sub findit()
On Error Resume Next
Dim i As Long
Dim myvalue As String

For i = 5 To Cells(Rows.Count, "G").End(xlUp).Row
myvalue = Columns("H").Find(Cells(i, "G")).Offset(, 3)
MsgBox (myvalue)
Next i
End Sub

This is the current version of the code you gave me. I'm no longer
getting the error (I didn't notice the ' until now), but the message
box doesn't say anything, so I don't know if it's working or not. Let
me see if I'm reading this right...

"Look for the data in column G in column H and then tell me if you
find a match in a message box, but offset the results by 3 columns."

Typing that out helped. I realized that the offset WAS working, and
throwing off my expected results. Took it out and the results of the
search show up in a message box. Thanks for everything. I'll
continue working on this to take the form that I want and ask more
questions as they come up.

 

[Don Guillett]

Your OP said OFFSET.... Do you no longer desire and offset?

 

[Jaybird]

No, I actually do. But not at this particular point. I'm using
offset to give me the data in the cells adjacent to the cell
containing the ID. There is another method I am considering, but I
haven't gotten it to work:

dim rng as range, cell as cell
dim wr as long

rw=2
set rg=range(cells(1,"H"),cells(rows.count,"H").end(xlup))
for each cell in rg
if cell.value=[whatever the id input method is] then
cell.entirerow.copy destination:worksheets("Sheet2").Cells(rw,1)
rw=rw+1
end if
next

Or some such; the above is incomplete. Much of it was borrowed from
an earlier post to the forum. I'm trying to figure out how to
integrate your find statement with the above and present the results
in a way that the user can understand without having to know specifics
about what is actually happening. This is actually a good project for
me. I lack knowledge and experience, but I hope to gain a little of
both from this excercise. I appreciate the assistance.