How to write formulas In VBA: and use a rage or a string results from a input box {inside a formula}

[rouzacct]

Hello,
I have googled a bunch and I find how to make inputbox promts and that part of my vba works.
But now I want to use that info collected from input boxes and insert that into my formulas to paste in various cells on a sheet.

here is a short version of what I have once i get one working i can replicate in various formulas i need to write.

Sub WRITEFORMULAS()

Dim STARTDATE As Range

Set STARTDATE = Application.InputBox("Select a range for START DATE", "Obtain Range of START DATE", Type:=8)
    
Sheets("sheet1").Select
Sheets("sheet1").[B2:B10].Formula = "=IF(M2="""","""",O2&""s ""&M2)"
Sheets("sheet1").[F2:F10].Formula = "=IF(A2="""",""""," & STARTDATE & ")"


End Sub

As im sure you may notice there is something wrong in my way of inserting the STARTDATE range!

what am I doing wrong?


I have about 15 Ranges and a few easy formulas and one long one that I will spare you the jobrish but so far I got Sheets("sheet1").[B2:B10].Formula
working fine but I dont know how to use the inputted Range properly inside my formulas

Sheets("sheet1").[F2:F10].Formula = "=IF(A2="""",""""," & STARTDATE & ")"

I also have a
Dim NAME As String 'with an input box

NAME = Application.InputBox("Please enter Name", "Type in name", Type:=2)


the input box comes up and i can write a text in it
I would need to use the inputted text in a formula so if its inserted differently than a range like above please mention how to insert it in a formula as well.


everything else i just have to edit names and formulas a 15 times and it should all work in harmony


Thanks for any help as possible

 

[rouzacct]

Hello,
I have googled a bunch and I find how to make inputbox promts and that part of my vba works.
But now I want to use that info collected from input boxes and insert that into my formulas to paste in various cells on a sheet.

here is a short version of what I have once i get one working i can replicate in various formulas i need to write.

Sub WRITEFORMULAS()

Dim STARTDATE As Range

Set STARTDATE = Application.InputBox("Select a range for START DATE", "Obtain Range of START DATE", Type:=8)
    
Sheets("sheet1").Select
Sheets("sheet1").[B2:B10].Formula = "=IF(M2="""","""",O2&""s ""&M2)"
Sheets("sheet1").[F2:F10].Formula = "=IF(A2="""",""""," & STARTDATE & ")"


End Sub

As im sure you may notice there is something wrong in my way of inserting the STARTDATE range!

what am I doing wrong?


I have about 15 Ranges and a few easy formulas and one long one that I will spare you the jobrish but so far I got Sheets("sheet1").[B2:B10].Formula
working fine but I dont know how to use the inputted Range properly inside my formulas

Sheets("sheet1").[F2:F10].Formula = "=IF(A2="""",""""," & STARTDATE & ")"

I also have a
Dim NAME As String 'with an input box

NAME = Application.InputBox("Please enter Name", "Type in name", Type:=2)


the input box comes up and i can write a text in it
I would need to use the inputted text in a formula so if its inserted differently than a range like above please mention how to insert it in a formula as well.


everything else i just have to edit names and formulas a 15 times and it should all work in harmony


Thanks for any help as possible

by the way

the selections that are being made during prompt boxes for ranges are full columns as in ='sheet2'!J:J
and so on..

but the sheet names and columns will change that's why I use the prompt box to select new ranges within new sheets but keep my formulas the same in the end result