Forum Discussion

minhhai91's avatar
minhhai91
Copper Contributor
Jun 24, 2021
Solved

Mapping between 2 sheets: Assign the value of a row according to the values of a column

Hello,

 

I want to map between 2 sheets, that is to say that in my first sheet (synthesis) I have a list of Group Number and in my second group I have a list of entity attached to these groups and I want to be able to assign on my synthesis sheet the name of the entity through a macro in VBA.

 

 

 

Thank you in advance.

 

Frédéric

Macros and VBA
  • HansVogelaar's avatar
    HansVogelaar
    Jun 24, 2021

    minhhai91 

    You can do this without VBA: in A6, enter the formula

    =VLOOKUP(Synthèse!B6,Mapping!$A$2:$B$3,2,FALSE)

    and fill down.

    If you prefer a macro:

    Sub RemplirEntité()
    Dim m As Long
    With Worksheets("Synthèse")
        m = .Range("B" & .Rows.Count).End(xlUp).Row
        With .Range("A6:A" & m)
            .Formula = "=IFERROR(VLOOKUP(Synthèse!B6,Mapping!$A$2:$B$3,2,FALSE),"""")"
            .Value = .Value
        End With
    End With
End Sub

2 Replies

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Jun 24, 2021

    minhhai91 

    You can do this without VBA: in A6, enter the formula

    =VLOOKUP(Synthèse!B6,Mapping!$A$2:$B$3,2,FALSE)

    and fill down.

    If you prefer a macro:

    Sub RemplirEntité()
    Dim m As Long
    With Worksheets("Synthèse")
        m = .Range("B" & .Rows.Count).End(xlUp).Row
        With .Range("A6:A" & m)
            .Formula = "=IFERROR(VLOOKUP(Synthèse!B6,Mapping!$A$2:$B$3,2,FALSE),"""")"
            .Value = .Value
        End With
    End With
End Sub

Resources