Auto Offset Column ???

[AmyJade]

Hi,

I don't know where to begin!!! I'm trying to have the user be able to do the following:

When the user makes a selection from a drop down list box, the specified column (range of cells) will automatically be filled with the selection. I have 40 items to choose from in the listbox, but the user can only choose 10 and fill the 10 specified columns. The items can be selected in any order so I can't assign ranges to each of the 40 items.

Any thoughts?

 

[SkipVought]

AmyJade,

The user makes a selection from a listbox. THis selection is WHAT? The value that is "copied" to some range?

How is the range specified?

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[AmyJade]

Hi,

The problem is that the listbox has 40 items that can be selected in any order. How do I find the first non blank column (I have headings for the columns to be filled), starting from say Range: A3 and have excel automatically fill each column to the right of each selected item? For example, Item3 is selected to fill in B3:B65, but then Item10 is selected to be filled in C3:C65.

AmyJade

 

[SkipVought]

Amy,

I do not understand what the problem is. You are assuming that I know stuff about your application, the data and the process that I do not know.

Please explain -- paint a picture for me.

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[AmyJade]

Sorry for being so vague.....

This is what I want to happen:

I'm creating a portfolio optimizer that allows the user to select from a list of funds which funds they wish to optimize. Each time they select a fund from the drop down list their historical returns (which are copied from another worksheet) gets pasted into the active sheet into the first column (say Range: A1). The user can select at random the funds they wish to optimize. Each time a fund is selected from the drop box, they are filled in the next column to the right to the one already filled with historical returns.

AmyJade

 

[SkipVought]

So what code do you have so far?

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[AmyJade]

Thanks for showing interest in my problem. I've decided to re-design my program to make it more user friendly. The coding that I have is much too difficult to modify in the future when other programmers update this optimizer. I will be back shortly with my new coding.

Thanks SO Much!!!

AmyJade

 

[SkipVought]

Hi,

Keep this in mind...

with activesheet.usedrange
  lastrow = .row + .rows.count - 1
  lastcol = .column + .columns.count - 1
end with

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884