Macro: Updates a formula or value when a data is entered.

[Qull666]

Please help!!! I have been trying to get the answer for this, a time too
many. I know its possible.

I have about 20,000 rows of data in Cell A2:A20001 to O2:O20001 and growing.
I have formulas for each row in columns P to V.

How to use the macro to record when data is entered into cell A2 Cell
P2,Q2,R2,S2,T2,U2,V2 automatically updates a formula or value? And when Cell
A2 is blank Cell P2-V2 is also blank.

Meaning, when data is entered in to any cell in column A, the same row of P
to V automatically updates itself.

I have tried the Macro recorder, but the result is rather restricted to what
has been done.

What I am trying to do is avoid copy or drag, but to automate.

The only thing I have now in my head is to copy or drag Cell Column P2 to
P65,536 : V2 to V65,536.

Thanks a billion!!!

 

[Bob Phillips]

using made up formulae

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "A:A" '<== change to suit

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
If .Value = "" Then
.Offset(0, 15).Resize(1, 7).ClearContents
Else
'add formulae
.Offset(0, 15).FormulaR1C1 = "=RC[-15]"
'etc.
.Offset(0, 21).FormulaR1C1 = "=SUM(RC[-6]:RC[-1])"
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click


--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)

 

[Qull666]

Thanks Bob, I've seen a lot of your solutions and teaching. One word!
Fantastic!

For fine tuning, 3 more questions. Hope you don't mind.

Question 1. I have tried adding this, but it doesn't work.

If Cells.Count > 1 Then Exit Sub
If Len(Target.Value) > 0 Then Exit Sub

What I am trying to do:
When I hit the delete button the formula would reinstate itself to the
formula coz when I hit the delete button, cell A2 must be re-entered in order
for the formula to re-appear (it also allows for change).

For example:

1st Time
Cell A2: 200
Cell P2: 200 (Offset(0, 15).FormulaR1C1 = "=RC[-15]")

2nd Time
Cell A2: 200
Cell P2: *blank* Deleted or changed by mistake.

Question 2. Approximately what is the best method to use to write the R1C1
formula below (headache!), the way I wrote the formula is a part by part.

This is not a problem ="CONCATENATE(RC[-1],""-"",RC[-2])" using the macro
recorder.

This is a problem ="IF(ISBLANK(C8),"
",IF(ISNA(INDEX(Dbase!C:C,MATCH(CONCATENATE(B8,"-",C8),Dbase!I:I,0))),"
",INDEX(Dbase!C:C,MATCH(CONCATENATE(B8,"-",C8),Dbase!I:I,0))&":
"&IF(VLOOKUP(INDEX(Dbase!C:C,MATCH(CONCATENATE(B8,"-",C8),Dbase!I:I,0)),'Com-Max'!A:B,2,FALSE)=0,"-",TEXT(VLOOKUP(INDEX(Dbase!C:C,MATCH(CONCATENATE(B8,"-",C8),Dbase!I:I,0)),'Com-Max'!A:B,2,FALSE),"mmm-yy"))))

Question 3. Is it possible to use something like this, it's much simpler
instead of R1C1 formula.

If Target.Address = "$L$6" Then Target.Formula= "=YEAR(TODAY())"
If Target.Address = "$L$12" Then Target.Formula=
"=IF(ISERROR(RIGHT(INDEX('31'!L:L,MATCH(CONCATENATE(G6,""-"",L6),'31'!S:S,0)),SEARCH(""-"",INDEX('31'!L:L,MATCH(CONCATENATE(G6,""-"",L6),'31'!S:S,0))))+1),""Not
Paid"",RIGHT(INDEX('31'!L:L,MATCH(CONCATENATE(G6,""-"",L6),'31'!S:S,0)),SEARCH(""-"",INDEX('31'!L:L,MATCH(CONCATENATE(G6,""-"",L6),'31'!S:S,0)))+1))"

I am still learning sumproduct to replace the concatenate function.

Thank you once again Bob.

 

[Bob Phillips]

Thanks Bob, I've seen a lot of your solutions and teaching. One word!
Fantastic!

For fine tuning, 3 more questions. Hope you don't mind.

Question 1. I have tried adding this, but it doesn't work.

If Cells.Count > 1 Then Exit Sub

Just add this line

If .Cells.Count > 1 Then GoTo ws_exit

after the line


With Target

If Len(Target.Value) > 0 Then Exit Sub
What I am trying to do:
When I hit the delete button the formula would reinstate itself to the
formula coz when I hit the delete button, cell A2 must be re-entered in
order
for the formula to re-appear (it also allows for change).

For example:

1st Time
Cell A2: 200
Cell P2: 200 (Offset(0, 15).FormulaR1C1 = "=RC[-15]")

2nd Time
Cell A2: 200
Cell P2: *blank* Deleted or changed by mistake.

I don't understand what you are trying to do here, testing the len for > 0
and exiting means that the cells P:V never get cleared down.

Question 2. Approximately what is the best method to use to write the R1C1
formula below (headache!), the way I wrote the formula is a part by part.

This is not a problem ="CONCATENATE(RC[-1],""-"",RC[-2])" using the macro
recorder.

