VBA how to manipulate multiple range selection

[dznuts]

Windows 7, xl2010

I am working on a day to day schedule worksheet for work but can not seem to pass this hurdle.

Structure:
This worksheet is meant to be a visual scheduler (like a Gaunt Chart). I have the employees on column A and the dates starting on column B and on. There are control buttons to start my macros and forms for user input.

When a new schedule is added, the user selects the cells that corresponds with the user(s) and date (same dates, in the same column).

Problem:
I need to work on the all the selected ranges (could be 1 or more). I'm using the row value of each selection to reference the names in column A. I am then going to use the copy and paste the selected workers in another worksheet. This data will be used on a form in a list box.

I'm new to vba and even newer to arrays. I'v been trying to set this up as an array but can't seem to get anything to work.

Thanks. This is my first post - Dennis

 

[AlphaFrog]

Problem:
I need to work on the all the selected ranges (could be 1 or more). I'm using the row value of each selection to reference the names in column A. I am then going to use the copy and paste the selected workers in another worksheet. This data will be used on a form in a list box.

I'm new to vba and even newer to arrays. I'v been trying to set this up as an array but can't seem to get anything to work.

Hi Dennis, and welcome to the forum.

The description of your problem is too vague. Are you trying to populate a Listbox control? If yes then are you trying to populate it with names from column A from multiple selected areas? What are you specifically doing with the "another worksheet"?

If you have some code (even if it doesn't work), could you post it? It can sometimes help in providing the needed details.

 

[dznuts]

Thanks for the reply Alpha Frog.
I gave up trying to put the names in a Array and just used

x = ActiveCell.Column 'to get the column number
y = ActiveCell.Row 'to get the row number.

Then used: to get the names and pasted it to another sheet.

fseRow = 2

For Each fserng In Application.Selection
Cells(fserng.Row, 1).Copy Destination:=WSL.Range("A" & fseRow)
fseRow = fseRow + 1
Next fserng

I then used this pasted data for my Form's List box. I wanted to put the names in an array to be used by the Form's List box. Maybe I'll figure it out later.

New Problem:
I am now trying to select a range of cells using two address variables
I'm having trouble with the correct syntex for selecting this range.
-----
FSEsch = Selection.Address
t = ActiveCell.Offset(0, Duration).Address

Range(FSEsch:t).Select 'not working
-----



--
Worksheet description -
This worksheet is setup like at a Gaunt chart. Names are in ColumnA and dates start on B4 and on. I select the corresponding Names/Date cell for the start date of a new schedule. I can pick multiple cells.
I have a command button that starts my macro that gets all info needed for the new schedule.
The Result is:
Expands the start date cell selection via the "duration" variable
Merges all the cells and changes the color.
Puts a label in the expanded cell.
Adds a comment for notes.
Logs all schedules created.

This schedule sheet will provide a cleaner way of looking at the schedule.
The log files will allow me to parse through the data.
Generate reports
Have an email function for changes

-Thanks Dennis

 

[dznuts]

One thing,

I can run:

Range(t).Select

 

[AlphaFrog]

For Each fserng In Application.Selection
Cells(fserng.Row, 1).Copy Destination:=WSL.Range("A" & fseRow)
fseRow = fseRow + 1
Next fserng

I then used this pasted data for my Form's List box. I wanted to put the names in an array to be used by the Form's List box. Maybe I'll figure it out later.

You don't necessarily need to put the names in an array. You can add them to a ListBox control one at a time from a loop.
Example code:

[color=darkblue]Private[/color] [color=darkblue]Sub[/color] UserForm_Initialize()
    [color=darkblue]Dim[/color] cell [color=darkblue]As[/color] Range
    [color=darkblue]For[/color] [color=darkblue]Each[/color] cell [color=darkblue]In[/color] Intersect(Range("A:A").SpecialCells(xlCellTypeConstants), Selection.EntireRow)
        ListBox1.AddItem cell.Value
    [color=darkblue]Next[/color] cell
[color=darkblue]End[/color] [color=darkblue]Sub[/color]

New Problem:
I am now trying to select a range of cells using two address variables
I'm having trouble with the correct syntex for selecting this range.
-----
FSEsch = Selection.Address
t = ActiveCell.Offset(0, Duration).Address

Range(FSEsch:t).Select 'not working
-----

Try something like this...

Range(FSEsch [COLOR="Red"]& ":" &[/COLOR] t).Select

Or this (if I understand correctly what you are trying to do)...

ActiveCell.Resize(, Duration).Select

Tip: You don't need to .Select a range of cells to do something with that range. Using .Select can greatly slow down your code.

   [color=green]'This[/color]
    Range("A1:A10").Copy

    [color=green]'...is much more efficient than this...[/color]
    Range("A1:A10").Select
    Selection.Copy

 

[dznuts]

Thanks for the pointers. Helped a lot.

I have almost completed my scheduler worksheet. I have hopefully one more problem.

I have a range of data in A2: A5. I want to compare C2.Value. If C2 equals any value in A2: A5, i wan to paste that data in A6. I've tried different loops but can't seem to figure this one out.

--Showed my boss this tool and he can't wait to use it.
-Thanks for the help

 

[AlphaFrog]

Thanks for the pointers. Helped a lot.

I have almost completed my scheduler worksheet. I have hopefully one more problem.

I have a range of data in A2: A5. I want to compare C2.Value. If C2 equals any value in A2: A5, i wan to paste that data in A6. I've tried different loops but can't seem to figure this one out.

--Showed my boss this tool and he can't wait to use it.
-Thanks for the help

    [color=darkblue]If[/color] [color=darkblue]Not[/color] Range("A2:A5").Find(Range("C2").Value, , xlValues, xlWhole, , , [color=darkblue]False[/color]) [color=darkblue]Is[/color] [color=darkblue]Nothing[/color] [color=darkblue]Then[/color]
        [color=green]'Match found[/color]
        Range("A6").Value = "That data"
    [color=darkblue]Else[/color]
        [color=green]'No Match[/color]
        Range("A6").ClearContents
    [color=darkblue]End[/color] [color=darkblue]If[/color]

 

[dznuts]

Thanks for the tips Alphafrog.
Than intersect code worked perfect.

I have one more problem. I have a cancel CommandButton on my userform. Right now it will run "Unload Me" when clicked. This works well for closing down the userform but I would like the Cancel CommandButton to stop the program.

I initiate userform in my Subroutine by "userform.show". When I cancel it, it closes the userform and runs the next line of code in my Subroutine.

What I would like, is to end the whole Subroutine when I press cancel in my userform.

Thanks.

 

[AlphaFrog]

Can you post your code for the cancel button?