How do you pause a Macro to move the cursor from the cell it is on to another cell

[Trebor J]

How do you pause a Macro and be allowed to move the cursor from the cell it is on to another cell?

I have written a macro that copies in a template consisting of 2 formulas and 2 input cells, all on the same row.

After the template is copied in, the cursor moves to the first input cell.

After data is entered and “Enter” pressed, the cursor moves to the second input cell.

After data is entered and “Enter” pressed, the cursor moves down one row and left 3 columns to sit in the cell just below the start of where the template was entered.

A message box then asks if you wish to “Move to Approximate Next Period End”.

If you click on Yes, the cursor then moves down 29 Rows.

All well and good except you might not be in the cell that is in the row that has a cell with the date you want in it. You need to move the cursor up or down a row or two.

I found some coding on the internet that purports to pause the Macro and allows one to move the cursor and it appears to do that.
I say “appears to do that” because a Message Box appears giving one the option to move the cursor, from the FROM CELL to the TO CELL, with the FROM CELL having a “Solid” border around it and the TO Cell having a “Dashed” border around it, if a new cell is chosen. The Message Box will then show the absolute address of the TO CELL.

If you then click on OK a new Message Box appears telling you that the Macro is “Working with the TO CELL’s address”. The TO Cell no longer has a “dashed” border. The FROM Cell still has a “Solid” border.

If you then click on OK to go ahead and then Yes to copy in the template, the template is copied into the FROM CELL. Not what I want !@%&). It should be copied into the TO CELL.

Any help or suggestion would be very much appreciated. Thanks guys and girls.

Sub CopyInPeriodTotalsFormulasTesting()

Dim MykWh1 As Variant
Dim MykWh2 As Variant

Dim vbResult As VbMsgBoxResult

  Do Until IsEmpty(ActiveCell.Offset(0, -1))

  vbResult = MsgBox("    ***    COPY IN PERIOD TOTALS FORMULAS    ***     " & Chr(10) & Chr(10) & "  CHECK TO SEE CURSOR IS IN THE CORRECT CELL      ", vbYesNo, "CONTINUE Y/N?")

    If vbResult = vbYes _
      Then
        ActiveCell.Offset(0, 0).Select
          Range("PeriodTotalsFormulas").Copy Destination:=ActiveCell.Offset(0, 0)      '   Copy in the PeriodTotalFormula into the current cell
           
            ActiveCell.Offset(0, 2).Select
              MykWh1 = InputBox("Enter the kWh for 49")
               ActiveCell.Value = MykWh1
               
            ActiveCell.Offset(0, 1).Select
              MykWh2 = InputBox("Enter the kWh for 64")
                ActiveCell.Value = MykWh2
               
        ActiveCell.Offset(1, -3).Select
       
        vbResult = MsgBox("  Move to Approx Next Period End   ", vbYesNo, "CONTINUE Y/N?")
       
        If vbResult = vbYes _
          Then
            ActiveCell.Offset(29, 0).Select                  'Move to next approximate cell below

            Dim Rng As Range
              On Error Resume Next
                Set Rng = Application.InputBox("Enter to Continue " & _
                        "or Select a cell with the Mouse", _
                        "Cell entry", ActiveCell.Address, , , , , 8)
                If Rng Is Nothing Then
                    MsgBox "You cancelled!"
                Else
                  MsgBox "Working with cell " & Rng(1).Address

                End If
         
          Else
            Exit Do
        End If
           
      Else
        Exit Do
     
    End If

Loop
 
End Sub

