paste formula with macro

[Rajkiran557]

Hi,

i need a macro to paste the below formula in a range for cells, however i need to select the cell reference with help of a input box because the value is not always in A2 and i also need to select the range where the formula has to be pasted

=IF(A2<=30,""0-30"",IF(A2<=60,""31-60"",IF(A2<=90,""61-90"",IF(A2<=120,""91-120"",""120+""))))

all i got was

Set UserRange = Application.InputBox(Prompt:="Please Select Range", Title:="Range Select", Type:=8)


UserRange.Value = _
"=IF(A2<=30,""0-30"",IF(A2<=60,""31-60"",IF(A2<=90,""61-90"",IF(A2<=120,""91-120"",""120+""))))"


appreciate your help....

 

[Rajkiran557]

Yes It is a Macro enabled workbook in fact It is pasted in my personal.xlsb and i am able to run all the other macros except MM1 when i click on MM1 the run button is disabled am i missing something ???

 

[Michael M]

Did you follow Howards instruction in the previous post and change the Sub name ???

 

[Rajkiran557]

Oh now it works the sub name was the problem.. thank you so much for support.

This one works perfect

Sub Aging()sr = Application.InputBox(Prompt:="Please Select First cell", Title:="Range Select", Type:=8).Address(False, False)
Set UserRange = Application.InputBox(Prompt:="Please Select Range", Title:="Range Select", Type:=8)
UserRange.Value = "=IF(" & sr & "<=30,""0-30"",IF(" & sr & "<=60,""31-60"",IF(" & sr & "<=90,""61-90"",IF(" & sr & "<=120,""91-120"",""120+""))))"
End Sub

 

[Michael M]

No worries, glad we could help

@Howard....I'll have to be careful of that one in future...I always use it in "This Workbook", so that may be the issue as well...

 

[L. Howard]

@ Michael, I used to use MUD and DUM as variables and sub names when I had the older versions of Excel.

I went nuts trying to figure out why that did not work with 2010. They both are column labels now.

Howard

 

[Michael M]

I'm gonna change to MYKL.....that should hold 'em off for a couple of versions !!!

 

[L. Howard]

I'm gonna change to MYKL.....that should hold 'em off for a couple of versions !!!

@ Michael, I used to use MUD and DUM as variables and sub names when I had the older versions of Excel.

 I went nuts trying to figure out why that did not work with 2010. They both are column labels now.

@ Michael, I do believe I have misspoke stating the above. Rick, one of the MVP's pointed out that using cell reference variables is perfectly okay, ie, the use if i and j and c as counters and longs etc.

Using Sub MM() in more tests works fine in every sheet or standard module. Not sure why that one time it threw an error saying "something" had to refer to a sheet.

Me thinks you can go back to your signature Sub MM() with no problem.

Sorry for crying "WOLF".

Howard

 

[Michael M]

Yeah, I thought it odd, as per my comment in post #10....I've always used it.
I was wondering if it was the digit causing the issue MM1 rather than simply MM.....one of the wonders of Excel, I guess !!!