Home / Questions / 551210
Accepted
eMihl
Asked: 2022-08-12 02:14:59 +0800 CST 2022-08-12 02:14:59 +0800 CST

VBA - Locate cell by header name and row name, and not by letter/number

  • 772

I have been trying for several days to figure out how to assign a value to a specific cell using VBA, but instead of using the excel coordinates (B4, D9..) or (5.6 - 6.9 - etc.), I use the name of the row and column, that is, use the first row as header, and the first column as index, to be able to dynamically locate the cells that interest me, since they will not always be located at the same coordinates.

That is, instead of doing this:

Range(Cells(4, 5), Cells(8, 9))
Range("C4")
Cells(4, 6).Value = variable_x

Being able to do something like this:

Cells("ID374839", "SALARIO")

Where ID374839 would be the row ID, and "SALARY" the column header.

Thank you very much in advance.

vba

3 Answers

  1. What you want can be achieved by assigning each column to constants and thus dynamically refer to them. I show you with an example:

    'Asignas el nombre de una columna a una constante.
Const COLUMNA As String = "A"

    Now assigning the name of a row to a variable, for example:

    'Asignamos el numero de fila a un Entero Largo
Dim fila As Long

    This way, we can dynamically reference cells like this:

    Celda.Range(COLUMNA & fila)
    • 1
  2. Best Answer

    After a few days I found the most correct way to find a field from the values ​​of the header and the first column, I expose it below:

    Sheets(1).Select

Range("A5").Select
fila = Range("A5", Range("A5").End(xlDown)).Find("Antonio", ActiveCell, , , xlByRows).Row

Range("B4").Select
columna = Range("B4", Range("B4").End(xlToRight)).Find("PRIMAS", ActiveCell, , , xlByColumns).Column

Cells(fila, columna).Copy Range("H11")

    In the variable "row" I store the index of the row where the worker "Antonio" is located (in this case the name would be his ID). And in the Column variable I store the index of the column where the "Premiums" column is located. In this way I will know Antonio's prime (100) doing, for example, "Cells(row, column).Copy".

    Example table

    The utility is that the workers change and can't always be located in the same row, so I can dynamically locate them and modify the values ​​I need. Later, I use the fetched values ​​to get the value of the located cell and copy it somewhere else.

    For more information on how to work with this function, which is called "Range.Find", I recommend the page where I found this solution:

    Range.Find in VBA

    • 0

  3. The Cells object is rarely used as it offers few options. The most logical thing is to use Range. See this article: Object Range

    In any case, what you need is this?

    Public Sub Rango()
Dim Rango As Range

Set Rango = Range("A2")
Rango.Name = "NombreRango"
Rango.Value = 12345678

    enter image description here

    • 0