how do I find next value?

[DannyDoobster]

I have a table like the sample below. If a or b or c is 1, I want to find the first instance of a 1 in d or e or f. By using Match function I get the row in which the first instance occur, but what I want is in which column(s) (d or/and e or/and f) the first instance of 1 occur.

For example, 1 is recorded in a1, then offset in d4. Next, 1 is recorded in b6 then offset in d8 AND f8.



. A B C D E F
1 0 0 0 0 0 0

2 1 0 0 0 0 0

3 0 0 0 0 0 0

4 0 0 0 1 0 0

5 0 0 0 0 0 0

6 0 1 0 0 0 0

7 0 0 0 0 0 0

8 0 0 0 1 1 0



any help would be much appreciated!

 

[MickG]

Hi, Try:-

Sub One()
Dim cl As Range, Num As String
For Each cl In UsedRange
    If cl = 1 Then
        Num = Num & cl.Address & Chr(10)
    End If
Next cl
MsgBox "The Following Cells have a ""1"" in them " & Chr(10) & Num
End sub

 

[DannyDoobster]

Thanks for your reply MickG

I'm not overly comfortable using coding. is there away of doing it using Excel functions?

cheers
Danny

 

[MickG]

Hi, This UDF is the best I can do !!
Open your sheet, Click "Alt+F11" , Vb window appears.
On VB Toolbar Select "Insert", "Module".
Paste the entire code into the New window.
Close VB Window.
To Run the function, enter, =One( Select your range here !!!)
The function will return the addresses of the cells in Range with a "1".

Function One(Rng As Range) As String
Dim cl As Range, Num As String
For Each cl In Rng
    If cl = 1 Then
        Num = Num & cl.Address & ","
    End If
Next cl
One = Num
End Function

Regards Mick