Fill Column (B) Only the days of the Week and Column (C) the same thing but only for the first letter

[A.Maurizio]

Hello everyone as always when it comes to evaluating by column; I lose myself in a glass of water.
My problem is this:
1) In Column (A2: A200) I should enter dates
2) I would like you to write me only the day of the week of that date every time I enter the dates in the column (B2: B200).
3) While in column (C2: C200) he wrote me the same thing as in column (B) but taking only the first letter.
As for example:
If in Column (B) it is written (Monday) in Column (C) it should only show the letter (L) that's all!
Thanks as always for all the help you want to give me about it. Greetings
from Maurizio

Modificare_Giorni_Set_e_Giorni_Set_Solo_Prima_Lettera.xlsm

[Haplox]

Hello @A.Maurizio, this isthecode you need (as Ia have understood). Regards

Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Range("A2:A200")) Is Nothing Then Exit Sub

Target.Offset(, 1) = Format(Target, "dddd")
Target.Offset(, 2) = UCase(Left(Target.Offset(, 1), 1))

End Sub

 

[A.Maurizio]

Hi Haplox
First I have to give you my most sincere congratulations on your work
As I after many years that I plan everything and more with your help
I am amazed when I am faced with having to interact with the columns.
I do not have yet what the denominator thread could be to understand how to interact even I am in a very simple way.

Having said that Thanks again for everything
But I wanted to ask you this:
You kept the fact that I interact with the dates and days of the week; I clicked on the cell of column (A)

But if I wanted to extract everything automatically by removing that annoying (Target - Intercet)
What could I write in their place to make sure that:
I write the dates in column (A), then I press a button placed on the sheet and in Automatic it brings me back to column (B) and (C) everything you and I have done.
So I wouldn't have to use (Target) that's all.
Thanks for all the help you want to give me. Sincere Greetings And Good Evening from Maurizio

[Haplox]

Hace 8 minutos , A.Maurizio dijo:

But if I wanted to extract everything automatically by removing that annoying (Target - Intercet)

I do not understand why you want to use a button every time you change a cell, if it can be done automatically .

Maybe you may search in Internet for the "Intersect" function. It is used, in this case, to discriminate where are the cells to be changed by the macro.

If you introduce a data in any other cell than in columnA, the macro will not work by the discrimination. That is the purpouse of the Intersect.

So if you change a cell in, for example A5, it INTERSECTS with the range "A2:A200" and the macro runs. In this way, if you change a cel in B7 it NOT INTERSECTS with range "A2:A200", an the macro does not run.

[A.Maurizio]

Hi Haplox you are right
but it was only mine a way to understand if there was another way to take advantage of the click of the button and not the chick on a cell that's all.
But don't worry it's beautiful even so
You are still great:
He always learns a lot with you. Thank you.

[Haplox]

Ahora mismo, A.Maurizio dijo:

but it was only mine a way to understand if there was another way to take advantage of the click of the button and not the chick on a cell that's all.

Only to clarify you some thing about... For using some button to run the code when you press it, How do you think that the macro is going to know WHICH CELL you changed, if it si not trough the WorkSheet_Change evnt? ?

Think on it

[A.Maurizio]

Hi Haplox you are absolutely right
I was thinking differently
I beg your pardon and it was my intention to offend your kindness in giving me explanations every time that for me are a source of inspiration and help you give me to increase my knowledge.
Infinite Thanks Truly.

(P.S) You will be so dentile in explaining this to me:
Then Ca Column (A) receives the Date and Column (B) Receives the Day of the Week and so far everything is OK
But if for example the Letter that belongs to Column (C) for personal need I also wanted to extend it by row and not by column It would be fine if I added Colunm in this point:
Written like this:

Target.Offset (, 2) = UCase (Left (Target.Offset (colunm, 1), 1))

Or you have to completely change the Method
That's all. Thank you

[Haplox]

Hace 5 horas, A.Maurizio dijo:

