Jump to content

Find and Write Letters of Acknowledgment in other Found Data Cells


Recommended Posts

Hi Everyone My new
problem is this: On
an Excel sheet in Column (C)

1) I would like that every time I enter a word like ("btp fut") or write ("btp ital") the letter (L) should appear in Column (P)

2) While If in Column (D) I enter a Date less
than 5 days compared to today's date We will have to create the lettara (D) in the column (P)

3) If in Column (I) the Writing ("change balance") is shown and in Column (J) it does not contain (0), the Letters (CS) in column (P) should appear

4) If in column (K) I write the word ("square *") Write the letter (Q) in Column (P)

Let's say I don't expect you to
do all the work for
me But that you help me to solve even his first questions Then I think I'll be able to do it all by myself thanks to the rest.
Greetings from A.Maurizio

macro controller.xlsm

Link to comment
Share on other sites

Hello @A.Maurizio, I do not understand well your question, but maybe this idea can help you. You can go on discriminating by ranges with the WorkSheet_Change event.
 

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("C1:C5")) Is Nothing Then
    If Target = "btp fut" Or Target = "btp ital" Then
        Cells(Target.Row, "P") = "L"
    End If
End If

If Not Intersect(Target, Range("D1:D5")) Is Nothing Then
    If Target < Date - 5 Then
        Cells(Target.Row, "P") = "D"
    End If
End If
'.
'.
'rest or your code
'.
'.
End Sub

You do not explain what hapens in the case, for example, of having "btp fut" in C1 and 16/05/2021 in D1... In column P must be a "L" or a "D"?

Regards

Link to comment
Share on other sites

Hello Haplox Fiatto Il Vacino!
Listen and just as
described by you
Or if you prefer what I said If in Column (C1: C200) in addition to writing other things, the name
(btp fut) or (btp ital) is
reported then in Column (P) an "L" must appear Clearly the (L) must be on the same line as the Term (btp fut) o (btp ital).
Ditto for column (D) as if I enter a lower date in Column (D1: D200) you have 5 working days compared to today's date
Then in Column (P) the letter (D) must be reported

A little more complicated is what is in the columns (I1: J200) as in this case it should do the evaluation
that if in Column (I) I
write (change balance) and in the adjacent cell of column (J) I write a number (> 0) In column (P) there should be written the word (CS)

And finally if in the column (K1: K200) I write the term (square)
in the column (P) the letter (Q) should appear And this and how much.

(P.S) I tried your routine but at the moment in column (P) nothing appears.

 

Link to comment
Share on other sites

Hi Aplox
Listen Always do
everything with peace of mind But if you help me to solve this problem; I solved all the others by modifying your Previous help.

the Problem and this
itself in the columns (I1: i200) I write the term (change balance) and in the adjacent cell of
the column (J1: J200)
I write a number (> 0) Column (P) should read the word (CS) all here thanks As always you are fantastic.

Link to comment
Share on other sites

Heelo @A.Maurizio. Understanding that you only need the change involved in column P, because the datas in columns I and J, the code you would need may be:

If Target.Column = 9 And Target = "cambio saldo" Then
    If Target.Offset(, 1) > 0 Then
        Cells(Target.Row, "P") = "CS"
    End If
End If

Link to comment
Share on other sites

Posted (edited)

Perfect Haplox
You are fantastic as always now I have no more problems to go on
Thank you so much and good evening to you, all your staff and your family.
Sincere Greetings From A.Maurizio

Edited by A.Maurizio
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