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

Irregular dates to enter automatically

0

Hello. I have a macro and I can see what is wrong, but I cannot work out a solution. My macro clears a small range and then goes to the beginning for the next due date. I then enter the next date manually into B25. I thought the following would take the next date from the A column starting from A53. When I run the macro I can see it keeps returning to A53 and not A54, A55 and so on.

Sub Reset_values()
'
' Reset values and take next date from cell A53 onwards and place in cell B25
'
    Dim nd As Integer
    nd = 53
    Range("E5:K5").Select
    Selection.ClearContents
    Range("B25").Select
    ActiveCell = "=A" & nd
    Range("E5").Select
End Sub

nd is the variable for the row number and it keeps returning to A53 every time it runs. Obviously I am going about this all wrong so the correct coding would be much appreciated. Thank you so much for looking at this. Hope you are all staying safe.

excel
MS Office Version: Office365
posted by: MrEMann (rep: 20) on Sat May 16, 2020 at 11:12 am
Answer
Discuss

Answers

0
Selected Answer

Hello, MrEMann.
You can solve it declaring a static variable, as follows:

Sub Reset_values()
   Static nd As Integer
    nd=nd+1
    Range("E5:K5").Select
    Selection.ClearContents
    Range("B25").Select
    ActiveCell = "=A" & 52+nd
    Range("E5").Select
End Sub

In this way, first time you run the macro you are going to start in A53, after that each time you run the macro you go A54, A55 and go on. This is valid for the same session in Excel, every time you close and open Excel you are to going to start in A53 again.

I hope this be helpfull for you. Please, let me know if it works.

posted by: Basilio (rep: 105) on Sat May 16, 2020 at 2:53 pm
Discuss
0

@Basilio made a very interesting suggestion which I didn't think of. But because I understood your problem differently the solution I programmed is different too. Take a look.

Sub Reset_values()
    ' Reset values and take next date from cell A53 onwards and place in cell B25

    Dim Rl          As Long                 ' last used row in column A

    Range("E5:K5").ClearContents            ' no need to 'Select' anything

    ' set Rl to the number of the last used row in column A
    '   but not smaller than 53
    Rl = Application.Max(Cells(Rows.Count, "A").End(xlUp).Row, 53)

    ' address cells as 'Cells' and a range as 'Range'
    Cells(25, "B").Value = Cells(Rl, "A").Value
    Cells(5, "E").Select
End Sub

Basilio thought that you perhaps paste some data into A53:A? and then need to read the date from there, one row after the other. In your next session - perhaps the next day - you would need the same again. You must close Excel to restart the counter from 53.

The above code presumes that you accumulate data in row 53 and below and that you always want the last date in that column to be read into B25. That date will be in row 53 only when you start your data collection and is unaffected by system restarts.

Which code works better for you is entirely a matter of your work flow. There is some overlap between the two procedures' functionality but, in essence, they are completely different.

posted by: Variatus (rep: 4889) on Sat May 16, 2020 at 8:32 pm
Discuss

Discussion

Hi. Thank you for your input. I had accepted Basilio as it seems to work as I had hoped. I will need about a week to be absolutely sure that is so. I tried your suggestion and it works apart from the Dim Rl As Long instruction/statement. The reason is that I entered the next couple of dates then dragged them down until my finger got tired holdin the mouse button down. So instead of jumpind down 3 or 4 days, it jumps about 280 years. What I am trying to achieve is to check my lottery numbers. When it has told me I haven't won again I reset the result and the date goes to the next cell in A ready for the next result. I have no doubt that your code if you want to modify it will be better than my code as modified by Basilio. But if not, I will be happy with it as it is now. Either way, thank you once again for taking an interest in my projects.
MrEMann (rep: 20) May 17, '20 at 2:53 am
Thanks for your feedback. Don't worry about the points. I have enough of them and for Basilio I wish all the encouragement you can give. A Dim statement doesn't do anything. Therefore it can't "not work". Perhaps you mean this: Application.Max(Cells(Rows.Count, "A").End(xlUp).Row, 53). But "not work" doesn't enable me to fix anything. The line is tested and returns the number of the last used row in column A, even if it's 280 years into the future LOL:, but not less than 53. This value is assigned to the variable Rl which was prepared for this in the Dim statement.
You don't need to hold down any key to scroll down. From the Ribbon's Home tab select Find & Select. In the dialog box that opens type A102270 (exactly 280 years,including the leap days) in the Reference field and confim. You can also type A2:A102270 to select the entire range to delete or paste.
I don't think it's a matter of one or the other of our solutions working better. They work differently. And unfortunately, I didn't understand, with sufficient precision, how you intend to work with either one. I suppose the question is whether losing the current row number is an asset to you or a liability. Perhaps, after you test for a week, you let us know.
Variatus (rep: 4889) May 17, '20 at 3:46 am
Thank you for your help with my project. I have tested you suggestion, which works only if I enter dates in column A so that A* will always be the last row. Simpler would just be to enter the next date straight into B25, as I currently do.
The following I have works better, initially:

Sub Reset_values()
' Reset values and take next date and place in cell B25    
Static nd As Integer
    Range("B25").Select    
ActiveCell = ("=A") & nd    
nd = nd + 1    
Range("E5:K5").ClearContents    
Range("E5").Select
End Sub

However, when I save the work, the next time I open it cell B25 displays the result =@A0. If I run the macro enough times it reaches the cell I need. If I type =nd in an empty cell I get the result #Name? which suggests the value of nd is not saved for the next time. If I never closed the workbook or turned off my pc it would work excellently.
The objective in mind was that the lottery is run every Tues and Fri. If they ever change it to say, Mon and Sat or Mon, Wed and Sat then it would be simple to make the changes in tha A column.
Therefore I have reverted back to entering ths next date manually in B25 and the data in column A is deleted. Thank you for helping and if nothing else, I do feel I am learning along the way. Many thanks to you and to Basilio.
MrEMann (rep: 20) May 19, '20 at 9:01 pm
We've lost the thread. There should be one question, an answer and a small discussion of the latter. Now the question has been explained (=modified) in the discussion and the answer is about to be changed in response of that modification. In the end question and answer won't match.
The problem here is that we don't know what's in A53. Please ask a new question, post your workbook with it, and in your workbook the code you presently use, and say in your question what that code doesn't do or what you would want it to do.
Thank you.
Variatus (rep: 4889) May 19, '20 at 11:49 pm
Add to Discussion


Answer the Question

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