Choose Function Macro

[Jim]

I need help in creating a simple macro that will make it easier for a user to enter the CHOOSE function into a cell

A co-worker of mine needs to pull financial data that is laid out by month in columns into another sheet in the same workbook. The data is not laid out in the optimum way so inputting CHOOSE functions to pull the correct row from each sheet is tedious (requires 12 different cell inputs into the formula in addition to the index number). So I tried to create a macro that would help. I wanted a macro that would prompt the user for the cell containing the index number and prompt for the first of the twelve cells. The cells are all in adjacent columns in the same row so the macro would construct the rest of the formula

Using the input box feature and a line with ActiveCell.Formula that concatinated a combination of text and variable fields, I was able to construct a macro to do this. But the macro does not work across different worksheets. Everything needs to be in the same sheet. When the ActiveCell (where the formula is the be inserted) is in one sheet, the index number in another, and the data in still another sheet, I can't get the macro to pull in the worksheet nname information to properly construct the formula

I would share the few lines of code I put together for my simple macro but it is on my work PC and I'm now at home. But I thought I would make this post to see if someone could give me some guidance. Any help would be appreciated. In advance, Thanks

Regards,

 

[Frank Kabel]

Hi
problem is probably that you don't reference the worksheet object. So
if you're back to work you mqay post your existing code. Should be
easily to be added this part.

In the meanwhile you may consider a different approach without VBA:
1. Put your values into a range of cells. lets say on sheet1 ranging
from A1:A12. The user can simply change the values in these cells
directly
2. Define a name for this list: Just highlight the cells and goto
'Insert - Name - Define'. Lest say you assign the name 'list'
2.a. If you like you could also create a dynamic list. So the user can
add additional rows. In this case also goto to the name define dialog
but enter the following formula (assumption: your data is in column A)
=OFFSET($A$1,0,0,COUNTA(A:A))

3. If you now put the index number in a cell (on a different worksheet)
you can use the following formula. (assumption cell A1 contains the
index number)
=INDEX(list,A1)

Is this approach feasible for you?

 

[Jim]

Frank

Thank you very much for your reply. Following your instructions, I tried the INDEX function and it works very well. It is also easier/faster than CHOOSE to create the needed formula. Selecting a range rather than having to select twelve different cells will also reduce the probability of formula errors. I also tried the INDEX function without defining a name for the list, and it also works

I would still like to pursue getting the CHOOSE macro to work. Below is my code that works within a worksheet but not with multiple worksheets. Any direction you can provide for getting it to work across worksheets will be greatly appreciated. Thanks again

Regards
Ji

Sub CreateChoose12(

' Keyboard Shortcut: Ctrl+Shift+

Set myIndex = Application.InputBox(prompt:="Select the Index Cell", Type:=8
Set myCell1 = Application.InputBox(prompt:="Select Cell #1 of 12", Type:=8
ActiveCell.Formula = "=Choose(" & myIndex.Address & "," & myCell1.Address & "," & myCell1.Offset(0, 1).Address & "," & myCell1.Offset(0, 2).Address & "," & myCell1.Offset(0, 3).Address & "," & myCell1.Offset(0, 4).Address & "," & myCell1.Offset(0, 5).Address & "," & myCell1.Offset(0, 6).Address & "," & myCell1.Offset(0, 7).Address & "," & myCell1.Offset(0, 8).Address & "," & myCell1.Offset(0, 9).Address & "," & myCell1.Offset(0, 10).Address & "," & myCell1.Offset(0, 11).Address & ")
End Su

----- Frank Kabel wrote: ----

H
problem is probably that you don't reference the worksheet object. S
if you're back to work you mqay post your existing code. Should b
easily to be added this part

In the meanwhile you may consider a different approach without VBA
1. Put your values into a range of cells. lets say on sheet1 rangin
from A1:A12. The user can simply change the values in these cell
directl
2. Define a name for this list: Just highlight the cells and got
'Insert - Name - Define'. Lest say you assign the name 'list
2.a. If you like you could also create a dynamic list. So the user ca
add additional rows. In this case also goto to the name define dialo
but enter the following formula (assumption: your data is in column A
=OFFSET($A$1,0,0,COUNTA(A:A)

3. If you now put the index number in a cell (on a different worksheet
you can use the following formula. (assumption cell A1 contains th
index number
=INDEX(list,A1

Is this approach feasible for you


-
Regard
Frank Kabe
Frankfurt, German


Jim wrote