?
Solved

Find and Replace with Excel VBA

Posted on 2003-02-20
12
Medium Priority
?
471 Views
Last Modified: 2010-05-18
I am reading in data from a csv file into a worksheet, the file is composed of name, and a number of tests, however some people have never attended a test therefore there is a blank there, I need a way of finding and replacing the blanks and replacing them with a value AB.  How would I go about doing this? with the following section of code:

Sub Find_Replace()
'
' Find_Replace Macro
' Macro recorded 20/02/2003 by Natalie Vance
'

'
    Cells.Replace What:="lyndsay", Replacement:="ab", LookAt:=xlPart, _
        SearchOrder:=xlByColumns, MatchCase:=True
End Sub

However, I am reading the data from the csv file into a worksheet using the following code:

Public Sub Open_Workbook(OpenName As String)
  Dim oCSV As Workbook
  Open OpenName For Input As #1
 
 Set oCSV = Workbooks.Open(OpenName)
 oCSV.Sheets(1).Copy ThisWorkbook.Worksheets(1)
   
 oCSV.Close False
         
 Find_replace()

 
 Set oCSV = Nothing
 Close #1
End Sub


 So I ideally I would like to be able to call the Find_Replace function within the open_workbook function.  Is this possible? Would I need to make the Find_Replace function public?

I would appreciate any help or suggestions.

Thanks in advance,
natalie
0
Comment
Question by:NatalieVance
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
12 Comments
 
LVL 16

Expert Comment

by:twalgrave
ID: 7987452
Why bother putting it into the workbook?  If they are going to run your code from VB anyway, why not just put the code in your VB App?

Public Sub Open_Workbook(OpenName As String)
 Dim oCSV As Workbook
 Open OpenName For Input As #1

Set oCSV = Workbooks.Open(OpenName)
oCSV.Sheets(1).Copy ThisWorkbook.Worksheets(1)
oCSV.Cells.Replace What:="lyndsay", Replacement:="ab", LookAt:=xlPart, _
       SearchOrder:=xlByColumns, MatchCase:=True
   
oCSV.Close False
       
Find_replace()


Set oCSV = Nothing
Close #1
End Sub
0
 
LVL 14

Expert Comment

by:Tommy Kinard
ID: 7988500
Excel.Application.ExecuteExcel4Macro("Find_replace")

Not sure how you have it setup but this should run your macro.
0
 

Author Comment

by:NatalieVance
ID: 7989295
In response to twalgraves comments, the code which you inserted: oCSV.Cells.Replace What:="lyndsay", Replacement:="ab", LookAt:=xlPart, _
      SearchOrder:=xlByColumns, MatchCase:=True
Wont work for me as I got a runtime error "438" claiming the system doesnt support this object or method.  

The code which I have within the find_replace sub works fine however, all I need to know is how to find blank spaces i.e instead of Replace What="lyndsay" I want it to find blank cells and replace them with "ab", I only used the word lyndsay as an example.  

In addition, as the code in the find replace function works, I just need to know how I would be able to invoke it  within the open workbook function because when I try to invoke the find_replace function within the openWorkbook function it I get a syntax error.

Overall, I dont really mind were the actual code for finding and replacing is located, as long as the process of finding and replacing the blank spaces is kept hidden from the user.  So if I could get the code working in the find_replace function or in the open workbook function that would be great!

I hope I have made myself a little clearer, sorry for any confusion, and I appreciate your comments.

Natalie



0
Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

 

Author Comment

by:NatalieVance
ID: 7989356
In response to DragonTooths comments, I am working with Excel 2000(may not make a difference).  I tried that code which thankfully doesnt produce errors however the find replace function doesnt actually do anything at all! Strange..Sometimes I wonder why I bother with computers!!

Anyway, you mentioned about setup, the open_workbook function or sub is public, the macro find_replace has just been recorded using the inbuilt excel macro recorder.  The reason I know that find_replace works is that I linked the macro to a button on the worksheet.  
However as the whole process is to be hidden from the user, I would like to invoke the macro myself through the open_workbook function.

Thank you, I appreciate any comments.

natalie
0
 
LVL 14

Expert Comment

by:Tommy Kinard
ID: 8009239
I could not get the replace function to work, so your one up on me. But to get the below code to work you must have it saved in the workbook. So save a "startup" spreadsheet, when opening the sheet the macro will run and then saveas or don't save, to keep the sheet with the macro. I do not think this is the best way, but it is  start.
Private Sub Workbook_Open()
    Dim c As Object
    Set c = Cells
'you will need to replace the A1:A20 to the range or column  that fits your requirements
    For Each c In [A1:A20]
    If c.Value = vbNullString Then
        c.Value = "AB"
    End If
    Next
End Sub
0
 

Author Comment

by:NatalieVance
ID: 8018753
In response to DragonTooth's comments, thankyou for responding, I really appreciate your help.  However, the csv file which my application will be accepting will be read into worksheet called "View All Data" and the worksheet is created as the file is read, so in this case so where would I save this in this instance?

