Type mismatch error in Excel

Hello,
I receive a type-mismatch error when compiling. The  cells being
referenced only have dates. Can someone help? Heres some of the code

Application.Windows("rpt_BigTicket.xls").Activate
a = Range("D:J")
b = DateValue(Format(Now(), "mm-dd-yyyy")) - Weekday(Now(), 1)
c = Application.VLookup(b, a, 2, False) "This is where the ERROR-13
'Tpye Mismatch occurs'

0
r_fordjr (4)
4/28/2006 4:23:15 PM
excel 39879 articles. 2 followers. Follow

1 Replies
582 Views

Similar Articles

[PageSpeed] 11

The default property for a range is the .Value property, so 

    a = Range("D:J") is returning an array of values, not a range object.

Try something like:

    Dim a As Range
    Dim b As Long
    Dim c As Variant
    Application.Windows("rpt_BigTicket.xls").Activate
    Set a = ActiveSheet.Range("D:J")
    b = CLng(Date - WeekDay(Date, 1))
    c = Application.VLookup(b, a, 2, False)

    In article <1146241395.124409.313770@i40g2000cwc.googlegroups.com>,
 "r_fordjr@msn.com" <r_fordjr@msn.com> wrote:

> Hello,
> I receive a type-mismatch error when compiling. The  cells being
> referenced only have dates. Can someone help? Heres some of the code
> 
> Application.Windows("rpt_BigTicket.xls").Activate
> a = Range("D:J")
> b = DateValue(Format(Now(), "mm-dd-yyyy")) - Weekday(Now(), 1)
> c = Application.VLookup(b, a, 2, False) "This is where the ERROR-13
> 'Tpye Mismatch occurs'
0
jemcgimpsey (6723)
4/28/2006 4:40:40 PM
Reply:

Similar Artilces:

Too Many IF Statements Nesting Error (Excel Formula Loop w/o VBA)
Hello Excel Problem Gurus, First of all, let me thank you in advance. I find it exemplary that you all can devote time to helping others who are having issues with their work. Hopefully one day I can be at a mentor level, and help others too. Hope you can help! I have an issue where I don't know how to write the formula that I need without going over on the nesting. The current formula that I have is as follows: =IF(OR(B7="",J7="",L7="",M7="",N7="",O7="",P7=""),"No Data",IF(V7="Yes",&qu...

How do I create a sample in Excel?
I am trying to create a sample, needing every 20th record out of around 3000 records. Mary, You need a VBA procedure. Sub AAA() Dim RowNdx As Long Dim DestRng As Range Set DestRng = Worksheets("Sheet2").Range("A1") For RowNdx = 1 To 3000 Step 20 Rows(RowNdx).Copy Destination:=DestRng Set DestRng = DestRng(2, 1) Next RowNdx End Sub -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Mary" <Mary@discussions.microsoft.com> wrote in message news:ADE0AD7F-B459-41AE-8B2...

Office 2003 on WinXP is receiving an error...
This operating is not presently configured to run this application.' This nmessage keeps popping up after I install WInXP Sp2. Right after I install office 2003, and I try to open MS word 2003, the above message appears. Can anyone point me in the right direction? RodneyJ Insert office cd>repair "RodneyJ" <u58446@uwe> wrote in message news:a43130535cd3e@uwe... > This operating is not presently configured to run this application.' This > nmessage keeps popping up after I install WInXP Sp2. Right after I > install > office 2003, and I t...

Trouble with Excel 2000 Help file
Running Excel 2000. When I click on "See Also" at the top left of my Help screen, I get an internet explorer error message: "An error has occurred in the script on this page" Line1, Character 1. It goes on with some other stuff. Also, at the top of the page, I don't see hyperlinks anything else, but I see two little blue squares with a dot in the middle of each, which I usually associate with images that don't download. I seem to have my help files confused. I've tried reinstalling Office, but that doesn't help. I've gone to the microsoft support w...

Excel 97 Win XP calc problem?
I have a machine running office 97 and Win XP. I have seen posts stating it runs fine, but I don't know how man people use spreadsheets that require a hard calculation: Excel will not force a calculation. i.e. ctrl-alt-f9 does not cause full workbook calculation as required. Meaning cells do not update and it doesn't calculate unless you clic in the cell to edit and hit enter. neither does the "Calc Now" button. Auto or manual calculation mode has no effect. Anybody else have this problem -- Message posted from http://www.ExcelForum.com Make sure that both the SR1 a...

Date display in Excel
Format column of cells as Date, display as mm/dd/yy. Date entered into cell, shows up correctly in the text entry field at the top of the screen, but the data on the worksheet displays as "33747", or similar number. Only happening on one workbook. Try tools|options|View tab|uncheck Formulas. Clark wrote: > > Format column of cells as Date, display as mm/dd/yy. Date > entered into cell, shows up correctly in the text entry > field at the top of the screen, but the data on the > worksheet displays as "33747", or similar number. Only > happening on one...

