Jump to content

Haplox

Members
  • Posts

    2,996
  • Joined

  • Last visited

  • Days Won

    99
  • Country

    Spain

Haplox last won the day on June 1

Haplox had the most liked content!

About Haplox

  • Birthday September 16

Profile information

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

Converted

  • Campos
    ;
  • Mi versión de Excel:
    2

Recent Profile Visitors

7,902 profile views

Haplox's Achievements

  1. Hola! Te puedo hcer una consulta respecto a este codigo, al ejecutarlo me entrega el valor que está en la celda incluida la formula, como podría hacer para que solo me entregué el valor?

    Sub Extraer_Datos()
    Dim shDestino As Worksheet
    Dim ufo As Integer, ufd As Integer
    Set shDestino = Worksheets.Add(After:=Sheets(Sheets.Count))
    shDestino.Name = "d"

    For I = 1 To Sheets.Count - 1
        If Sheets(I).Name = "ESCUELAS" Then
        Else
            ufo = Sheets(I).Range("A" & Cells.Rows.CountLarge).End(xlUp).Row
            ufd = shDestino.Range("A" & Cells.Rows.Values).End(xlUp).Row
            Sheets(I).Range("AA2:AJ" & ufo).Copy shDestino.Range("A" & ufd)
            
        End If
    Next I
    End Sub
     

    1. Haplox

      Haplox

      Cambia esra línea:

      Sheets(I).Range("AA2:AJ" & ufo).Copy shDestino.Range("A" & ufd)

      por:
       

      Sheets(I).Range("AA2:AJ" & ufo).Copy
      shDestino.Range("A" & ufd).PasteSpecial xlPasteValues

      Saludos

  2. Pues no sé, pero si no puedo hacer nada yo tampoco, pues hasta aquí llegamos amigos
  3. @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
  4. 👏👏👏.... ¡¡¡Ayyy... qué haríamos sin ti!!!
  5. 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
  6. 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
  7. @mrive,debes subir tu archivo de ejemplo. Aparte ¿Si coincide el 1 y el 2? ¿En qué...?
  8. @Julio Angel, debe subir tu fichero de ejemplo para poder trabajar sobre él
  9. 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
  10. 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
  11. 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.
  12. 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
  13. 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
×
×
  • Create New...

Important Information

Privacy Policy