Code to add Formulas with new records

[ssGuru]

I have a structured SS1 without any formulas. Header row and records.
I have a SS 2 that does all the calculations needed to report on SS1.
Its formulas point to SS1.

How can I have code automatically add formulas after the previous last
row in SS2 when a new record is added to SS1?

Currently I manually copy these IF THEN formulas for about 500 empty
records at a time in SS2 in preparation to receive new records in
SS1. They return "" if no values in SS1.

Is it possible to automate this process one record at a time?

Thanks for your feedback,
Dennis

 

[Guest]

Using a worksheet_change macro on sheet1 you can add formulas into sheet2.

 

[ssGuru]

Using a worksheet_change macro on sheet1 you can add formulas into sheet2.









- Show quoted text -

Could you give a brief code example or point me to a site for
something to work with?
Dennis

 

[Guest]

Put the following code in a new workbook as follows

1) Go to sheet 1
2) right click tab on bottom of worksheet that is labeled Sheet1
3) Select last item "View Code"
4) Copy and Past the Macro below into the VBA sheet.


Now anything you type on sheet 1 will get referenced on sheet 2 with a
formula.


Sub Worksheet_Change(ByVal Target As Range)

If Not IsEmpty(Target) Then
With Sheets("Sheet2")
If IsEmpty(.Range("A1")) Then
NewRow = 1
Else
NewRow = .Cells(Rows.Count, "A"). _
End(xlUp).Row + 1
End If

.Cells(NewRow, "A").FormulaR1C1 = _
"='Sheet1'!R" & Target.Row & "C" & _
Target.Column
End With
End If

 

[ssGuru]

Put the following code in a new workbook as follows

1) Go to sheet 1
2) right click tab on bottom of worksheet that is labeled Sheet1
3) Select last item "View Code"
4) Copy and Past the Macro below into the VBA sheet.

Now anything you type on sheet 1 will get referenced on sheet 2 with a
formula.

Sub Worksheet_Change(ByVal Target As Range)

If Not IsEmpty(Target) Then
With Sheets("Sheet2")
If IsEmpty(.Range("A1")) Then
NewRow = 1
Else
NewRow = .Cells(Rows.Count, "A"). _
End(xlUp).Row + 1
End If

.Cells(NewRow, "A").FormulaR1C1 = _
"='Sheet1'!R" & Target.Row & "C" & _
Target.Column
End With
End If






- Show quoted text -

Thanks for your excellent example Joel.
This gives me a place to start at least. The result of your example
is that as Values are added to Sheet1, Col1, Col2, Col3... they are
automatically referenced for example down ColA as:
=Sheet1!$A$1
=Sheet1!$B$1
=Sheet1!$C$1
=Sheet1!$A$2
=Sheet1!$B$2
=Sheet1!$C$2
=Sheet1!$B$3
=Sheet1!$B$4

My goal is to replicate some calculation formulas I already have in
sheet2 across some columns that do reference the same row in Sheet1 by
using Named Column/Fields. When a new record is added to Sheet1 Row 5
for example I want to replicate all the column formulas I already have
in Sheet2 Row 4 and copy them down to Sheet2 Row5.

For example a formula I now have manually copied down a column in
Sheet2 may be something like:
=IF(ProspectRetail<>"",VLOOKUP(ProspectRetail,TblDiscount,3,TRUE),"")
The result is for example this formula references a column in Sheet1
named "ProspectRetail" in the same row as this formula, looks up that
$value amount in a table named "TblDiscount" and returns the discount
rate from column3 of TblDiscount. If there is NO value in Sheet1 it
returns a "". I have many similar formulas in Sheet2 copied down a
fixed number of rows which works just fine. UNTIL at some future time
I have more records in Sheet1 than I have already copied formulas in
to Sheet2.

Maybe for every new record created in Sheet1 by adding a value in ANY
column of a new record, Worksheet_Change can just trigger a paste of a
predetermined set of formulas into Sheet2 in the first empty row by
counting the existing rows. Perhaps the formulas can be held in code
OR I can copy them from the last row currently in Sheet2 with formulas
and PASTE/SPECIAL/FORMULAS into the entire new row in Sheet2 that now
needs formulas because of a new record in Sheet1.

