VBA: Choose with drop down list column A or B to calculate

[zeno]

Hello,
I have 2 columns with a variable number of rows in Excel. With VBA I want to perform a calculation on either column A or B.
I make that selection through another specific cell where I define a drop-down list with titles of each column to choose from. Through the selection of a column in that cell VBA will make its calculation.
How can I indicate this?
I hope my question is clear.
Thanks!

 

[tigeravatar]

One way, assuming the cell containing the drop-down list with the A1 and B1 headers is E1:

Sub tgr()
 
    Dim rng As Range
 
    Set rng = Range("A1:B1").Find(Range("E1").Value)
    If Not rng Is Nothing Then
        Set rng = Range(rng, Cells(Rows.Count, rng.Column).End(xlUp))
        'Perform calculation
        MsgBox rng.Address
    End If
 
End Sub

 

[zeno]

Thank you for your response.
I get however message 'run-time error 1004: range of object _Global failed' on line

Set rng = Range(rng, Cells(Rows.Count, rng.Column).End(xlUp))
Why would that happen?