MovingTheCursorTesting 20221109 1852.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1
2		Data for Moving the Cursor Testing
3
4		DATE	Daily kW hrs	Period Total kW hrs	Invoiced kWh
5					Total	u49	u64
6
7
8		16-Nov-15	28.90	28.90	-			Period Total Formula
9		17-Nov-15	17.70
10		18-Nov-15	22.84
11		19-Nov-15	26.29
12		20-Nov-15	23.43
13		21-Nov-15	22.63
14		22-Nov-15	14.17
15		23-Nov-15	21.65
16		24-Nov-15	12.54
17		25-Nov-15	11.35
18		26-Nov-15	34.90
19		27-Nov-15	46.42
20		28-Nov-15	51.17
21		29-Nov-15	50.52
22		30-Nov-15	38.09
23		01-Dec-15	29.71
24		02-Dec-15	35.24
25		03-Dec-15	44.87
26		04-Dec-15	43.85
27		05-Dec-15	40.08
28		06-Dec-15	39.68
29		07-Dec-15	48.57
30		08-Dec-15	39.57
31		09-Dec-15	38.32
32		10-Dec-15	34.87
33		11-Dec-15	28.82
34		12-Dec-15	29.97
35		13-Dec-15	32.39
36		14-Dec-15	41.39
37		15-Dec-15	42.27
38		16-Dec-15	32.11	Start							Start the macro from cell D38
39		17-Dec-15	39.25								Note the Months vary from 31, 30 & 28 days
40		18-Dec-15	48.59								and the invoice is not always the same day of the month
41		19-Dec-15	44.95								hence need for ability to move the cursor.
42		20-Dec-15	41.10
43		21-Dec-15	32.45
44		22-Dec-15	36.08
45		23-Dec-15	42.15								The Testing Macro will copy in the Template from cells D8:G8, and then take one to the Input Cells. Just enter any number into each of those 2 cells, and then follow the Messages to go through the proceedure. When requested move the cursor to the next 16th of the month cell.
46		24-Dec-15	40.62
47		25-Dec-15	33.86
48		26-Dec-15	26.67
49		27-Dec-15	29.36
50		28-Dec-15	31.38
51		29-Dec-15	28.15
52		30-Dec-15	29.76
53		31-Dec-15	34.27
54		01-Jan-16	24.19
55		02-Jan-16	27.83
56		03-Jan-16	25.08
57		04-Jan-16	49.32
58		05-Jan-16	41.48
59		06-Jan-16	34.25
60		07-Jan-16	28.42
61		08-Jan-16	28.43
62		09-Jan-16	34.72
63		10-Jan-16	21.53
64		11-Jan-16	33.26
65		12-Jan-16	30.88
66		13-Jan-16	32.73
67		14-Jan-16	34.51
68		15-Jan-16	32.07
69		16-Jan-16	30.02	Next							D69 is the Next cell to copy the template into.
70		17-Jan-16	33.26
71		18-Jan-16	24.31
72		19-Jan-16	20.14
73		20-Jan-16	19.52
74		21-Jan-16	19.13
75		22-Jan-16	22.89
76		23-Jan-16	27.83
77		24-Jan-16	29.13
78		25-Jan-16	25.72
79		26-Jan-16	32.10
80		27-Jan-16	28.36
81		28-Jan-16	23.22
82		29-Jan-16	32.54
83		30-Jan-16	26.29
84		31-Jan-16	27.35
85		01-Feb-16	24.21
86		02-Feb-16	29.18
87		03-Feb-16	27.22
88		04-Feb-16	30.62
89		05-Feb-16	32.56
90		06-Feb-16	29.34
91		07-Feb-16	25.93
92		08-Feb-16	30.58
93		09-Feb-16	29.40
94		10-Feb-16	25.25
95		11-Feb-16	30.79
96		12-Feb-16	27.29
97		13-Feb-16	26.08
98		14-Feb-16	27.79
99		15-Feb-16	28.23
100		16-Feb-16	25.79	Next
101		17-Feb-16	28.12
102		18-Feb-16	27.50
103		19-Feb-16	22.92
104		20-Feb-16	28.76
105		21-Feb-16	27.85
106		22-Feb-16	25.28
107		23-Feb-16	28.63
108		24-Feb-16	26.60
109		25-Feb-16	33.88
110		26-Feb-16	27.21
111		27-Feb-16	25.48
112		28-Feb-16	27.48
113		29-Feb-16	29.00
114		01-Mar-16	24.01
115		02-Mar-16	29.31
116		03-Mar-16	27.85
117		04-Mar-16	23.21
118		05-Mar-16	22.86
119		06-Mar-16	27.91
120		07-Mar-16	27.11
121		08-Mar-16	30.77
122		09-Mar-16	28.07
123		10-Mar-16	24.27
124		11-Mar-16	25.00
125		12-Mar-16	30.45
126		13-Mar-16	26.99
127		14-Mar-16	32.15
128		15-Mar-16	28.89
129		16-Mar-16	27.65	Next
130		17-Mar-16	29.04
131		18-Mar-16	28.74
132		19-Mar-16	33.60
133		20-Mar-16	29.67
134		21-Mar-16	26.62
135		22-Mar-16	32.27
136		23-Mar-16	27.61
137		24-Mar-16	26.75
138		25-Mar-16	26.52
139		26-Mar-16	31.65
140		27-Mar-16	23.74
141		28-Mar-16	29.15
142		29-Mar-16	28.04
143		30-Mar-16	28.29
144		31-Mar-16	25.82
145		01-Apr-16	27.32
146		02-Apr-16	28.14
147		03-Apr-16	30.21
148		04-Apr-16	28.20
149		05-Apr-16	33.65
150		06-Apr-16	31.01
151		07-Apr-16	26.81
152		08-Apr-16	27.10
153		09-Apr-16	31.83
154		10-Apr-16	25.37
155		11-Apr-16	27.50
156		12-Apr-16	33.30
157		13-Apr-16	34.12
158		14-Apr-16	27.30
159		15-Apr-16	23.56
160		16-Apr-16	23.56	Next
161		TEST END
162
163
Testing