Can you suggest a better method or any help with the Worksheet_Change
code in Sheet1 that will force it to copy the formulas I already have
to the corresponding row(s) in Sheet2?

Thanks again for your excellent suggestions and help,
Dennis

 

[Guest]

Sub Worksheet_Change(ByVal Target As Range)

Lastcolumn = Cells(Target.Row, Columns.Count). _
End(xlToLeft).Column

Set CopyRange = Range(Cells(Target.Row, "A"), _
Cells(Target.Row, Lastcolumn))

For Each cell In CopyRange
If Not IsEmpty(cell) Then
myformula = cell.Formula
myformula = "=Sheet1!" & Mid(myformula, 2)
Sheets("Sheet2").Cells(Target.Row, Target.Column). _
Formula = myformula
End If
Next cell
End Sub

 

[ssGuru]

Sub Worksheet_Change(ByVal Target As Range)

Lastcolumn = Cells(Target.Row, Columns.Count). _
End(xlToLeft).Column

Set CopyRange = Range(Cells(Target.Row, "A"), _
Cells(Target.Row, Lastcolumn))

For Each cell In CopyRange
If Not IsEmpty(cell) Then
myformula = cell.Formula
myformula = "=Sheet1!" & Mid(myformula, 2)
Sheets("Sheet2").Cells(Target.Row, Target.Column). _
Formula = myformula
End If
Next cell
End Sub

Thanks Joel but the code now breaks at and doesn't add anything to SS2
"... Sheets("Sheet2").Cells(Target.Row, Target.Column). _
Formula = myformula
...."

For example in SS1 I might have headings and data such as:
PipeDate ProLic LicCost
07/05/07 4 $100.00
07/03/07 3 $200.00

In SS2 which ONLY has formulas I might have headings and formulas such
as:
SalesRecID LicTotalGross
=IF(Sheet1!A2<>"",Sheet1!A2&"PName","") =IF(A2<>"",(Sheet1!
B2*Sheet1!C2),"")
=IF(Sheet1!A3<>"",Sheet1!A3&"PName","") =IF(A3<>"",(Sheet1!
B3*Sheet1!C3),"")

Records in SS1 will be bulk loaded from another template. There may
be one or many.

My goal again is when NEW record(s) are added to SS1 that code then
copies and paste/special/formula or INSERT the formulas(example) from
the last row in SS2 (or from code) to the same number of blank rows in
SS2 that I have new records just added to SS1. Of course I need to
keep the number of records in SS2 in sync with the number of records
in SS1.

ALSO do you think that the best approach to keeping the number of
records in SS1 and SS2 in sync is to use an onchange for SS1 which
fires with each change in each cell? In addition to new records being
added, some existing records in SS1 may be periodically updated with
different values and it would not be necessary to change any formulas
for that corresponding row in SS2.

I think I would like to keep the calculating formulas for SS2 in code
rather than copying from previous last row of SS2 if possible.

Thanks again for your consideration and help,
Dennis

 

[Guest]

I've been really busy last couple of days. The code was a little tricky.
The check to make sure the row wasn't already on sheet 2 wasn't easy. I
don't know what you wanted for PName so this mnay need some corrections.

Sheet2 doesn't get an entry until columns A, B, & C all have entries in
sheet 1.


Sub Worksheet_Change(ByVal Target As Range)

For Each cell In Target
If Not IsEmpty(Cells(Target.Row, "A")) And _
Not IsEmpty(Cells(Target.Row, "B")) And _
Not IsEmpty(Cells(Target.Row, "C")) Then

With Sheets("Sheet2")

If IsEmpty(.Range("A1")) Then
NewRow = 1
Else
NewRow = .Cells(Rows.Count, "A"). _
End(xlUp).Row + 1
End If

MyformulaColA = "=TEXT(Sheet1!R" & _
Target.Row & "C" & Target.Column

'check if entry already exist
Found = False
For RowCount = 1 To (NewRow - 1)

cellref = .Cells(RowCount, "A").FormulaR1C1

beginofcellref = _
Left(cellref, Len(MyformulaColA))
If StrComp(MyformulaColA, _
beginofcellref) = 0 Then

