+ Reply to Thread
Results 1 to 7 of 7

keep previous result when change, urgent

  1. #1
    jiang
    Guest

    keep previous result when change, urgent

    I made a validation list in A1 for different scenarios, I could choose 3
    different secnarios from this drop-down list.

    each time, when I choose one scenario, there is a result in B2, for instance
    if I choose scenario1 in A1, b1=100; choose scenario2,b1=200; choose
    scenario3, b1=300.

    The question is,
    Can I make c1, c2 and c3 store b1 value based on different choosen scenario?
    For example, I choose scenario1 in drop-down list in A1, b1 show equal to
    100, c1 store a result 100; if I change my choice in drop-down list to
    scenario2, b1 will be 200, but c1 will still keep 100 result, at the same
    time c2 will be 200.

    I tried Iteration function in Excel sheet, but it doesn't work, I THINK vba
    might be the only choice. could someone help me out?

    Thanks in advance.

  2. #2
    Tom Ogilvy
    Guest

    Re: keep previous result when change, urgent

    Use the Change event in xl2000 and later

    See Chip Pearson's page on events:
    http://www.cpearson.com/excel/events.htm

    --
    Regards,
    Tom Ogilvy


    "jiang" <jiang@discussions.microsoft.com> wrote in message
    news:A98BA1BF-75F0-45A8-B94E-D0E5F082FA6B@microsoft.com...
    > I made a validation list in A1 for different scenarios, I could choose 3
    > different secnarios from this drop-down list.
    >
    > each time, when I choose one scenario, there is a result in B2, for

    instance
    > if I choose scenario1 in A1, b1=100; choose scenario2,b1=200; choose
    > scenario3, b1=300.
    >
    > The question is,
    > Can I make c1, c2 and c3 store b1 value based on different choosen

    scenario?
    > For example, I choose scenario1 in drop-down list in A1, b1 show equal to
    > 100, c1 store a result 100; if I change my choice in drop-down list to
    > scenario2, b1 will be 200, but c1 will still keep 100 result, at the same
    > time c2 will be 200.
    >
    > I tried Iteration function in Excel sheet, but it doesn't work, I THINK

    vba
    > might be the only choice. could someone help me out?
    >
    > Thanks in advance.




  3. #3
    jiang
    Guest

    Re: keep previous result when change, urgent

    Thanks, Tom

    I'm reading article, and trying to write but don't know how to complete it.
    Could anyone help me out? Thanks in advance.

    Private Sub Worksheet_Change(ByVal Target As Range)
    If a1 = 1 Then c1=b1

    endif
    End Sub

    "Tom Ogilvy" wrote:

    > Use the Change event in xl2000 and later
    >
    > See Chip Pearson's page on events:
    > http://www.cpearson.com/excel/events.htm
    >
    > --
    > Regards,
    > Tom Ogilvy
    >
    >
    > "jiang" <jiang@discussions.microsoft.com> wrote in message
    > news:A98BA1BF-75F0-45A8-B94E-D0E5F082FA6B@microsoft.com...
    > > I made a validation list in A1 for different scenarios, I could choose 3
    > > different secnarios from this drop-down list.
    > >
    > > each time, when I choose one scenario, there is a result in B2, for

    > instance
    > > if I choose scenario1 in A1, b1=100; choose scenario2,b1=200; choose
    > > scenario3, b1=300.
    > >
    > > The question is,
    > > Can I make c1, c2 and c3 store b1 value based on different choosen

    > scenario?
    > > For example, I choose scenario1 in drop-down list in A1, b1 show equal to
    > > 100, c1 store a result 100; if I change my choice in drop-down list to
    > > scenario2, b1 will be 200, but c1 will still keep 100 result, at the same
    > > time c2 will be 200.
    > >
    > > I tried Iteration function in Excel sheet, but it doesn't work, I THINK

    > vba
    > > might be the only choice. could someone help me out?
    > >
    > > Thanks in advance.

    >
    >
    >


  4. #4
    Norman Jones
    Guest

    Re: keep previous result when change, urgent

    Hi Jiang,

    Try:

    '=========================>>
    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim EndCell As Range
    If Not Intersect(Target, Range("A1")) Is Nothing Then

    If IsEmpty(Range("C1")) Then
    Set EndCell = Range("C1")
    ElseIf IsEmpty(Range("C2")) Then
    Set EndCell = Range("C2")
    Else
    Set EndCell = Cells(Rows.Count, "C").End(xlUp)(2)
    End If

    Select Case Target.Value
    Case 1
    Range("B1").Value = 100
    Case 2
    Range("B1").Value = 200
    Case 3
    Range("B1").Value = 300
    Case Else
    Range("B1").Value = ""
    End Select
    EndCell.Value = Range("B1").Value

    End If
    '<<=========================

    ---
    Regards,
    Norman



    "jiang" <jiang@discussions.microsoft.com> wrote in message
    news:0DE65F3E-D04E-4E70-8953-989CA6B0E65B@microsoft.com...
    > Thanks, Tom
    >
    > I'm reading article, and trying to write but don't know how to complete
    > it.
    > Could anyone help me out? Thanks in advance.
    >
    > Private Sub Worksheet_Change(ByVal Target As Range)
    > If a1 = 1 Then c1=b1
    >
    > endif
    > End Sub
    >
    > "Tom Ogilvy" wrote:
    >
    >> Use the Change event in xl2000 and later
    >>
    >> See Chip Pearson's page on events:
    >> http://www.cpearson.com/excel/events.htm
    >>
    >> --
    >> Regards,
    >> Tom Ogilvy
    >>
    >>
    >> "jiang" <jiang@discussions.microsoft.com> wrote in message
    >> news:A98BA1BF-75F0-45A8-B94E-D0E5F082FA6B@microsoft.com...
    >> > I made a validation list in A1 for different scenarios, I could choose
    >> > 3
    >> > different secnarios from this drop-down list.
    >> >
    >> > each time, when I choose one scenario, there is a result in B2, for

    >> instance
    >> > if I choose scenario1 in A1, b1=100; choose scenario2,b1=200; choose
    >> > scenario3, b1=300.
    >> >
    >> > The question is,
    >> > Can I make c1, c2 and c3 store b1 value based on different choosen

    >> scenario?
    >> > For example, I choose scenario1 in drop-down list in A1, b1 show equal
    >> > to
    >> > 100, c1 store a result 100; if I change my choice in drop-down list to
    >> > scenario2, b1 will be 200, but c1 will still keep 100 result, at the
    >> > same
    >> > time c2 will be 200.
    >> >
    >> > I tried Iteration function in Excel sheet, but it doesn't work, I THINK

    >> vba
    >> > might be the only choice. could someone help me out?
    >> >
    >> > Thanks in advance.

    >>
    >>
    >>




  5. #5
    jiang
    Guest

    Re: keep previous result when change, urgent

    Dear Norman,
    d
    Thanks for your help, I tried your programs but the result is a bit
    different to my expectation. When I change the selection in drop-down list,
    the result won't replace existing in C1,C2and C3 content, more result shows
    following C column. such situation is because you used 'endcell' in program
    instead of specifying postion of result.

    But you example already helps me lots, I'm still trying to understand and
    adjust to my expectation. Thanks again and best wishes.

    jiang

    "Norman Jones" wrote:

    > Hi Jiang,
    >
    > Try:
    >
    > '=========================>>
    > Private Sub Worksheet_Change(ByVal Target As Range)
    > Dim EndCell As Range
    > If Not Intersect(Target, Range("A1")) Is Nothing Then
    >
    > If IsEmpty(Range("C1")) Then
    > Set EndCell = Range("C1")
    > ElseIf IsEmpty(Range("C2")) Then
    > Set EndCell = Range("C2")
    > Else
    > Set EndCell = Cells(Rows.Count, "C").End(xlUp)(2)
    > End If
    >
    > Select Case Target.Value
    > Case 1
    > Range("B1").Value = 100
    > Case 2
    > Range("B1").Value = 200
    > Case 3
    > Range("B1").Value = 300
    > Case Else
    > Range("B1").Value = ""
    > End Select
    > EndCell.Value = Range("B1").Value
    >
    > End If
    > '<<=========================
    >
    > ---
    > Regards,
    > Norman
    >
    >
    >
    > "jiang" <jiang@discussions.microsoft.com> wrote in message
    > news:0DE65F3E-D04E-4E70-8953-989CA6B0E65B@microsoft.com...
    > > Thanks, Tom
    > >
    > > I'm reading article, and trying to write but don't know how to complete
    > > it.
    > > Could anyone help me out? Thanks in advance.
    > >
    > > Private Sub Worksheet_Change(ByVal Target As Range)
    > > If a1 = 1 Then c1=b1
    > >
    > > endif
    > > End Sub
    > >
    > > "Tom Ogilvy" wrote:
    > >
    > >> Use the Change event in xl2000 and later
    > >>
    > >> See Chip Pearson's page on events:
    > >> http://www.cpearson.com/excel/events.htm
    > >>
    > >> --
    > >> Regards,
    > >> Tom Ogilvy
    > >>
    > >>
    > >> "jiang" <jiang@discussions.microsoft.com> wrote in message
    > >> news:A98BA1BF-75F0-45A8-B94E-D0E5F082FA6B@microsoft.com...
    > >> > I made a validation list in A1 for different scenarios, I could choose
    > >> > 3
    > >> > different secnarios from this drop-down list.
    > >> >
    > >> > each time, when I choose one scenario, there is a result in B2, for
    > >> instance
    > >> > if I choose scenario1 in A1, b1=100; choose scenario2,b1=200; choose
    > >> > scenario3, b1=300.
    > >> >
    > >> > The question is,
    > >> > Can I make c1, c2 and c3 store b1 value based on different choosen
    > >> scenario?
    > >> > For example, I choose scenario1 in drop-down list in A1, b1 show equal
    > >> > to
    > >> > 100, c1 store a result 100; if I change my choice in drop-down list to
    > >> > scenario2, b1 will be 200, but c1 will still keep 100 result, at the
    > >> > same
    > >> > time c2 will be 200.
    > >> >
    > >> > I tried Iteration function in Excel sheet, but it doesn't work, I THINK
    > >> vba
    > >> > might be the only choice. could someone help me out?
    > >> >
    > >> > Thanks in advance.
    > >>
    > >>
    > >>

    >
    >
    >


  6. #6
    Norman Jones
    Guest

    Re: keep previous result when change, urgent

    Hi Jiang,

    I am not sure that I correctly interpret your requirements but try:

    '===================>>
    Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    Dim EndCell As Range
    Static i As Long
    If Not Intersect(Target, Range("A1")) Is Nothing Then

    If IsEmpty(Range("C1")) Then
    Set EndCell = Range("C1")
    ElseIf IsEmpty(Range("C2")) Then
    Set EndCell = Range("C2")
    ElseIf IsEmpty(Range("C3")) Then
    Set EndCell = Range("C3")
    Else
    Set EndCell = Range("C1").Offset(i)
    i = IIf(i + 1 > 2, 0, i + 1)
    End If

    Select Case Target.Value
    Case 1
    Range("B1").Value = 100
    Case 2
    Range("B1").Value = 200
    Case 3
    Range("B1").Value = 300
    Case Else
    Range("B1").Value = ""
    End Select
    EndCell.Value = Range("B1").Value

    End If
    Application.EnableEvents = True
    End Sub
    '===================>>

    ---
    Regards,
    Norman



    "jiang" <jiang@discussions.microsoft.com> wrote in message
    news:DAAC86AF-0145-4C02-A50E-F4A0A8939B71@microsoft.com...
    > Dear Norman,
    > d
    > Thanks for your help, I tried your programs but the result is a bit
    > different to my expectation. When I change the selection in drop-down
    > list,
    > the result won't replace existing in C1,C2and C3 content, more result
    > shows
    > following C column. such situation is because you used 'endcell' in
    > program
    > instead of specifying postion of result.
    >
    > But you example already helps me lots, I'm still trying to understand and
    > adjust to my expectation. Thanks again and best wishes.
    >
    > jiang
    >
    > "Norman Jones" wrote:
    >
    >> Hi Jiang,
    >>
    >> Try:
    >>
    >> '=========================>>
    >> Private Sub Worksheet_Change(ByVal Target As Range)
    >> Dim EndCell As Range
    >> If Not Intersect(Target, Range("A1")) Is Nothing Then
    >>
    >> If IsEmpty(Range("C1")) Then
    >> Set EndCell = Range("C1")
    >> ElseIf IsEmpty(Range("C2")) Then
    >> Set EndCell = Range("C2")
    >> Else
    >> Set EndCell = Cells(Rows.Count, "C").End(xlUp)(2)
    >> End If
    >>
    >> Select Case Target.Value
    >> Case 1
    >> Range("B1").Value = 100
    >> Case 2
    >> Range("B1").Value = 200
    >> Case 3
    >> Range("B1").Value = 300
    >> Case Else
    >> Range("B1").Value = ""
    >> End Select
    >> EndCell.Value = Range("B1").Value
    >>
    >> End If
    >> '<<=========================
    >>
    >> ---
    >> Regards,
    >> Norman
    >>
    >>
    >>
    >> "jiang" <jiang@discussions.microsoft.com> wrote in message
    >> news:0DE65F3E-D04E-4E70-8953-989CA6B0E65B@microsoft.com...
    >> > Thanks, Tom
    >> >
    >> > I'm reading article, and trying to write but don't know how to complete
    >> > it.
    >> > Could anyone help me out? Thanks in advance.
    >> >
    >> > Private Sub Worksheet_Change(ByVal Target As Range)
    >> > If a1 = 1 Then c1=b1
    >> >
    >> > endif
    >> > End Sub
    >> >
    >> > "Tom Ogilvy" wrote:
    >> >
    >> >> Use the Change event in xl2000 and later
    >> >>
    >> >> See Chip Pearson's page on events:
    >> >> http://www.cpearson.com/excel/events.htm
    >> >>
    >> >> --
    >> >> Regards,
    >> >> Tom Ogilvy
    >> >>
    >> >>
    >> >> "jiang" <jiang@discussions.microsoft.com> wrote in message
    >> >> news:A98BA1BF-75F0-45A8-B94E-D0E5F082FA6B@microsoft.com...
    >> >> > I made a validation list in A1 for different scenarios, I could
    >> >> > choose
    >> >> > 3
    >> >> > different secnarios from this drop-down list.
    >> >> >
    >> >> > each time, when I choose one scenario, there is a result in B2, for
    >> >> instance
    >> >> > if I choose scenario1 in A1, b1=100; choose scenario2,b1=200; choose
    >> >> > scenario3, b1=300.
    >> >> >
    >> >> > The question is,
    >> >> > Can I make c1, c2 and c3 store b1 value based on different choosen
    >> >> scenario?
    >> >> > For example, I choose scenario1 in drop-down list in A1, b1 show
    >> >> > equal
    >> >> > to
    >> >> > 100, c1 store a result 100; if I change my choice in drop-down list
    >> >> > to
    >> >> > scenario2, b1 will be 200, but c1 will still keep 100 result, at the
    >> >> > same
    >> >> > time c2 will be 200.
    >> >> >
    >> >> > I tried Iteration function in Excel sheet, but it doesn't work, I
    >> >> > THINK
    >> >> vba
    >> >> > might be the only choice. could someone help me out?
    >> >> >
    >> >> > Thanks in advance.
    >> >>
    >> >>
    >> >>

    >>
    >>
    >>




  7. #7
    jiang
    Guest

    Re: keep previous result when change, urgent

    Hi, Norman
    Thanks for your reply and your answer helps me a lot. I couldn't post
    message untill today.

    Actually both of way you made are all very useful in different cases and
    really gave me a nice structure to follow (I'm still a basic user of Excel
    with little knowledge about VBA, by reading help from VBA, I could more or
    less understand why such codes are written ).

    is it possible to show all results by changing selection in drop-down list,
    for instance, cell C1-C3 keep all three results but in specific FIXED
    positions, but I need result 1 is always shown in 'C1', result 2 is always
    shown in 'C2', result 3 is always shown in 'C3'. if I change number of
    Scenario in code, the new result will be shown in fixed position.

    A B
    C
    1 drop-down list showing selection in list result1(100)
    2
    result2(200)
    3
    result3(300)

    The key is in the following code you wrote,


    1) Set EndCell = Cells(Rows.Count, "C").End(xlUp)(2)
    in your first code, when I select different scenario in A1's drop-down list,
    the results will be shown by following C column, can't replace existing
    result;

    2) Set EndCell = Range("C1").Offset(i)
    i = IIf(i + 1 > 2, 0, i + 1)
    In your second code, when I select drop-down list, the new result will
    repeatly shown in C1,C2and C3, but not in their previous/fixed postion.

    Now is it possible to fix location of result ?

    I hope I explain clear this time. Your answer helps me to compare different
    results, which all come from same calculation, at the same graph.

    Thanks again
    Best regards
    jiang


    "Norman Jones" wrote:

    > Hi Jiang,
    >
    > I am not sure that I correctly interpret your requirements but try:
    >
    > '===================>>
    > Private Sub Worksheet_Change(ByVal Target As Range)
    > Application.EnableEvents = False
    > Dim EndCell As Range
    > Static i As Long
    > If Not Intersect(Target, Range("A1")) Is Nothing Then
    >
    > If IsEmpty(Range("C1")) Then
    > Set EndCell = Range("C1")
    > ElseIf IsEmpty(Range("C2")) Then
    > Set EndCell = Range("C2")
    > ElseIf IsEmpty(Range("C3")) Then
    > Set EndCell = Range("C3")
    > Else
    > Set EndCell = Range("C1").Offset(i)
    > i = IIf(i + 1 > 2, 0, i + 1)
    > End If
    >
    > Select Case Target.Value
    > Case 1
    > Range("B1").Value = 100
    > Case 2
    > Range("B1").Value = 200
    > Case 3
    > Range("B1").Value = 300
    > Case Else
    > Range("B1").Value = ""
    > End Select
    > EndCell.Value = Range("B1").Value
    >
    > End If
    > Application.EnableEvents = True
    > End Sub
    > '===================>>
    >
    > ---
    > Regards,
    > Norman
    >
    >
    >
    > "jiang" <jiang@discussions.microsoft.com> wrote in message
    > news:DAAC86AF-0145-4C02-A50E-F4A0A8939B71@microsoft.com...
    > > Dear Norman,
    > > d
    > > Thanks for your help, I tried your programs but the result is a bit
    > > different to my expectation. When I change the selection in drop-down
    > > list,
    > > the result won't replace existing in C1,C2and C3 content, more result
    > > shows
    > > following C column. such situation is because you used 'endcell' in
    > > program
    > > instead of specifying postion of result.
    > >
    > > But you example already helps me lots, I'm still trying to understand and
    > > adjust to my expectation. Thanks again and best wishes.
    > >
    > > jiang
    > >
    > > "Norman Jones" wrote:
    > >
    > >> Hi Jiang,
    > >>
    > >> Try:
    > >>
    > >> '=========================>>
    > >> Private Sub Worksheet_Change(ByVal Target As Range)
    > >> Dim EndCell As Range
    > >> If Not Intersect(Target, Range("A1")) Is Nothing Then
    > >>
    > >> If IsEmpty(Range("C1")) Then
    > >> Set EndCell = Range("C1")
    > >> ElseIf IsEmpty(Range("C2")) Then
    > >> Set EndCell = Range("C2")
    > >> Else
    > >> Set EndCell = Cells(Rows.Count, "C").End(xlUp)(2)
    > >> End If
    > >>
    > >> Select Case Target.Value
    > >> Case 1
    > >> Range("B1").Value = 100
    > >> Case 2
    > >> Range("B1").Value = 200
    > >> Case 3
    > >> Range("B1").Value = 300
    > >> Case Else
    > >> Range("B1").Value = ""
    > >> End Select
    > >> EndCell.Value = Range("B1").Value
    > >>
    > >> End If
    > >> '<<=========================
    > >>
    > >> ---
    > >> Regards,
    > >> Norman
    > >>
    > >>
    > >>
    > >> "jiang" <jiang@discussions.microsoft.com> wrote in message
    > >> news:0DE65F3E-D04E-4E70-8953-989CA6B0E65B@microsoft.com...
    > >> > Thanks, Tom
    > >> >
    > >> > I'm reading article, and trying to write but don't know how to complete
    > >> > it.
    > >> > Could anyone help me out? Thanks in advance.
    > >> >
    > >> > Private Sub Worksheet_Change(ByVal Target As Range)
    > >> > If a1 = 1 Then c1=b1
    > >> >
    > >> > endif
    > >> > End Sub
    > >> >
    > >> > "Tom Ogilvy" wrote:
    > >> >
    > >> >> Use the Change event in xl2000 and later
    > >> >>
    > >> >> See Chip Pearson's page on events:
    > >> >> http://www.cpearson.com/excel/events.htm
    > >> >>
    > >> >> --
    > >> >> Regards,
    > >> >> Tom Ogilvy
    > >> >>
    > >> >>
    > >> >> "jiang" <jiang@discussions.microsoft.com> wrote in message
    > >> >> news:A98BA1BF-75F0-45A8-B94E-D0E5F082FA6B@microsoft.com...
    > >> >> > I made a validation list in A1 for different scenarios, I could
    > >> >> > choose
    > >> >> > 3
    > >> >> > different secnarios from this drop-down list.
    > >> >> >
    > >> >> > each time, when I choose one scenario, there is a result in B2, for
    > >> >> instance
    > >> >> > if I choose scenario1 in A1, b1=100; choose scenario2,b1=200; choose
    > >> >> > scenario3, b1=300.
    > >> >> >
    > >> >> > The question is,
    > >> >> > Can I make c1, c2 and c3 store b1 value based on different choosen
    > >> >> scenario?
    > >> >> > For example, I choose scenario1 in drop-down list in A1, b1 show
    > >> >> > equal
    > >> >> > to
    > >> >> > 100, c1 store a result 100; if I change my choice in drop-down list
    > >> >> > to
    > >> >> > scenario2, b1 will be 200, but c1 will still keep 100 result, at the
    > >> >> > same
    > >> >> > time c2 will be 200.
    > >> >> >
    > >> >> > I tried Iteration function in Excel sheet, but it doesn't work, I
    > >> >> > THINK
    > >> >> vba
    > >> >> > might be the only choice. could someone help me out?
    > >> >> >
    > >> >> > Thanks in advance.
    > >> >>
    > >> >>
    > >> >>
    > >>
    > >>
    > >>

    >
    >
    >


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1