Find and Replace Question

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm using Visual Studio 2005 rather than VBA to write this program. I needed
to find and replace all "#N/A" with "". If there are no "#N/A" s in the
worksheet I get a message box from excel saying there was no data to
replace... what code would i need to close this message box without having to
do it manually? Thanks!
 
I don't use Visual Studio, but in VBA you could eliminate the messagebox:

On Error Resume Next
ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas, 16).Select
For Each c In Selection
If c.Text = "#N/A" Then c.Value = ""
Next c

Or did you wnat to see the messagebox for a few seconds and then have it
disappear automatically?

- David
 
Well this is what I have in VS:

On Error Resume Next
oApp.Range("A1:X16").Replace("#N/A", "", XlLookAt.xlPart,
XlSearchOrder.xlByRows, MatchCase:=False, SearchFormat:=False,
ReplaceFormat:=False)

oAppBk11.Save()
oAppBk11.Close()

oApp is the excel object and oAppBk11 is the workbook name.... it still
gives me the message box, any ideas?
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

loop and fine & replace 2
MS word Find and Replace using Regex - Help required 0
Find and Replace in VBA 3
Find and Replace Within a Range Versus Workbook - Suggestion 2
VBA to find filename and replace 2
MACRO: Multiple Find / Replace 3
Reset Find/Replace settings? 4
Excel wait for Find Replace dialog box in Word 4

Back
Top