Find & replace with VBA
 

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!

Find & replace with VBA
 

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.

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...l_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.

--

Find & replace with VBA
 

Thanks jamie, it works great.

Sorry for the time delay, it is now 12:20 by me.

Les Stout

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