Find & Replace

[pl456]

I have a problem with a macro that finds a date and replaces it with nothing leaving an empty cell.

When I record the macro it works fine, however when I run it again on new data it doesnt find the date, even though I can clearly see the date exists.

Another strange thing is if I select the cell an click on the data in the formula bar then run the macro it finds and replaces the date.

Does anbody have any idea how to resolve this?

 

[chergh]

You need to tell us a bit more.

Whats the criteria for finding the date? Could you also post the code generated by recording the macro?

 

[pl456]

The code is below.

Cells.Replace What:="00/01/1900", Replacement:="", LookAt:=xlWhole, _
SearchOrder:=xlByColumns, MatchCase:=False

 

[chergh]

Do you want to replace specific dates or all dates?

 

[chergh]

Anyway the problem here is a type mismatch.

What:="00/01/1900"

The above is looking for a string dates are numbers.

Replace what you have with:

What:=#00/01/1900#

 

[pl456]

It is just this date, the data is extracted from a database and the value 00/01/1900 are from fields that have not been filled in.

I have put the # # like you said but this now returns a syntax error.

 

[chergh]

Hmm this one seems to be a bit of a bitch actually.

Only thing I can think to do is :

thisworkbook.worksheets("SheetName").Colums("Column with dates in it").numberformat = "@"

Cells.Replace What:="00/01/1900", Replacement:="", LookAt:=xlWhole, _
SearchOrder:=xlByColumns, MatchCase:=False

thisworkbook.worksheets("SheetName").Colums("Column with dates in it").numberformat = "mm/dd/yyyy;@"

 

[pl456]

The code does not get around the problem.

I initially thought is was something to do with the format but tried different settings but no joy.
I also tried setting validation on the cells so that date could not be accepted but it is (for some reasons) when the data is first extracted from the database.

Completely confused

 

[chergh]

The code worked when I mocked up a spreadsheet with your problem date.

Are you getting an error message? Have you changed the sheetname to reflect the actual sheetname? There is also a typo, colums should be columns.

 

[pl456]

Sorry, I had clocked the typo , the code runs fine it just can not find the date.

 

[chergh]

Well I'm out of ideas now.

 

[pl456]

OK, I have stuck a formula in the next column along.
The IF logic test which I have set to return the value of the date cells in my original column when true and when false return a blank string.

This appears to work leaving the cells in the new column that should have had 00/01/1900 empty.

What do you think this means???????

It is like the value doesn't actually exists, it is visible but......

 

[shades]

What formula are you using?
________
Colorado marijuana dispensaries

 

[pl456]

=IF(H2,H2,"")

Yes, I don't actually understand whats happening but it appears to omit the data I don't need.

 

[shades]

Should it be this?

=IF(H2="00/01/1900","",H2)
________
VERMONT MEDICAL MARIJUANA

 

[pl456]

This is the probelm.

You are correct but that doesn't work. What this has done is put identical values in, including 00/01/1900.

 

[shades]

Can you post a sample of the workbook?
________
Nevada medical marijuana dispensaries

 

[pl456]

attahed example

 

[Brianwarnock]

You did not supply the macro, but anyway look for 0 not 00/01/1900

in Col formatted for date =if(f2=0,"",F2) will give you what you want, testing Column F in your spreadsheet.

Brian

 

[pl456]

Thanks, this works.

If anybody out there wants to add a reason why it thinks the cell has no data it would be appreciated.
Perhaps sometimes we just have to except things are the way they are.

Thanks everyone for chipping in your ideas, much appreciated.