Calling a Sub from Functiuon to Change Values in Active Sheet

Hello Friends!!

I am using following code to create a header row at "Cell_ref" Location.

Public Function Pressure_Drop(Cell_ref As String)
'Generates Header Row for Pressure Drop Calculations
'Dim Cell_ref As String
'Cell_ref = "A2"
Pressure_Drop = "Pressure Drop Calculation as per Crane Technical Paper"
Evaluate "Pressure_Drop_Header(" & Cell_ref & ")"
End Function

Public Sub Pressure_Drop_Header(Cell_ref As String)
Dim Cell1 As Range
Set Cell1 = Range(Cell_ref)
Cell1.Value = "Fitting Type"
Cell1.Offset(0, 1).Value = "Fluid"
Cell1.Offset(0, 2).Value = "Inlet Pressure, in Pa"
Cell1.Offset(0, 3).Value = "Pipe Material"
Cell1.Offset(0, 4).Value = "NPS Unit"
Cell1.Offset(0, 5).Value = "NPS"
Cell1.Offset(0, 6).Value = "Schedule"
Cell1.Offset(0, 7).Value = "Ft, Friction Factor for NPS Crane"
Cell1.Offset(0, 8).Value = "K, Pressure Drop Factor"
Cell1.Offset(0, 9).Value = "f, Friction Factor"
Cell1.Offset(0, 10).Value = "Head Loss hL, in m"
Cell1.Offset(0, 11).Value = "Pressure Drop, in Pa"
Cell1.Offset(0, 12).Value = "Outlet Pressure, in Pa"
Columns("A:Z").AutoFit
End Sub

On calling Function from worksheet like:

=Pressure_Drop("A2")

I get error in value. Can somebody help please.

Originally Posted by rorya

I don't know without knowing why you think a formula is the best option for this.

If not formula then, what could be the other way round to fill cells with some prefixed data.

Originally Posted by MrShorty

Is there a reason you could not use a named array constant -- like this example using the names of months defined as "Quarter1" --

Thanks Mr Shorty!

It was exactly the thing I was looking to do but, was not aware of doing it this way. Very Thanks.

Originally Posted by rorya

You didn't really answer my question. So:
1. Why can't you use a button?
2. Why does it need to be done in code?
3. What made you decide that a formula was necessary? Given that the column headers are static, why can't you just type them in the cells in the first place?

Hi Rorya,

1. Using a button: well I can but I wanted to call it like we do call functions from spreadsheet
2. I have written the code which worked well when it was in sub but, as soon as I convert it to function it stopped working. One problem was with quotes, you rightly pointed.
3. Yes I can, but then I have written some data validation code for following rows using a button. So just went about inserting header too with a function.

Should I post you the code, I am still working on some issues with data validation there.