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....

 

[Michael M]

Ok, so what else doo you need...what you have will do the trick

Sub MM1()
Set UserRange = Application.InputBox(Prompt:="Please Select Range", Title:="Range Select", Type:=8)
UserRange.Formula = "=IF(A2<=30,""0-30"",IF(A2<=60,""31-60"",IF(A2<=90,""61-90"",IF(A2<=120,""91-120"",""120+""))))"
End Sub

 

[Rajkiran557]

Ok, so what else doo you need...what you have will do the trick

Sub MM1()
Set UserRange = Application.InputBox(Prompt:="Please Select Range", Title:="Range Select", Type:=8)
UserRange.Formula = "=IF(A2<=30,""0-30"",IF(A2<=60,""31-60"",IF(A2<=90,""61-90"",IF(A2<=120,""91-120"",""120+""))))"
End Sub

yeah with that i am able to paste the formula but i need to select the cell reference in the formula.. that is i don't want the " A2 " to be fixed i need to select it before i paste the formula.. 'excuse me I am not familiar with the technical words' sorry if a got you confused.

Thank in advance..

 

[Michael M]

Okay, now I get it....maybe this way then

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

 

[Rajkiran557]

Okay, now I get it....maybe this way then

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

Thanks for that Mike

But i think i am missing something when i try to run the macro it just goes blank on me nothing happens..

actually i don't see the run option itself

 

[Michael M]

Where are you putting the code ??
Try pasting it in a Standard module or "this Workbook" module

 

[Rajkiran557]

Where are you putting the code ??
Try pasting it in a Standard module or "this Workbook" module

Yes i pasted in it in the Standard module , when i hit alt+F8 i don't see a option of run

I am using excel 2007

 

[Michael M]

Ok, when you ALT + F8, what is selected in the
"Macros in:" box down the bottom ??
It is a Macro enabled workbook, isn't it ?

There is no reason it won't show

 

[L. Howard]

I tried the code as posted and got an error saying it needed to reference a sheet. (Standard module)

Changed MM1 to XX_MM1 and it works fine for me.

MM1 is a cell reference, may be the problem...?

Regards,
Howard

 

[Michael M]

@ Hi Howard....that's odd....I always use MM1 as my sub names.....even in 2013 ....