dcsimg


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 12 of 12

Thread: Replace function for excel

  1. #1
    Join Date
    Nov 2009
    Posts
    6

    Thumbs down Replace function for excel

    Hi there..

    I've just finished to write my code in VBA, and now moving it to VB for compilation..
    But I have a problem...
    My code should look in the selected active sheet, and do a find&replace for an in put string..
    For some reason I keep getting an error..

    This is where I get the error :
    xlsCell.Replace(strOldName, strNewName)

    Anyone ?? HELP !!

    Thanks alot !

  2. #2
    Join Date
    Nov 2003
    Location
    Portland, OR
    Posts
    8,387
    What does the error message say?
    Phil Weber
    http://www.philweber.com

    Please post questions to the forums, where others may benefit.
    I do not offer free assistance by e-mail. Thank you!

  3. #3
    Join Date
    Nov 2009
    Posts
    6

    Wrong syntax..

    Wrong syntax..

  4. #4
    Join Date
    Nov 2003
    Location
    Portland, OR
    Posts
    8,387
    Try it without the parentheses:

    xlsCell.Replace strOldName, strNewName
    Phil Weber
    http://www.philweber.com

    Please post questions to the forums, where others may benefit.
    I do not offer free assistance by e-mail. Thank you!

  5. #5
    Join Date
    Nov 2009
    Posts
    6

    here it is

    attached the error
    Attached Images Attached Images

  6. #6
    Join Date
    Apr 2007
    Location
    Sterling Heights, Michigan
    Posts
    8,663
    The replace function requires you pass it 4 parameters.

    You are only passing it two.

    That is what "Number of parameters passed does not match the expected number" means.

    To use replace you need:
    Old Text
    Where in the old text to start the replacement - this is an integer
    How many characters in the old text should be replaced - this is an integer
    New Text

  7. #7
    Join Date
    Nov 2009
    Posts
    6

    still same problem

    I'm using Visual Studio...
    Maybe that's the reason ?
    It is not mandatory in Visual Studio to give anything more then old string and the new string to be replaced...

    Wrong forum ?

  8. #8
    Join Date
    Apr 2007
    Location
    Sterling Heights, Michigan
    Posts
    8,663
    You may be using Visual Studio, but the code you are writing is Excel VBA and the Replace Function you are using is the Excel Replace function, not the VB.NET Replace Function.

    As such, it requires 4 parameters. If you were using the VB.NET Replace function, it would not have given you that error.

  9. #9
    Join Date
    Nov 2009
    Posts
    6

    OK.. So can I do that ?

    This is the original line i've created in Excel VBA.. But couldn't "move" it to Visual Studio..


    Can you help me to know how should it be in Visual Studio ?
    Code:
        Cells.Replace What:=srcName, Replacement:= _
            dstName, LookAt:=xlWhole, SearchOrder:=xlByRows, _
            MatchCase:=False

    THANKS !!!
    Last edited by mojo2405; 11-11-2009 at 10:35 AM.

  10. #10
    Join Date
    Nov 2009
    Posts
    6
    Anyone ?

  11. #11
    Join Date
    Mar 2005
    Location
    Los Angeles, Calif. AKA: Gangsta Yoda™
    Posts
    455
    Ok I see a few things off a bit.

    1. Your screenshot shows you using the variable "Cell" while your posted code shows "Cells".

    2. "Cells" is a reserved word as its a collection of cells and should be avoided in variable naming. This "could" be causing an ambiguity.

    3. "Cell"- Same as #2 but not a collection.

    4. Are you using Option Explicit On?

    5. With the use of either reserved word the error may be coming from the compiler thinking you are using the reserved word so no parameters or different numbers are expected.


    Solution:
    1. Rename your variables
    2. Turn Option Explicit On
    3. Try something like labeling your arguments like below...

    Code:
    Dim oCell As Excel.Range
    '...
    '...
    For Each oCell In xlsCell
        If Not oCell.Value Is Nothing Then
            If oCell.Value.ToString = strWhatever Then
                oCell.Replace(What:="Source String", Replacement:="String to replace with", _
                LookAt:=Microsoft.Office.Interop.Excel.XlLookAt.xlWhole, _
                SearchOrder:=Microsoft.Office.Interop.Excel.XlSearchOrder.xlByRows, _
                MatchCase:=False)
                '...
    Last edited by RobDog888; 11-23-2009 at 04:45 AM.
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer code questions via PMs.
    Microsoft MVP 2006-2009
    Office Development FAQ (VBA, VB6, VB.NET, C#)
    Software Engineer MCP (VB6 & .NET)
    Reps & Rating PostsVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6Outlook Global Address List

  12. #12
    Join Date
    Dec 2009
    Posts
    1
    Really a great help...Thanks a lot for this..

    Regards

    Daevone

Similar Threads

  1. REPLACE function with '
    By Jonas in forum Database
    Replies: 3
    Last Post: 12-12-2002, 06:05 AM
  2. replace function
    By dennis in forum Database
    Replies: 1
    Last Post: 11-12-2002, 03:42 PM
  3. How to use Replace() function?
    By Bassel Tabbah in forum VB Classic
    Replies: 4
    Last Post: 08-15-2001, 07:33 AM
  4. verify local admin
    By Patrick Comeau in forum VB Classic
    Replies: 6
    Last Post: 03-22-2001, 11:50 PM
  5. Trying to print a PDF File from VB
    By Kunal Sharma in forum VB Classic
    Replies: 2
    Last Post: 04-25-2000, 03:45 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center