Home / user-240045

victor julio's questions

Martin Hope
victor julio
Asked: 2022-08-06 19:32:45 +0800 CST
  • -1

Let's suppose:

In sheet1 of the Excel book I have this table: patient and In sheet2 of the Excel book I have this other table: age group

The problem that I want to solve by VBA macro is that if I write any name in cell A2 of the "Patients" table, and whose name is in the set of cells (A2:A4) of the "Age Group" table, then the macro automatically fills in cells B2 and C2 of this table, with the values ​​that correspond to the specific name written there.

Cheers!

vba
Martin Hope
victor julio
Asked: 2022-07-30 12:17:53 +0800 CST
  • 4

The macro has this problem:

  • If Monica buys only flowers (see image 1), then on Sheet 2 she inserts 4 empty rows.

  • If Monica buys only flowers and wrapping paper, then on Sheet 2 she inserts 3 empty rows.

  • If Monica buys only flowers, gift wrap and a card, then on Sheet 2 she inserts 2 rows (see image 2).

How can I make it so that the macro does not insert empty rows in Sheet 2?

Table "Customer Purchases"

Table "Sales List"

Sub Compras_del_Cliente()

    'Compras del cliente Macro
    Sheets("Hoja2").Select
    Range("A9:A13").EntireRow.Insert

    'Nombre / Empresa
    Sheets("Hoja1").Select
    Range("C9").Copy      
    Sheets("Hoja2").Select
    Range("A9").PasteSpecial xlPasteValues

    'Código
    Sheets("Hoja1").Select
    If Range("B12:B16").Select <> Empty Then
        Range("B12:B16").Copy 
    End If
    Sheets("Hoja2").Select
    Range("C9").PasteSpecial xlPasteValues

    'Descripción del Producto
    Sheets("Hoja1").Select
    If Range("C12:C16").Select <> Empty Then
        Range("C12:C16").Copy    
    End If
    Sheets("Hoja2").Select
    Range("B9").PasteSpecial xlPasteValues

    'Cantidad
    Sheets("Hoja1").Select
    If Range("D12:D16").Select <> Empty Then
        Range("D12:D16").Copy    
    End If
    Sheets("Hoja2").Select
    Range("D9").PasteSpecial xlPasteValues

    'Precio
    Sheets("Hoja1").Select
    If Range("E12:E16").Select <> Empty Then
        Range("E12:E16").Copy    
    End If
    Sheets("Hoja2").Select
    Range("E9").PasteSpecial xlPasteValues

End Sub

Sub Borrar()
    'Botón para borrar valores en la tabla "Compras del CLIENTE" en Hoja 1
    Range("C9").Value = Empty
    Range("B12:B16").Value = Empty
    Range("C12:C16").Value = Empty
    Range("D12:D16").Value = Empty
    Range("E12:E16").Value = Empty
End Sub

Sub Eliminar_venta()
    'Botón para eliminar venta en la tabla "Lista de VENTAS" en la Hoja 2
    Sheets("Hoja2").Select
    Selection.EntireRow.Delete
End Sub
vba