VBA type mismatch error

Hello all,
	The following line is in an Excel function:

j = Application.Find(v, Cells(cnt, 1).Value)

When watched, v has a value of "s" with type variant/string. 
cells(cnt,1).value has a value of 2 with a type of variant/double.

I assume that the type mismatch occurs because the character "S" can't 
be found in an integer. How do I get this to work?

TIA. Pierre.

-- 
Posted via a free Usenet account from http://www.teranews.com

0
prf51 (6)
3/17/2008 2:41:32 AM
excel 39879 articles. 2 followers. Follow

5 Replies
613 Views

Similar Articles

[PageSpeed] 36

VBA has its own version of =find().

Take a look at VBAs help for InStr.

=find() will return an error if there isn't a match.

Dim j as Variant 'could be an error
j = application.Find(v, Cells(cnt, 1).Value)
if iserror(j) then
   msgbox "not there"
else
   'it's there
end if

may work for you, but InStr will be much easier.

Pierre Fichaud wrote:
> 
> Hello all,
>         The following line is in an Excel function:
> 
> j = Application.Find(v, Cells(cnt, 1).Value)
> 
> When watched, v has a value of "s" with type variant/string.
> cells(cnt,1).value has a value of 2 with a type of variant/double.
> 
> I assume that the type mismatch occurs because the character "S" can't
> be found in an integer. How do I get this to work?
> 
> TIA. Pierre.
> 
> --
> Posted via a free Usenet account from http://www.teranews.com

-- 

Dave Peterson
0
petersod (12005)
3/17/2008 1:50:55 AM
Dave,
	Terrific, it worked with InStr. Many thanks. I have another question. 
How does one force a cell to be text. Let's say the cell is 
cells(cnt,1). What is the appropriate VBA code? TIA. Pierre.

Dave Peterson wrote:
> VBA has its own version of =find().
> 
> Take a look at VBAs help for InStr.
> 
> =find() will return an error if there isn't a match.
> 
> Dim j as Variant 'could be an error
> j = application.Find(v, Cells(cnt, 1).Value)
> if iserror(j) then
>    msgbox "not there"
> else
>    'it's there
> end if
> 
> may work for you, but InStr will be much easier.
> 
> Pierre Fichaud wrote:
>> Hello all,
>>         The following line is in an Excel function:
>>
>> j = Application.Find(v, Cells(cnt, 1).Value)
>>
>> When watched, v has a value of "s" with type variant/string.
>> cells(cnt,1).value has a value of 2 with a type of variant/double.
>>
>> I assume that the type mismatch occurs because the character "S" can't
>> be found in an integer. How do I get this to work?
>>
>> TIA. Pierre.
>>
>> --
>> Posted via a free Usenet account from http://www.teranews.com
> 

-- 
Posted via a free Usenet account from http://www.teranews.com

0
prf51 (6)
3/17/2008 2:11:03 PM
Dave,
	Also, is there a standard reference work for VBA, one that is 
considered the "Bible"? TIA.

Dave Peterson wrote:
> VBA has its own version of =find().
> 
> Take a look at VBAs help for InStr.
> 
> =find() will return an error if there isn't a match.
> 
> Dim j as Variant 'could be an error
> j = application.Find(v, Cells(cnt, 1).Value)
> if iserror(j) then
>    msgbox "not there"
> else
>    'it's there
> end if
> 
> may work for you, but InStr will be much easier.
> 
> Pierre Fichaud wrote:
>> Hello all,
>>         The following line is in an Excel function:
>>
>> j = Application.Find(v, Cells(cnt, 1).Value)
>>
>> When watched, v has a value of "s" with type variant/string.
>> cells(cnt,1).value has a value of 2 with a type of variant/double.
>>
>> I assume that the type mismatch occurs because the character "S" can't
>> be found in an integer. How do I get this to work?
>>
>> TIA. Pierre.
>>
>> --
>> Posted via a free Usenet account from http://www.teranews.com
> 

-- 
Posted via a free Usenet account from http://www.teranews.com

0
prf51 (6)
3/17/2008 3:56:04 PM
You have a few choices.

