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