Naming Ranges with code

  • Thread starter Thread starter Les
  • Start date Start date
L

Les

Hello all, i have a report that has rows added to it and it has both a
columnm that is a range name, so the new cell has to be added to the range
and secondly within that range the cell has it's own name dependant on what
the user puts in.

C D E
Desc. Code P/O

Bonnet Hood "HoodPo">Range name
Bumper(Font) BF "BFPo"
Bumper(Rear) BR "BRPo"
Front end FRT "FRTPo"
IMC-SAS IMC "IMCPo"
New Part Test "TestPo"-- New range Name for cell.

The old PORange in column "E" (E1:E5) must expand to (E1:E6)

The "code" will be put in by the user.
 
More info. However, probably best to have the name defined by the offset
formula

insert>name>define>name it>in the refers to box type
=offset($C$1,1,0,counta($C:$C)-1,1)
Now when you add/subtract a row of info the range will auto adjust.
For the data, user can input or use a macro using inputbox
 
Hello all, i have a report that has rows added to it and it has both a
columnm that is a range name, so the new cell has to be added to the range
and secondly within that range the cell has it's own name dependant on what
the user puts in.

    C                    D           E
 Desc.               Code       P/O

Bonnet              Hood      "HoodPo">Range name
Bumper(Font)    BF          "BFPo"
Bumper(Rear)   BR           "BRPo"
Front end          FRT        "FRTPo"
IMC-SAS           IMC        "IMCPo"
New Part           Test       "TestPo"-- New range Name for cell.

The old PORange in column "E" (E1:E5) must expand to (E1:E6)

The "code" will be put in by the user.
Click to expand...

Hi Les

Put this in the code sheet for the desired worksheet

Private Sub Worksheet_Change(ByVal Target As Range)
Dim vName As String
Dim tRange As Range
If Target.Column = 4 Then
vName = Target.Value
Set tRange = Range("E1", Cells(Target.Row, 4))
vName = vName & "Po"
tRange.Name = vName
Target.Offset(0, 1) = vName
End If
End Sub

// Per
 
Back
Top