Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Macro: Updates a formula or value when a data is entered.
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!!! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Macro: Updates a formula or value when a data is entered.
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) " wrote in message ... 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!!! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Macro: Updates a formula or value when a data is entered.
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(B 8,"-",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(CONCATENAT E(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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Macro: Updates a formula or value when a data is entered.
" wrote in message ... 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(B 8,"-",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(CONCATENAT E(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 & "), |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Macro: Updates a formula or value when a data is entered.
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
macro | Excel Discussion (Misc queries) | |||
error when running cut & paste macro | Excel Worksheet Functions | |||
CAE macro for green screen updates with Excel data | Excel Discussion (Misc queries) | |||
Macro question | Excel Worksheet Functions | |||
Excel Macro to Copy & Paste | Excel Worksheet Functions |