 |
 | 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 / March 2008VBA 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.  SignaturePosted via a free Usenet account from http://www.teranews.com 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. > Hello all, > The following line is in an Excel function: [quoted text clipped - 11 lines] > -- > Posted via a free Usenet account from http://www.teranews.com SignatureDave Peterson 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. > VBA has its own version of =find(). > [quoted text clipped - 27 lines] >> -- >> Posted via a free Usenet account from http://www.teranews.com SignaturePosted via a free Usenet account from http://www.teranews.com 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. > Dave, > Terrific, it worked with InStr. Many thanks. I have another question. [quoted text clipped - 35 lines] > -- > Posted via a free Usenet account from http://www.teranews.com SignatureDave Peterson Dave, Also, is there a standard reference work for VBA, one that is considered the "Bible"? TIA. > VBA has its own version of =find(). > [quoted text clipped - 27 lines] >> -- >> Posted via a free Usenet account from http://www.teranews.com SignaturePosted via a free Usenet account from http://www.teranews.com 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. > Dave, > Also, is there a standard reference work for VBA, one that is [quoted text clipped - 34 lines] > -- > Posted via a free Usenet account from http://www.teranews.com SignatureDave Peterson
|
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.