+ Reply to Thread
Results 1 to 31 of 31

R1C1 cell reference in Vlookup...with wildcard?

  1. #1
    Registered User
    Join Date
    10-03-2012
    Location
    Knightdale NC
    MS-Off Ver
    Excel 2010
    Posts
    16

    R1C1 cell reference in Vlookup...with wildcard?

    Hi All,
    My VBA Vlookup is working, but producing the wrong results, because I don't have an exact match of the text between the table where my formula goes, and the lookup table. Please help if you're able. I'd greatly appreciate it. Below is my functioning formula.

    Please Login or Register  to view this content.
    it works for lookup values from table A, that have no other values similar in table B, such as "YO GABBA GABBA! LI" which is in table A, "Yo Gabba Gabba - AC Entertainment and Broadway Series South" which is in table B. I am not using exact match because the wording from table to table is not the same.

    The problem comes when I have a value in table A that is very similar to values in table B. The V lookup seems to pick the first close match, not the one that is really the matching title. Ex. "THE GIBSON BROTHER" is in Table A, and "The Gathering: A Holiday Concert - PineCone Piedmont Council of Traditional Music" and The Gibson Brothers - PineCone Piedmont Council of Traditional Music" are consecutive row entries in table B. The Vlookup seems picks, ""The Gathering: A Holiday Concert - PineCone Piedmont Council of Traditional Music", even though the "lookup value referred to with R1C1 notation is actually "THE GIBSON BROTHER". I think this is because the Vlookup is only matching the first several characters of the "lookupvalue" R1C1 reference, but I'm not sure, and I'd love for this vlookup to work correctly. I'd thought that, if there's a way to put a wildcard (*) on the R1C1 reference, so that it looks for any of the contents in the cell, not just the first few letters, that'd be great, but I've been searching online so far, with no success.
    Thanks for any help you can give!
    Musicman715
    Last edited by JBeaucaire; 08-24-2013 at 11:03 AM. Reason: Added CODE tags, as per Forum Rules. Take a moment to read the Forum Rules in the menu bar above. Thanks.

  2. #2
    Forum Expert
    Join Date
    08-02-2013
    Location
    Québec
    MS-Off Ver
    Excel 2003, 2007, 2013
    Posts
    1,412

    Re: R1C1 cell reference in Vlookup...with wildcard?

    Hi,
    You are missing the last argument in your VLOOKUP formula to force exact match.
    By default, the value of this argument is TRUE and in this case, VLOOKUP returns the first value found. If your list is not sorted, you will may not get an exact match.

    Please Login or Register  to view this content.
    GC Excel

    If this post helps, then click the star icon (*) in the bottom left-hand corner of my post to Add reputation.

  3. #3
    Registered User
    Join Date
    10-03-2012
    Location
    Knightdale NC
    MS-Off Ver
    Excel 2010
    Posts
    16

    Re: R1C1 cell reference in Vlookup...with wildcard?

    Thanks GC Excel, but the exact match returns "#N/A" because the description "THE GIBSON BROTHER" from table A, is not an exact match for the description "The Gibson Brothers - PineCone Piedmont Council of Traditional Music" from Table B. If you or anyone else comes up with a solution, that'd be awesome, and I really appreciate your help.

  4. #4
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,802

    Re: R1C1 cell reference in Vlookup...with wildcard?

    Quote Originally Posted by musicman715
    The Vlookup seems picks, ""The Gathering: A Holiday Concert - PineCone Piedmont Council of Traditional Music", even though the "lookup value referred to with R1C1 notation is actually "THE GIBSON BROTHER". I think this is because the Vlookup is only matching the first several characters of the "lookupvalue" R1C1 reference, but I'm not sure,
    From the help file http://office.microsoft.com/en-us/ex...011.aspx?CTT=1
    Quote Originally Posted by MS Office help
    If range_lookup is either TRUE or is omitted, an exact or approximate match is returned. If an exact match is not found, the next largest value that is less than lookup_value is returned.
    What does this mean? When dealing with text strings, less than and greater than are basically referring to alphabetical order. So aaaa<ab<abbbb<abc<abccc. In your case, "The Gathering..." < "The Gibson Brothers" < "The Gibson Brothers: PineCone..." When lookup_value is between two entries in the lookup table, VLOOKUP() chooses the lesser value (in this case "The Gathering...").

    Would you be allowed to sort the list in descending order instead of ascending? Will your lookup_value always be the beginning the of the text string? If yes to both, then you might be able to use the =MATCH() function (http://office.microsoft.com/en-us/ex...679.aspx?CTT=1) instead of VLOOKUP(). VLOOKUP() is limited in that the fourth argument is either true or false. The MATCH() function allows for three possibilities in that fourth argument. A value of -1 in the fourth argument for a lookup table sorted in descending order, and MATCH() will locate the entry that is just larger than lookup_value. Since MATCH() returns the index number of the entry (instead of the entry itself), people usually nest the MATCH function inside of the INDEX() function.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  5. #5
    Registered User
    Join Date
    10-03-2012
    Location
    Knightdale NC
    MS-Off Ver
    Excel 2010
    Posts
    16

    Re: R1C1 cell reference in Vlookup...with wildcard?

    Thanks Mr Shorty. Now I understand Why my Vlookup is choosing "The Gathering"...instead of "The Gibson Brothers". But, I don't know that I can sort decending. Because, from the Microsoft website, "Important If range_lookup is either TRUE or is omitted, the values in the first column of table_array must be placed in ascending sort order; otherwise, VLOOKUP might not return the correct value." When I've used Vlookup before, and not sorted, then it has returned incorrect values. I do believe this sort has to be ascending to return the correct value. Also, my lookup value is not always at the beginning of the text string. The key word in the cell might be the 3rd, 4the 5th or any other word or words in the cell. This is why I tried to put a wildcard on the lookup value, so that it would search for the best match for all the words in the cell. But, A.) I still don't know if even that would produce the correct result, and B.) I have not yet been able to find a way to put a wildcard on an R1C1 reference, or a "Cells(n,2)" type reference. Is there a way I can use a FormulaR1C1 format to put a formula in the cell, so it points to the cell reference without naming an actual cell, but still will look at ALL of the words from Table A before finding the best match in table B? The matching words won't be the same for each iteration of the for loop, nor will they be in the same spot in the text string of each cell. This is why I'm using R1C1 rather than an actual cell reference, or actual words. Thanks for any help anyone can give. I really do appreciate you all!
    Musicman715

  6. #6
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,802

    Re: R1C1 cell reference in Vlookup...with wildcard?

    You are correct that you must have your lookup table sorted in ascending order in order to use the VLOOKUP() function. However, the =MATCH() function is not limited in the same way. Are you not allowed to use the MATCH() function?

  7. #7
    Registered User
    Join Date
    10-03-2012
    Location
    Knightdale NC
    MS-Off Ver
    Excel 2010
    Posts
    16

    Re: R1C1 cell reference in Vlookup...with wildcard?

    Thanks Mr. Shorty, I probably can use the Match function, I'm just having trouble with how to set the VBA code up to put that match function into the correct. Cells. If I give you Examples of my tables, could you maybe see if you can help me figure out the correct syntax for the match function.
    Here again is my current code. My effort would be to replace the Vlookup with a Match().
    Y4 = Range("A1", Range("A1").End(xlDown)).Rows.Count

    Please Login or Register  to view this content.
    Table A
    GL Description Amount Event ID
    668 The Gibson Bro$55.00 (currently Vlookup gets put here by my macro to retrieve event ID from Asscending Sorted Table B)
    524 Yo Gabba Ga $42.00 (currently Vlookup gets put here by my macro to retrieve event ID from Asscending Sorted Table B)
    323 Storyb Dora $15.00 (currently Vlookup gets put here by my macro to retrieve event ID from Asscending Sorted Table B)

    Table B
    Description Start Date End Date Event ID
    The Gibson B 02/14/13 02/17/13 20733
    The Gathering 01/13/12 01/13/12 20730
    Dora The Expl 11/01/13 11/03/13 21493

    I'd like the New MATCH formula to be insereted by the VBA in the cell of table A where the vlookup currently is. I know how to do that. But I'd like the match formula to be able to see that "Gibson", or "Dora" are part of the cell contents in both Table A, and Table B, and so, pick up on that, even though other wording might be the same or different. But I'd like to do this with either a "Cells(n,2)" type reference or a R1C1 type reference, since with each iteration of the For loop, I'll be in a new cell...so that I don't have to name an actual cell reference, or put the words from table A hardcoded into the macro, since I won't know which words are different in each iteration.
    I'll work on seeing if I can work out this match possibility, and if you'd be so kind to see if you can work up some code from the example above, I'd truly appreciate your help. Thank You, Thank You!
    Musicman715
    Last edited by JBeaucaire; 08-24-2013 at 11:02 AM. Reason: Added CODE tags, as per Forum Rules. Take a moment to read the Forum Rules in the menu bar above. Thanks.

  8. #8
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,802

    Re: R1C1 cell reference in Vlookup...with wildcard?

    I assume you read through the help file. Can I ask if there is a specific part of the MATCH() function you are having trouble with? Before putting it into VBA, can you hand enter the function into the cell and get the correct answer?

  9. #9
    Registered User
    Join Date
    10-03-2012
    Location
    Knightdale NC
    MS-Off Ver
    Excel 2010
    Posts
    16

    Re: R1C1 cell reference in Vlookup...with wildcard?

    Thanks Mr. Shorty.
    I'm reading about INDEX Match right now, and how I guess, supposedly it conquers VLookup! After my reading. I may be better equipped to tackle the whole match formula on my own. I have not used Match very much before, if at all. I'll get back to you on the result once I complete my reading and give it a try. Thank you, so far, for pointing me in this direction. I think It will be a big help. Again, I'll get back with you once I've read some more, and tried it.
    Cheers,
    Musicman715

  10. #10
    Registered User
    Join Date
    10-03-2012
    Location
    Knightdale NC
    MS-Off Ver
    Excel 2010
    Posts
    16

    Re: R1C1 cell reference in Vlookup...with wildcard?

    Hi Mr. Shorty. Still Struggling with Match. I've read a bunch. Getting N/A error on =Index('[event master lookup table.xls]Sheet1'!$D:$D,("*"&B88&"*",'[event master lookup table.xls]Sheet1'!$A:$A,0). Also getting NA on the plain old =MATCH("*"&B88&"*",'[event master lookup table.xls]Sheet1'!$A:$A,0). And I'm not sure why. I read:

    "If match_type is 0 and lookup_value is a text string, you can use the wildcard characters — the question mark (?) and asterisk (*) — in the lookup_value argument. A question mark matches any single character; an asterisk matches any sequence of characters. If you want to find an actual question mark or asterisk, type a tilde (~) before the character." from the Microsoft website, so that's what I tried above.

    Also have tried sorting Table B in decending order with -1 in the match formula which results in NA. The "*"&B88&"*" part of my Match formula is to make excel see the text in the cell, but I don't think it's working. I think it should because
    Table A column 2 has "YO GABBA GABBA! LI"
    Table B(sorted ascending for the 0 currently) has "Yo Gabba Gabba - AC Entertainment and Broadway Series South" which my Vlookup found correctly before, and returned the proper event ID, so I don't understand why I'm getting NA now, when it worked properly in my vlookup formula.
    3 things. If I can get the match function to work properly, get the cell reference to properly wildcard on the text, and put that wildcarded reference in R1C1, I think this will work. Here's hoping, and thanks for your help!

  11. #11
    Registered User
    Join Date
    10-03-2012
    Location
    Knightdale NC
    MS-Off Ver
    Excel 2010
    Posts
    16

    Re: R1C1 cell reference in Vlookup...with wildcard?

    to note, the = Index...Match formula in the first line of the above reply had a "Match" before the Parenthese preceeding "*"&B88 etc.

  12. #12
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,802

    Re: R1C1 cell reference in Vlookup...with wildcard?

    If match_type is 0 and lookup_value is a text string, you can use the wildcard characters — the question mark (?) and asterisk (*) — in the lookup_value argument.
    FWIW, this is also true for the VLOOKUP function when the 4th argument is FALSE. If you get the wildcard character to work, you can probably go back to the VLOOKUP function instead of MATCH if it is easier.

    I'm not sure why it is not working. If I copy your three album titles into a new spreadsheet and sort them in descending order, either method works correctly for me.
    Please Login or Register  to view this content.
    Please Login or Register  to view this content.
    Is it possible there are extraneous characters (leading spaces or other non-printing characters) that are not being accounted for? It looks to me like it should work either way.

  13. #13
    Registered User
    Join Date
    10-03-2012
    Location
    Knightdale NC
    MS-Off Ver
    Excel 2010
    Posts
    16

    Re: R1C1 cell reference in Vlookup...with wildcard?

    I'll check for extraneous spaces, but I don't think so because the vlookup worked before for "Yo Gabba" because there were no similar entries. Although Not for "Gibson" because of the "Alphabetizing" reason you mentioned before. Did you try this match, first when having the lookup value in a different workbook than the lookup array? Also, looks like it worked even though D2 said "The Gibson" while the look up array said "The Gibson Brothers - PineCone Piedmont Council of Traditional Musi". Is this correct?
    Thanks Musicman715

  14. #14
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,802

    Re: R1C1 cell reference in Vlookup...with wildcard?

    No, these were all in the same worksheet. I don't believe it should matter if the references are to other worksheets or other workbooks.

    Here's the spreadsheet:
    Attached Files Attached Files

  15. #15
    Registered User
    Join Date
    10-03-2012
    Location
    Knightdale NC
    MS-Off Ver
    Excel 2010
    Posts
    16

    Re: R1C1 cell reference in Vlookup...with wildcard?

    I still get the N/A in my workbooks. I copied your formula into table A called 'remittance', looking up from table B 'event master lookup...' The only things I changed were 'E20' to 'B88', and $A19:$A21 to $A$1:$A$65536. Attached are the two files. Hopefully you can help.
    Attached Files Attached Files

  16. #16
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,802

    Re: R1C1 cell reference in Vlookup...with wildcard?

    A couple of observations:

    1) It appears that the lookup table is still sorted in ascending order, so using match_type=-1 will not help.
    2) I also note that there is no entry in lookup table containing "Yo gabba gabba! LI." There is a "Yo gabba gabba..." entry, but none that contain "yo gabba gabba li". Even with the wildcard characters, the lookup function is going to insist that the entire text string given is contained in the lookup values. I copied your formula into several other cells and sometimes it worked and sometimes it returned N/A. The N/A's are because the search string, even with wildcards, is not present in the lookup table.

    It looks to me like you need to change either the lookup values in the lookup table or you need to more carefully choose your lookup string in the lookup function.

  17. #17
    Registered User
    Join Date
    10-03-2012
    Location
    Knightdale NC
    MS-Off Ver
    Excel 2010
    Posts
    16

    Re: R1C1 cell reference in Vlookup...with wildcard?

    MrShorty, I'll check back in Monday. I'll keep thinkin on this. Hopefully, I or you can come up with a solution. To Any of you other Excel nerds out there...If you want to take a crack, I'd greatly appreciate anybody's help! Thanks again MrShorty for your help so far. I hope there's a solution. (Personally, I think there is, I just haven't hit it yet.
    Thanks,
    Musicman715

  18. #18
    Registered User
    Join Date
    10-03-2012
    Location
    Knightdale NC
    MS-Off Ver
    Excel 2010
    Posts
    16

    Re: R1C1 cell reference in Vlookup...with wildcard?

    PS. "Excel Nerds" Is not meant to be derogatory! I'm one, and proud of it!

  19. #19
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,802

    Re: R1C1 cell reference in Vlookup...with wildcard?

    When you get back, perhaps you might explain exactly how you want this spreadsheet to be able to function. Just scanning through the database file, I see several entries that are going to cause problems. For example, there are about 30 "City Staff Meeting" entries. Will you need your lookup function to be able to distinguish between these identical entries? In your search file, you ask it to search for "The Drowsy Chaperone", but there is no such entry. There is an entry for "Drowsy Chaperone...." Do you need the lookup function to be "smart" enough to recognize that the "The" is not essential to finding the match?

  20. #20
    Registered User
    Join Date
    10-03-2012
    Location
    Knightdale NC
    MS-Off Ver
    Excel 2010
    Posts
    16

    Re: R1C1 cell reference in Vlookup...with wildcard?

    The "City Staff Meeting" will never come into play, because it will never b on table A. Table A, is only events for which my company sold tickets. Table B is all the events from the past year. I will need it to detect that "Drowsy Chaperone" is the same as "The Drowsy Chaperone". Also, there are several "North Carolina Symphony Events in Table A, and such in table B, I'd want the formula to pick up that each is different...such as "North Carolina Sym Disney" in table A would be the same as "North Carolina Symphony Presents The Magic of Disney" or whatever it says like such in table B. Or that "Storybook Theater Dora" from Table A is the same as "Dora the Explorer-Broadway series south" from table B. I do foresee some issues with this, but I'm trying to figure out a way around them as well. Thanks so much for your help.
    Musicman715

  21. #21
    Registered User
    Join Date
    10-03-2012
    Location
    Knightdale NC
    MS-Off Ver
    Excel 2010
    Posts
    16

    Re: R1C1 cell reference in Vlookup...with wildcard?

    Therein lies my crux of the problem right now. There is no "exact matching" column that is shared between the two tables, because they both come from different sources, where people manually enter the title of the event. The ticketing company (Table A) calls the events by a slightly different name than do our staff who put the event in our software.
    Thanks,
    Musicman715

  22. #22
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: R1C1 cell reference in Vlookup...with wildcard?

    Your post #1 and #7 did not comply with Rule 3 of our Forum RULES. Use code tags around code.

    Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

    Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

    ----------------
    I have made the correction in your posts for you this time, as a courtesy. Please remember in your future posts. This has merely been a harmless warning to underline the importance that you read and follow the Forum Rules we are all expected to abide. Thank you for your understanding and compliance.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  23. #23
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,802

    Re: R1C1 cell reference in Vlookup...with wildcard?

    I've thought about your problem. A few things that have crossed my mind.

    I'm assuming that the database file cannot/will not be changed. This means that any changes to get the lookup function to find "search string" will need to occur in the searching file.

    One thing to recognize is that Excel's lookup functions employ simple lookup algorithms -- not robust "search engine quality" searches.

    When Excel's lookup functions return N/A, it means that it did not find "search string" in the lookup column. "Fixing" a not found result means "fixing" the search string until Excel can match "search string" with something in the lookup column in the database -- even with wildcard characters. As I go through this, keep in mind that you will have to decide how much of this you would want the user to do and how much do you want your code to do. The more you make the user do, the easier it will be to write the code.

    A search string like "*The Drowsy Chaperone*" will search for an entry that contains that exact phrase. Since that phrase is not present, it returns N/A. You could modify to "*the*drowsy*chaperone*" and it will now look for those three keywords, but it will insist that all three be present and in that order. Again a non-detect. Recognizing that "the" is a common word that probably doesn't help identify an entry, we could try "*drowsy*chaperone*" -- and we would get the desired result. This will be more difficult for a search string like "*wierd al yankovich", but we could do some trial and error trying different combinations of the three keywords.

    To formalize the procedure, it might look something like this:

    1) Parse "search string" into keywords. "the drowsy chaperone" becomes "the" "drowsy" "chaperone". "weird al yankovich" becomes "wierd" "al" "yankovich".
    2) separating keywords with asterisks, try different combinations of the keywords. A part of this might include recognizing small words like "the" "and" "or" etc. so they are not included in "search*string"
    3) test if correct result is returned. If not, try a different combination of keywords.
    4) repeat until correct result is returned.

    As I said, you could put some or all of this on the user. User instructions could be "enter search string with words separated by '*'s instead of spaces. If n/a is returned, try a different search string." and your code would probably work just fine as is (subject to user's ability to build/choose good search strings).

    Another problem I noted: it seems that there were "north carolina symphony" entries and "nc symph" entries. will you need the algorithm to be able to recognize that sometimes "north carolina" is abbreviated "nc" and search accordingly?

  24. #24
    Registered User
    Join Date
    10-03-2012
    Location
    Knightdale NC
    MS-Off Ver
    Excel 2010
    Posts
    16

    Re: R1C1 cell reference in Vlookup...with wildcard?

    Thanks JBeaucaire, will keep this in mind for my future posts.
    Mr. Shorty, I am the user writing the VBA, but also doing the labor of searching in the table to find the correct events. Originally, I have been typing search strings into our event software to find the correct events, but since I have several sheets to do this to, from each week of last year, and continuing, It's a tedious process, that, I thought could be sped up by exporting the table to be searched from our software to excel. That is what table B is. I thought, "Now that that table is in excel, I can do a vlookup, (or match) and easily find the correct events, but as you say, our software has that "search engine" type quality where you can type in "Yo Gabba" and everything with that comes up. I thought wildcards would do this in excel, but not so far. Also, to make the VBA process worth while, I'd need it to do this searching that I've been doing in our software with the VBA, not manually, otherwise it is no more efficient than what I am doing. Finally, I'm hoping to find a method to do that search on a R1C1 cell reference rather than actual words, because those words will change with each iteration of my loop. If you can think of any way to make this automated, great, but if you are stumped as I how to do that automation of the search in an R1C1 format, please let me know. I'll keep thinking on this, but for now, until the solution is found, I can keep doing the "search process manually. Again please let me know if you want to hang this up, or if you'd like to keep working on it. Thank you so much for all your help. I do appreciate it, I've learned much already from you, and hopefully, there is a solution out there! Thanks again!
    musicman715

  25. #25
    Registered User
    Join Date
    10-03-2012
    Location
    Knightdale NC
    MS-Off Ver
    Excel 2010
    Posts
    16

    Re: R1C1 cell reference in Vlookup...with wildcard?

    Mr Shorty,
    I am back with a possible solution. The following code works in excel itself if I have my two previous worksheets open [code] For n = Y4 - 3 To 2 Step -1
    'Cells(n, 4).FormulaR1C1 = "=VLOOKUP(SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(B114,"" "",REPT("" "",LEN(B114))),LEN(B114))),TRIM(RIGHT(SUBSTITUTE(B114,"" "",REPT("" "",LEN(B114))),LEN(B114))),""*""&TRIM(RIGHT(SUBSTITUTE(B114,"" "",REPT("" "",LEN(B114))),LEN(B114)))&""*""),'[event master lookup table.xls]Sheet1'!$A:$D,4,FALSE)"
    'Next n [code]

    The Vlookup actually works in excel, but When I change all of the "B114" s to "RC[-2]", I get "Run Time Error 1004: Application-defined or Object Defined Error" I took the spaces out between the ""'s with no better result. Any help to get the "Vlookup" correctly worded and working in VBA would be awesome. Again, it works in excel.
    Thanks
    musicman715

  26. #26
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,802

    Re: R1C1 cell reference in Vlookup...with wildcard?

    I wish I could help, but I use Excel to manipulate numbers. Manipulating text strings on that level sends me searching for a straitjacket and a padded room.

    I don't understand why it would work correctly when you assign an A1 reference to the formular1c1 property, then fail when you assign an r1c1 reference to the formular1c1 property.

    I think you are headed the right direction -- figuring out how to manipulate the text string so the lookup function is using a search string it can find. Beyond that, I'm not going to be much help in figuring out exactly how to perform that manipulation.

  27. #27
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: R1C1 cell reference in Vlookup...with wildcard?

    What code did you actually use when you tried the RC version? Did you replace the Sheet1'!$A:$D part with Sheet1'!C1:C4?
    Remember what the dormouse said
    Feed your head

  28. #28
    Registered User
    Join Date
    10-03-2012
    Location
    Knightdale NC
    MS-Off Ver
    Excel 2010
    Posts
    16

    Re: R1C1 cell reference in Vlookup...with wildcard?

    Yes Rory, that's what I did, when I changed it to RC...Also to note. If you can help at all, that'll be much appreciated. Here are the two documents I updated with tag words to make the vlookup formula work. Check out "Master Upload File" in sheet "Remittance"...Column 4. The lookup is for the most part retrieving the data I need. from "Event Master lookup" table attached. The one circumstance where it didn't, I changed the formula that was in cell D2:5 to an actual hard coded number. But I want the formula in R1C1 in VBA, so that I can get away from doing the hard number for the most part. Look in the "Remitance file Column 4 and you'll see the vlookup I used. I recorded the Vlookup formula, and instead of putting the formula in the cell, the recorded macro puts the text "=VLookup...." in the cell. I think that is because it took a long time to type the formula during the recording, and maybe it timed out? Not sure. Attached are the files, and below is the recorded code that I want to work, and change to R1C1.
    Please Login or Register  to view this content.
    Thanks musicman715
    Attached Files Attached Files

  29. #29
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: R1C1 cell reference in Vlookup...with wildcard?

    Too many quotes:

    Please Login or Register  to view this content.

  30. #30
    Registered User
    Join Date
    10-03-2012
    Location
    Knightdale NC
    MS-Off Ver
    Excel 2010
    Posts
    16

    Re: R1C1 cell reference in Vlookup...with wildcard?

    Yeah...Figured that out, but tried deleting the extra sets with no different result. But, finally figured out the problem. When I was recording this monster vlookup formula, I should've written it in the cell first, copied and pasted to word. Then go back to excel, hit record, Pasted it in the formula bar, not just click once on the cell. Then Excel will write it in VBA for me, and R1C1, which is what I was trying to do all along. Thanks everyone for your help! This is the code I ended up with that works!
    Musicman715...Solved...Can anyone tell me how to mark this "Solved"?
    Please Login or Register  to view this content.

  31. #31
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: R1C1 cell reference in Vlookup...with wildcard?

    I'm not sure you can after 2 days have passed - you'll have to ask a moderator.

  32. #32
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: R1C1 cell reference in Vlookup...with wildcard?

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] R1C1 keep reference cell fixed in copy
    By ctosborne89 in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 09-05-2012, 05:22 PM
  2. Use Match or Lookup to create dynamic cell reference in VBA R1C1 formula
    By rmunsun1 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 01-20-2012, 06:10 AM
  3. old (Excel 2.x) macro R1C1 reference to active cell
    By riwiseuse in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 10-19-2010, 09:15 AM
  4. Reference a cell in another workbook using R1C1
    By JonPugh in forum Excel General
    Replies: 3
    Last Post: 01-08-2010, 05:55 PM
  5. absolute cell reference in R1C1 reference style
    By Prorocentrum in forum Excel General
    Replies: 1
    Last Post: 06-18-2007, 04:59 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1