Macro to select data via input box's

Hello,

I'm attempting to write my first macro from scratch and have not been able to figure out if I'm even in the ballpark on the structure and direction of my code. What I'm trying to accomplish is for a user to be able to select a range of data (weekending dates) that will populate an existing graph(example attached). The graph is updated weekly and if all the user has to do is enter two dates and the macro will use those dates to find the range of data to populate the graph. Anyways, here is my poor attempt:

Please Login or Register  to view this content.

I'm sure there is a mush better approach at this but I'm not sure how to do it. If anyone has the time to either help with my existing code or show me a better approach, I would appreciate it a lot.

Thank you,

-Justair07

Probably something along the lines of:

Please Login or Register  to view this content.

yudlugar,

That worked great, thank you very much! I'm glad to know I was off to the right start but I never would of figured out the rest. One thing I did notice though is that the chart does not show data more than five dates or less than five. Is this something you can help me figure out?

I'd guess the way to do it would be to delete the existing seriescollection and then add a new one with the data range you want but I'm not sure. Afraid I don't have the time to take a look now but I'll get onto later if I can.

Originally Posted by Justair07

yudlugar,

That worked great, thank you very much! I'm glad to know I was off to the right start but I never would of figured out the rest. One thing I did notice though is that the chart does not show data more than five dates or less than five. Is this something you can help me figure out?

I also just relized that the data never changes, only the dates. Any ideas?

Originally Posted by yudlugar

I'd guess the way to do it would be to delete the existing seriescollection and then add a new one with the data range you want but I'm not sure. Afraid I don't have the time to take a look now but I'll get onto later if I can.

Ok thanks again

So I was looking at better ways to select the data and I believe this is what I need to build off of appose to what I was using before.

Please Login or Register  to view this content.

This will select the range for the dates and the Actual Rejection %, I'm just not sure how to use this with GetRange?

Hi yudlugar,

I'm getting "invalid qualifier" error for Address:

Please Login or Register  to view this content.

Any ideas?

Thanks again

Sorry, get rid of the address bit:
Range("A" & Searchrow).row

yudlugar,

Works perfect! thanks so much for the help!

So I tried to add the code to a command button but for some reason it won't work, I keep getting a Method 'Range' of object'_Worksheet failed run-time error on the very last line of code.

Any ideas why?

Change the last line to:
msgbox("Sheet1!$A$1,Sheet1!$D$1:$E$1,Sheet1!$A$" & GetRangestart & ":$A$" & GetRangeend & ",Sheet1!$D$" & GetRangestart & ":$E$" & GetRangeend & "")

What do you get in the messagebox and does it look like a reasonable range reference?

It won't run, I'm getting a 'type mismatch' for msgbox

Just looked and I think it's got "") on the end when it shoudl be ")", that might be the problem.

Here is what i put

ActiveChart.SetSourceData Source:=MsgBox("Sheet2!$A$1,Sheet2!$D$1:$E$1,Sheet2!$A$" & GetRangestart & ":$A$" & GetRangeend & ",Sheet2!$D$" & GetRangestart & ":$E$" & GetRangeend & ")"

but i'm getting a compile error

Here is what I tried as well but got a compile error:

ActiveChart.SetSourceData Source:=Range("Sheet2!$A$1,Sheet2!$D$1:$E$1,Sheet2!$A$" & GetRangestart & ":$A$" & GetRangeend & ",Sheet2!$D$" & GetRangestart & ":$E$" & GetRangeend & ")"

Sorry, I meant replace the whole line with msgbox(...), delete the "activechart.setsourcedata source:=" part, what you end up with in the messagebox is what you will be setting your range to, it should probably be:
MsgBox("Sheet2!$A$1,Sheet2!$D$1:$E$1,Sheet2!$A$" & GetRangestart & ":$A$" & GetRangeend & ",Sheet2!$D$" & GetRangestart & ":$E$" & GetRangeend)

I think.

Here is my msgbox reply

123.jpg

Fyi, I did modify the code to work with a chart that is on a different worksheet but it works fine as long as I don't use a command button.

Still no luck, I tried to reuse the msgbox as the new range but it didnt work

Can't see any obvious problems, could you upload your workbook with the macro in that isn't working?

Here you go...

Thanks again,

Sorry, haven't got a clue why that isn't working ....

Oh well, no big deal. I can just use a form button. Thanks for all the help!