How to code it with array ?

[karmapala]

To be honest, I always have my headache for array coding,
even after I've tried to learn it again and again by reading the internet.

Case:
The user type a transaction in input area
click InputToData button
then the macro put the data in data area

Before I write the macro, I make a preparation by making a template range, which looks like image below :
2021-02-22_16-16-35.gif

The code I wrote :

Please Login or Register  to view this content.

The code works as expected. There's no problem.

The problem is :
Now I've been thinking on how to do the code using array without the need to make a template.
So, there's no template area (the yellow one), and hoping the code lines will be fewer.

Again, I'm totally lost if it's about array.

Any kind of help would be greatly appreciated.
Thank you in advanced.

[mohadin]

what about

Please Login or Register  to view this content.

[mohadin]

And for the yellow area

Please Login or Register  to view this content.

[maniacb]

Not sure if this will help, but it sources info from green input area

Please Login or Register  to view this content.

[jindon]

Please Login or Register  to view this content.

[Bo_Ry]

Please try

Please Login or Register  to view this content.

[maras_mak]

The results are in a new sheet 'Sheet2'.

Please Login or Register  to view this content.

[karmapala]

Thank you for everyone who donate the code for me.
I've tried each of them. But I am very very sorry that I forget to mention these :

1. The input area will always change based on when any transaction happen.
For example, the next time input is 21 Feb 2021 (still the same day)
but the name is Xena, the ID is BZ, the item are Apple 3, Orange 10, Avocado 2...
next time, the name is Boboho, the ID is BX, the item are Chicken 20,
and so on.

Tomorrow, the date for surely is 22 Feb 2021...
but the name, the ID and the how many rows of item name never fix.

2. The data area (the blue header) is already manually created before (not created by the code).
the row value below the header is the one which will be created by the code,
and the row will grow because the data from the input area will always be put to the last blank row available of column DATE each time the user click the "input" button.

3. actually there is a last line for the "input" button code
which clearcontents the value of the name, the ID, the item and the val in the input area.
(the Date column in the input area has a "=today()" formula).

Again, I'm very sorry that I forget to mention about that.

So....
@maniacb,
please have a look if your modified code is OK :

Please Login or Register  to view this content.

.
.
.

@jindon,
I'm very sorry as I don't know on how to modify the code.
.
.
.

@Bo_Ry,
please have a look if your modified code is OK :

Please Login or Register  to view this content.

.
.
.

@maras,
please have a look if your modified code is OK :

Please Login or Register  to view this content.

.
.
.

@mohadin,
I'm sorry I can't use the code, because it seems that your code still use the "template" range.

Thank you for all once again.

[jindon]

I don't understand what you are trying to say.

My code is flexible of the changes in Input area as long as the first part is in [A1:B5] and the 2nd part is in one block of range after A6.
And my code generates header from the 1st and 2nd range.

Are you saying the number of rows in 1st part may change as well?

[mohadin]

Ahhh
I see

Please Login or Register  to view this content.

[maras_mak]

Not sure I understand everything, but try...

Please Login or Register  to view this content.

[karmapala]

I don't understand what you are trying to say.

My code is flexible of the changes in Input area as long as the first part is in [A1:B5] and the 2nd part is in one block of range after A6.

What I mean is, the input area is something like a user form.
The first condition (before the user type a value) is :
Date : ...
Name : ...
ID : ...

ITEM VAL
... ...
... ...
... ...
available until 10 rows.
(all those ... are empty cells)

And my code generates header from the 1st and 2nd range.

Before running the code, the header already created manually, jindon.
So, there is already the blue header before running the code.

Are you saying the number of rows in 1st part may change as well?

Yes, the number of rows for Item/Val may change all the time, depends on how the transaction happen.

For example:
today at 10 am, John buy 5 items with their respective quantity as seen in the example.
the user input it in the "user form" (input area).
So, the number of non-empty rows under Item/Val here is 5 rows.
Then the user click the button.
the code put/move the value (which the user input in the "user form")
to the last blank row of the blue header column DATE,
then clear the value in the "user form" which user just typed it before.

next, at 10.30 am, Xena buy 2 items : book 2 pcs, glass 1 pcs.
the user input it in the "user form" (input area).
So, the number of non-empty rows under Item/Val here is 2 rows.
Then the user click the button.
the code put/move the value (which the user input in the "user form")
to the last blank row of the blue header column DATE,
then clear the value in the "user form" which user just typed it before.

and so on.

That's why in my code (using the template),
I always count first to get how many non-empty rows are there under Item/Val of the "user form",
after I have the count, I use this to set the range of the template to be "copied".

I hope I am clearer now.
Thank you jindon.

[mohadin]

Then
Try this version



[UPDATED]

Please Login or Register  to view this content.

[jindon]

Have you really tried?

[karmapala]

Not sure I understand everything, but try...

Thank you, maras.

Your first code in reply #7 actually works after the "making of the header" code line is deleted,
and in "With .[a1].Resize(UBound(a2))", the [a1] is change to be always the last blank row.

The code from you which I modify (in post #8) has exactly the same result with your code in post #11.

Thank you maras_mak.

[karmapala]

Ahhh
I see

I am sorry, as it still doesn't give me the expected result, mohadin.

Anyway, thank you for your help.

[karmapala]

I am very sorry as I'm limited in English, I can't express what I mean correctly.
Below is a sample condition :

01.png
1. The green area (input form) has no value
2. the blue header has already one row of data

.
.
.

02.png
After the user type the value in the input form (there are two kind of items)

.
.
.

03.png
After the user click the button:
1. The green area has no value again
2. now the blue header has three rows of data
where the last two rows of data are from what user typed before in the input form (green area)

.
.
.

Next, the user again input a value into the user form (there are four kind of items) :
04.png

.
.
.

After the user click the button:
05.png
1. The green area has no value again
2. now the blue header has seven rows of data
where the last four rows of data are from what user typed before in the input form (green area)

I hope my explanation is better now

[jindon]

Change to

Please Login or Register  to view this content.

[Bo_Ry]

Please try

Please Login or Register  to view this content.

[maras_mak]

Try. The results in Sheet2.

Please Login or Register  to view this content.