CODEWITHSUNDEEP
vbaexcel
cdamian
cdamian

Reputation: 5

Return the Row of of an address that is a value in a cell

I have several cells that contain the addresses of other cells, such as:

I would like to assign the row of each of these values to a variable and append a different column. Ive tried to do it this way with no success:

    Dim X1 As String
    Dim X2 As String
    Dim Y1 As String
    Dim Y2 As String
    Dim ChtSheet As Chart

    Set ChtSheet = ActiveWorkbook.Charts.Add
.    


     X1 = "C" & Sheets("Sheet1").Range("K6").Rows.Value      'I want X1 = "$C$95"
        X2 = "C" & Sheets("Sheet1").Range("K7").Rows.Value      'I want X2 = $C$137

    Y1 = Sheets("Sheet1").Range("K6").Value  'where the value in this cell is $F$95
    Y2 = Sheets("Sheet1").Range("K7").Value  'Where the value in this cell is $F$137

Any help is greatly appreciated.

Thanks.

Upvotes: 0

Views: 227

Answers (1)

Excelosaurus
Excelosaurus

Reputation: 2849

Let's concentrate on K6 containing $F$95:

X1 = Sheet1.Range(Sheet1.Range("K6").Value).EntireRow.Cells(1, "C").Address

will yield "$C$95".

Notice that Sheet1 is assumed to be the CodeName of your worksheet named "Sheet1" on the Excel tab. The CodeName is a better way to refer to worksheets, as it remains the same even if your rename the worksheet from Excel. You can view and change a worksheet's CodeName from the VBA editor, by clicking on the worksheet in the Project Explorer and displaying its properties (F4); just assign to the (Name) property.

The principle above is to use the value in K6, which is an address, to obtain the range pointed to by this address, then consider the whole row's Cells at position (1, "C"), i.e. row 1, column "C". Whatever the column mentioned in K6, you'll always end up with column "C" in the result.

Not sure what you want to do with X1; you typically wouldn't need this string anyways because you could work with the range:

Dim myRange As Excel.Range

Set myRange = Sheet1.Range(Sheet1.Range("K6").Value).EntireRow.Cells(1, "C") 'Who cares about the address :-)
'... do something with this range...

Upvotes: 1

Related Questions