Found = True
Exit For
End If
Next RowCount

If Found = False Then
stringdate = ", ""mm/dd/yy"")"

MyformulaColA = MyformulaColA & _
stringdate & "&"" PName"""
.Cells(NewRow, "A").FormulaR1C1 = _
MyformulaColA
MyformulaColB = "=Sheet1!R" & _
Target.Row & "C2 * " & _
"Sheet1!R" & Target.Row & "C3"
.Cells(NewRow, "B").FormulaR1C1 = _
MyformulaColB
.Cells(NewRow, "B").NumberFormat = _
"$#,##0.00"

End If
End With
End If
Next cell
End Sub

 

[ssGuru]

I've been really busy last couple of days. The code was a little tricky.
The check to make sure the row wasn't already on sheet 2 wasn't easy. I
don't know what you wanted for PName so this mnay need some corrections.

Sheet2 doesn't get an entry until columns A, B, & C all have entries in
sheet 1.

Sub Worksheet_Change(ByVal Target As Range)

For Each cell In Target
If Not IsEmpty(Cells(Target.Row, "A")) And _
Not IsEmpty(Cells(Target.Row, "B")) And _
Not IsEmpty(Cells(Target.Row, "C")) Then

With Sheets("Sheet2")

If IsEmpty(.Range("A1")) Then
NewRow = 1
Else
NewRow = .Cells(Rows.Count, "A"). _
End(xlUp).Row + 1
End If

MyformulaColA = "=TEXT(Sheet1!R" & _
Target.Row & "C" & Target.Column

'check if entry already exist
Found = False
For RowCount = 1 To (NewRow - 1)

cellref = .Cells(RowCount, "A").FormulaR1C1

beginofcellref = _
Left(cellref, Len(MyformulaColA))
If StrComp(MyformulaColA, _
beginofcellref) = 0 Then

Found = True
Exit For
End If
Next RowCount

If Found = False Then
stringdate = ", ""mm/dd/yy"")"

MyformulaColA = MyformulaColA & _
stringdate & "&"" PName"""
.Cells(NewRow, "A").FormulaR1C1 = _
MyformulaColA
MyformulaColB = "=Sheet1!R" & _
Target.Row & "C2 * " & _
"Sheet1!R" & Target.Row & "C3"
.Cells(NewRow, "B").FormulaR1C1 = _
MyformulaColB
.Cells(NewRow, "B").NumberFormat = _
"$#,##0.00"

End If
End With
End If
Next cell
End Sub













- Show quoted text -

Thanks Joel for all your time and expertise. I thought I had posted a
response but don't see it so posting another.
The sample code does create records on S2 after entering values in S1
so thanks again for giving me a start on this project.

Still more than a couple of problems I need to resolve however. I'm
apparently too dense to find where in the code that a resultant S2
ColA formula is forced to point to the correct column in S1. In the
sample it needs to point to S1 ColA or to the NAMED range DateRec
which are the same thing.

The formulas created in S2 ColA by the code have incorrectly referred
to S1 ColC instead of S1 ColA.
Example: =TEXT(Sheet1!$C$2, "mm/dd/yy")&" PName"
=TEXT(Sheet1!$C$3, "mm/dd/yy")&" PName"
(BTW PName should refer to a Named cell to return a company name and
the date should just return the serial value so date formatting NOT
needed.)
The result value tries to use the cost of a license from ColC instead
of the date of the record ColA from S1 so it fails to return a correct
value.

ALSO
ANY change to any existing record fields and the code fires off
another record entry in S2 rather than the number of records in S1 and
S2 being the same count.

ALSO any deletion of a record in S1 means that the corresponding
record in S2 returns the #REF error since its reference cells are
gone.

I'm sorry but I have not been able to define where in the code that
ColC is obtained instead of ColA. Thanks for showing me this can be
done at least even though I'm still struggling. The calculations for
S2 Colb are working perfectly since they point to the correct S1
columns to multiply them together.