You could give the cell a numberformat of text:

with activesheet.cells(cnt,1) 
    .numberformat = "@"
    .value = "00001234"
end with

Or you could precede your entry with an apostrophe:

with activesheet.cells(cnt,1)     
    .value = "'00001234"
    'or
    .value = "'" & format(1234,"00000000")
end with

Samething goes if you're doing data entry manually--preformat the range as text
or start with an apostrophe.

Pierre Fichaud wrote:
> 
> Dave,
>         Terrific, it worked with InStr. Many thanks. I have another question.
> How does one force a cell to be text. Let's say the cell is
> cells(cnt,1). What is the appropriate VBA code? TIA. Pierre.
> 
> Dave Peterson wrote:
> > VBA has its own version of =find().
> >
> > Take a look at VBAs help for InStr.
> >
> > =find() will return an error if there isn't a match.
> >
> > Dim j as Variant 'could be an error
> > j = application.Find(v, Cells(cnt, 1).Value)
> > if iserror(j) then
> >    msgbox "not there"
> > else
> >    'it's there
> > end if
> >
> > may work for you, but InStr will be much easier.
> >
> > Pierre Fichaud wrote:
> >> Hello all,
> >>         The following line is in an Excel function:
> >>
> >> j = Application.Find(v, Cells(cnt, 1).Value)
> >>
> >> When watched, v has a value of "s" with type variant/string.
> >> cells(cnt,1).value has a value of 2 with a type of variant/double.
> >>
> >> I assume that the type mismatch occurs because the character "S" can't
> >> be found in an integer. How do I get this to work?
> >>
> >> TIA. Pierre.
> >>
> >> --
> >> Posted via a free Usenet account from http://www.teranews.com
> >
> 
> --
> Posted via a free Usenet account from http://www.teranews.com

-- 

Dave Peterson
0
petersod (12005)
3/18/2008 12:25:03 PM
There are lots of good books.

Debra Dalgleish has a list of books at her site:
http://www.contextures.com/xlbooks.html

John Walkenbach's books are very good to start.

Pierre Fichaud wrote:
> 
> Dave,
>         Also, is there a standard reference work for VBA, one that is
> considered the "Bible"? TIA.
> 
> Dave Peterson wrote:
> > VBA has its own version of =find().
> >
> > Take a look at VBAs help for InStr.
> >
> > =find() will return an error if there isn't a match.
> >
> > Dim j as Variant 'could be an error
> > j = application.Find(v, Cells(cnt, 1).Value)
> > if iserror(j) then
> >    msgbox "not there"
> > else
> >    'it's there
> > end if
> >
> > may work for you, but InStr will be much easier.
> >
> > Pierre Fichaud wrote:
> >> Hello all,
> >>         The following line is in an Excel function:
> >>
> >> j = Application.Find(v, Cells(cnt, 1).Value)
> >>
> >> When watched, v has a value of "s" with type variant/string.
> >> cells(cnt,1).value has a value of 2 with a type of variant/double.
> >>
> >> I assume that the type mismatch occurs because the character "S" can't
> >> be found in an integer. How do I get this to work?
> >>
> >> TIA. Pierre.
> >>
> >> --
> >> Posted via a free Usenet account from http://www.teranews.com
> >
> 
> --
> Posted via a free Usenet account from http://www.teranews.com

-- 

Dave Peterson
0
petersod (12005)
3/18/2008 12:26:06 PM
Reply:

Similar Artilces:

Excel /vba / computer IP address
I need urgent help. How can I protect an excel file to a specific computer using VBA? (A excel file that can only be open or used in a specific computer) I was thinking in using the computer IP address, does somebody knows how can I get the computer IP, using visual basic in a excel worksheet? I would really appreciate you help Thanks (My email fcarvalho@portugalmail.com) -- jolipe ------------------------------------------------------------------------ jolipe's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=24024 View this thread: http://www.excelforum.c...

