How do I create a macro in excel that varies depending on the cell?

Question

I am working on a workbook in excel that has several worksheets within it.  On the last page I have created a template to be filled in from numbers on the previous page.  The numbers will be taken from all 12 of the previous pages.  However, I would like to be able to select a different cells at will to perform this action on.  i.e. Sometimes on the first name on the list, sometimes on the last name.  How do I do this?  I hope I explained this well. If not ask.

devilishblueyes · Accepted Answer

You'll need to use an InputBox.  Go to the Visual Basic Editor (Alt + F11), then use help to look up the InputBox Method.  Look at the last example for the InputBox method.  That will allow you to select a cell range.  

Dim myCell As Range

Worksheets("Sheet1").Activate
Set myCell = Application.InputBox( _
    prompt:="Select a cell", Type:=8)

Then set the cell you want equal to myCell.

Type 8 specifies a range object.

By the way, I was looking at your sample code.  You could way simplify that by using a For Each...Next Loop.  You also don't need all of those select statements.

For example:

For Each x In [B5:B20]

'Your code here.

Next x

Trending News

How do I create a macro in excel that varies depending on the cell?

1 Answer