|
Fixed Text-Private Sub Worksheet_Change(ByVal Target As Excel.Rang
Quill,
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 = "$B$5" Then Target.Value = "Enter Something" If Target.Address = "$B$6" Then Target.Value = "Enter New Code" If Target.Address = "$C$1" Then Target.Value = "Enter Old Code" If Target.Address = "$C$2" Then Target.Value = "Enter Old Code" 'And so on...... ' I hope you can see the logic Application.EnableEvents = True End Sub HTH, Bernie MS Excel MVP " wrote in message ... I have attached below and the result in Cell B5=Enter Something Private Sub Worksheet_Change(ByVal Target As Excel.Range) If Target.Address = "$B$5" Then If Len(Target.Value) = 0 Then Target.Value = "Enter Something" End If End Sub Question: In Cell B6 can I use some kind of formula resulting in "Enter New Code" along with the same formula above. Meaning: Same worksheet: B5=Enter Something B6=Enter New Code Additional: Example of Result Same worksheet: B5=Enter Something B6=Enter New Code C1 to C2=Enter Old Code E1=Help Me. Thanks a bundle. Quill666 |
Fixed Text-Private Sub Worksheet_Change(ByVal Target As Excel.
Thanks Bernie.
By the way, I have one question what is the first thing to do before becoming a MVP or some sort of VBA programmer. "Bernie Deitrick" wrote: Quill, 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 = "$B$5" Then Target.Value = "Enter Something" If Target.Address = "$B$6" Then Target.Value = "Enter New Code" If Target.Address = "$C$1" Then Target.Value = "Enter Old Code" If Target.Address = "$C$2" Then Target.Value = "Enter Old Code" 'And so on...... ' I hope you can see the logic Application.EnableEvents = True End Sub HTH, Bernie MS Excel MVP " wrote in message ... I have attached below and the result in Cell B5=Enter Something Private Sub Worksheet_Change(ByVal Target As Excel.Range) If Target.Address = "$B$5" Then If Len(Target.Value) = 0 Then Target.Value = "Enter Something" End If End Sub Question: In Cell B6 can I use some kind of formula resulting in "Enter New Code" along with the same formula above. Meaning: Same worksheet: B5=Enter Something B6=Enter New Code Additional: Example of Result Same worksheet: B5=Enter Something B6=Enter New Code C1 to C2=Enter Old Code E1=Help Me. Thanks a bundle. Quill666 |
Fixed Text-Private Sub Worksheet_Change(ByVal Target As Excel.
Quill,
In one word: practice. Do a defined, complicated project for yourself. Work on the problems here, and answer as many as you can. Read and understand the replies to those that you cannot answer. But you cannot choose to become an MVP - you are chosen to receive the award. Bernie MS Excel MVP " wrote in message ... Thanks Bernie. By the way, I have one question what is the first thing to do before becoming a MVP or some sort of VBA programmer. "Bernie Deitrick" wrote: Quill, 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 = "$B$5" Then Target.Value = "Enter Something" If Target.Address = "$B$6" Then Target.Value = "Enter New Code" If Target.Address = "$C$1" Then Target.Value = "Enter Old Code" If Target.Address = "$C$2" Then Target.Value = "Enter Old Code" 'And so on...... ' I hope you can see the logic Application.EnableEvents = True End Sub HTH, Bernie MS Excel MVP " wrote in message ... I have attached below and the result in Cell B5=Enter Something Private Sub Worksheet_Change(ByVal Target As Excel.Range) If Target.Address = "$B$5" Then If Len(Target.Value) = 0 Then Target.Value = "Enter Something" End If End Sub Question: In Cell B6 can I use some kind of formula resulting in "Enter New Code" along with the same formula above. Meaning: Same worksheet: B5=Enter Something B6=Enter New Code Additional: Example of Result Same worksheet: B5=Enter Something B6=Enter New Code C1 to C2=Enter Old Code E1=Help Me. Thanks a bundle. Quill666 |
Fixed Text-Private Sub Worksheet_Change(ByVal Target As Excel.
That is good advice.
Going back to the private sub worksheet_change, lets say: How do I use the VBA, When Cell A1 (trigger) is not blank, then Cell C3 will have "Enter someting" or in my case the formula "=VLOOKUP(A1,Calendar!A:B,2,FALSE)" And it has to able to list automatically downward say from line 1 to line 65,000. Say: Cell A45 is 0 then C45 will list the fomula (if Cell A45 is blank, Cell C45 is also empty) * I use another sheet named Calendar! to group the days into month, say 08-Jan-07 or 09 Jan-07 = 31-Jan-07 and when Cell A1 (trigger) is blank, there is nothing, or just empty or another pre-defined formula (IF function). "Cell C:C is fully automate in a way", triggered by column A:A and same row. I am doing it the conventional way of listing it or using IsBlank + Vlookup + lots of formula, and using the pivot table to generate the reports and copying the formulas downwards if the data line exceeds the formula lines. Thanks very much for your time. "Bernie Deitrick" wrote: Quill, In one word: practice. Do a defined, complicated project for yourself. Work on the problems here, and answer as many as you can. Read and understand the replies to those that you cannot answer. But you cannot choose to become an MVP - you are chosen to receive the award. Bernie MS Excel MVP " wrote in message ... Thanks Bernie. By the way, I have one question what is the first thing to do before becoming a MVP or some sort of VBA programmer. "Bernie Deitrick" wrote: Quill, 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 = "$B$5" Then Target.Value = "Enter Something" If Target.Address = "$B$6" Then Target.Value = "Enter New Code" If Target.Address = "$C$1" Then Target.Value = "Enter Old Code" If Target.Address = "$C$2" Then Target.Value = "Enter Old Code" 'And so on...... ' I hope you can see the logic Application.EnableEvents = True End Sub HTH, Bernie MS Excel MVP " wrote in message ... I have attached below and the result in Cell B5=Enter Something Private Sub Worksheet_Change(ByVal Target As Excel.Range) If Target.Address = "$B$5" Then If Len(Target.Value) = 0 Then Target.Value = "Enter Something" End If End Sub Question: In Cell B6 can I use some kind of formula resulting in "Enter New Code" along with the same formula above. Meaning: Same worksheet: B5=Enter Something B6=Enter New Code Additional: Example of Result Same worksheet: B5=Enter Something B6=Enter New Code C1 to C2=Enter Old Code E1=Help Me. Thanks a bundle. Quill666 |
Fixed Text-Private Sub Worksheet_Change(ByVal Target As Excel.
I tried this, but there was an error. Please help.
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 = "$L$6" Then Target.Value = "=YEAR(TODAY())" If Target.Address = "$L$10" Then Target.Value = "187863U" If Target.Address = "$L$12" Then Target.Value = "=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)" If Target.Address = "$L$14" Then Target.Value = "=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)" If Target.Address = "$L$18" Then Target.Value = "=INDEX('31'!J:J,MATCH(CONCATENATE(G6," - ",L6),'31'!S:S,0))" If Target.Address = "$L$8" Then Target.Value = "=SUMIF('31'!S:S,CONCATENATE(G6," - ",L6),'31'!H:H)" Application.EnableEvents = True End Sub ------------------------------------------------------------------------------ " wrote: That is good advice. Going back to the private sub worksheet_change, lets say: How do I use the VBA, When Cell A1 (trigger) is not blank, then Cell C3 will have "Enter someting" or in my case the formula "=VLOOKUP(A1,Calendar!A:B,2,FALSE)" And it has to able to list automatically downward say from line 1 to line 65,000. Say: Cell A45 is 0 then C45 will list the fomula (if Cell A45 is blank, Cell C45 is also empty) * I use another sheet named Calendar! to group the days into month, say 08-Jan-07 or 09 Jan-07 = 31-Jan-07 and when Cell A1 (trigger) is blank, there is nothing, or just empty or another pre-defined formula (IF function). "Cell C:C is fully automate in a way", triggered by column A:A and same row. I am doing it the conventional way of listing it or using IsBlank + Vlookup + lots of formula, and using the pivot table to generate the reports and copying the formulas downwards if the data line exceeds the formula lines. Thanks very much for your time. "Bernie Deitrick" wrote: Quill, In one word: practice. Do a defined, complicated project for yourself. Work on the problems here, and answer as many as you can. Read and understand the replies to those that you cannot answer. But you cannot choose to become an MVP - you are chosen to receive the award. Bernie MS Excel MVP " wrote in message ... Thanks Bernie. By the way, I have one question what is the first thing to do before becoming a MVP or some sort of VBA programmer. "Bernie Deitrick" wrote: Quill, 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 = "$B$5" Then Target.Value = "Enter Something" If Target.Address = "$B$6" Then Target.Value = "Enter New Code" If Target.Address = "$C$1" Then Target.Value = "Enter Old Code" If Target.Address = "$C$2" Then Target.Value = "Enter Old Code" 'And so on...... ' I hope you can see the logic Application.EnableEvents = True End Sub HTH, Bernie MS Excel MVP " wrote in message ... I have attached below and the result in Cell B5=Enter Something Private Sub Worksheet_Change(ByVal Target As Excel.Range) If Target.Address = "$B$5" Then If Len(Target.Value) = 0 Then Target.Value = "Enter Something" End If End Sub Question: In Cell B6 can I use some kind of formula resulting in "Enter New Code" along with the same formula above. Meaning: Same worksheet: B5=Enter Something B6=Enter New Code Additional: Example of Result Same worksheet: B5=Enter Something B6=Enter New Code C1 to C2=Enter Old Code E1=Help Me. Thanks a bundle. Quill666 |
Fixed Text-Private Sub Worksheet_Change(ByVal Target As Excel.
Quill,
You need to set the .Formula property of the target cell: see below. HTH, Bernie MS Excel MVP 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 = "$L$6" Then Target.Formula= "=YEAR(TODAY())" If Target.Address = "$L$10" Then Target.Value = "187863U" If Target.Address = "$L$12" Then Target.Formula= "=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)" If Target.Address = "$L$14" Then Target.Formula = "=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)" If Target.Address = "$L$18" Then Target.Formula= "=INDEX('31'!J:J,MATCH(CONCATENATE(G6," - ",L6),'31'!S:S,0))" If Target.Address = "$L$8" Then Target.Formula= "=SUMIF('31'!S:S,CONCATENATE(G6," - ",L6),'31'!H:H)" Application.EnableEvents = True End Sub " wrote in message ... I tried this, but there was an error. Please help. 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 = "$L$6" Then Target.Value = "=YEAR(TODAY())" If Target.Address = "$L$10" Then Target.Value = "187863U" If Target.Address = "$L$12" Then Target.Value = "=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)" If Target.Address = "$L$14" Then Target.Value = "=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)" If Target.Address = "$L$18" Then Target.Value = "=INDEX('31'!J:J,MATCH(CONCATENATE(G6," - ",L6),'31'!S:S,0))" If Target.Address = "$L$8" Then Target.Value = "=SUMIF('31'!S:S,CONCATENATE(G6," - ",L6),'31'!H:H)" Application.EnableEvents = True End Sub ------------------------------------------------------------------------------ " wrote: That is good advice. Going back to the private sub worksheet_change, lets say: How do I use the VBA, When Cell A1 (trigger) is not blank, then Cell C3 will have "Enter someting" or in my case the formula "=VLOOKUP(A1,Calendar!A:B,2,FALSE)" And it has to able to list automatically downward say from line 1 to line 65,000. Say: Cell A45 is 0 then C45 will list the fomula (if Cell A45 is blank, Cell C45 is also empty) * I use another sheet named Calendar! to group the days into month, say 08-Jan-07 or 09 Jan-07 = 31-Jan-07 and when Cell A1 (trigger) is blank, there is nothing, or just empty or another pre-defined formula (IF function). "Cell C:C is fully automate in a way", triggered by column A:A and same row. I am doing it the conventional way of listing it or using IsBlank + Vlookup + lots of formula, and using the pivot table to generate the reports and copying the formulas downwards if the data line exceeds the formula lines. Thanks very much for your time. "Bernie Deitrick" wrote: Quill, In one word: practice. Do a defined, complicated project for yourself. Work on the problems here, and answer as many as you can. Read and understand the replies to those that you cannot answer. But you cannot choose to become an MVP - you are chosen to receive the award. Bernie MS Excel MVP " wrote in message ... Thanks Bernie. By the way, I have one question what is the first thing to do before becoming a MVP or some sort of VBA programmer. "Bernie Deitrick" wrote: Quill, 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 = "$B$5" Then Target.Value = "Enter Something" If Target.Address = "$B$6" Then Target.Value = "Enter New Code" If Target.Address = "$C$1" Then Target.Value = "Enter Old Code" If Target.Address = "$C$2" Then Target.Value = "Enter Old Code" 'And so on...... ' I hope you can see the logic Application.EnableEvents = True End Sub HTH, Bernie MS Excel MVP " wrote in message ... I have attached below and the result in Cell B5=Enter Something Private Sub Worksheet_Change(ByVal Target As Excel.Range) If Target.Address = "$B$5" Then If Len(Target.Value) = 0 Then Target.Value = "Enter Something" End If End Sub Question: In Cell B6 can I use some kind of formula resulting in "Enter New Code" along with the same formula above. Meaning: Same worksheet: B5=Enter Something B6=Enter New Code Additional: Example of Result Same worksheet: B5=Enter Something B6=Enter New Code C1 to C2=Enter Old Code E1=Help Me. Thanks a bundle. Quill666 |
Fixed Text-Private Sub Worksheet_Change(ByVal Target As Excel.
Thanks Bernie, Appreciate it very much.
I can do this but not VBA. ="MTD: " & CONCATENATE(TEXT(VLOOKUP(TODAY(),Calendar!A:B,2,FA LSE),"mmm-yy")," Out: ",TEXT(SUMIF(Out!K:K,VLOOKUP(TODAY(),Calendar!A:B, 2,FALSE),Out!I:I),"#,###"),"; " & "In : ",TEXT(SUMIF(IN!J:J,VLOOKUP(TODAY(),Calendar!A:B,2 ,FALSE),IN!F:F),"#,###")) =IF(ISBLANK(C6)," ",IF(ISNA(INDEX(Dbase!C:C,MATCH(CONCATENATE(B6 ,"-",C6),Dbase!I:I,0)))," ",INDEX(Dbase!C:C,MATCH(CONCATENATE(B6,"-",C6),Dbase!I:I,0))&": "&IF(VLOOKUP(INDEX(Dbase!C:C,MATCH(CONCATENATE(B6, "-",C6),Dbase!I:I,0)),'Com-Max'!A:B,2,FALSE)=0,"-",TEXT(VLOOKUP(INDEX(Dbase!C:C,MATCH(CONCATENATE(B 6,"-",C6),Dbase!I:I,0)),'Com-Max'!A:B,2,FALSE),"mmm-yy")))) 2 More Questions: Question 1: If I want to select a range, it will be If Target.Address.RANGE = A:A?? Question 2: Automation: How do I use the VBA, When Cell A1 (trigger) is not blank, then Cell C3 will have "Enter someting" or in my case the .formula "=VLOOKUP(A1,Calendar!A:B,2,FALSE)". And it has to be able to list automatically downward say from line 1 to line 65,000 but subject to Cell A content. Say: Cell A45 is 0 then C45 will show the fomula (if Cell A45 is blank, Cell C45 is also empty) * I use another sheet named Calendar! to group the days into month, say 08-Jan-07 or 09 Jan-07 = 31-Jan-07 "Cell C:C is fully automate in a way", triggered by column A:A and same row. I am doing it the conventional way of listing it or using IsBlank + Vlookup + lots of formula, and using the pivot table to generate the reports and copying formulas downwards if the data line exceeds the formula lines. Thanks very much for your time. "Bernie Deitrick" wrote: Quill, You need to set the .Formula property of the target cell: see below. HTH, Bernie MS Excel MVP 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 = "$L$6" Then Target.Formula= "=YEAR(TODAY())" If Target.Address = "$L$10" Then Target.Value = "187863U" If Target.Address = "$L$12" Then Target.Formula= "=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)" If Target.Address = "$L$14" Then Target.Formula = "=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)" If Target.Address = "$L$18" Then Target.Formula= "=INDEX('31'!J:J,MATCH(CONCATENATE(G6," - ",L6),'31'!S:S,0))" If Target.Address = "$L$8" Then Target.Formula= "=SUMIF('31'!S:S,CONCATENATE(G6," - ",L6),'31'!H:H)" Application.EnableEvents = True End Sub " wrote in message ... I tried this, but there was an error. Please help. 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 = "$L$6" Then Target.Value = "=YEAR(TODAY())" If Target.Address = "$L$10" Then Target.Value = "187863U" If Target.Address = "$L$12" Then Target.Value = "=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)" If Target.Address = "$L$14" Then Target.Value = "=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)" If Target.Address = "$L$18" Then Target.Value = "=INDEX('31'!J:J,MATCH(CONCATENATE(G6," - ",L6),'31'!S:S,0))" If Target.Address = "$L$8" Then Target.Value = "=SUMIF('31'!S:S,CONCATENATE(G6," - ",L6),'31'!H:H)" Application.EnableEvents = True End Sub ------------------------------------------------------------------------------ " wrote: That is good advice. Going back to the private sub worksheet_change, lets say: How do I use the VBA, When Cell A1 (trigger) is not blank, then Cell C3 will have "Enter someting" or in my case the formula "=VLOOKUP(A1,Calendar!A:B,2,FALSE)" And it has to able to list automatically downward say from line 1 to line 65,000. Say: Cell A45 is 0 then C45 will list the fomula (if Cell A45 is blank, Cell C45 is also empty) * I use another sheet named Calendar! to group the days into month, say 08-Jan-07 or 09 Jan-07 = 31-Jan-07 and when Cell A1 (trigger) is blank, there is nothing, or just empty or another pre-defined formula (IF function). "Cell C:C is fully automate in a way", triggered by column A:A and same row. I am doing it the conventional way of listing it or using IsBlank + Vlookup + lots of formula, and using the pivot table to generate the reports and copying the formulas downwards if the data line exceeds the formula lines. Thanks very much for your time. "Bernie Deitrick" wrote: Quill, In one word: practice. Do a defined, complicated project for yourself. Work on the problems here, and answer as many as you can. Read and understand the replies to those that you cannot answer. But you cannot choose to become an MVP - you are chosen to receive the award. Bernie MS Excel MVP " wrote in message ... Thanks Bernie. By the way, I have one question what is the first thing to do before becoming a MVP or some sort of VBA programmer. "Bernie Deitrick" wrote: Quill, 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 = "$B$5" Then Target.Value = "Enter Something" If Target.Address = "$B$6" Then Target.Value = "Enter New Code" If Target.Address = "$C$1" Then Target.Value = "Enter Old Code" If Target.Address = "$C$2" Then Target.Value = "Enter Old Code" 'And so on...... ' I hope you can see the logic Application.EnableEvents = True End Sub HTH, Bernie MS Excel MVP " wrote in message ... I have attached below and the result in Cell B5=Enter Something Private Sub Worksheet_Change(ByVal Target As Excel.Range) If Target.Address = "$B$5" Then If Len(Target.Value) = 0 Then Target.Value = "Enter Something" End If End Sub Question: In Cell B6 can I use some kind of formula resulting in "Enter New Code" along with the same formula above. Meaning: Same worksheet: B5=Enter Something B6=Enter New Code Additional: Example of Result Same worksheet: B5=Enter Something B6=Enter New Code C1 to C2=Enter Old Code E1=Help Me. Thanks a bundle. Quill666 |
Fixed Text-Private Sub Worksheet_Change(ByVal Target As Excel.
Quill,
Sorry, that was me being inattentive and lazy. I should have noted that when your formulas have " in them internally, you need to double up the quotes: So, for example, this line of code If Target.Address = "$L$12" Then Target.Formula= _ "=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)" Should have been: If Target.Address = "$L$12" Then Target.Formula= _ "=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)" The easiest way to ensure that your code is correct for formulas is to get the formula working, then start the macro recorder, select the cell, press F2 and then press Enter. Excel will give you code like this (recorded by enterint the formula above into cell L12): ActiveCell.FormulaR1C1 = _ "=RIGHT(INDEX('31'!C,MATCH(CONCATENATE(R[-6]C[-5],"" - "",R[-6]C),'31'!C[7],0)),SEARCH("" - "",INDEX('31'!C,MATCH(CONCATENATE(R[-6]C[-5],"" - "",R[-6]C),'31'!C[7],0)))+1)" Which you can edit to the A1 style shown above, or leave in the R1C1 style as recorded. HTH, Bernie MS Excel MVP " wrote in message ... Thanks Bernie, Appreciate it very much. I can do this but not VBA. ="MTD: " & CONCATENATE(TEXT(VLOOKUP(TODAY(),Calendar!A:B,2,FA LSE),"mmm-yy")," Out: ",TEXT(SUMIF(Out!K:K,VLOOKUP(TODAY(),Calendar!A:B, 2,FALSE),Out!I:I),"#,###"),"; " & "In : ",TEXT(SUMIF(IN!J:J,VLOOKUP(TODAY(),Calendar!A:B,2 ,FALSE),IN!F:F),"#,###")) =IF(ISBLANK(C6)," ",IF(ISNA(INDEX(Dbase!C:C,MATCH(CONCATENATE(B6 ,"-",C6),Dbase!I:I,0)))," ",INDEX(Dbase!C:C,MATCH(CONCATENATE(B6,"-",C6),Dbase!I:I,0))&": "&IF(VLOOKUP(INDEX(Dbase!C:C,MATCH(CONCATENATE(B6, "-",C6),Dbase!I:I,0)),'Com-Max'!A:B,2,FALSE)=0,"-",TEXT(VLOOKUP(INDEX(Dbase!C:C,MATCH(CONCATENATE(B 6,"-",C6),Dbase!I:I,0)),'Com-Max'!A:B,2,FALSE),"mmm-yy")))) 2 More Questions: Question 1: If I want to select a range, it will be If Target.Address.RANGE = A:A?? Question 2: Automation: How do I use the VBA, When Cell A1 (trigger) is not blank, then Cell C3 will have "Enter someting" or in my case the .formula "=VLOOKUP(A1,Calendar!A:B,2,FALSE)". And it has to be able to list automatically downward say from line 1 to line 65,000 but subject to Cell A content. Say: Cell A45 is 0 then C45 will show the fomula (if Cell A45 is blank, Cell C45 is also empty) * I use another sheet named Calendar! to group the days into month, say 08-Jan-07 or 09 Jan-07 = 31-Jan-07 "Cell C:C is fully automate in a way", triggered by column A:A and same row. I am doing it the conventional way of listing it or using IsBlank + Vlookup + lots of formula, and using the pivot table to generate the reports and copying formulas downwards if the data line exceeds the formula lines. Thanks very much for your time. "Bernie Deitrick" wrote: Quill, You need to set the .Formula property of the target cell: see below. HTH, Bernie MS Excel MVP 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 = "$L$6" Then Target.Formula= "=YEAR(TODAY())" If Target.Address = "$L$10" Then Target.Value = "187863U" If Target.Address = "$L$12" Then Target.Formula= "=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)" If Target.Address = "$L$14" Then Target.Formula = "=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)" If Target.Address = "$L$18" Then Target.Formula= "=INDEX('31'!J:J,MATCH(CONCATENATE(G6," - ",L6),'31'!S:S,0))" If Target.Address = "$L$8" Then Target.Formula= "=SUMIF('31'!S:S,CONCATENATE(G6," - ",L6),'31'!H:H)" Application.EnableEvents = True End Sub " wrote in message ... I tried this, but there was an error. Please help. 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 = "$L$6" Then Target.Value = "=YEAR(TODAY())" If Target.Address = "$L$10" Then Target.Value = "187863U" If Target.Address = "$L$12" Then Target.Value = "=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)" If Target.Address = "$L$14" Then Target.Value = "=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)" If Target.Address = "$L$18" Then Target.Value = "=INDEX('31'!J:J,MATCH(CONCATENATE(G6," - ",L6),'31'!S:S,0))" If Target.Address = "$L$8" Then Target.Value = "=SUMIF('31'!S:S,CONCATENATE(G6," - ",L6),'31'!H:H)" Application.EnableEvents = True End Sub ------------------------------------------------------------------------------ " wrote: That is good advice. Going back to the private sub worksheet_change, lets say: How do I use the VBA, When Cell A1 (trigger) is not blank, then Cell C3 will have "Enter someting" or in my case the formula "=VLOOKUP(A1,Calendar!A:B,2,FALSE)" And it has to able to list automatically downward say from line 1 to line 65,000. Say: Cell A45 is 0 then C45 will list the fomula (if Cell A45 is blank, Cell C45 is also empty) * I use another sheet named Calendar! to group the days into month, say 08-Jan-07 or 09 Jan-07 = 31-Jan-07 and when Cell A1 (trigger) is blank, there is nothing, or just empty or another pre-defined formula (IF function). "Cell C:C is fully automate in a way", triggered by column A:A and same row. I am doing it the conventional way of listing it or using IsBlank + Vlookup + lots of formula, and using the pivot table to generate the reports and copying the formulas downwards if the data line exceeds the formula lines. Thanks very much for your time. "Bernie Deitrick" wrote: Quill, In one word: practice. Do a defined, complicated project for yourself. Work on the problems here, and answer as many as you can. Read and understand the replies to those that you cannot answer. But you cannot choose to become an MVP - you are chosen to receive the award. Bernie MS Excel MVP " wrote in message ... Thanks Bernie. By the way, I have one question what is the first thing to do before becoming a MVP or some sort of VBA programmer. "Bernie Deitrick" wrote: Quill, 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 = "$B$5" Then Target.Value = "Enter Something" If Target.Address = "$B$6" Then Target.Value = "Enter New Code" If Target.Address = "$C$1" Then Target.Value = "Enter Old Code" If Target.Address = "$C$2" Then Target.Value = "Enter Old Code" 'And so on...... ' I hope you can see the logic Application.EnableEvents = True End Sub HTH, Bernie MS Excel MVP " wrote in message ... I have attached below and the result in Cell B5=Enter Something Private Sub Worksheet_Change(ByVal Target As Excel.Range) If Target.Address = "$B$5" Then If Len(Target.Value) = 0 Then Target.Value = "Enter Something" End If End Sub Question: In Cell B6 can I use some kind of formula resulting in "Enter New Code" along with the same formula above. Meaning: Same worksheet: B5=Enter Something B6=Enter New Code Additional: Example of Result Same worksheet: B5=Enter Something B6=Enter New Code C1 to C2=Enter Old Code E1=Help Me. Thanks a bundle. Quill666 |
Fixed Text-Private Sub Worksheet_Change(ByVal Target As Excel.
Hey Bernie, its understood that you have very much in the mind. Hey! its
great that you are willing to spend some time on this. Very much appreciated. At least now I know, the first thing to do is read a VBA book. No short cuts in life!!! "Bernie Deitrick" wrote: Quill, Sorry, that was me being inattentive and lazy. I should have noted that when your formulas have " in them internally, you need to double up the quotes: So, for example, this line of code If Target.Address = "$L$12" Then Target.Formula= _ "=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)" Should have been: If Target.Address = "$L$12" Then Target.Formula= _ "=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)" The easiest way to ensure that your code is correct for formulas is to get the formula working, then start the macro recorder, select the cell, press F2 and then press Enter. Excel will give you code like this (recorded by enterint the formula above into cell L12): ActiveCell.FormulaR1C1 = _ "=RIGHT(INDEX('31'!C,MATCH(CONCATENATE(R[-6]C[-5],"" - "",R[-6]C),'31'!C[7],0)),SEARCH("" - "",INDEX('31'!C,MATCH(CONCATENATE(R[-6]C[-5],"" - "",R[-6]C),'31'!C[7],0)))+1)" Which you can edit to the A1 style shown above, or leave in the R1C1 style as recorded. HTH, Bernie MS Excel MVP " wrote in message ... Thanks Bernie, Appreciate it very much. I can do this but not VBA. ="MTD: " & CONCATENATE(TEXT(VLOOKUP(TODAY(),Calendar!A:B,2,FA LSE),"mmm-yy")," Out: ",TEXT(SUMIF(Out!K:K,VLOOKUP(TODAY(),Calendar!A:B, 2,FALSE),Out!I:I),"#,###"),"; " & "In : ",TEXT(SUMIF(IN!J:J,VLOOKUP(TODAY(),Calendar!A:B,2 ,FALSE),IN!F:F),"#,###")) =IF(ISBLANK(C6)," ",IF(ISNA(INDEX(Dbase!C:C,MATCH(CONCATENATE(B6 ,"-",C6),Dbase!I:I,0)))," ",INDEX(Dbase!C:C,MATCH(CONCATENATE(B6,"-",C6),Dbase!I:I,0))&": "&IF(VLOOKUP(INDEX(Dbase!C:C,MATCH(CONCATENATE(B6, "-",C6),Dbase!I:I,0)),'Com-Max'!A:B,2,FALSE)=0,"-",TEXT(VLOOKUP(INDEX(Dbase!C:C,MATCH(CONCATENATE(B 6,"-",C6),Dbase!I:I,0)),'Com-Max'!A:B,2,FALSE),"mmm-yy")))) 2 More Questions: Question 1: If I want to select a range, it will be If Target.Address.RANGE = A:A?? Question 2: Automation: How do I use the VBA, When Cell A1 (trigger) is not blank, then Cell C3 will have "Enter someting" or in my case the .formula "=VLOOKUP(A1,Calendar!A:B,2,FALSE)". And it has to be able to list automatically downward say from line 1 to line 65,000 but subject to Cell A content. Say: Cell A45 is 0 then C45 will show the fomula (if Cell A45 is blank, Cell C45 is also empty) * I use another sheet named Calendar! to group the days into month, say 08-Jan-07 or 09 Jan-07 = 31-Jan-07 "Cell C:C is fully automate in a way", triggered by column A:A and same row. I am doing it the conventional way of listing it or using IsBlank + Vlookup + lots of formula, and using the pivot table to generate the reports and copying formulas downwards if the data line exceeds the formula lines. Thanks very much for your time. "Bernie Deitrick" wrote: Quill, You need to set the .Formula property of the target cell: see below. HTH, Bernie MS Excel MVP 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 = "$L$6" Then Target.Formula= "=YEAR(TODAY())" If Target.Address = "$L$10" Then Target.Value = "187863U" If Target.Address = "$L$12" Then Target.Formula= "=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)" If Target.Address = "$L$14" Then Target.Formula = "=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)" If Target.Address = "$L$18" Then Target.Formula= "=INDEX('31'!J:J,MATCH(CONCATENATE(G6," - ",L6),'31'!S:S,0))" If Target.Address = "$L$8" Then Target.Formula= "=SUMIF('31'!S:S,CONCATENATE(G6," - ",L6),'31'!H:H)" Application.EnableEvents = True End Sub " wrote in message ... I tried this, but there was an error. Please help. 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 = "$L$6" Then Target.Value = "=YEAR(TODAY())" If Target.Address = "$L$10" Then Target.Value = "187863U" If Target.Address = "$L$12" Then Target.Value = "=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)" If Target.Address = "$L$14" Then Target.Value = "=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)" If Target.Address = "$L$18" Then Target.Value = "=INDEX('31'!J:J,MATCH(CONCATENATE(G6," - ",L6),'31'!S:S,0))" If Target.Address = "$L$8" Then Target.Value = "=SUMIF('31'!S:S,CONCATENATE(G6," - ",L6),'31'!H:H)" Application.EnableEvents = True End Sub ------------------------------------------------------------------------------ " wrote: That is good advice. Going back to the private sub worksheet_change, lets say: How do I use the VBA, When Cell A1 (trigger) is not blank, then Cell C3 will have "Enter someting" or in my case the formula "=VLOOKUP(A1,Calendar!A:B,2,FALSE)" And it has to able to list automatically downward say from line 1 to line 65,000. Say: Cell A45 is 0 then C45 will list the fomula (if Cell A45 is blank, Cell C45 is also empty) * I use another sheet named Calendar! to group the days into month, say 08-Jan-07 or 09 Jan-07 = 31-Jan-07 and when Cell A1 (trigger) is blank, there is nothing, or just empty or another pre-defined formula (IF function). "Cell C:C is fully automate in a way", triggered by column A:A and same row. I am doing it the conventional way of listing it or using IsBlank + Vlookup + lots of formula, and using the pivot table to generate the reports and copying the formulas downwards if the data line exceeds the formula lines. Thanks very much for your time. "Bernie Deitrick" wrote: Quill, In one word: practice. Do a defined, complicated project for yourself. Work on the problems here, and answer as many as you can. Read and understand the replies to those that you cannot answer. But you cannot choose to become an MVP - you are chosen to receive the award. Bernie MS Excel MVP " wrote in message ... Thanks Bernie. By the way, I have one question what is the first thing to do before becoming a MVP or some sort of VBA programmer. "Bernie Deitrick" wrote: Quill, 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 = "$B$5" Then Target.Value = "Enter Something" If Target.Address = "$B$6" Then Target.Value = "Enter New Code" If Target.Address = "$C$1" Then Target.Value = "Enter Old Code" If Target.Address = "$C$2" Then Target.Value = "Enter Old Code" 'And so on...... ' I hope you can see the logic Application.EnableEvents = True End Sub HTH, Bernie MS Excel MVP " wrote in message ... I have attached below and the result in Cell B5=Enter Something Private Sub Worksheet_Change(ByVal Target As Excel.Range) If Target.Address = "$B$5" Then If Len(Target.Value) = 0 Then Target.Value = "Enter Something" End If End Sub Question: In Cell B6 can I use some kind of formula resulting in "Enter New Code" along with the same formula above. Meaning: Same worksheet: B5=Enter Something B6=Enter New Code Additional: Example of Result Same worksheet: B5=Enter Something B6=Enter New Code C1 to C2=Enter Old Code E1=Help Me. Thanks a bundle. Quill666 |
| All times are GMT +1. The time now is 09:56 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com