Getting macro to return input made into input box

**MrPoesiat** · 11-10-2011, 11:24 PM

Hi i am very new to excel macros and i am very lost. I am trying to get a macro to calculate the sum of a number of cells and from that calculate an escalation based on the input made into an input box.. this is working fine but i need to get the macro to return the input made into the input box in the cell below with a percentage symbol. This i can't figure out.. This is what i've tried..

Please Login or Register  to view this content.

It's the last two lines that aren't right. For example if i put 25 into the inpur box i need the macro to put 25% into the cell below the other two values.

Can anyone help me please??

**abousetta** · 11-10-2011, 11:40 PM

Hello and welcome to the forum. What is val in your last formula? Is this a user-defined function because Excel doesn't seem to recognize it.

abousetta

**MrPoesiat** · 11-10-2011, 11:55 PM

Well i'm not entirely sure. I just thought i used Val to get it to use the value of intExcRate but i really have no idea what i'm doing. This is the first time i've ever used macros..

Everything works but the last two lines and i kinda just made them up to try and get it to return the input made into the input box.. that's what i need the last bit to do but like i said i have no clue ..

**abousetta** · 11-11-2011, 12:15 AM

OK. I'm not exactly sure what you are attempting to accomplish but this should give you the results you are after.

Please Login or Register  to view this content.

If you could post a workbook then we may be able to tweak this better for you.

Hope this helps.

abousetta

**MrPoesiat** · 11-11-2011, 12:26 AM

That works perfectly!!! I don't understand it though! lol .. Thank you SOOO much for helping me! Now if i could just work out how you did what you just did i'd be fine!

**abousetta** · 11-11-2011, 05:31 AM

Hi,

No problem. So let's break it down:

Please Login or Register  to view this content.

This saves time in writing the code and running it. Instead of Activecell. something and Activecell. something you can simply use With... End With and make sure the dot stays with the next lines.

Please Login or Register  to view this content.

Offset method you already know so I won't go into it, but all I did was use the Activecell as the reference and offseted 1 and 2 cells and added the formulas. For the second formula I divided by 100 (to get a percentage) and formatted the cell as percentage rather than general.

Hope this helps.

abousetta

P.S. Thanks for marking the thread as Solved.

**davidcailler** · 02-13-2012, 04:09 PM

This is what I am doing in my macro:

I take temp measurements with a data logger. We are suppose to the data with the max temp at 25 deg plus an adjusted ambient of 40 deg. as shown below (ActiveCell.FormulaR1C1 = "=RC[-2]+40-RC[-6]"). The macro works great, but from time to time we are required to use a different adjusted ambient i.e 50 deg or 60 deg.

What I would liek to add to the macro isan input box asking the user for the adjusted ambient, then put that number into the formula but I can't figure out how to retrieve the information and put it in the formula.

Below is just a small portion of the complete macro. It shows me creating the input box and asking the prper question. But I have no idea what to do next.

Range("B2").Select
Set myCell = Application.InputBox(prompt:="What is the adjusted ambient", Title:="Ambient", Type:=1)
ActiveCell.FormulaR1C1 = "=RC[-2]+'myCell'-RC[-3]"
Range("E6").Select
Selection.Copy
Range("E7:E1597").Select
ActiveSheet.Paste
Range("G6").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=RC[-1]-RC[-5]"
Range("H6").Select
ActiveCell.FormulaR1C1 = "=RC[-2]+40-RC[-6]"

I would appreciate any help on this.

Thanks,

David