PDA

View Full Version : Asking the user where to output the macro's results



Idiot
05-18-2010, 09:46 AM
First, I just want to say I've been trying to learn a lot from these forums and actually feel kind of bad for asking so many questions without me giving anything back except for a "Thank You" (a gracious thank you though... that's worth something right? :) ) And I am trying to figure things out for myself, but usually all my attempts turn into dead ends for me. I apologize for asking so many questions and relying heavily on you guys. I suppose I'll just cut right to it.

Since I have been getting a lot of help from here and Mr. Excel (just learned cross posting is bad, I promise I won't ever do it again [only did it once, but never again I promise]), results from the macros I got help with display the results just below the the data (which was fantastic and exactly what I asked for - I'm NOT complaining by any means and still very thankful). But, because this happening, the data that's below other data is getting overwritten. So I thought "hey, I should make it so it outputs the results where the user wants to display it without them having to enter any vba code." I promptly look at the vba code and go "Oh crap".

I was trying to take the code that you guys and mr. excel gave me and attempted to do it myself. I thought by first doing:


' Prompt for input
On Error Resume Next
n = Application.InputBox("Where do you want the output?", "Enter a cell", , Type:=1)
If n = 0 Then Exit Sub 'User canceled
On Error GoTo 0

would be good allowing the user enter a range like C4 or whatever range the user wanted and store it in the variable n. And then do something like

Cells(n).Formula = "=SUM(" & Cells(b, a).Resize(3, 3).Address & ")"
or whatever formula comes after "Cells().Formula" given the macro.

But it's not the simple apparently. Is there a universal way of doing this? Here are some links to other threads that I successfully got help with, I just didn't want to annoy and keep asking more than I deserve from those people:
http://www.mrexcel.com/forum/showthread.php?t=466822&highlight=triangle
http://www.vbaexpress.com/forum/showthread.php?t=32096&highlight=convert+monthly

As always, I appreciate your help and I WILL learn.

GTO
05-18-2010, 03:37 PM
In a new/blank wb, try:

Sub exa2()
Dim rngDest As Range

On Error Resume Next
Set rngDest = Application.InputBox("", "", , , , , , 8)
On Error GoTo 0

If rngDest Is Nothing Then Exit Sub
If rngDest.Count > 1 Then Exit Sub

rngDest.Value = Application.WorksheetFunction.Sum(Range("A1:A5"))

End Sub


I believe you want type 8 to return a Range object, and you'll need to set a referene to it.

Hope that helps,

Mark

Aussiebear
05-18-2010, 11:42 PM
(just learned cross posting is bad...)

While we don't encourage cross posting, what we absolutely insist on is posting the link, if you really feel the need to cross post.


So I thought "hey, I should make it so it outputs the results where the user wants to display it without them having to enter any vba code."

Nothing wrong with a little imagination


I promptly look at the vba code and go "Oh crap".

ROFL..... and you think you're alone in this?

Idiot
05-19-2010, 12:22 PM
In a new/blank wb, try:

Sub exa2()
Dim rngDest As Range

On Error Resume Next
Set rngDest = Application.InputBox("", "", , , , , , 8)
On Error GoTo 0

If rngDest Is Nothing Then Exit Sub
If rngDest.Count > 1 Then Exit Sub

rngDest.Value = Application.WorksheetFunction.Sum(Range("A1:A5"))

End Sub

I believe you want type 8 to return a Range object, and you'll need to set a referene to it.

Hope that helps,

Mark

OMG Thank you so much for this. I could hug you! I need to do a snooch more testing with it, but I think this is absolutely perfect!! Thank you!

Idiot
05-19-2010, 12:31 PM
While we don't encourage cross posting, what we absolutely insist on is posting the link, if you really feel the need to cross post.



Nothing wrong with a little imagination



ROFL..... and you think you're alone in this?
Oh, I know I'm not alone, I just don't want to annoy anyone and I hate bugging people for help.