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

zeno

Board Regular
Joined
Feb 16, 2012
Messages
71
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!
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
One way, assuming the cell containing the drop-down list with the A1 and B1 headers is E1:
Code:
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
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,103
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top