how to use a cell value in an active column in a named row range in VBA

Question

In a worksheet, there are two named ranges, each of which only contains a row, e.g. Range1 = Range("B5:H5") and Range2 = Range("B9:H9"). My question is: how can I reference a cell in Range1, say C5, and a cell in Range2, say C9 in VBA so that I can do something with the values in these two cells? The VBA should only run for the active column. Thank you in advance.

Answer 1

Maybe you should see this link.

How to avoid using Select in Excel VBA macros

As Siddarth stated,

Two Main reasons why .Select/.Activate/Selection/Activecell/Activesheet/Activeworkbook etc... should be avoided

It slows down your code.
It is usually the main cause of runtime errors.

How do we avoid it?

1) Directly work with the relevant objects

Consider this code

Sheets("Sheet1").Activate
Range("A1").Select
Selection.Value = "Blah"
Selection.NumberFormat = "@"

This code can also be written as

With Sheets("Sheet1").Range("A1")
    .Value = "Blah"
    .NumberFormat = "@"
End With

2) If required declare your variables. The same code above can be written as

Dim ws as worksheet

Set ws = Sheets("Sheet1")

With ws.Range("A1")
    .Value = "Blah"
    .NumberFormat = "@"
End With

Answer 2

Use can

Range1.offset() 

method to refer adjacent cell

You can refer here for detail .

Answer 3

Would this work?

Range("Range1").Cells(1, 1).Select     'Selects Range("B5") - first cell in Range1
Range("Range1").Cells(1, "A").Select   'Also selects first cell in the named range

'copies cell 2 (C9) from Range2 into cell 2 (C5) of Range1; .Cells(row, col)
Range("Range1").Cells(1, 2) = Range("Range2").Cells(1, 2)

Answer 4

By using the Cells method, you can specify the appropriate row using Range1.Row (and Range2.Row), and the appropriate column using (if I understand you correctly) Selection.Column.

So perhaps something like:

Dim Range1 As Range
Dim Range2 As Range
Set Range1 = Range("B5:H5")
Set Range2 = Range("B9:H9")

'Display the value in row 5 for the current column
MsgBox Cells(Range1.Row, Selection.Column).Value
'Display the value in row 9 for the current column
MsgBox Cells(Range2.Row, Selection.Column).Value

'Change row 9 to be the value from row 5
Cells(Range2.Row, Selection.Column).Value = Cells(Range1.Row, Selection.Column).Value

'Display the updated value in row 9 for the current column
MsgBox Cells(Range2.Row, Selection.Column).Value