Formatting issue with constructing an array.

[GAJITCS]

I have the code below, written by someone else and face an issue we have not seen before.

ReDim b(1 To UBound(a) * UBound(a, 2), 1 To 5)
For i = 2 To UBound(a, 1)
ant = a(i, 2)
ini = 0.01
For j = 2 To UBound(a, 2)
If ant <> a(i, j) Then
If ant <> "POA" Then
k = k + 1
b(k, 1) = a(i, 1)
b(k, 3) = ini
b(k, 4) = a(1, j - 1)
b(k, 5) = ant
End If
ini = a(1, j - 1) + 0.01
End If
ant = a(i, j)
Next j
Next i

The issue occurs when writing b(k 3) = ini to the array after the For statement has looped a number of times and the value of ini is incremented adding the + 0.01
There are ocassions where ini had a whole number value, which is then increased by 0.01 and becomes 11.01 or 12.01.
My issue is that while during the building of the array, the ini value shows correctly, when written to a worksheet, 11.01 and 12.01 are converted to a date format.

Can I prevent this from happening?

 

[Wookiee]

If you format the range before writing the array to it, it will display correctly.

This is part of a macro I use frequently which writes cells from a sheet into an array and then pastes it somewhere. This is where it evaluates whether the user wants their data written into text-formatted cells and updates the format before writing to the sheet:

If bText Then rngData.NumberFormat = "@"
rngData.Value = arrReturnData

 

[GAJITCS]

If you format the range before writing the array to it, it will display correctly.

This is part of a macro I use frequently which writes cells from a sheet into an array and then pastes it somewhere. This is where it evaluates whether the user wants their data written into text-formatted cells and updates the format before writing to the sheet:

If bText Then rngData.NumberFormat = "@"
rngData.Value = arrReturnData

Wookiee,
Many thanks. I am not great with VBA.
can I ask the silly questions please.

What are bText, rngdata and arrreturndata?

 

[Wookiee]

Wookiee,
Many thanks. I am not great with VBA.
can I ask the silly questions please.

What are bText, rngdata and arrreturndata?

Not a silly question; I apologize. I should have clarified what my variables represented.

bText is a boolean variable (if it's TRUE, then the macro makes the range format TEXT)*
rngData is the range of cells where the array will be "pasted"
arrReturnData is the variant (array) which gets written to the cells

*come to think of it, I probably should have just removed the boolean from the code

Here's a better version suited to your stated format preference:

rngData.NumberFormat = "0.00"
rngData.Value = arrReturnData

 

[GAJITCS]

Thank you.
Understanding your code and adapting slightly to be consistent with the style that the existing VBA has been constructed, I ended up with this.

Sheets("R_M").Range("C:C").NumberFormat = "0.00"
Sheets("R_M").Range("A2").Resize(k, 5).Value = b

With b being my array return data.
And indeed, this does change for formatting of column C to Numberm but where the values are 11.01 and 12.01 still as the array is compiled, they still convert to date numeric values (44958.00 and 44927.00)
Do I need to, and indeed, is it possible, to apply formatting to the array as it is compiled?

 

[GAJITCS]

Hold fire on that... Looks like I may have been corrupting the formatting further down in the code.

Could well be operator error. (gulps)

 

[GAJITCS]

Confirmed. It was my own fault.
While looking at the array as being compiled, the data looked okay. Once written to the destination and the macro coming to an end, the data format had changed. Putting pauses in the code after the loops had completed, helped identify where the format change occurred and it was within a section of code I had amended to fix another issue.
But... What I aware of is that while we post questions here, we don't often admit to our own failings and that can make some of the answers given by others look like they don't work, when actually, they do.

All earning is good.

Thank you Wookiee, your answer was on the mark, I just managed to corrupt the results a bit further down in the code.

 

[GAJITCS]

(all learning is good)