how to convert to VBA - Weekending date

[Bryan123]

im trying to create a code to get the weekending date. However, there is an error when i try to run it.

=A1+(7-WEEKDAY(A1)) - this is the code i'd like to convert to vba


WE.Value = transdate.Value + (8 - Weekday(transdate.Value))
-----****Note
WE.value is a textbox where the weekending date should be recorded. transdate.value is the transaction date.
Please assist.
Thanks
Bryan

 

[Joe4]

Try something like:

[COLOR=#333333]WE.Value = transdate.Value + (7 - Application.WorksheetFunction.Weekday(transdate.Value))[/COLOR]

 

[Bryan123]

Hi,
thanks for the quick revert. I still have error - Unable to get the weekday property of the worksheetfuction class. I dont know where to position that code. My program when run, will automatically fill in the transdate and what i would like to achieve is also fill in the Weekending date from the transdate. Dont know where to put it.
Thank you
Bryan

 

[Joe4]

Try commenting out that line of code, and add these lines and let me know what they return:

MsgBox transdate.Value
MsgBox IsDate(transdate.Value)