Jump to content

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


Recommended Posts

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

Link to comment
Share on other sites

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

 

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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 :huh:.

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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 :rolleyes:

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

 

Link to comment
Share on other sites

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
Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

  • Crear macros Excel

  • Posts

    • Buenos días a todos; -Necesito de vuestra ayuda. Para mejor comprensión adjunto enlace de un video y comentario. Saludos y gracias de antemano     Adjunto también la macro. MEvento.zip
    • No debe importarnos que el usuario que abrió el tema no vuelva a consultarlo porque nuestras respuestas le llegaron demasiado tarde... Lo importante es poder ayudar a otros usuarios que tengan un problema similar en el futuro...
    • Es una opción original e ingeniosa pero creo que difícil de comprender para un usuario que sepa fórmulas sencillas... Adjunto otra opción con fórmulas desbordadas que puede que sea más fácil de comprender para un usuario que esté aprendiendo a formular, pues hay 3 pasos separados: Columna D : A cada valor se le añade 1> a la izquierda, se sustituye el primer + por 2> y el segundo + por 3>. De paso se quitan los signos , y . para convertir los valores en números. Todo ello con la función SUSTITUIR. ="1>"&SUSTITUIR(SUSTITUIR(SUSTITUIR(SUSTITUIR($C2;",";"");".";"");"+";"2>";1);"+";"3>";1)   Columna E (desbordada hacia la derecha en las columnas F y G): Extrae los valores y letras de 1>, 2> y 3>. Todo ello con una versión matricial de la función EXTRAE, con la ayuda de la función ENCONTRAR. =SI.ERROR(SUSTITUIR(EXTRAE($D2;ENCONTRAR({"1>"\"2>"\"3>"};$D2);SI.ERROR(ENCONTRAR({"2>"\"3>"\"0>"};$D2);100)-ENCONTRAR({"1>"\"2>"\"3>"};$D2));{"1>"\"2>"\"3>"};"");"")   Sumas de C, T y V: Suma las cantidades consumidas de cada letra con la función SUMAPRODUCTO. Salu2, Pedro Wave Sumar Letras PW1.xlsx
    • Hola,  Estoy intentando vía InputBox rellenar con el dato introducido una columna. Pero no consigo que lo haga desde la primera fila libre de A. Sería pegar el dato a partir de la primera celda libre de la columna A (está en verde), en función del Nº de filas de la columna B No consigo modificarla y se pega desde el comienzo.  Podéis echarle un vistazo? La macro está en el ejemplo. ¡Muchísimas gracias!      ej_InputBox.xlsm
    • La mía. Sub Mostrar() Application.ScreenUpdating = False Range("B:CM").EntireColumn.Hidden = False End Sub '-- Sub Ocultar() Dim Filtro As Range Application.ScreenUpdating = False Mostrar For y = 2 To Columns("CM").Column If WorksheetFunction.CountIf(Cells(8, y).Resize _ (Range("A" & Rows.Count).End(xlUp).Row, 1), "<>" & Empty) = 0 Then Columns(y).Hidden = True End If Next End Sub  
  • Recently Browsing

    No registered users viewing this page.

×
×
  • Create New...

Important Information

Privacy Policy