Jump to content


  • Posts

  • Joined

  • Last visited

  • Days Won

  • Country


Haplox last won the day on June 1

Haplox had the most liked content!

About Haplox

  • Birthday September 16

Profile information

  • Gender
  • Localización:
  • Interests
    Sr. Shrödinger, su gato ha sufrido un accidente. Tengo buenas y malas noticias…


  • Campos
  • Mi versión de Excel:

Recent Profile Visitors

7,506 profile views

Haplox's Achievements

  1. @Maria_80,usa el siguiente código: Sub suma_condicional() Dim uF& Dim ID As New Collection Dim item Dim i&, j&, suma& uF = Range("A" & Rows.Count).End(xlUp).Row For i = 2 To uF On Error Resume Next ID.Add Cells(i, 1), CStr(Cells(i, 1)) Next i On Error GoTo 0 h = 2 For Each item In ID suma = 0 For j = 2 To uF If Cells(j, 1) = item And Cells(j, 2) = "compra" And Cells(j, 3) <> "" Then suma = suma + Cells(j, 3) End If Next j Cells(h, 6) = item Cells(h, 7) = suma h = h + 1 Next item End Sub
  2. 👏👏👏.... ¡¡¡Ayyy... qué haríamos sin ti!!!
  3. Primero ten en cuenta que para que funcione, AMBOS FORM deben tener la propiedad "ShowModal" en FALDE. Solo cambia el código del botón del UserForm2 a: Private Sub CONFIRMAR_Click() CANT = TXTCANTIDAD.Value UserForm1.LBPedidos.List(UserForm1.LBPedidos.ListCount - 1, 4) = CANT UserForm2.Hide UserForm1.Show End Sub
  4. Te me adelantastes por la mano... @Maria_80,no puedes usar directamente VlookUp directamente sobre un rango filtrado. Otro enfoque con el filtro Sub buscar_filtrados() Dim rng, cel As Range, ufo&, ufd& If Worksheets("Hoja1").FilterMode Then Worksheets("Hoja1").ShowAllData 'Quitamos el filtro Worksheets("Hoja1").Range("B1").AutoFilter Field:=2, Criteria1:="B", Operator:=xlFilterValues ufo = Range("A" & Rows.Count).End(xlUp).Row Set rng = Sheets("Hoja1").Range("A2:A" & ufo).SpecialCells(xlCellTypeVisible) Sheets("Hoja2").Activate ufd = Range("A" & Rows.Count).End(xlUp).Row For Each cel In rng For x = 2 To ufd If Cells(x, 1) = cel Then Cells(x, 2) = cel.Offset(, 2) Exit For End If Next x Next cel End Sub
  5. @mrive,debes subir tu archivo de ejemplo. Aparte ¿Si coincide el 1 y el 2? ¿En qué...?
  6. @Julio Angel, debe subir tu fichero de ejemplo para poder trabajar sobre él
  7. 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
  8. 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
  9. 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.
  10. 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
  11. 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
  12. 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
  • Create New...

Important Information

Privacy Policy