This is a problem ="IF(ISBLANK(C8),"
",IF(ISNA(INDEX(Dbase!C:C,MATCH(CONCATENATE(B8,"-",C8),Dbase!I:I,0))),"
",INDEX(Dbase!C:C,MATCH(CONCATENATE(B8,"-",C8),Dbase!I:I,0))&":
"&IF(VLOOKUP(INDEX(Dbase!C:C,MATCH(CONCATENATE(B8,"-",C8),Dbase!I:I,0)),'Com-Max'!A:B,2,FALSE)=0,"-",TEXT(VLOOKUP(INDEX(Dbase!C:C,MATCH(CONCATENATE(B8,"-",C8),Dbase!I:I,0)),'Com-Max'!A:B,2,FALSE),"mmm-yy"))))

In this case, I would do two things.

First, I wouldn't use R1C1, I would use A1 notation and pick up the row from
Target

and second, I would build the string up bit by bit, so that I could debug
what was happening more easily.

Something like this (I haven't got your formula correct, this is just to
show the technique

sFormula = "=IF(ISBLANK(C" & .Row & "),"
sFormula = sFormula & ",IF(ISNA(INDEX(Dbase!C:C,"
sFormula = sFormula & "MATCH(CONCATENATE(B" & .Row & "," - ",C" & .Row &
"),Dbase!I:I,0))),"
sFormula = sFormula & ",INDEX(Dbase!C:C,MATCH(CONCATENATE(B" & .Row & "," -
",C" & .Row & "),Dbase!I:I,0)@"
sFormula = sFormula & "):&IF(VLOOKUP(INDEX(Dbase!C:C,"
sFormula = sFormula & "MATCH(CONCATENATE(B" & .Row & "," - ",C" & .Row &
"),Dbase!I:I,0)@"
sFormula = sFormula & "),'Com-Max'!A:B,2,FALSE)=0," -
",TEXT(VLOOKUP(INDEX(Dbase!C:C,"
sFormula = sFormula & "MATCH(CONCATENATE(B" & .Row & "," - ",C" & .Row &
"),Dbase!I:I,0)),'Com-Max'!A:B,2,FALSE),""mmm-yy""))))"

..Offset(0, 17).Formula = sFormula

Question 3. Is it possible to use something like this, it's much simpler
instead of R1C1 formula.

If Target.Address = "$L$6" Then Target.Formula= "=YEAR(TODAY())"
If Target.Address = "$L$12" Then Target.Formula=
"=IF(ISERROR(RIGHT(INDEX('31'!L:L,MATCH(CONCATENATE(G6,""-"",L6),'31'!S:S,0)),SEARCH(""-"",INDEX('31'!L:L,MATCH(CONCATENATE(G6,""-"",L6),'31'!S:S,0))))+1),""Not
Paid"",RIGHT(INDEX('31'!L:L,MATCH(CONCATENATE(G6,""-"",L6),'31'!S:S,0)),SEARCH(""-"",INDEX('31'!L:L,MATCH(CONCATENATE(G6,""-"",L6),'31'!S:S,0)))+1))"

Yes, that is the point I was making in 2, but you have to replace the
hard-coded row number with the dynamic .row, for instance

CONCATENATE(G6,""-"",L6)

becomes

CONCATENATE(G" & Target.Row & ",""-"",L" & Target.Row & "),

 

[Qull666]

In the diagram below, Column A to D represents DATA like (key in)

A: Date
B: Document Number
C: Sales Person
D: Amount

Column E to G represents FORMULA like
E: Vlookup / Index Match
F: SumIF
G: + - x /

Sheet 1
------------Data-------------+---Formula-----Column
-----A-----B-----C-----D-----E-----F-----G
1---
2---
3---
4---
5---
6---
7---
8---
9---
Row

Current Method:
Lets say the FORMULA row is only up to E3 after the latest access (3 rows of
data). And after some data entry the DATA line it reaches Row 9.

What I will do is copy / drag E3:G3 downwards to row 9 and the formula is
pegged for each line. (Manual)

I use lots of Pivot Table. The structure of the source data is as per
diagram. One row to contain all information.

When your codes contain R1C1, that is beyond my ability.

Your codes do automate, however its a little tough! I guess I am using
surface functions to steer IN the VBA (that deserves a kick in the ***) Thats
why I have lots of hard code and long formulas!

For the Len thing: Copy and Paste !!! (extracted from the script below)

The VBA script below is for fixed cell but it does not automatically peg a
data line. (Acquired earlier)

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

If Target.Cells.Count > 1 Then Exit Sub
If Len(Target.Value) > 0 Then Exit Sub

Application.EnableEvents = False

If Target.Address = "$E$6" Then Target.Formula= "=YEAR(TODAY())"

If Target.Address = "$F$6" Then Target.Formula=
"=SUMIF('31'!S:S,CONCATENATE(G6,""-"",L6),'31'!H:H)"

If Target.Address = "$G$6" Then Target.Formula=
"=IF(ISERROR(LEFT(INDEX('31'!L:L,MATCH(CONCATENATE(G6,""-"",L6),'31'!S:S,0)),SEARCH(""-"",INDEX('31'!L:L,MATCH(CONCATENATE(G6,""-"",L6),'31'!S:S,0)))-1)),"""",LEFT(INDEX('31'!L:L,MATCH(CONCATENATE(G6,""-"",L6),'31'!S:S,0)),SEARCH(""-"",INDEX('31'!L:L,MATCH(CONCATENATE(G6,""-"",L6),'31'!S:S,0)))-1))

Trial & error!!!!

Hey Bob, thanks ! Appreciate your effort and time. I will use the codes you
have given me.