Sheet1 performs a series of calculations everytime and "event" occurs.
Each "event" will always occur on a unique date. However, the even
does not occur every day.

The goal is for the user to type the event data into the input section
on sheet1. Upon pushing a command button (ie "calculate"), the input
will be run through the calculations and outputs pasted into a uniqu
row on sheet2. Each time a unique date is put in the inputs section
the next row on sheet2 will be pasted with data associated with tha
date.


Is there a VB code capable of doing this?


Thanks

--
Message posted from http://www.ExcelForum.com

I am sure VB code can be written that is capable of doing this.

Any code would have to be set up to be specific to your layout, so not much
can be offered beyond the above.

--
Regards,
Tom Ogilvy

"sowetoddid " wrote in message
...
Sheet1 performs a series of calculations everytime and "event" occurs.
Each "event" will always occur on a unique date. However, the event
does not occur every day.

The goal is for the user to type the event data into the input sections
on sheet1. Upon pushing a command button (ie "calculate"), the inputs
will be run through the calculations and outputs pasted into a unique
row on sheet2. Each time a unique date is put in the inputs section,
the next row on sheet2 will be pasted with data associated with that
date.


Is there a VB code capable of doing this?


Thanks.


---
Message posted from http://www.ExcelForum.com/

This is the code that I am try to work with...

Sub BlowdownVolume()

Range("D22").Select
ActiveCell.FormulaR1C1 = _

"=1.96*(R[-16]C[1]+14.7)*(R[-4]C[-2]^2)*R[-15]C[-2]*1000/(R[-3]C[-2]*10^6)"
Range("F23").Select
Selection.NumberFormat = "#,##0"
Selection.Copy
Sheets("Blowdown Log").Select
Range("F5").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone
SkipBlanks:= _
False, Transpose:=False
Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone
SkipBlanks:= _
False, Transpose:=False
Range("F5").Select
End Sub


The code above is taking the user inputs and calculating one of th
outputs by pasting the formula into the specified cell. The result
are then pasted into the appropriate cell on the Blowdowns sheet. Thi
basic operation will be repeated for each of the ouput calculations.

The first operation should be to compare the inputted date on sheet
with the previously pasted dates on the "blowdown log". If the curren
input is already listed, then it writes over it with the most recen
outputs. If the date is not shown on "blowdown log", then the mos
recent outputs should be pasted on the first available row on th
"Blowdown log"

--
Message posted from http://www.ExcelForum.com

Hopefully this will make it a little more clear...


Sub Calculate()


' Begin pasting formulas to calculate the outputs on sheet1
Range("D22").Select
ActiveCell.FormulaR1C1 = _
"=1.96*(R[-15]C+14.7)*(R[-3]C^2)*R[-14]C*1000/(R[-2]C*10^6)"
Range("D23").Select
ActiveCell.FormulaR1C1 = _

"=1.96*(R[-16]C[3]+14.7)*(R[-4]C^2)*R[-15]C*1000/(R[-3]C*10^6)"
Range("D25").Select
ActiveCell.FormulaR1C1 = _
"=0.75*R[-21]C*1000*R[-5]C*60/((R[-6]C^2)*(R[-18]C+14.45)*24)"
Range("D27").Select
ActiveCell.FormulaR1C1 = "=R[-2]C*60/5280"
Range("D29").Select
ActiveCell.FormulaR1C1 = "=R[-21]C/R[-2]C"
Range("D31").Select
ActiveCell.FormulaR1C1 = "=R[-23]C*5280*3.14*7.484348/(4*144*42)"
' End pasting formulas

' (Insert code to evaluate existing dates on "Blowdown Log" and compar
with the current date in D3 of Sheet1)

' Copy the date input from sheet1
Range("D3").Select
Selection.Copy

' Insert code for locating the first available row and/or code to past
over a duplicate date (determined from evaluation performed previously)

