Populating Worksheet in VBA

[AlexanderBB]

Struck this a few years ago and can't remember what fixed it.

I'm clearing a sheet by Worksheets("Sheet1").Cells.ClearContents then filling parts of it with .Cells(row, column)=
Datatypes are mixed and what shows in the worksheet does not always follow, e.g. "1" may appear as 00 Jan 00.

Is there a way to set up the worksheet so whatever the datatypes are, keep their format?
Or do I need to set every individual cell format prior to assigning a value?

 

[JLGWhiz]

Struck this a few years ago and can't remember what fixed it.

I'm clearing a sheet by Worksheets("Sheet1").Cells.ClearContents then filling parts of it with .Cells(row, column)=
Datatypes are mixed and what shows in the worksheet does not always follow, e.g. "1" may appear as 00 Jan 00.

Is there a way to set up the worksheet so whatever the datatypes are, keep their format?
Or do I need to set every individual cell format prior to assigning a value?

When you clear the contents, include:

Cells.NumerFormat = "General"

Or you can just do it manually by clicking in the box above the row number "1" which will select all the cells, right click, "Format Cells", "Number Format", "General", "OK"

Excel has some built in features that automatically convert cell number formats to dates if a certain type entry is made into it, and it does not change back unless the user changes the number format or closes the file without saving it.

 

[AlexanderBB]

Thanks very much... all sorted

 

[JLGWhiz]

Thanks very much... all sorted

You're welcome,
regards, JLG

 

[RoryA]

If you use Worksheets("Sheet1").Cells.Clear instead, it would reset all the formatting to the default Normal cell style as well.

 

[AlexanderBB]

Thanks Rory, have changed the code. One command instead of two, nice.