Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Selecting next date from column A

0

Hi. I still have a problem with a sheet you helped me with previously. I hope more information will help you solve this for me.I believe the word 'Static' in the macro 'reset_values' resets the variable 'nd' back to 0 then to 1 every time it is run. ND simply refers to the Next Date if that conflicts with any Excel keywords.

In the file protection allows only white cells to be modified. There is no password. If you enter 1 to 7 in the topmost row then run the macro a few times you will see the aim of the sheet. 

Column A is the dates and the columns after the 'board' contains the formulas that controls the output. It works perfectly apart from resetting 'nd' to 0/1 every time.

I'm sorry to be a pain but I feel I am almost there this time. Thank you once again for your time.

excelmacros-vba
MS Office Version: Office365
posted by: MrEMann (rep: 20) on Wed Jun 10, 2020 at 3:23 pm
Answer
Discuss

Answers

0
Selected Answer

Hello, MrEMann

Please try the file I attached. I take off the row where the static variable is declared and I added two rows (one for read, one for write variable nd). In cell B1 I store the variable nd in order not to losse its value each time the file is closed

Sub Reset_values()

    nd = Range("B1")
    ActiveSheet.Unprotect
    Range("E5:K5").ClearContents
    Range("B25").Select
    ActiveCell.Formula = "=A" & nd
    nd = nd + 1
    Range("B1") = nd
    Range("E5").Select
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    ActiveSheet.Protect

End Sub

I hope this help.

Regards
Basilio

posted by: Basilio (rep: 105) on Thu Jun 11, 2020 at 8:15 pm
Discuss

Discussion

Thank you Basilio. I have run and saved your version a couple of times and it works perfectly. Thank you again for your persevence.
MrEMann (rep: 20) Jun 11, '20 at 9:50 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login