VBA Function for Match and Offset
 

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 a

=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.

VBA Function for Match and Offset
 

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Jaybird" wrote in message
...
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 a

=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.

VBA Function for Match and Offset
 

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

VBA Function for Match and Offset
 

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.

VBA Function for Match and Offset
 

try

Dim i As Long
Dim mr As String

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Jaybird" wrote in message
...
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.

VBA Function for Match and Offset
 

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.

VBA Function for Match and Offset
 

yes
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Jaybird" wrote in message
...
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.

VBA Function for Match and Offset
 

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!

VBA Function for Match and Offset
 

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?

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Jaybird" wrote in message
...
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!

VBA Function for Match and Offset
 

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.

VBA Function for Match and Offset
 

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Jaybird" wrote in message
...
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.

VBA Function for Match and Offset
 

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(xl up))
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.