Autofill XltoLeft

Bench

Board Regular
Joined
Aug 27, 2009
Messages
134
Hi,

Need some help with either an XltoLeft or XltoRight Autofill

I have a userform where the user selects their shift start time and then their shift end time both from inbuilt combobox to ensure uniformity.

For example:
Shift Start Time 9
Shift End Time 17

On pressing submit, in this particular example, this will place a 1 under 9 on the spreadsheet and 1 under 17 on the spreadsheet.
(The spreadsheet has 8,9,10,11,12 etc running horizontally)

What i need to do is have ones underneath the hours in between the users selction, in this example, 10,11,12,13,14,15 & 16.

I assume the easiest way to do this is

Code:
Range(ActiveCell, ActiveCell.End(xlToLeft)).Select
or
Range(ActiveCell, ActiveCell.End(xlToRight)).Select

Dependant on which ComboBox value i have outputting last, this would then be followed by an autofill command.

What i can't workout is how to then turn this into an autofill command to put the required ones in as described earlier.

Any ideas, appreaciate your help as always

Thanks

Ben
 

Some videos you may like

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

Bench

Board Regular
Joined
Aug 27, 2009
Messages
134
Hi,

Need some help with either an XltoLeft or XltoRight Autofill

I have a userform where the user selects their shift start time and then their shift end time both from inbuilt combobox to ensure uniformity.

For example:
Shift Start Time 9
Shift End Time 17

On pressing submit, in this particular example, this will place a 1 under 9 on the spreadsheet and 1 under 17 on the spreadsheet.
(The spreadsheet has 8,9,10,11,12 etc running horizontally)

What i need to do is have ones underneath the hours in between the users selction, in this example, 10,11,12,13,14,15 & 16.

I assume the easiest way to do this is

Code:
Range(ActiveCell, ActiveCell.End(xlToLeft)).Select
or
Range(ActiveCell, ActiveCell.End(xlToRight)).Select

Dependant on which ComboBox value i have outputting last, this would then be followed by an autofill command.

What i can't workout is how to then turn this into an autofill command to put the required ones in as described earlier.

Any ideas, appreaciate your help as always

Thanks

Ben

Managed to sort it this way.

Code:
    Range("B" & Rows.Count).End(xlUp).Select
    ActiveCell.End(xlToRight).Select
'    move one cell to the right from the last used cell
    ActiveCell.Offset(0, 1).Select
        
    Do Until IsEmpty(ActiveCell.Value) = False
    If IsEmpty(ActiveCell.Value) Then
    ActiveCell.FormulaR1C1 = "1"
    ActiveCell.Offset(0, 1).Select
    ElseIf IsEmpty(ActiveCell.Value) = False Then
    ActiveCell.Offset(0, 1).FormulaR1C1 = "1"
    ActiveCell.Offset(0, 1).Select
    End If
    Loop
 

Watch MrExcel Video

Forum statistics

Threads
1,123,295
Messages
5,600,789
Members
414,405
Latest member
Zaurb

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
Top