Find & replace with VBA

T

Tempy

Good day, i pleeeease need some help with the following:

(I am not a programmer so it may be a bit clumsy)

I am downloading a spreadsheet from SAP in wk1 format. The columns with
dates in, i format so that i can use the spreadsheet in Access; by using
the following code:

The original format is: 20040501

Range("H:H,L:L,M:M,T:T,X:X,AB:AB").Select
Selection.NumberFormat = "d-mmm-yy"

Columns("H:H").Select
Selection.TextToColumns Destination:=Range("H1"),
DataType:=xlFixedWidth, _
FieldInfo:=Array(0, 5), TrailingMinusNumbers:=True

It then looks like: 01-May-04, which is great.

However my problem is the fields that contain this:
00000000, this then becomes 01-Jan-00 and in the formula bar it looke
like this 1900/01/00.
I then need to remove this so i used the macro recorder and searched for
1900/01/00 and replaced with nothing, it worked but when i try and run
the code it won't work?
I then replaced the search criteria with 01-Jan-00 and it still does not
work ?

If i use the search function, manually, it finds 1900/01/00.

Any help is very welcome.

Les Stout

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!
 
J

Jamie Collins

Tempy wrote ...
I am downloading a spreadsheet from SAP in wk1 format. The columns with
dates in, i format so that i can use the spreadsheet in Access; by using
the following code:

The original format is: 20040501

Range("H:H,L:L,M:M,T:T,X:X,AB:AB").Select
Selection.NumberFormat = "d-mmm-yy"

Columns("H:H").Select
Selection.TextToColumns Destination:=Range("H1"),
DataType:=xlFixedWidth, _
FieldInfo:=Array(0, 5), TrailingMinusNumbers:=True

It then looks like: 01-May-04, which is great.

However my problem is the fields that contain this:
00000000, this then becomes 01-Jan-00 and in the formula bar it looke
like this 1900/01/00.
Click to expand...

Try

.NumberFormat = "d-mmm-yy;;"

Because you are using the data elsewhere, I wouldn't recommend
replacing the zeros with empty string. This could cause an undesirable
mixed types situation (see
http://www.dicks-blog.com/excel/2004/06/external_data_m.html). You
will have to handle the situation where the date is zero which is a
valid date value. BTW note that with the Jet provider the following
query:

SELECT DAY(0), MONTH(0), YEAR(0)

returns 30, 12, 1899 respectively.

Jamie.

--
 
Top