Cell Formulas
Range	Formula
D8	D8	=SUM($C$7:C8)-SUM($D$7:D7)
E8	E8	=+F8+G8

 

[alexofrhodes]

Add this at the point where you want to wait:
Application.Wait (Now + TimeValue("0:00:10"))
Although it would be better if you could modify the macro to minimize the required user interaction.

range.select is not necessary, you can use
set cell = activesheet.range("A1") or set cell=activesheet.columns(1).find(.... or set cell=inputbox(....8....
cell.value= ....

 

[Trebor J]

Add this at the point where you want to wait:
Application.Wait (Now + TimeValue("0:00:10"))
Although it would be better if you could modify the macro to minimize the required user interaction.

range.select is not necessary, you can use
set cell = activesheet.range("A1") or set cell=activesheet.columns(1).find(.... or set cell=inputbox(....8....
cell.value= ....

Hi Alex,
Thanks for your reply.

I previously tried using "Application.Wait (Now + TimeValue("0:00:10"))", but it does not allow one to move the cursor to another cell.
It just gives you 10 seconds before a message box comes up.

Some of the user interaction is a bit much, and I would cut a lot out so that one wizzes through only entering the input data, if I could find a solution to my question.

I not sure about how to use your suggestion:
set cell = activesheet.range("A1") or set cell=activesheet.columns(1).find(.... or set cell=inputbox(....8....cell.value= ....

The ("A1") part looks as though it is locking on to cell Address A1, which I don't think is going to help.
Can you clarify for me please.

 

[Trebor J]

To those who are reading this, I should mention that the Range "PeriodTotalsFormulas" refers to the cells D8:G8.
This is the first time I have used XL2BB. I missed out on ensuring that Range Names were shown alongside the Cell Formulas in the Mini Worksheet pasted in.

 

[My Aswer Is This]

I have been watching this posting but have not commented.
I think it would be best to tell us your overall objective here.
Wanting a script to pause so you can move the cursor and then run some more code and then pause again for the cursor to be moved again is not a good way to do things. And using wait just stops the script from running for a few seconds but you cannot move the cursor during this time.

So again, it would be best to tell us your overall objective without thinking you need to move the cursor. And using select on and on is not the way to write a script.
Like if you want to enter the value "Alpha" into sheet named "Bravo" Range("G1")

All you need to do is write the code like this:

Sheets("Bravo").Range("G1").value="Alpha"

See I selected nothing

But some users think first you have to select or activate the sheet name "Bravo"
Then select Range("G1") and then write code saying selection.value="Alpha"

 

[Trebor J]

I have been watching this posting but have not commented.
I think it would be best to tell us your overall objective here.
Wanting a script to pause so you can move the cursor and then run some more code and then pause again for the cursor to be moved again is not a good way to do things. And using wait just stops the script from running for a few seconds but you cannot move the cursor during this time.

So again, it would be best to tell us your overall objective without thinking you need to move the cursor. And using select on and on is not the way to write a script.
Like if you want to enter the value "Alpha" into sheet named "Bravo" Range("G1")

All you need to do is write the code like this:

Sheets("Bravo").Range("G1").value="Alpha"

See I selected nothing

But some users think first you have to select or activate the sheet name "Bravo"
Then select Range("G1") and then write code saying selection.value="Alpha"

Hi

Thankyou for replying to my post.

Back in 2015 my electricity supplier came and replaced the Manually Read Meters with Smart Meters that could be read remotely. Down the track, it provided a facility on its web site whereby, after registering, a consumer would have access to all of their account information and be able to download consumption details.

The consumption details entail the kW hours consumed every half hour during the day.

I had a look at what I had consumed over several billing periods and compared it with the totals showing on the invoices charging me for that consumption. They did not reconcile, and the differences were not in my favour.

As a consequence of that I downloaded all available data and am in the process of building macros to analyse it.

I had 122,324 rows of information, in date and time order.

There are 48 ½hrs in a day. 122,324 -:- 48 = 2548.41667, which signifies that something is wrong because I should have got a whole number.

The data downloaded included columns for Date, Start Time, End Time and kWh.

I then set up some columns, formulas and macros to run through the data to ensure that there were 48 rows for each day, and to highlight if not. That produced a number of cases where there had been double ups, 50 rows for a day, and missings, 46 rows for a day. I adjusted for them and now have 122,304 rows of data covering 2548 days.

I then set up a macro to take that data and transpose it onto another worksheet so that the ½hr consumption for a day was set across 48 columns, with a column showing the total consumption for a day. I now have 2548 rows of data, comprising approximately 85 billing periods.

OBJECTIVE

The objective now is to add up the consumption for each billing period and compare it with the consumption invoiced to me, and do it by way of an efficient macro.

It could well be that there is some code that could be written to allow the next date to be entered and the macro will look down the Date Column to find that row, then position itself in the correct cell in that row, to copy in the “PeriodTotalsFormulas”, then move to the first input cell.

I want to use these macros to do this analysis for my offspring and interested friends, so even though I have analysed my data, there is still more to do.

The macro I have built does the job but has the draw back that it does not always stop at the correct row. The number of days in a billing period can vary from 28, 30, 31, and sometimes even less or more. In my macro, I have it moving down 29 rows to the approximate next cell to enter the “PeriodTotalsFormulas”. On reflection I probably should be putting 30 in there, being 1@28, 4@30 & 7@31. But then again, the sometimes more/less seems to override the pattern here.

Note – I have amended the “PeriodsTotalFormulas” to include a column to show the difference between the Period Total kW hrs and the Invoiced kW hrs Total.

I trust this make the picture clearer for you and look forward to your comments.

Regards

 

[Trebor J]

I have been watching this posting but have not commented.
I think it would be best to tell us your overall objective here.
Wanting a script to pause so you can move the cursor and then run some more code and then pause again for the cursor to be moved again is not a good way to do things. And using wait just stops the script from running for a few seconds but you cannot move the cursor during this time.

So again, it would be best to tell us your overall objective without thinking you need to move the cursor. And using select on and on is not the way to write a script.
Like if you want to enter the value "Alpha" into sheet named "Bravo" Range("G1")

All you need to do is write the code like this:

Sheets("Bravo").Range("G1").value="Alpha"

See I selected nothing

But some users think first you have to select or activate the sheet name "Bravo"
Then select Range("G1") and then write code saying selection.value="Alpha"

I would say you have "selected" cell G1 on the sheet "Bravo", and done it with code that does not use the word "select".
That is great, I have learnt something there. But it requires you to nominate/select a definite cell address.
With a lot of the code I write, I do not know the cell address, but I know how to get there using a "railway lines" approach, hence the use of "select".
With regard to my particular problem, I do not know the precise cell address, only know the approximate location and am looking for ways to zero in on the exact desired location. Could it be that there is some way to enter a date and then using the column with consecutive dates in it be taken to desired cell in the row with that date in it?

 

[My Aswer Is This]

Well, I can write the script to run whenever you enter a value in column 1 or column 3
Or row 1 or whatever you want

Or I can have the script run when you press a button or do other things.
You tell me what you want to do to cause a script to run. but there is not script I can write that will run when you move the cursor.

 

[Trebor J]

Error

 

[Trebor J]

Well, I can write the script to run whenever you enter a value in column 1 or column 3
Or row 1 or whatever you want

Or I can have the script run when you press a button or do other things.
You tell me what you want to do to cause a script to run. but there is not script I can write that will run when you move the cursor.

Using my example data, above:
I started my macro, which copied in the template and requested me to enter data in cells F8 & G8.
I have done that and am in cell G8.
I now want to move down to the cell in column D that is in the row that has the date of 16/12/2015 in it.
I do not know that cells address and do not know how many rows down it is, so I am thinking that if the macro asked me to enter the date of 16/12/2015, or even just 16, it would go down to that cell in column B with that date in it, and then to column D in that row.
I would then be in the cell I want to be in, and the macro would loop back to the start.
I look forward to your scrip.
Thanks