Find and replace – sort of (Excel 2000)

Topic

New Reply

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

Reply | Quote

Replies

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?

Reply | Quote

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

Reply | Quote

Here is the 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


Reply | Quote

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

Reply | Quote

In H1 enter:

=(I1=1)+(J1=1)*2

and copy/autofill down the columns

Steve

Reply | Quote