Errors in .pst file
I experienced the dreaded blue screen. After the computer re-started I could not open my Outloook, and received the error message, "Errors have been detected the the Outlook.pst file.l It further instructed me to use the Inbox repair tool (Scanpst.exe) to diagnose and repair errors in the file. I ran Scanpst.exe and it detects errors but freezes up when the 'repair' button is clicked. No repairs result. I found the Scanpst.exe in Program Files\Microsoft Office\Office 12. I am running Office 2007 Operating Sytem is XP Service Pack 2 (maybe 3) Is there somewhere I ...

using macro to save a file produces runtime error 4198
I upgraded from Win-XP/Office 2003 to Win-7/Office 2007; the code below ran fine in Word 2003 but in Word 2007 generates runtime error 4198. The variables and directory location are valid. The parameters for FileName are updated for Word 2007; the grammer for the FileName appears correct (no grammer errors when macro runs as is, but grammer errors if I change the string.) When I substitute an actual file name in place of the variables, the code runs w/o error, but the Debug.Print statements generate correct strings. I feel kind of dumb- but what is different about use of variab...

Attaching items to e mails
I have a user trying to attach items from SharePoint libraries to e mails in Outlook (2007). When she selects an item and tries to attach it she gets this message "The operation is not supported until the entire message is downloaded. Download the message and try again" which makes no sense in the context. She is using an XP machine but I cannot duplicate this error on either XP or Vista machines on the same network. No one else is having the same problem. Anybody know a possible cause (and a solution)? ...

Help-Error validating multiple documents
Hello. I've got a total of 5 schemas. They are: 1 - 1.xsd - a description of a city 2 - 2.xsd - a description of a town 3 - CityHall.xsd - a description of a cityhall-meant to be part of a document based on 1.xsd in "user area". 4 - TownHall.xsd - a description of a townhall-meant to be part of a document based on 2.xsd in "user area". 5 - testmultischema.xsd - a document which combines in a batch instances of cities and towns based upon 1.xsd and 2.xsd. I get the following xml validation error: ERROR: The 'http://adamExt.org:TownHall' element is not declar...

Trying begin VBA template project, get error "macros disabled"?
I am beginning a project to code and update a Word template using VBA. When I try to run the form given to me I get error "The macros in this project are disabled?" Any suggestions? Thank you. Save the template in your templates folder, which is a trusted location. -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP, originally posted via msnews.microsoft.com "The Applicant" <The Applicant@discussions.microsoft.com> wrote in message news:D2E642C...

Error when I try to save doc with equations in 2007
I am a math student and I frequently use Word 2007 to write up my assignments with the new equation feature. Lately I have been having trouble in that when I try to save it gives me a file error For example: "A file error has occured (c:\...\doc1.docx)" ...

Error Messages #6
I keep getting an error message that I can't seem to find an answer for...if someone has found this problem before and has fixed it...please let me know... Receiving' reported error (0x80042110) : 'Your POP3 server is providing inconsistent information. The number of messages returned by the STAT command does not match the number of items returned by the UIDL or LIST commands. Contact your POP3 or Internet service provider. thanks John And what did your ISP tell you when you contacted them? >-----Original Message----- >I keep getting an error message that I can'...

HTTP Error when accessing properties of Public Folders
Greetings.... First, thanks for the previous help I have received in this forum. ;) I will try to keep this brief, but afraid it may require more...........Exch2k Server with all service packs up to date. Moving accounts and Public Folder structure from one Exch2k server to another. Will be removing the older of the two from the domain when done. Right-click on the Public Folder to look at the properties of the folder to setup replication of the folder and get the following error message: The HTTP Service used by Public Folders is not available, possible causes are that Public Stor...

All Internal Passwords.xls Error 91
As with Nathan below, I ran this code on Office XP (XL) with XP Pro and received the same error. I loaded ONLY allinternalpasswords.xls and ran the Macro. Does another W/S have to be loaded? How does one apply this macro to a specific sheet? As Nathan Gutman & myself found: When trying to run it it gets to line: WinTag = .ProtectStructure Or .ProtectWindows and gives runtime error 91 Object variable or With block variable not set. Can someone help to undertand why and how to fix that? Dennis, You should open the workbook whose passwords you want to break. This should be...

