Unpivot code set returns an error in Resize Ubound function

Question

I'm trying to unpivot an array of data. I was able to do this before from a slightly different Source data, thanks to our good sir's help mr. Tim Williams who answered my previous question on this ( Link to my previous question), however, now i'm using a slightly different source data.

I was pretty sure that I have to only change the number of columns that will be fixed in

p = UnPivotData(ThisWorkbook.Sheets("Projects Data").Range("A1").CurrentRegion, _
    114, True, True)

i.e. 114. But for some reason I get an error in this part

.Range("A1").Resize(UBound(p, 1), UBound(p, 2)).Value = p

It also does work for some couple of lines and after that the error kicks in. I'm not sure what seem to be the error here. I've been trying to work on this for two days and I'm sleep deprived and frustrated,so as usual, I would really be very happy and appreciate your help on this.

Here's an extract of the file that i'm working on with it's code.

Which error do you get? What is the result of Debug.Print UBound(p, 1) and Debug.Print UBound(p, 2)? — Pᴇʜ
– Pᴇʜ, Commented Jul 14, 2021 at 7:55
Please note that you share a lot of real date including names that should probably not be public! Always anonymize your data before sharing! — Pᴇʜ
– Pᴇʜ, Commented Jul 14, 2021 at 8:04
Why not use Power Query itself to do the unpivot? That functionality is built in — Ron Rosenfeld
– Ron Rosenfeld, Commented Jul 14, 2021 at 10:38

Pᴇʜ · Accepted Answer · 2021-07-14 08:15:23Z

1

The issue is that in your data appears #1837-10-07 17:31:12#. But Excel cannot display dates before 1900. Therefore it fails to write that data and aborts.

The problem is the column DI in Projects Data is formatted as Date but there are no dates in it. Fix that number format to General and your function works as expected.

answered Jul 14, 2021 at 8:15

Pᴇʜ

57.9k10 gold badges55 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

BoyDespasito Over a year ago

Thank you sir it worked. I never thought excel does consider that with this code. Thank you so much.

Collectives™ on Stack Overflow

Unpivot code set returns an error in Resize Ubound function

1 Answer 1

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Linked

Related