' Paste the date into the first available row in Column A of sheet1.
Sheets("Blowdown Log").Select
Range("A5").Select
ActiveSheet.Paste
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone
SkipBlanks:= _
False, Transpose:=False
'Begin a series of copying and pasting the outputs from sheet1 int
Blowdown log. !!These should all be pasted in the row corresponding t
the date!!
Range("B5").Select
Sheets("Sheet1").Select
Range("D5").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Blowdown Log").Select
Range("C5").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone
SkipBlanks:= _
False, Transpose:=False
Range("D5").Select
Sheets("Sheet1").Select
Range("D8").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Blowdown Log").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone
SkipBlanks:= _
False, Transpose:=False
Range("E5").Select
Sheets("Sheet1").Select
Range("D7").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Blowdown Log").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone
SkipBlanks:= _
False, Transpose:=False
Range("F5").Select
Sheets("Sheet1").Select
Range("G7").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Blowdown Log").Select
Application.CutCopyMode = False
Sheets("Sheet1").Select
Selection.Copy
Sheets("Blowdown Log").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone
SkipBlanks:= _
False, Transpose:=False
Range("G5").Select
Sheets("Sheet1").Select
Range("D22").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Blowdown Log").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone
SkipBlanks:= _
False, Transpose:=False
Range("H5").Select
Sheets("Sheet1").Select
Range("D23").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Blowdown Log").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone
SkipBlanks:= _
False, Transpose:=False
'End series of copying and pasting outputs
'Insert a final formula into "Blowdown Logs"
Range("I5").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=RC[-2]-RC[-1]"
'Format numbers
Range("G5:I5").Select
Selection.NumberFormat = "#,##0"
End Su

--
Message posted from http://www.ExcelForum.com

assume the date to find is in D21
and in column A of the blowdown log

Dim rng as Range
Dim res as Variant
With worksheets("BlowDown Log")
set rng = .Range(.cells(1,1),.cells(1,1).End(xldown))
End with
res = Application.Match(clng(Range("D21")),rng,0)
if not iserror(rng) then
Range("F23").copy
rng(res).offset(0,5).Pastespecial xlValues
else
rng.offset(rng.rows.count,0).Resize(1,1).Value = Range("D21")
rng.offset(rng.rows.count,0).Resize(1,1).offset(0, 5).PasteSpecial xlValues
end if

--
Regards,
Tom Ogilvy

"sowetoddid " wrote in message
...
This is the code that I am try to work with...

Sub BlowdownVolume()

Range("D22").Select
ActiveCell.FormulaR1C1 = _


"=1.96*(R[-16]C[1]+14.7)*(R[-4]C[-2]^2)*R[-15]C[-2]*1000/(R[-3]C[-2]*10^6)"
Range("F23").Select
Selection.NumberFormat = "#,##0"
Selection.Copy
Sheets("Blowdown Log").Select
Range("F5").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
Range("F5").Select
End Sub


The code above is taking the user inputs and calculating one of the
outputs by pasting the formula into the specified cell. The results
are then pasted into the appropriate cell on the Blowdowns sheet. This
basic operation will be repeated for each of the ouput calculations.

The first operation should be to compare the inputted date on sheet1
with the previously pasted dates on the "blowdown log". If the current
input is already listed, then it writes over it with the most recent
outputs. If the date is not shown on "blowdown log", then the most
recent outputs should be pasted on the first available row on the
"Blowdown log".


---
Message posted from http://www.ExcelForum.com/

The code is generating a Type 13 mismatch error. Any ideas. Microsof
suggests that this problem comes from comparing with String, but th
code you provided is using Variant, so that should be fine....I am jus
guessing. Please help.

The error occurs on the following line...

rng(res).offset(0,5).PasteSpecial xlValues



Thank

--
Message posted from http://www.ExcelForum.com

there is a typo in the code:

Dim rng as Range
Dim res as Variant
With worksheets("BlowDown Log")
set rng = .Range(.cells(1,1),.cells(1,1).End(xldown))
End with
res = Application.Match(clng(Range("D21")),rng,0)
if not iserror(res) then ' < == correction made
Range("F23").copy
rng(res).offset(0,5).Pastespecial xlValues
else
rng.offset(rng.rows.count,0).Resize(1,1).Value = Range("D21")
rng.offset(rng.rows.count,0).Resize(1,1).offset(0, 5).PasteSpecial xlValues
end if

--
Regards,
Tom Ogilvy

