Application.Transpose

[AlexanderBB]

This was recommended as a way to build an array from Values in a Worksheet row, In this example Col Y to AF in row chRow

Dim Ary as variant
 Ary = Application.Transpose(Application.Transpose _
             (Worksheets(.Name).Range("Y" & chRow & ":AF" & chRow).Value))

If works great but I've struck a problem. If only Col Y contains a value Ary is a string.
My code fails as it expects an array.
What is a good way to resolve this ? I can test isArray(ary) and perhaps force the string into an array but it doesn't seem right.

 

[AlexanderBB]

Without CDate I get a type mismatch error.
The cell is custom Formatted dd mmm yyyy and is a string.
Why? I think at the time it was how I got it looking 'right'. The date Formats didn't include that Format and it wasn't obvious how to add it.
Now (so,me years later) it's a done thing.

 

[MARK858]

I'll have to take your word for it that what you are seeing is a string, what I get with a real date formatted as dd mmm yyyy is a number (if it is changing the number format then it must be a number as it wouldn't change if it was text), having said that I know I have used CLng(Cdate()) before but just can't remember why.

Book1
	A	B	C	D
4	14 Feb 2025			TRUE
Sheet1

Cell Formulas
Range	Formula
D4	D4	=ISNUMBER(A4)

 

[AlexanderBB]

How is your range A4 formatted? Mine's

 

[MARK858]

 

[AlexanderBB]

That's pretty weird, isn't it. You'd think we'd get identical results
If I debug.print clng(Range("V1000")) it still goes to error 13.

 

[MARK858]

If you change the cells format to General what do you see?

 

[StephenCrump]

That's pretty weird, isn't it. You'd think we'd get identical results

No. Mark is working with a number. You have a string.

So for you, changing the number format should have no impact on what's displayed.

 

[AlexanderBB]

> No. Mark is working with a number. You have a string.
I'm missing something, . Doesn't his picture in msg 14 suggest we're using the same cell format.?

 

[MARK858]

I'm missing something, . Doesn't his picture in msg 14 suggest we're using the same cell format.?

It isn't the number format (that is just an image, like putting a filter on a camera lens), it is the underlying value that defines if it is a number or text
.
Mine is a number (a "real" date is just a number formatted to look like a date), yours is text.

You have either

1. entered it in a syntax that isn't recognized as a date in your regional settings
2. entered it in a cell that is formatted as Text before you made the entry (and typed 21 Oct 1989)

If you formatted your cell as General you would find yours would remain as 21 Oct 1989 whereas mine would change to 45702

Edit: as @StephenCrump stated

So for you, changing the number format should have no impact on what's displayed.

 

[AlexanderBB]

You entered the date as a number then changed Cell format to dd mmm yyyy ?
Yes, changing to General doesn't change the cell. TBH I don't remember exactly how it was entered, but most likely it was not a number.
When new to Excel, this gave me a lot of trouble. Something about the cells format needing to be set when the cell was empty.