Solved: Find and Replace not working

[Vwhee]

Hi

As part of a larger macro I did a simple macro record of a find and replace which worked perfectly as I did it from within the spreadsheet but as soon as I tried to run it as a macro from the code that Excel wrote itself it wouldn't work. I can't see why not as I have not altered the code.

I'm on OS Windows 2003 Enterprise Edition and running Excel 2007. This is the simple code

  Sub Replace()
  range("H310:O345").Select
          Selection.Replace What:="00/01/1900", Replacement:="", LookAt:=xlWhole, _
          SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=True, _
          ReplaceFormat:=False
End sub

Does anyone have any ideas? It has my colleague (a seasoned VBA writer) and myself (newbie) completely stumped

Thanks Vwhee

[Tinbendr]

Does anyone have any ideas?

Where is the code? In the sheet code page, or in a module?

If it's in the module, you'll probably need to specify which sheet it applies to.

[VBA]
WorkSheets("MySheet").Range("H310:O345").Select
[/VBA]

[Paul_Hossler]

it wouldn't work.

How not exactly?

Paul

[Vwhee]

Tinbendr - It is in a module. I had tried pointing to the sheet too and have just tried again but it makes no difference.

Paul - it just does nothing - it highlights the range and that's it. I have put a message box in code after it and it comes up to say it's complete but it hasn't replaced the detail in any of the cells.
If I run the code through the debug it doesn't fall over on any of the lines. It just doesn't do the job.
If I run find and replace from the worksheet using the same parameters it works fine.
As I say, I just recorded the macro directly through excel and that is the code that it came up with and (apart from trying pointing to the sheet and adding a msgbox I haven't altered the code at all)

It has me completely stumped!

Thanks for trying to help

Vwhee

[Teeroy]

Check whether the value in the cell has a space on the front or tail end of the string. An easy way to test for this is to change LookAt:=xlWhole to LookAt:=xlPart and see if you get the find and replace to work.

It sounds obvious but it can be painful to track down this sort of error (I found this out the hard way ).

[Vwhee]

Hi Teeroy,
Good suggestion - but I tried changing that bit of the code to Part but it still doesn't work and there shouldn't be any extra spaces because the date 00/01/1900 has been put in by excel because the cell has been multiplied by 1 so effectively 0x1 in a date formatted cell so excel has put that in all my empty cells.

I've tried replacing 00/01/1900 with just 0 as that is effectively how excel sees that cell but that doesn't work either. Of course if I put 0 in the standard find and replace from the worksheet it strips the 0s out of all the dates in that range so that they're not dates anymore

Thanks

VWhee

[Teeroy]

OK, the extra information was quite helpful. A dateserial of 0 does seems to be a valid date but doesn't seem to work with all date functions so I'm not sure this can be done with a find and replace.

There are two other ways to handle it though;
1.Use an IF statement in the worksheet e.g. =IF(B2<>0,B2,"").
2. By VBA code;

[vba]
For Each cell In Selection
If IsDate(cell.Value) And cell.Value = 0 Then cell.Value = ""
Next cell
[/vba]
I hope this helps you.

[GTO]

Could you post a sample workbook that fills the cells similarly (and the tried code) in .xls format?

[Teeroy]

Could you post a sample workbook that fills the cells similarly (and the tried code) in .xls format?

Hi GTO this simple sample was where I tried the fix to prove it works (if that's what you were after). Remember though it only affects Date formatted cells so in the test workbook the first 2 cells will change but not the third. This was intentional but if Vwhee doesn't care and wants any 0 cell Blanked then he can simply remove the first part of the IF statement.

[Aflatoon]

Try:
[vba]Sub Replace()
range("H310:O345").Replace What:="00/01/1900", Replacement:="", LookAt:=xlWhole, Lookin:=xlFormulas, _
SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=True, _
ReplaceFormat:=False
End sub[/vba]

[Vwhee]

Hi everyone,

I've tried Teeroy's
If IsDate(cell.Value) And cell.Value = 0 Then cell.Value = "" solution
which does work but unfortunately my actual range is 8 columns and about 15000 rows so this is REALLY slow. I definitely want a macro solution rather than an "in workbook" solution - it's part of a massive workbook with many sheets and multiple different macros although could I use the IF statement in conditional formatting in Excel 2007 now?

Aflatoon - I'm about to leave work so I'll give your solution a go tomorrow.

Thanks for your help everyone.

Vwhee

[Tinbendr]

Are you trying to delete zero dates?

If so, then give this a try.

[vba]Sub DeleteZeroDates()
Dim WB As Workbook
Dim WS As Worksheet
Dim LastRow As Long
Dim Rng As Range
Dim aCell As Range
Set WB = ActiveWorkbook
Set WS = WB.Worksheets(1)
With WS
LastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
End With
'Change to suit.
Set Rng = WS.Range("A1:H" & LastRow)
For Each aCell In Rng
With aCell
If IsDate(.Value) Then
If .Value = 0 Then
.NumberFormat = "General"
.Clear
End If
End If
End With
Next
End Sub[/vba]

[Vwhee]

Hi everyone

Aflatoon - I gave your code a go and the debug picks the code up at LookIn:= and gives me a Compile error: Named argument not found. Do I need to refer to something else at the beginning of the coding?

Tinbendr - I gave your code a go and again it did nothing, much like my original code. The debug tool runs through all the lines fine but doesn't make any changes on the spreadsheet.

Thanks for your help

VWhee

[Vwhee]

Hi everyone

I thought I'd attach an example spreadsheet of the problem. This is the format that I have the cells in and the macro code that I've been working with.

Thanks

Vwhee

[Aflatoon]

Sorry that was dumb. Lookin only applies to a Find.
Use:
[vba]Sub Replace_0dates()
Dim sFormat As String
With Worksheets("Dates by IP (P)").Range("A1:H14")
sFormat = .NumberFormat
.NumberFormat = "General"
.Replace What:=0, Replacement:="", LookAt:=xlWhole, _
SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
.NumberFormat = sFormat
End With
MsgBox "Complete"
End Sub
[/vba]

[Vwhee]

Hi Aflatoon - you're a genius :-) that works perfectly. I don't suppose you could take a further few minutes out of your day to explain why it works? I only started learning VBA about a month ago so a very steep learning curve at the moment!

Thanks for everyone's help

[Aflatoon]

I'll try but this is a fuzzy area in my opinion. Find tends to operate in two modes:
1. If you specify to look in Values, it looks at the displayed values as formatted rather than necessarily their actual values.

2. If you specify to look in Formulas, it looks at the formula (i.e. what you see in the formula bar) rather than at the displayed values. Of course, if your values are the result of formulas, this won't work as it will look at the formula itself.

Replace always looks in formulas since you can't replace the value result of a formula.

The grey area is VBA which always works in US formats unless you can persuade it otherwise. So to replace say 02/07/2012 in your example file, you have to convert it to an actual date value using DateValue("02/07/2012").

Unfortunately in your case datevalue will not accept "00/01/1900" as a date, so my workaround was to store the current formatting, convert everything to general so all the dates show as numbers (even in the formula bar) and then replace the 0.

Make any sense? (I rarely do)

[Vwhee]

Which is why all the numbers temporarily went to their "general - so many days from date 0" format before converting back to the format I was actually after.

That does make sense - thanks very much for your help and for the patient explanation. I'm determined to understand this coding thing eventually

[Tinbendr]

Thanks for the sample.

In my code I changed [vba] If .Value = "12:00:00 AM" Then
.NumberFormat = "General"
.Value = ""
[/vba]and it works.

Aflatoon
Nice work. Definitely gonna snip this one and stick it in the 'dates' page of my ref book.

[Vwhee]

Hi Tinbendr,

I couldn't get this to work either - sorry I must be REALLY thick.

Vwhee :-(