"sowetoddid " wrote in message
...
The code is generating a Type 13 mismatch error. Any ideas. Microsoft
suggests that this problem comes from comparing with String, but the
code you provided is using Variant, so that should be fine....I am just
guessing. Please help.

The error occurs on the following line...

rng(res).offset(0,5).PasteSpecial xlValues



Thanks


---
Message posted from http://www.ExcelForum.com/

This is what I have now...Any ideas on the Error Type Mismatch

' Begin pasting formulas to calculate the outputs on Pipelin
Blowdowns
Range("D22").Select
ActiveCell.FormulaR1C1 = _
"=1.96*(R[-15]C+14.7)*(R[-3]C^2)*R[-14]C*1000/(R[-2]C*10^6)"
Range("D23").Select
ActiveCell.FormulaR1C1 = _

"=1.96*(R[-16]C[3]+14.7)*(R[-4]C^2)*R[-15]C*1000/(R[-3]C*10^6)"
Range("D25").Select
ActiveCell.FormulaR1C1 = _
"=0.75*R[-21]C*1000*R[-5]C*60/((R[-6]C^2)*(R[-18]C+14.45)*24)"
Range("D27").Select
ActiveCell.FormulaR1C1 = "=R[-2]C*60/5280"
Range("D29").Select
ActiveCell.FormulaR1C1 = "=R[-21]C/R[-2]C"
Range("D31").Select
ActiveCell.FormulaR1C1 = "=R[-23]C*5280*3.14*7.484348/(4*144*42)"
' End pasting formulas

' Code to evaluate existing dates on "Blowdown Log" and compare wit
the current date in D3 of "Pipeline Blowdowns")
' Code for locating the first available row and/or code to paste over
duplicate date (determined from evaluation performed previously)

Dim rng As Range
Dim res As Variant
With Worksheets("Blowdown log")
Set rng = .Range(.Cells(1, 1), .Cells(1, 1).End(xlDown))
End With
res = Application.Match(CLng(Range("D21")), rng, 0)
If Not IsError(rng) Then
Range("F23").Copy
rng(res).Offset(0, 5).PasteSpecial xlValues
Else
rng.Offset(rng.Rows.Count, 0).Resize(1, 1).Value = Range("D21")
rng.Offset(rng.Rows.Count, 0).Resize(1, 1).Offset(0
5).PasteSpecial xlValues
End If

' Paste the date into the first available row in Column A of "Pipelin
Blowdowns".
Sheets("Blowdown Log").Select
Range("A5").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone
SkipBlanks:= _
False, Transpose:=False
'Begin a series of copying and pasting the outputs from Pipelin
Blowdowns into Blowdown log. !!These should all be pasted in the ro
corresponding to the date!!
Range("B5").Select
Sheets("Pipeline Blowdowns").Select
Range("D5").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Blowdown Log").Select
Range("C5").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone
SkipBlanks:= _
False, Transpose:=False
Range("D5").Select
Sheets("Pipeline Blowdowns").Select
Range("D8").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Blowdown Log").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone
SkipBlanks:= _
False, Transpose:=False
Range("E5").Select
Sheets("Pipeline Blowdowns").Select
Range("D7").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Blowdown Log").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone
SkipBlanks:= _
False, Transpose:=False
Range("F5").Select
Sheets("Pipeline Blowdowns").Select
Range("G7").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Blowdown Log").Select
Application.CutCopyMode = False
Sheets("Pipeline Blowdowns").Select
Selection.Copy
Sheets("Blowdown Log").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone
SkipBlanks:= _
False, Transpose:=False
Range("G5").Select
Sheets("Pipeline Blowdowns").Select
Range("D22").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Blowdown Log").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone
SkipBlanks:= _
False, Transpose:=False
Range("H5").Select
Sheets("Pipeline Blowdowns").Select
Range("D23").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Blowdown Log").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone
SkipBlanks:= _
False, Transpose:=False
'End series of copying and pasting outputs
'Insert a final formula into "Blowdown Logs"
Range("I5").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=RC[-2]-RC[-1]"
'Format numbers
Range("G5:I5").Select
Selection.NumberFormat = "#,##0"
End Su

--
Message posted from http://www.ExcelForum.com