range determined by cell value

[Steve64]

Hi

Range("M6").Select
Selection.AutoFill Destination:=Range(“M6: ?”), Type:=xlFillDefault


Where ? is I need this to be a cell determined by the value in L6.

Ie L6 = 4 then ? = P6.

regards

[GarysStudent]

Range can input a String. Lets put one together:



Sub dural()
Dim s As String
s = "M6:M" & Range("L6").Value
Range("M6").Select
Selection.AutoFill Destination:=Range(s), Type:=xlFillDefault
End Sub

[Steve64]

I can see the logic but the autofill dosn't, fill!

[GarysStudent]

Insert:

MsgBox s

after the value has been calculated to see what is wrong.

[Steve64]

by the way, the data in m6 is a date..

[Steve64]

Insert:

MsgBox s

after the value has been calculated to see what is wrong.

msgbox = M6:M3

[Steve64]

ok got to work of sorts BUT the autofill is pasting all data in column M, I need it to go across ie rows so s should be "M6:P6" if L6 is 4 at presant its "M6:M4" and pasting upwards, if you see what I mean.

[GTO]

A workbook attached (preferably in .xls format) with what you have (on one sheet), and what you would like to have (on another sheet) would be nifty...

[Steve64]

A workbook attached (preferably in .xls format) with what you have (on one sheet), and what you would like to have (on another sheet) would be nifty...

ok, easy to drag the data across but i need to do it in vba.
the value in L3 is varible.

[GTO]

Hi Steve,

Is this a step in the right direction? I wasn't sure what is supposed to fire this, but using the worksheet's change event, maybe:
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address(0, 0, , -1) = Me.Range("M3").Address(0, 0, , -1) Then
If IsNumeric(Me.Range("L3").Value) Then
Target.AutoFill Destination:=Target.Resize(, Int(Me.Range("L3").Value))
End If
End If
End Sub
Hope that helps,

Mark

[Steve64]

thats the stuff, thanks.

[Steve64]

One question, how can I get the dates to show only workdays?

[GTO]

Hi Steve,

This starts at an offset, in case the date in M3 is a Saturday or Sunday.
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim n As Long
Dim dtmMyDate As Date

Application.EnableEvents = False
If Target.Address(0, 0, , -1) = Me.Range("M3").Address(0, 0, , -1) Then
If IsNumeric(Me.Range("L3").Value) And Me.Range("L3").Value >= 1 Then
If IsDate(Me.Range("M3").Value) Then
'// IF we can safely clear the column to the right... //
Target.Offset(, 1).Resize(, Target.Parent.Columns.Count - Target.Column).ClearContents

If Weekday(Me.Range("M3").Value, vbMonday) > 5 Then
dtmMyDate = CDate(Me.Range("M3").Value)
Do While Weekday(dtmMyDate, vbMonday) > 5
dtmMyDate = dtmMyDate + 1
Loop
Me.Range("M3").Offset(, 1).Value = dtmMyDate
Else
Me.Range("M3").Offset(, 1).Value = CDate(Me.Range("M3").Value)
End If

For n = 2 To Int(Me.Range("L3").Value)
Debug.Print Weekday(Me.Range("M3").Offset(, n - 1).Value, vbMonday)
If Weekday(Me.Range("M3").Offset(, n - 1).Value, vbMonday) = 5 Then
Me.Range("M3").Offset(, n).Value = CDate(Me.Range("M3").Offset(, n - 1) + 3)
ElseIf Weekday(Me.Range("M3").Offset(, n - 1).Value, vbMonday) = 6 Then
Me.Range("M3").Offset(, n).Value = CDate(Me.Range("M3").Offset(, n - 1) + 2)
Else
Me.Range("M3").Offset(, n).Value = CDate(Me.Range("M3").Offset(, n - 1) + 1)
End If
Next
End If
End If
End If
Application.EnableEvents = True
End Sub
Hope that helps,

Mark

[GTO]

...except of course you can leave out the Debug...