 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / December 2007Error msg with vlookup
Does anyone know why I get an error #NAME? from this : activecell.formulaR1C1 = "= VLOOKUP(RC[-1],Sheet1!C[-1]:C[75],hdr,0)" hdr has been DIM as an integer. When running the value of hdr=33 If I put 33 in place of hdr qne run the macro, the lookup runs fine? Any help is appreciated  SignatureKWB The variable "hdr" is named within VBA but you are posting a formula to a cell. The formula in some cell reads = VLOOKUP(RC[-1],Sheet1!C[-1]:C[75],hdr,0) but "hdr" is not a named cell or definition within Excel only within VBA environment. best wishes SignatureBernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email > Does anyone know why I get an error #NAME? from this : > [quoted text clipped - 4 lines] > > Any help is appreciated Is there a way to use a variable in Vlookup? SignatureKWB > The variable "hdr" is named within VBA but you are posting a formula to a > cell. The formula in some cell reads [quoted text clipped - 10 lines] > > > > Any help is appreciated Yes, take it out of the string and concatenate the variable in its place (that will put in the value in the variable rather than its name). activecell.formulaR1C1 = "= VLOOKUP(RC[-1],Sheet1!C[-1]:C[75]," & hdr & ",0)" I got a feeling the above line will word wrap in your newsreader. If it does, the above was meant to be all on one line. Rick > Is there a way to use a variable in Vlookup? > [quoted text clipped - 12 lines] >> > >> > Any help is appreciated Amazing. Works like a charm. Thanks Rick , that was incredibly helpful. SignatureKWB > Yes, take it out of the string and concatenate the variable in its place > (that will put in the value in the variable rather than its name). [quoted text clipped - 23 lines] > >> > > >> > Any help is appreciated You are welcome. The key thing to remember is anything you put inside quote marks is text, characters without any code meaning, and nothing else. Rick > Amazing. Works like a charm. > Thanks Rick , that was incredibly helpful. [quoted text clipped - 30 lines] >> >> > >> >> > Any help is appreciated |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.