In addition, as the process of accepting the file is automated I don't know in advance what the range will be because the file could be of any size, is there any way of querying what range is being used ?

Regards,
Natalie

P.S
0
 

Author Comment

by:NatalieVance
ID: 8040839
Hi, I have tried to modify the code which DragonTooth suggested as I cannot specify the range as I am unaware of the range as it is read in from a file, as below:

Sub AmendedDragonToothComments()

   Dim Sheet As Worksheet
 
   For Each Sheet In Worksheets
 If IsEmpty(Cells.Value) = True Then ' Line 4
       Sheet.Cells.Value = "AB"
   End If
   Next
End Sub

I have also tried to use this piece of code also instead of line 4, i.e. If Cells.Value = vbNullString Then

However, with both of these pieces of code, a run time error "7", In debug mode it states that Cells.Value = <Out of Memory>.

Is there any other way which I can find a blank cell and replace it without explicitly specifying a range ?

Regards, Natalie  
0
 
LVL 14

Expert Comment

by:Tommy Kinard
ID: 8044385
The below code works. BUT it takes about 3 seconds per row to fill in. This in my book is not going to get it. You could limit te number of rows or columns. I would start with a 10000 limit on the rows and columns. I don't know who in there right mind would take 10,000 tests ;). But on the other hand there could be 65536 people taking test. Is a csv file a comma delimited file? Let me know if I can be of more help.

Sub AmendedDragonToothComments()
  Dim Sheet As excel.Worksheet
  Dim RowMax As Long
  Dim ColMax As Long
  Dim colcnt As Long
  Dim rowcnt As Long
  For Each Sheet In Xcel.Worksheets 'xcel.worksheets is a global active instance of excel with the workbook already open
        RowMax = Sheet.Rows.Count '65536 is the maximum
        ColMax = Sheet.Columns.Count '65536 is the maximum
        For rowcnt = 1 To RowMax
            For colcnt = 1 To ColMax
                If IsEmpty(Sheet.Cells(rowcnt, colcnt).Value) Then ' Line 4
                    Sheet.Cells(rowcnt, colcnt).Value = "AB"
                End If
            Next
        Next
  Next
End Sub
0
 

Author Comment

by:NatalieVance
ID: 8047332
Hi Dragon Tooth,
Well the csv file is a comma delimited file.  I would say that the amount of people taking tests would not exceed 500 nor would the amount of tests taking place exceed 500.  

however, when I try and run the function detailed above, I get a Run Time Error 424, Object required and it highlights the line of code:
 For Each Sheet In Xcel.Worksheets

If the mouse is held over the word sheet, it states Sheet=Nothing.  Do you think this in any way linked to the function which I am calling it from i.e. OpenWorkbook?

I appreciate any help or suggestions,
Many thanks, Natalie
0
 
LVL 14

Accepted Solution

by:
Tommy Kinard earned 150 total points
ID: 8057578
Public Xcel As Object
sub OpenXcelWrksheet
   Dim jobsheet As String
   Set Xcel = CreateObject("Excel.Application")
   jobsheet = "C:\My Documents\" & JobNumber & ".xls"
   Set Xcel = GetObject(jobsheet)
end sub

You will need to change the varaible names to match your app but this will get the error 424 to go away.

If you are only working on 1 sheet you would not have to go through each sheet just pass the sheet (oCSV.Sheets(1)?) you want to process.

0
 

Author Comment

by:NatalieVance
ID: 8060464
Many thanks for all your help and suggestions.

Natalie
0
 
LVL 14

Expert Comment

by:Tommy Kinard
ID: 8067131
This will process your csv file for ",," and replace with ",AB,". This will elliminate the need for the number of columns or rows and searching each cell for IsEmpty. The file that is imported will have no blanks or null values. It is also faster than cycling through each cell. This is an approach I feel is better, less headaches, less maintenance.

Public Sub Open_Workbook(OpenName As String)
 Dim oCSV As Workbook
 Dim NewName as string
 Dim InStrng as string
 Dim OutStrng as string
 Dim FileId as integer
 NewName = "Hld.csv"
 Open OpenName For Input As #1
 FileId = FreeFile
 Open NewName For Output As #FileId
 While Not EOF(1)
   Line Input #1, InStrng
   OutStrng = Replace (InStrng,",,",",AB,")
   Print #FileId, OutStrng
 Wend
 Close #1
 Close #2
 FileCopy NewName OpenName
 Kill NewName
 Set oCSV = Workbooks.Open(OpenName)
 oCSV.Sheets(1).Copy ThisWorkbook.Worksheets(1)
 oCSV.Close False
 Set oCSV = Nothing
 Close #1
End Sub
0

Featured Post

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Whether you’re a college noob or a soon-to-be pro, these tips are sure to help you in your journey to becoming a programming ninja and stand out from the crowd.
Today, the web development industry is booming, and many people consider it to be their vocation. The question you may be asking yourself is – how do I become a web developer?
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …

801 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question