We help IT Professionals succeed at work.

Excel VBA Populate Column with value from Date Picker

535 Views
Last Modified: 2017-04-06
Hi All,

I have an excel workbook, with a MSConDTPicker on Sheet 1, I want the user to select the week start date and then have the value entered into all cells in column W on Sheet 2.

How do I do this?

Thank you
Comment
Watch Question

CERTIFIED EXPERT
Top Expert 2016

Commented:
Hi,

pls try
Private Sub DTPicker1_Change()
Sheets("Sheet2").Range("W:W").Value = DTPicker1.Value
End Sub

Open in new window

Regards
ShumsManaging Director/Excel VBA Developer
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
Instead of filling dates in whole Column, try below:
Assigned linked cell for Date Picker in Sheet 1, in my case I assigned F10, change accordingly and change the last row to be filled.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim SrcWs As Worksheet
Dim SrcLR As Long
Set SrcWs = Worksheets("Sheet2")
SrcLR = SrcWs.Range("A" & Rows.Count).End(xlUp).Row 'Change "A" to the last row to be filled

If Target.Cells.Count > 1 Then Exit Sub
    If Not Application.Intersect(Range("F10"), Target) Is Nothing Then
    SrcWs.Range("W2:W" & SrcLR).Value = ActiveSheet.Range("F10").Value
    End If

End Sub

Open in new window

Kindly note above code should be in Sheet1 module

Author

Commented:
HI,

I've tried to include it in another macro --

Sub ConvertStartDate()
LastRW = Range("A" & Rows.Count).End(xlUp).Row
Sheets("T+A EXTRACT").Range("W2:W45").Value = DTPicker1.Value
Range("V2:V" & LastRW).Formula = "=RIGHT(RC[-19],2) & ""/"" & MID(RC[-19],5,2) & ""/"" & LEFT(RC[-19],4)"
End Sub

Open in new window


As ultimately I want the user to click a button for a number of commands to run...

But I get a Object expected error

Sheet 1 is called 'INSTRUCTIONS'

Thanks

Author

Commented:
How do you assign a linked cell to the datepicker?

Thanks
ShumsManaging Director/Excel VBA Developer
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
Try below:
Private Sub DTPicker1_Change()
Dim SrcWs As Worksheet
Dim SrcLR As Long
Set SrcWs = Worksheets("T+A EXTRACT")
SrcLR = SrcWs.Range("A" & Rows.Count).End(xlUp).Row 'Change "A" to the last row to be filled
SrcWs.Range("W2:W45").Value = DTPicker1.Value
SrcWs.Range("V2:V" & SrcLR).Formula = "=RIGHT(RC[-19],2) & ""/"" & MID(RC[-19],5,2) & ""/"" & LEFT(RC[-19],4)"
End Sub

Open in new window

CERTIFIED EXPERT
Top Expert 2016

Commented:
Could you send a dummy?
CERTIFIED EXPERT
Top Expert 2016

Commented:
in any case the formula should be R1C1

SrcWs.Range("V2:V" & SrcLR).FormulaR1C1 = "=RIGHT(RC[-19],2) & ""/"" & MID(RC[-19],5,2) & ""/"" & LEFT(RC[-19],4)"

Open in new window

Author

Commented:
Hi Guys,

I've attached an example of what I'm trying to do... its not working so far :)

ThanksT-A-Macro--2-.xlsm
Managing Director/Excel VBA Developer
CERTIFIED EXPERT
Distinguished Expert 2018
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
Shums, thats FANTASTIC thank you....

One more question, now I have column X in which I have written the formula =IF(V2<>W2,"Y","N")

The problem that I have is that it's not working, is this because the data in V is a formula?

Code looks like -

Private Sub DTPicker21_Change()
Dim SrcWs As Worksheet
Dim SrcLR As Long
Set SrcWs = Worksheets("T+A EXTRACT")
SrcLR = SrcWs.Range("A" & Rows.Count).End(xlUp).Row 'Change "A" to the last row to be filled
SrcWs.Range("W2:W" & SrcLR).Value = DTPicker21.Value
SrcWs.Range("V2:V" & SrcLR).Formula = "=RIGHT(RC[-19],2) & ""/"" & MID(RC[-19],5,2) & ""/"" & LEFT(RC[-19],4)"
SrcWs.Range("X2:X" & SrcLR).Formula = "=IF(RC[-2]<>RC[-1],""Y"",""N"")"
End Sub

Open in new window


Thanks mate
CERTIFIED EXPERT
Top Expert 2016

Commented:
then try
Private Sub DTPicker21_Change()
Dim SrcWs As Worksheet
Dim SrcLR As Long
Set SrcWs = Worksheets("T+A EXTRACT")
SrcLR = SrcWs.Range("A" & Rows.Count).End(xlUp).Row 'Change "A" to the last row to be filled
SrcWs.Range("W2:W" & SrcLR).Value = DTPicker21.Value
SrcWs.Range("V2:V" & SrcLR).FormulaR1C1 = "=RIGHT(RC[-19],2) & ""/"" & MID(RC[-19],5,2) & ""/"" & LEFT(RC[-19],4)"
SrcWs.Range("X2:X" & SrcLR).FormulaR1C1 = "=IF(RC[-2]<>RC[-1],""Y"",""N"")"
End Sub

Open in new window

Author

Commented:
Sorry Rgonzo1971 - no joy.

Thanks
ShumsManaging Director/Excel VBA Developer
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
Changed;
Private Sub DTPicker21_Change()
Dim SrcWs As Worksheet
Dim SrcLR As Long
Set SrcWs = Worksheets("T+A EXTRACT")
SrcLR = SrcWs.Range("A" & Rows.Count).End(xlUp).Row 'Change "A" to the last row to be filled
SrcWs.Range("W2:W" & SrcLR).Value = DTPicker21.Value
SrcWs.Range("V2:V" & SrcLR).FormulaR1C1 = "=RIGHT(RC[-19],2) & ""/"" & MID(RC[-19],5,2) & ""/"" & LEFT(RC[-19],4)"
SrcWs.Range("X2:X" & SrcLR).FormulaR1C1 = "=IF(RC22<>RC23,""Y"",""N"")"
End Sub

Open in new window

T-A-Macro--V3.xlsm
CERTIFIED EXPERT
Top Expert 2016
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
FANTASTIC guys - thank you so much  :)
ShumsManaging Director/Excel VBA Developer
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
You're Welcome! Glad we're able to help you
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.