Custom Fields/Formulas Receiving an error "NA" in Project Center
Hello All, Was wondering if I could get some help, we are running project server 2007 latest edition with all patches. And are running a custom formula. "IIf([Milestone]=Yes,"Milestone",IIf([PPC_Result]>=1,"100.00%",IIf([Baseline Start]=ProjDateValue("NA") Or [Baseline Finish]=ProjDateValue("NA") Or [Baseline Duration]=ProjDateValue("NA"),"No Baseline",IIf([Baseline Start]>Now(),"Baseline start in future",Format(([PPC_NUM]/[Baseline Duration]),"percent")))))" The formula runs off a ...

How do I get numbers on a colum to stay as I typed them on an exc.
I created a workbook with information to be used for merging to a letter. I created a heading colum to read "HOURS" and entered the numbers as 1:23. When I merged the files, the information showed at 1:23:00 A.M. How do I correct this on my workbook excel file or on the Word document? I do not want the numbers to be changed to time. Please help!!!!!! There's an article on the Microsoft web site that might help you: Answer Box: Numbers don't merge right in Word http://office.microsoft.com/en-ca/assistance/HA011164951033.aspx Desperate School Secretary...

OWA error #3
I have select Users receiving the following error when accessing OWA: HTTP/1.0 401 Unauthorized The error only occurs on a few Users. Any ideas? Thanks in advance. Try this: http://support.microsoft.com/?id=327843 -- Neil Hobson Exchange MVP For Exchange news, links and tips, check: http://www.msexchangeblog.com "Jeff" <anonymous@discussions.microsoft.com> wrote in message news:043e01c42482$46e0c2e0$a001280a@phx.gbl... > I have select Users receiving the following error when > accessing OWA: > HTTP/1.0 401 Unauthorized > > The error only occurs on a few ...

OpenDatabase Error
All, In my spreadsheet, I have a command button that calls code to create a recordset based on a SELECT statement which queries a table in Access. I have tried this in Excel 2007 and Excel 2010 and get the same problem. I have a reference to DAO 3.6 in my project. The following code snippet fails with a message box with a Red X in the middle, with NO TEXT describing the error, and an OK and Help button. The Help button gives me nothing. Can somone point me in the right direction on how to query Access for some data without importing the entire table (it is very large). Tha...

SNMP error
My domain controller keeps freezing at night. Now all i see is a event message from SNMP 1501 - The SNMP Service encountered an error while setting up the incoming transports.\n The IP transport has been dropped out. After this i begin to receive messages from Lost delayed Write data, till dhcp, wins, server and ntds errors. server finishes freezing and have to be hard reseted any ideas? server works at 100% at day when fully loaded... strange isn't it? "Rick" wrote: > My domain controller keeps freezing at night. Now all i see is a event > message ...

Error message on starting word
on starting up word I get the following message: "Some macros in this template will not run properly in this version of Word. Please contact your administrator or the macro vendor for an updated version of the template." Does any one have any ideas about what is causing this or even better how to fix it. You probably have installed an add-in for an earlier Word version that is incompatible with your current version. See http://www.gmayor.com/what_to_do_when_word_crashes.htm then http://word.mvps.org/FAQs/AppErrors/ProblemsStartingWord.htm or http://support.microso...

Hardware error cod. 11
hello to all, from some days I look these error in eventvwr windows: Tipo evento: Errore Origine evento: Disk Categoria evento: Nessuno ID evento: 11 Data: 14/11/2009 Ora: 9.04.31 Utente: N/D Computer: CAMILLO Descrizione: Il driver ha rilevato un errore del controller su \Device\Harddisk1\D. Per ulteriori informazioni, consultare la Guida in linea e supporto tecnico all'indirizzo http://go.microsoft.com/fwlink/events.asp. Dati: 0000: 04 01 68 00 01 00 b6 00 ..h...¶. 0008: 00 00 00 00 0b 00 04 c0 .......À 0010: 01 01 00 00 00 00 00 00 ........ 0018...

