Make a string a value from the current row, and the column a named range

[RockEd]

Hello,

This is a very basic question but I don't know what the answer is!

I have a the current code:

With ActiveCell
        ClientName = ActiveSheet.Cells(.Row, 2)

Client_name_1.Caption = ClientName

End with

The issue I have is that I want to be able to add new columns into my spreadsheet without fear that I'm going to break the code.

In cell B1 I have named cell (the 'named range') - "Client_name_column" however this code won't run:

With ActiveCell
        ClientName = ActiveSheet.Cells(.Row, "Client_name_column")

Client_name_1.Caption = ClientName

End with

Is there a way around this?

thanks

 

[My Aswer Is This]

Please in just words tell me what your overall objective is.

Here is an example of part of what you may want.

Sub Client_Name()
'Modified 11/8/2022  12:36:50 PM  EST
Dim Client_Name As String
Client_Name = Sheets("Master").Range("G1").Value
MsgBox Client_Name
Application.ScreenUpdating = True
End Sub

 

[Fluff]

If B1 has the columns number or letter you could use

With ActiveCell
        ClientName = ActiveSheet.Cells(.Row, Range("Client_name_column").Value)

Client_name_1.Caption = ClientName

End With

 

[RockEd]

If B1 has the columns number or letter you could use

With ActiveCell
        ClientName = ActiveSheet.Cells(.Row, Range("Client_name_column").Value)

Client_name_1.Caption = ClientName

End With

Thank you, but B1 doesn't have the column letter or number however I've worked it out now - I just needed to use .column at the end instead of .value; like this:

With ActiveCell
        ClientName = ActiveSheet.Cells(.Row, Range("Client_name_column").column)

Client_name_1.Caption = ClientName

End With

 

[Fluff]

Glad you sorted it & thanks for letting us know.