Errors saving to network drive
When my users open an Excel spreadsheet located on a network drive, make their changes, and then try to save the spreadsheet, they get an error message saying "Your changes could not be saved to Whateverfile.xls, but were saved to XXXXXXXX ( 8 digit hex number ). Close the existing document, then open the temporary document and save it under a new name" The users have been explicitly granted full rights, including Delete and Modify, to the shared folder in which the spreadsheet resides. I have disabled their Symantec Antivirus realtime protection for the time being. This pr...

Cannot open Excel attachment from e-mail when Excel is already ope
This is driving me crazy. If I already have Excel open (with or with out a spreadsheet open) I cannot open an Excel spreadsheet attached to an e-mail. If I close Excel and retry it will open Excel and the spreadsheet. Any ideas? I hate having to close all my open spreadsheets when I want to open a spreadsheet that's been e-mailed to me! One of these usually works with a similar problem starting workbooks by double clicking them in Windows Explorer. Maybe one will work for you and your email problem. Tools|Options|General|Ignore other applications (uncheck it) --- or --- Close E...

Excel and some disappeared sheets
Anybody has the same or similar experiences : - I worked with excel file / closed as usually - new day opened and unfortunatelly some very important sheets disappeared / why ? Don=B4t know - I had about 7-9 sheets just now only 3 ones are there but not so important as another ones - the file has original size / I quickly looked into file by normal text editor only for reading and all relevant data are there, however if I open file by Excel, NO original sheets or data are there. - this file is VERY IMPORTANT for me - why happened ? Don=B4t know since I ask anybody for help Thanks in advan...

office 2000 [excel] cannot open previously OK file. Help
When trying to open a Microsoft Excel Work Sheet that I had accessed modified and saved to only yesterday [Office 2000] a window pops up with: "name.xls cannot be accessed. The file may be resd-only, or you may be trying toaccess a read only location. Or, the server the document is stored on may not be responding." Older files stored on the same mediums ;HD, Flash memory or 3.5in. diskette are accessed with no problem. This is on a network drive? ...

new sheet the cell doesn't let me type words & numbers
It was working before, now Excel dosen't let me type any words & numbers in the cell. The funtion manues are light color. Please help me. My computer version is home xp 2002 Cynthia Nip Is the worksheet protected? Tools|protection|unprotect sheet (you may need a password) excel general question wrote: > > It was working before, now Excel dosen't let me type any words & numbers in > the cell. The funtion manues are light color. Please help me. > My computer version is home xp 2002 > > Cynthia Nip -- Dave Peterson ...

Starting an Office Program (word, excel) in Outlook to send email
I'm taking an Outlook 2002 class. The latest assignment requires I open a Microsoft app in Outlook to send an email. Troubleshooting so far: Have tried to install some addl features from CD, restarted the pc (O/S WINXP) but still get error "Cannot complete action". Get no reason why Outlook cannot perform this action and the HELP hasn't only points me to inserting an object or some variation thereof. My Outlook is configured to use with AOL 9.0. Steps (abbreviated) given in textbook to perform: Actions New mail using Microsoft Office Also tried to perform from Wo...