Hyperlink Error
When sending text in emails that has an inserted hyperlink to a web page anchor from Outlook 2007, it appears that users of Outlook 2003 only receive the link to the page, rather than to the page anchor. For example; an INSERTED hyperlink such as; http://www.bogus.com.au/ism/website/webpage.asp#page_anchor arrives as http://www.bogus.com.au/ism/website/webpage.asp Obviously, because it is inserted into the text in the email, the link itself is not visible to the end user. The idea here is to provide a link to a specific part of a web page, instead of landing at the top...

Dictionary Assembly Generator (DAG) Errors
Hi all, I added a field in the SOP Entry window using the Modifier.Now I want to access it via the VS Tools. According to documentation, I have to create an Application Assembly using the DAG tool. But I am having trouble with the Dictionary Assembly Generator. I am in a dos window, in the SDK folder and issuing this command : dag.exe 0 "C:\Program Files\Microsoft Dynamics\GP$GP10\Dynamics.set" /F This command returns the following error : "Error: Cannot find Application.MicrosoftDynamicsGp.dll. Generate Application.MicrosoftDynamicsGp.dll before generating Application....

Error on Saving appointments
Hi.. When I am creating a new appointment and saving it, I am getting the following error :- "The requested record was not found or you do not have sufficient permissions to view it. General failure in scheduling engine" Can someone please tell me why I am getting this error. Regards Rahul Submitted via EggHeadCafe - Software Developer Portal of Choice Break the Roles in SharePoint Lists http://www.eggheadcafe.com/tutorials/aspnet/c3ac8915-3861-4406-bec7-42f2d9110d79/break-the-roles-in-sharep.aspx ...

MS OUTLOOK OFFICE SBE 2003
Hi, I am not able to perform actions like ReplyAll, Updating a task item.. on my outlook 2003, am getting the following error - The messaging interface has returned an unknown error if the problem persists restart outlook. I have unistalled OFFICE SBE 2003 and reinstalled it. The problem persists. Anybody knows of a solution? Roger Bertrand EggHeadCafe - .NET Developer Portal of Choice http://www.eggheadcafe.com I often use my MS Outlook and almost every time I don't have any problems with my tool. But once I opened my software and noticed a very unpleasant thing,exacter some of my c...

Re-adding an old Tender Type
Thanks to an Accountant who setup my GL accounts incorrect, I need to re-import all of my RMS into a new Accounting program. My issues is that when I first used RMS, I had separate tenders for Visa and MasterCard. When I try to post all of the transactions, I get the "Transaction out of balance error" I figured why as there were transactions to the old MasterCard Tender prior to deleting it. Now I have re-entered it so the numbers will balance. After creating the tender again and assigning the GL to it, the posting still does not show it. Is this due to the way it was set...

Event ID 8270: LDAP error
Hi, I have successfully installed a new E2K3 server into an existing W2K/E2K environment. I kept on getting the following error in the Application Log on the new E2K3 server: *********************************************************** Event ID: 8270 , Source: MsExchangeAL LDAP returned the error [34] Unavailable when importing the transaction dn: <GUID=714227EF-1CEC-40C3-A958-A861AF721352> changetype: Modify member:add:<GUID=67D22146-1936-4770-8B5C-795B8799EB27> - DC=net,DC=mis,DC=ap *********************************************************** I could not find any article relat...

An error has occurred. For more information,contact your system ad
I just migrated our system from Goldmine to MsCRM. One problem is I can view activity details. When I select an activity item and double-click it, a dialog appeared and the error message is : "An error has occurred. For more information,contact your system administrator". If I manually create an activity, then I can open it correctly. I go to the CRM server and find an event: ========================== Source: Microsoft CRM Category:None User: N/A Error Message : An unhandled exception occurred during the execution of the current web request. Please review the stack trace for ...

how do i change the default number type which is formatted in new.
how do i change the default number type which is formatted in new spreadsheet Create a new workbook Change the Normal style (Format>Styles from the menu) to reflect your preferred number format. You can also change font style and size if your prefer. Save the workbook as a template - choosing File>Save As and select Template under the 'Save As Type' dropdown at the bottom, saving it with the name BOOK.XLT in the directory C:\Program Files\Microsoft Office\Office10\XLStart\ If you have an older or newer version, you may not see the Office10 subdirectory, so adjust as appr...