I beg your pardon and it was my intention to offend your kindness in giving me explanations every time

You donot may askfor any pardon.. All we here are for learning ?. About your question, I do not understand very well. Where do you want to put the letter from the target?

Takeinto account that the Offsethas the next rules:

Target.Offset("rows from the target" , "columns from the target")

So, if you wantto put theletter, for example, one column far from the target, and two rows below from it:

 

Target.Offset(1,2)=UCase (Left (Target.Offset (0, 1), 1))

In this the importantpart is theone beforethe "=". It is where you want to put the data. After the "=" is what you want to pun in it

 

[A.Maurizio]

Ciao Haplox
in pratica Volevo ; Sempre per curiosità e per Apprendimento Personale
Capire questo:
Visto ché il tuo metodo e perfetto cosi come me lo hai scritto tu.
Ma e in formato Verticale, cioè ad ogni colonna il suo dato
Mi stavo chiedendo questo:
E se la sola lettera della colonna (C) anziché evidenziarsi in forma Verticale
Io lo volessi nel Formato (Orizzontale) ma solo questa colonna; Come e cosa dovrei Scrivere per ottenere tutto questo.
mi spiego meglio :

Ora io a partire dalla colonna (A3:C100) ottengo questo formato:

15/04/2021	giovedì	G
16/04/2021	venerdì	V
17/04/2021	sabato	S

[A.Maurizio]

Hace 31 minutos , A.Maurizio dijo:

Ciao Haplox
in pratica Volevo ; Sempre per curiosità e per Apprendimento Personale
Capire questo:
Visto ché il tuo metodo e perfetto cosi come me lo hai scritto tu.
Ma e in formato Verticale, cioè ad ogni colonna il suo dato
Mi stavo chiedendo questo:
E se la sola lettera della colonna (C) anziché evidenziarsi in forma Verticale
Io lo volessi nel Formato (Orizzontale) ma solo questa colonna; Come e cosa dovrei Scrivere per ottenere tutto questo.
mi spiego meglio :

Ora io a partire dalla colonna (A3:C100) ottengo questo formato:

15/04/2021	giovedì	G
16/04/2021	venerdì	V
17/04/2021	sabato	S

Ciao Haplox

in practice I wanted; Always out of curiosity and for Personal Learning
Understand this:
Since your method is perfect as you wrote it to me.
But it is in Vertical format, that is, its data for each column
I was wondering this:
And if the only letter of the column (C) instead of being highlighted in Vertical form
I wanted it in the Format (Horizontal) but only this column; How and what should I write to get all this.
I'll explain :

Now starting from the column (A3: C100) I get this format:

15/04/2021 Thursday G
04/16/2021 Friday V.
17/04/2021 Saturday S.

But if I wanted to get the same thing but only that column C instead of being written in Vertical form I wanted it in Horizontal form!
Written Cosi?

15/04/2021 Thursday G V S
04/16/2021 Friday
17/04/2021 Saturday

I assume you have to change the state from (Row) to (Colunm) only that in your case already perfect so
I don't know where to put my hands.
Therefore I wonder how I can change this situation.
or is it not possible?

(P.S) I found on the internet that it is possible to achieve all this by writing this function

=MATR.TRASPOSTA(C3:C5) But now I can adapt it to my function created by you. now I try and then I'll let you know Thanks anyway for your support; I always reiterate that you are Fantastic. Thanks

[A.Maurizio]

Hi Haplox don't worry about what I wrote in my last post.
As in the end of everything I then solved it in this way:
Which was what I wanted to achieve from the start
And this is my finished work.

Infinite Thanks for everything you have done so far
Sincere greetings from A.Maurizio
(P.S) For about a month or so I won't bother you anymore
As I have to do my job in Florida
Therefore it will take some time before I can think of a new project.
Hello and Thanks With Sympathy from A.Maurizio

Formatazione_Del_Tipo_Gantt_Per_Alessandro_Colombo (version 2).xlsm