Replacing whole date with only "Day" using for next loop or other methods.

[Asad Mir]

Hello Everyone!
Hope you are safe and sound...

In the column D of the sheet i attached, i have complete dates.
I want to extract only the day and place that day in the same cell as of complete date, replacing the complete dates with day.
and i want to do it for the whole column.
I tried to solve this but type mismatch error arises. Please help...

Please Login or Register  to view this content.

Please do note that, before using above code i also performed some other functions on this worksheet i.e deleting blank rows through filtering,
For that i wrote another code first, after removing those blank rows, i wanted to change the complete dates in "Posting Date" column with only "Day".
After filtering the blank rows, i have 109 rows, data starting from Row 2.

[nankw83]

Try the below code …

Please Login or Register  to view this content.

[mjr veverka]

Replacing whole date with only "Day" using for next loop or other methods

Please Login or Register  to view this content.

[Asad Mir]

Thanks alot.....

[Asad Mir]

Thanks porucha vevrku!!!

I have two questions...
First:
I couldn't understand this line of code.
tbl is a variant and what does (i,1) means?

Please Login or Register  to view this content.

Second:
After For Next Loop, you are changing number formatting to General and other things, My question
here is... can you not do the same inside the loop?

Clarification....

Please Login or Register  to view this content.

What i understand from the above mentioned line of code is that "rws" is last used row of the sheet,
but, why are you adding 1 to last row?

[jindon]

No loop

Please Login or Register  to view this content.

[Asad Mir]

No loop

Please Login or Register  to view this content.

Thanks alot Jindon
A brief explanation would be very helpful and appreciative.
Actually, could not understand the following part of code.

Please Login or Register  to view this content.

.Value = .Parent.Evaluate("index(day(" & .Address & "),,)")

Please Login or Register  to view this content.

[jindon]

One of the Evaluate method is to calculate the formula.
e.g

Please Login or Register  to view this content.

However when calculation needs Worksheet Function, it need to prepare an Array for individual calculated data, otherwise it only shows the first calculated result you saw in Post #2 in this thread.

Please Login or Register  to view this content.

Read the link here which I posted first time and pgc01 explained nicely.

[nankw83]

otherwise it only shows the first calculated result you saw in Post #2 in this thread.

Actually the code in post #2 works as expected but in Excel 365 as Excel creates the array for you. In normal spread sheet, if I type "=DAY(D2:D130)" in cell G2, it just creates an array of results from G2 until G130 … You can read more HERE if interested

But thanks jindon for clarifying that the Excel version of the user is something I have to consider next time and maybe it's time to update your Excel version to explore & enjoy the new added features/behaviors

[Asad Mir]

Can't thank you enough...

[Asad Mir]

One of the Evaluate method is to calculate the formula.
e.g

Please Login or Register  to view this content.

However when calculation needs Worksheet Function, it need to prepare an Array for individual calculated data, otherwise it only shows the first calculated result you saw in Post #2 in this thread.

Please Login or Register  to view this content.

Read the link here which I posted first time and pgc01 explained nicely.

can't thank you enough...

[Asad Mir]

Thanks! All of You...

[jindon]

You are welcome and thanks for the rep.

Please don't forget to mark the thread as "Solved" when your problem is solved.

[mjr veverka]

... tbl is a variant and what does (i,1) means?

Please Login or Register  to view this content.

Ad 1. "(i, 1)" means the value of the variable from the "i-th row" and "j-th column" (here j = 1) of the data table.
When you load data from a worksheet to an array, the internal vbe mechanism creates a row-column layout as in a matrix.
So when you get data from 1 column and many rows, the layout is created:

1st row, 1st column
2nd row, 1st column
3rd row, 1st column
...
n row, 1st column

You refer to the individual elements of this layout through their indexes (measures of location).
In an array variable ("tbl(i,j)"), this index will be a certain symbolic variable (here "i"), which will increase according to the rhythm of successive, repeated references to it (loops, cycles).

Ad 2. Variables in vb (and in other "languages" ​​also) have the property that their current-original value can be modified (on the fly) and reassigned to the same variable.
The entry:

Please Login or Register  to view this content.

means that from the data table you take/get an element from the "i-th row" and the "first column", then from this element you take/get some value (here "day") and this new value you reassign to this data array element

... changing number formatting to General and other things ...
... can you not do the same inside the loop? ...

In this code, the actions in the loop refer only to the data table taken (to the RAM memory) from the sheet and not to the sheet itself.
You could do formatting inside the loop, but it would take longer because of referencing individual cells of the sheet.
A one-time reference to the entire range of cells is shorter and faster.

... why are you adding 1 to last row? ...
...

Please Login or Register  to view this content.

...

Because of the table header in the worksheet.
First you had to reduce "rws" by 1 because only 129 rows were loaded to the table (no header lines), but then, you had to restore the "rws" variable to its original value again so that the data was entered in the correct range of cells in the worksheet ("D2:D130").

[Asad Mir]

Really, I am short of words to appreciate how porucha vevrku explained...
It felt like i am learning in a real face to face environment...
Can't Thank you enough....

Best Wishes!!!

[Asad Mir]

One Last Question...

What's the purpose of below mentioned code and how does it work?

Please Login or Register  to view this content.

tbl = Empty

[mjr veverka]

... What's the purpose ...

'Empty' indicates an uninitialized variable value for 'Variant' sub-type - it's like "clearing" a variable.
Since the 'Variant' type is a rather strange variable type, I aim/endevour to "reset" this variable after each use.
(if a given variable of the 'Variant' type passes through the code all the time in one sub-type, this is not needed)

Ps:

... how... explained...

"Oki" ... Asad Mir ... : ) ... thank you for the nice words, but "this" font size is not needed, if you can still edit your post, change it to normal size ... : ) ...

greetings

[Asad Mir]

Sure, Thanks!