MrExcel.com - Your One Stop for Excel Tips & Solutions

Pause a macro to get a User Defined Range.

Posted by Dave on August 09, 2000 3:03 PM

Please Help:

I got some code off of the JWalk.com site and it works for pauseing a macro that I want to sort... . But now I want to use it to permit a user to make a selection, click the 'ok' button and Excel will copy that selection to a new worksheet.

I'm stuck at the point where it makes the selection and then activates that selection as the range to copy.

Here is an example of the mess I'm in:

Sub Copy_User_Defined_Range()

Application.ScreenUpdating = True

Dim UserRange As Range

Prompt = "Please Make Your Selection"
Title = "Select Your Range"

' Display the Input Box
On Error Resume Next
Set UserRange = Application.InputBox( _
Prompt:=Prompt, _
Title:=Title, _
Default:=ActiveCell.Address, _
Type:=8) 'Range selection

' Was the Input Box canceled?
If UserRange Is Nothing Then
MsgBox "Canceled."
Else
UserRange.Range("A1") = Output
'This above line is my problem

End If

Selection.Copy
Sheets("Block Entries Alone").Select
ActiveSheet.Paste
Application.CutCopyMode = False

End Sub


All that happens is what ever active cell is selected when i run the macro is what gets copied to the "Block Entries Alone" worksheet instead of the selection.

Posted by Celia on August 10, 0100 7:48 AM

Re: given a specific number of years, months and days, calculte the 1/3, 1/4 and half of it. Also add or sustract that 1/2 or 1/4 to a given year


If the Start Date is in cell A1 and the End Date is in Cell B1 :-

1.The difference between the dates in years/months/days
=DATEDIF(A2,B2,"y")&"y "&DATEDIF(A2,B2,"ym")&"m "&DATEDIF(A2,B2,"md")&"d"

2.One quarter of the difference (y/m/d) counting from the start date
=DATEDIF(A2,(A2+(B2-A2)/4),"y")&"y "&DATEDIF(A2,(A2+(B2-A2)/4),"ym")&"m "&DATEDIF(A2,(A2+(B2-A2)/4),"md")&"d "

3.One third of the difference (y/m/d) counting from the start date
=DATEDIF(A2,(A2+(B2-A2)/3),"y")&"y "&DATEDIF(A2,(A2+(B2-A2)/3),"ym")&"m "&DATEDIF(A2,(A2+(B2-A2)/3),"md")&"d "

4. One half of the difference (y/m/d) counting from the start date
=DATEDIF(A2,(A2+(B2-A2)/2),"y")&"y "&DATEDIF(A2,(A2+(B2-A2)/2),"ym")&"m "&DATEDIF(A2,(A2+(B2-A2)/2),"md")&"d "

5.One quarter of the difference (y/m/d) counting from the end date
=DATEDIF((B2-(B2-A2)/4),B2,"y")&"y "&DATEDIF((B2-(B2-A2)/4),B2,"ym")&"m "&DATEDIF((B2-(B2-A2)/4),B2,"md")&"d "

6.One third of the difference (y/m/d) counting from the end date
=DATEDIF((B2-(B2-A2)/3),B2,"y")&"y "&DATEDIF((B2-(B2-A2)/3),B2,"ym")&"m "&DATEDIF((B2-(B2-A2)/3),B2,"md")&"d "

7.One half of the difference (y/m/d) counting from the end date
=DATEDIF((B2-(B2-A2)/2),B2,"y")&"y "&DATEDIF((B2-(B2-A2)/2),B2,"ym")&"m "&DATEDIF((B2-(B2-A2)/2),B2,"md")&"d "

8.Start date plus one quarter(expressed as a date)
=A2+(B2-A2)/4

9.Start date plus one third(expressed as a date)
=A2+(B2-A2)/3

10.Start date plus one half(expressed as a date)
=A2+(B2-A2)/2

11.End date less one quarter(expressed as a date)
=B2-(B2-A2)/4

12.End date less one third(expressed as a date)
=B2-(B2-A2)/3

13.End date less one half(expressed as a date)
=B2-(B2-A2)/2

Should the need arise, does the above qualify me for a reduced sentence or perhaps time off for cooperating with the Court?

Celia

Posted by Celia on August 10, 0100 7:52 AM

Correction

The Start Date should of course be in A2 and the End Date in B2.
Celia

Posted by Michael Liu on August 09, 0100 4:08 PM

Try these modifications:

'#####Not sure why this line was here, so I took it out.
'UserRange.Range("A1") = Output
'This above line is my problem

End If

'####Changed selection to UserRange
UserRange.Copy
Sheets("Block Entries Alone").Select
'####You may want to select a certain cell for where stuff will be pasted to
'####or else it defaults to whatever cell was last selected on this sheet.
range("A1").select

Posted by Francisco J. Estaba S. on August 09, 0100 4:19 PM

given a specific number of years, months and days, calculte the 1/3, 1/4 and half of it. Also add or sustract that 1/2 or 1/4 to a given year

Hi, i´m a Judge here in Venezuela, and part of my duties are to carry out the... ah.... how do you spell it? well, i have to carry out the time people have to pay in jail in order to return to society. I do the arithmetic manually, but i have at my disposal a series of computer (None advanced) that could do the work for me. I have almost no knowledge os Excel, but i think the program can calculate for me the number of years, months and days between two dates, and calculate the 1/3, 1/4 and half of any given number of years (Ex: (8y 6m 23d)/3 ). Also add or sustract that 1/2 or 1/4 to a given year

Posted by Francisco J. Estaba S. on August 10, 0100 8:57 PM

Re: Correction

I, acting on the powers confered to me by the Republic of venezuela, hereby find you guilty of being extremely helpful. As you know, this is a severe felony, and i will show no mercy on your behalf!. So..... <MUACK!> there your are!
Bwahahahahahahahaha!

Seriously, I´m very grateful for your help, i will try to test your formulae asap!