Adding support for mfc / atl types in a Win32 project
MSDN says this about adding support for classes shared between atl & mfc (I'm just after points and rects btw)... just add the header ( atltypes.h ). I'm getting linker errors for these classes though. Is there a lib I need to add? Any screwy build options need changing? It's in VC 2003. In the project wizard I note that atl and mfc support aren't available when creating an empty Win32 application. Ta. On 26 May 2006 07:42:09 -0700, Simon L wrote: > MSDN says this about adding support for classes shared between atl & > mfc (I'm just after points and rect...

is there a way to program my Excel file to do a loop?
Hi all, If I want B10 to B17 all follow the change of the same number(copy cell), let's say I put it in A1, and C10 follows the change of A2(copy cell), and C11 follows the change of A3(copy cell), and C12 follows the change of A4(copy cell), then I have 4 variables in my calculations: A1, A2, A3, A4. I want to loop each of the variables in a different set, then I hope the whole worksheet will be able to refresh following the change of A1, A2, A3, A4, and then I want to find the very set of A1, A2, A3, A4 that gives the smallest value of D10, how do I program the whole procedure...

Error loading XML into DataSet
Hello, When I try to load this xml file (see below) into an asp.net dataset using the following code: ds.ReadXml("xmlfilepath\xmlfile.xml", XmlReadMode.Auto) I get the following error: "The same table (description) cannot be the child table in two nested relations. " If I take out the anchor tag in the description it works fine, however, I need the anchor tag in there. Does anyone have any suggestions? <?xml version="1.0" ?> <rss version="2.0"> <channel> <title>Liftoff News</title> <link>http://liftoff.msf...

Excel 2007 PC damages Mac Excel 2008 Files
Version: 2008 Operating System: Mac OS X 10.4 (Tiger) Processor: Intel When I open a Mac Excel 2008 file on Excel 2007 and I can work with the file, however when the file is returned to the Mac, it no longer works correctly, leading to a crash as soon as I try to save or eventually it might change the file to look like a bunch of letters and characters. Here is the error report: <br><br>Microsoft Error Reporting log version: 2.0 <br><br>Error Signature: <br> Exception: EXC_BAD_ACCESS <br> Date/Time: 2010-01-15 17:05:56 -0600 <br> Application N...

Excel. I am having a problem with hidden text
As I type text in some cells, it does not always dispaly if it exceeds the cell length. I wish to keep the cell lenghts for the entire document, but do wish for teh text in that particular row to be displayed. How about if you select that cell, then format|cells|alignment tab|check wrap text And with that row selected format|row|autofit SHR77 wrote: > > As I type text in some cells, it does not always dispaly if it exceeds the > cell length. I wish to keep the cell lenghts for the entire document, but do > wish for teh text in that particular row to be displayed. -- Da...

Excel Grid Lines in Outlook Preview Pane
Is there any way to send a spreadsheet to a mail recipient in the body of the message and not have the grid lines show up in the Outlook preview pane when the recipient views the e-mail? If you open the e-mail in Outlook, the grid lines disappear, but in the preview pane they are there. Using Excel and Outlook 2003. Uzytkownik "Kimberly" <Blackrose73@gmail.com> napisal w wiadomosci news:1143658600.180739.308310@v46g2000cwv.googlegroups.com... > Is there any way to send a spreadsheet to a mail recipient in the body > of the message and not have the grid lines show up ...

Run time error 5 : HQClient
When I double click HQ Client I have message box "Run time error 5 : Invalid procedure call or argument" how should I do? I used RMS 2.0 -- TOY2TOY ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/Businesssolutions/Community/NewsGr...

LNK2001 and LNK2019 errors with template class
This is a multi-part message in MIME format. ------=_NextPart_000_00BB_01C695ED.3958DA70 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable I have a smart pointer class that uses template functions that works = fine under VC6 but it not linking under VS2005. 1>TestGridToolkitView.obj : error LNK2019: unresolved external symbol = "void __cdecl PrismGCSmartPointer(class CPrismSmartPointer<class = CDialog> &)" = (?PrismGCSmartPointer@@YAXAAV?$CPrismSmartPointer@VCDialog@@@@@Z) = referenced in function "protected: __...

delivery failure error
"The e-mail account does not exist at the organization this message was sent to" then says user unknown, even though other comupters don't have that problem. I get this error for a few clients, yet other clients can email the same address. I have Windows small business server 2000, I am pretty sure everything has updated patches. I know i security updates automatically. My clients are an updated Windows XP SR 1 with Office 2003(outlook) SR1. ...

on Writexml error
(Type your message here) Hi all, I have a XML file as a datasource. I read that file using dataset.readxml.Then in that dataset I make some changes. Now I want to write that changed dataset by overwriting the file that was created initially. I someone please tell me how to do it. -------------------------------- From: ratnesh gupta ----------------------- Posted by a user from .NET 247 (http://www.dotnet247.com/) <Id>sCyOfoucUUyYsxAsNxg9AA==</Id> ...

Excel error 1706
Whenever I try to start excel on my computer it displays an error message and it shuts itself down, any other program from the office pack works properly, except for the one I mentioned. I reinstalled it and it does the exact same thing. Thanks in advance. ...

Error with Bold
What's wrong? With Worksheets("Recent") Range(Cells(1, 1), Cells(1, 2)).Font.FontStyle = "Bold" End with Take a look at the activesheet. I bet that range is bolded. But if you want Recent to be the sheet with the change, add a couple of dots. With Worksheets("Recent") .Range(.Cells(1, 1), .Cells(1, 2)).Font.FontStyle = "Bold" End With Without those dots, the range refers to the activesheet (if the code is in a general module). crapit wrote: > > What's wrong? > With Worksheets("Recent") > Range(Cells(1...

Oulook 2003 dial up sync errors
we are using outlook 2003 will all current updates insatlled and are using cashed exchange mode. our operating system is windows 2000 SP4 with all the current updates as well. when the user connects and hits f9 to sync we get the following error in the sync log (i have run detect and repair and removed and recreated the users outlook profile) any suggestions??: 18:57:44 Synchronizer Version 11.0.5604 18:57:44 Could not connect to public folder server. 18:57:44 [8004011D-526-80040115-0] 18:57:44 The Microsoft Exchange Server computer is not available. Either there are networ...