The date field in "Data" was importing as text so there was a type mismatch
that I've been able to fix to solve "date=rngInput.offset(-3,0)".
--
Jim


"Jim G" wrote:

The "new month date" is always in Sheets("Data") at cell F1. However the
comparable date will always be the heading of the column the formula values
are written to in Sheets("Cheops") and therefore changes each month.

'Im trying to use MthDate = rngInput.offset(-3,0) but I think I have the
wrong syntax. I want to test it after the input selection and warn if it's
not equal (data period mismatch).

--
Jim


"Bob Phillips" wrote:

Jim,

I am not sure where the date cell is, which cell you want to compare
against, or where you want that check in the code, but testing cells is just
a simple IF test

If cell1 = cell2 Then

and so on.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Jim G" wrote in message
...
Thanks bob, so simple yet so effective, you're a genius!

Any thoughts on the date match?

--
Jim


"Bob Phillips" wrote:

Sub Formula()
' Enters formulas in current month "ACTUAL" column to retreive PFRM data
Dim col As String
Dim adjCol As String
Dim Col2 As String
Dim iRow As Long, r As Long
Dim Net As Range
Dim rngInput As Range
Dim CalcType As Variant
Dim ProjResult As String
Dim Projnet As String
Dim Lastrow As Long

Worksheets("Cheops").Select
Rows(13).Select 'to ensure the screen is at the first row for input

With Application
CalcType = .Calculation
.Calculation = xlCalculationManual
End With

' to select the column of the current month
On Error Resume Next
Set rngInput = Application.InputBox("Select Starting Cell in
Highlighted
Row ", Type:=8)
On Error GoTo 0

If Not rngInput Is Nothing Then

Application.ScreenUpdating = False

col = Split(rngInput.Address, "$")(1)
Col2 = Split(rngInput.Offset(0, 2).Address, "$")(1)
adjCol = Split(rngInput.Offset(0, 1).Address, "$")(1)
iRow = rngInput.Row

ProjResult = "=SUMIF(Data!$A:$A,$A" & iRow &
",Data!$D:$D)+SUMIF(Data!$C:$C,$C" & iRow & ",Data!$D:$D)"
Debug.Print (ProjResult) ' temp test the for result
Projnet = "=(" & col & iRow & "+" & adjCol & iRow & ")"

With Worksheets("Cheops")
Lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
.Range(col & iRow & ":" & col & Lastrow).Value = ProjResult
.Range(col & iRow & ":" & col & Lastrow).Value = _
.Range(col & iRow & ":" & col & Lastrow).Value
.Range(Col2 & iRow & ":" & Col2 & Lastrow).Formula = Projnet
.Range(Col2 & iRow & ":" & Col2 & Lastrow).Value = _
.Range(Col2 & iRow & ":" & Col2 & Lastrow).Value

End With
With Application
.ScreenUpdating = True
.Calculation = CalcType
End With
End If
End Sub



--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"Jim G" wrote in message
...
I use the following code to enter formulae into a sheet that has three
columns for each month (Actual, Adjustment,Net-starting at row 12).
Each
month new data is added to the Data Sheet. The formulae are added to
each
line (starting at row 13)and the month actuals updated. Is it possible
to
enter the result of the formula in each row or should I just copy the
column
and paste values after the main code has run?

I would also appreciate any ideas on error handling to allow the use to
back
out if they don't want to make the choice of starting cell.

The data sheet has a date that I would like to validate against the
column
date (cell above Actual-row 11) and warn or terminate the code.
--------------------------------------------
Option Explicit

Sub Formula()
' Enters formulas in current month "ACTUAL" column to retreive PFRM
data
Dim col As String
Dim adjCol As String
Dim Col2 As String
Dim iRow As Long, r As Long
Dim Net As Range
Dim rngInput As Range
Dim CalcType As Variant
Dim ProjResult As String
Dim Projnet As String
Dim Lastrow As Long

Worksheets("Cheops").Select
Rows(13).Select 'to ensure the screen is at the first row for input

With Application
CalcType = .Calculation
.Calculation = xlCalculationManual
End With

' to select the column of the current month
Set rngInput = Application.InputBox("Select Starting Cell in
Highlighted
Row", Type:=8)

Application.ScreenUpdating = False

col = Split(rngInput.Address, "$")(1)
Col2 = Split(rngInput.Offset(0, 2).Address, "$")(1)
adjCol = Split(rngInput.Offset(0, 1).Address, "$")(1)
iRow = rngInput.Row

ProjResult = "=SUMIF(Data!$A:$A,$A" & iRow &
",Data!$D:$D)+SUMIF(Data!$C:$C,$C" & iRow & ",Data!$D:$D)"
Debug.Print (ProjResult) ' temp test the for result
Projnet = "=(" & col & iRow & "+" & adjCol & iRow & ")"

With Worksheets("Cheops")
Lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
.Range(col & iRow & ":" & col & Lastrow).Value = ProjResult
.Range(Col2 & iRow & ":" & Col2 & Lastrow).Formula = Projnet

End With
With Application
.ScreenUpdating = True
.Calculation = CalcType
End With


End Sub


--
Jim