All the columns in my real project are NAMED ranges so my formulas in
S2 refer to the NAMED ranges in S1 and return the values on the SAME
row between the two sheets. Most are fairly complex but work like a
charm. So if you were to examine ANY record in S2 the formulas would
look the same as any other record in S2. Deleting or editing records
in S1 has no effect on S2. It just keeps happily reporting on its
row. The problem I needed to solve was that in order to maintain this
project I have to remember to copy and maintain the same number of
rows in S2 as I have in S1 for some complex calculations and counts to
work. I felt the ability to programmatically maintain the formulas
needed for each record by NAMED column range in S2 and add or remove
S2 records as needed to match the count in S1 would be very useful.
It is proving to be a greater challenge than it seems that it should
be.

I feel that maintaining separate DATA and CALCULATION spreadsheets is
good practice and allows more accurate importation and editing of the
DATA without incurring any problems with any calculations. I was
surprised that we didn't see more feedback on this issue to help us
resolve the problems.
Again Joel, thanks for your valuable time and expertise. If you can
help point me to resolving the sample code problems that would be much
appreciated. If you can help point me on how to use NAMED ranges
rather than just RC references in this sample automation code that
would be great.

Dennis

 

[Guest]

I found 1 error. PNAME is a $%!&* (mind my french). Make sure you get the
right number of double quotes. There are single, double, and quadruple. I
never get this right the first time. Pname now refer to a name on the
worksheet.

The function ActiveWorkbook.Names("PName") put an equal sign in front of the
name which causes an error. It took me a while to figure out to use

ActiveWorkbook.Names("PName").RefersToRange.Formula

which removes the equal sign. Hope this isn't too difficult for you to
understand.

the best way to learn the code is to single step through the code yourself.
Set a break point on the first line of code by left click on code then press
F9. Then enter you data on worksheet. When you get to a break point press
F8 to step through code. Highlighting a variable and right click lets you
add variable to watch window. Moving cursor over the code also makes
variable visable.

from:
MyformulaColA = "=TEXT(Sheet1!R" & _
Target.Row & "C" & Target.Column
to:
MyformulaColA = "=TEXT(Sheet1!R" & _
Target.Row & "C1"

This problem is causing another entry in S2 when changes are made.

I need to do the date formating if in the same cell you are combining a
string with the date. If I don't do this the date is treated as the number
of days from Jan 1, 1900 instead of date format.

To fix PNAME
from:
MyformulaColA = MyformulaColA & _
stringdate & "&"" PName"""
to:
LocalPName = ActiveWorkbook.Names("PName"). _
RefersToRange.Formula

MyformulaColA = MyformulaColA & _
stringdate & " & """ & LocalPName & """"

The new code should be as follows:


Sub Worksheet_Change(ByVal Target As Range)

For Each cell In Target
If Not IsEmpty(Cells(Target.Row, "A")) And _
Not IsEmpty(Cells(Target.Row, "B")) And _
Not IsEmpty(Cells(Target.Row, "C")) Then

With Sheets("Sheet2")

If IsEmpty(.Range("A1")) Then
NewRow = 1
Else
NewRow = .Cells(Rows.Count, "A"). _
End(xlUp).Row + 1
End If

MyformulaColA = "=TEXT(Sheet1!R" & _
Target.Row & "C1"

'check if entry already exist
Found = False
For RowCount = 1 To (NewRow - 1)

cellref = .Cells(RowCount, "A").FormulaR1C1

beginofcellref = _
Left(cellref, Len(MyformulaColA))
If StrComp(MyformulaColA, _
beginofcellref) = 0 Then

Found = True
Exit For
End If
Next RowCount

If Found = False Then
stringdate = ", ""mm/dd/yy"")"
LocalPName = ActiveWorkbook.Names("PName"). _
RefersToRange.Formula

MyformulaColA = MyformulaColA & _
stringdate & " & """ & LocalPName & """"

.Cells(NewRow, "A").FormulaR1C1 = _
MyformulaColA
MyformulaColB = "=Sheet1!R" & _
Target.Row & "C2 * " & _
"Sheet1!R" & Target.Row & "C3"
.Cells(NewRow, "B").FormulaR1C1 = _
MyformulaColB
.Cells(NewRow, "B").NumberFormat = _
"$#,##0.00"

End If
End With
End If
Next cell
End Sub