Home / Questions / 475300
Accepted
victor julio
Asked: 2022-08-06 19:32:45 +0800 CST 2022-08-06 19:32:45 +0800 CST

What would be the VBA macro to write the AGE AND WEIGHT values ​​of a specific NAME?

  • 772

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

1 Answers

  1. Best Answer

    Let's see

    In sheet1, which I have called Patients, you paste this code (What it does is show a message that cell B6 has been changed if it changes its value and execute the ExtractData function)

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim KeyCells As Range

    Set KeyCells = Range("B6")

    If Not Application.Intersect(KeyCells, Range(Target.Address)) _
       Is Nothing Then

        MsgBox "Se ha moificado la celda " & Target.Address & "."
    
        ExtraerDatos

    End If
End Sub

    You create a module, and paste this other code (What it does is store the name entered in cell B6 of Sheet1 in a variable. Then it executes a loop from cell B5 comparing the contents of the cells that have some data and evaluating that match the text with the name of Sheet1.

    If it matches, it copies the contents of the cells on the right, years and weight, and pastes them into the corresponding cells on Sheet1

    Sub ExtraerDatos()

    Sheets("Pacientes").Activate
    Sheets("Pacientes").Select

    Dim nombre As String

    nombre = Range("B6").Text

    Sheets("GrupoEtario").Activate
    Sheets("GrupoEtario").Select
    Range("B5").Select
  
      Do Until IsEmpty(ActiveCell)

         If nombre = ActiveCell.Value Then
             Sheets("Pacientes").Range("C6").Value = ActiveCell.Offset(0, 1).Value
             Sheets("Pacientes").Range("D6").Value = ActiveCell.Offset(0, 2).Value
            Exit Do
         End If
         ActiveCell.Offset(1, 0).Select
      Loop
End Sub
    • 3