Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Atlanta, Georgia, USA
    Posts
    274
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Find and replace - sort of (Excel 2000)

    I need your help once again.
    I'm trying to find a way in code to do this:
    Column H is empty
    If columm I has a 1 in it put a 1 in column H. If column J has a 1 in it put a 2 in column H.
    If column I & J both have a 1 in them put a 3 in column H
    I & J have either 1's or 0's in them

    Thanks,
    Scott

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Find and replace - sort of (Excel 2000)

    What do you want in H if I & J both contain 0?

    Also, this can easily be done with a formula, do you really need VBA code?
    Legare Coleman

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Find and replace - sort of (Excel 2000)

    In H1 enter:
    <pre>=(I1=1)+(J1=1)*2</pre>


    and copy/autofill down the columns

    Steve

  4. #4
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Atlanta, Georgia, USA
    Posts
    274
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Find and replace - sort of (Excel 2000)

    Sorry they will never both be 0's
    I want to use VBA because of the the code that will insert an option group into column H.
    I found that if I have values already in column H and the code for creating the option group runs, the 3 options arent selected and all the values that were in column H
    are changed to all 0's.
    If add, copy, move, etc. the 1,2, or 3 in column H after the option groups are created, the correct options are filled in.

    Scott

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Find and replace - sort of (Excel 2000)

    Here is the code:

    <code>
    Public Sub SetH()
    Dim I As Long, LLast As Long
    LLast = Range("I65536").End(xlUp).Row - 1
    For I = 0 To LLast
    With Range("H1").Offset(I, 0)
    .Value = Abs((.Offset(0, 1) = 1) + (.Offset(0, 2) = 1) * 2)
    End With
    Next I
    End Sub
    </code>
    Legare Coleman

  6. #6
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Atlanta, Georgia, USA
    Posts
    274
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Find and replace - sort of (Excel 2000)

    Legare,
    Thank you very much.
    Works like a charm.
    Scott

Posting Permissions

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