Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Function for Match and Offset
Wow! Much shorter than I anticipated... I will try it as soon asI
can. Thank you so much! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Function for Match and Offset
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
IF, OFFSET,MATCH Please Help | Excel Discussion (Misc queries) | |||
index, match, offset worksheet function | Excel Worksheet Functions | |||
Offset function with nested match function not finding host ss. | Excel Worksheet Functions | |||
Complicated Index Match Offset function | Excel Worksheet Functions | |||
Offset, indirect, match function limitation on linked worksheets. | Excel Worksheet Functions |