VBA get the first two cells with value

[Nervatos]

Hello everyone.

First at all, my English is not that good, so I would do it as great I can.

I have tried to made an VBA code for getting two cells value. But have problem to get what kind of cells come first.

Public Function FindI()
    Application.Volatile
    On Error GoTo Finish
    Dim ws As Worksheet
    Set ws = Worksheets("Test")

    Const RN = 3

    For Each y In ws.Range("H29:L29")
        For Each x In ws.Range("H20:L20")
            If IsNumeric(y.Value) And y.Value <> "" And IsNumeric(x.Value) And x.Value <> "" Then
                FindI = y.Value & "/" & x.Value & " = " & Round(y.Value / x.Value, RN)
                Exit Function
            End If
        Next x
    Next y

    For Each y In ws.Range("H30:L30")
        For Each x In ws.Range("H34:L34")
            If IsNumeric(y.Value) And y.Value <> "" And IsNumeric(x.Value) And x.Value <> "" Then
                FindI = x.Value & "/" & y.Value & " = " & Round(x.Value / y.Value, RN)
                Exit Function
            End If
        Next x
    Next y
    
Finish:
    FindI = ""
End Function

The code working fine. But my problem is, if values come late in H29: L29 and same with H20: H20, then it's still this formula that wins. Makes good sense, but I would like if there are values before in H30: L30 and H34: L34 it should come first and end the function. I can't figure out how to solve it.

That is, if there are numbers in I30 and L34 and there are numbers in H29 and L29, then For Each y In ws.Range ("H30: L30") & For Each x In s.Range ("H34: L34 ") come before For Each y In ws.Range (" H29: L29 ") For Each x In ws.Range (" H20: L20 ") - the same reverse.

Hope it makes a little sense of where I want to go. It is basically a matter of calculating some results on an ongoing basis from other values at all times. So that we end up with a finished result.

Public Function FindI()
    Application.Volatile
    Dim ws As Worksheet
    Set ws = Worksheets("Test")

    Dim RN As Double
    RN = 3
    
    For Each y In Range("H29:H29")
        For Each x In Range("H20:H20")
            If IsNumeric(y.Value) And y.Value <> "" And IsNumeric(x.Value) And x.Value <> "" Then
                FindI = y.Value & "/" & x.Value & " = " & Round(y.Value / x.Value, RN)
                Exit Function
            End If
        Next x
    Next y
    
   For Each y In Range("H30:H30")
        For Each x In Range("H34:H34")
            If IsNumeric(y.Value) And y.Value <> "" And IsNumeric(x.Value) And x.Value <> "" Then
                FindI = x.Value & "/" & y.Value & " = " & Round(x.Value / y.Value, RN)
                Exit Function
            End If
        Next x
    Next y
    
    For Each y In Range("I29:I29")
        For Each x In Range("I20:I20")
            If IsNumeric(y.Value) And y.Value <> "" And IsNumeric(x.Value) And x.Value <> "" Then
                FindI = y.Value & "/" & x.Value & " = " & Round(y.Value / x.Value, RN)
                Exit Function
            End If
        Next x
    Next y
    
   For Each y In Range("I30:I30")
        For Each x In Range("I34:I34")
            If IsNumeric(y.Value) And y.Value <> "" And IsNumeric(x.Value) And x.Value <> "" Then
                FindI = x.Value & "/" & y.Value & " = " & Round(x.Value / y.Value, RN)
                Exit Function
            End If
        Next x
    Next y
    
    For Each y In Range("J29:J29")
        For Each x In Range("J20:J20")
            If IsNumeric(y.Value) And y.Value <> "" And IsNumeric(x.Value) And x.Value <> "" Then
                FindI = y.Value & "/" & x.Value & " = " & Round(y.Value / x.Value, RN)
                Exit Function
            End If
        Next x
    Next y
    
   For Each y In Range("J30:J30")
        For Each x In Range("J34:J34")
           If IsNumeric(y.Value) And y.Value <> "" And IsNumeric(x.Value) And x.Value <> "" Then
                FindI = x.Value & "/" & y.Value & " = " & Round(x.Value / y.Value, RN)
                Exit Function
            End If
        Next x
    Next y
    
    For Each y In Range("K29:K29")
        For Each x In Range("K20:K20")
            If IsNumeric(y.Value) And y.Value <> "" And IsNumeric(x.Value) And x.Value <> "" Then
                FindI = y.Value & "/" & x.Value & " = " & Round(y.Value / x.Value, RN)
                Exit Function
            End If
        Next x
    Next y
    
   For Each y In Range("K30:K30")
        For Each x In Range("K34:K34")
           If IsNumeric(y.Value) And y.Value <> "" And IsNumeric(x.Value) And x.Value <> "" Then
                FindI = x.Value & "/" & y.Value & " = " & Round(x.Value / y.Value, RN)
                Exit Function
            End If
        Next x
    Next y
    
    FindI = ""
Finish:
End Function

That's working, but I have many other calculations that need to go into the same function. So if there is a solution, it would be delicious.

Merry Christmas

 

[Nervatos]

Hello everyone

I have tried to make a new one, but I just get one problem.

On Error Resume Next
    For Each y In Ws.Range("D11, H22:L22").Find("*", , xlValues) 'IV
        If IsNumeric(y.Value) And y.Value <> "" Then
            For Each x In Ws.Range("D12, H24:L24").Find("*", , xlValues) 'IR
                For Each c In Ws.Range("D10, H23:L23").Find("*", , xlValues) 'IC
                    For Each a In Ws.Range("D13, H30:L30").Find("*", , xlValues) 'COS
                        If a.Column <= c.Column And a.Column <= x.Column Then
                            'FindI = "I = IV/" & ws.Range("C13").Value & " = " & y.Value & "/" & a.Value & " = " & Round(y.Value / a.Value, RN)
                            'Exit Function
                        ElseIf x.Column > 0 And c.Column > 0 Then
                            FindI = "I = IV^2+(IR-IC)^2 = " & y.Value & "^2+(" & x.Value & "-" & c.Value & ")^2 = " & Round(Sqr(WorksheetFunction.power(y.Value, 2) + WorksheetFunction.power((x.Value - c.Value), 2)), RN)
                            Exit Function
                        ElseIf x.Column > 0 And UCase(Ws.Range("F7").Value) = "YES" And UCase(Ws.Range("H7").Value) = "NO" Then
                            FindI = "I = IV^2+IR^2 = " & y.Value & "^2+" & x.Value & "^2 = " & Round(Sqr(WorksheetFunction.power(y.Value, 2) + WorksheetFunction.power(x.Value, 2)), RN)
                            Exit Function
                        ElseIf UCase(Ws.Range("H7").Value) = "YES" And UCase(Ws.Range("F7").Value) = "NO" And c.Column > 0 Then
                            FindI = "I = IV^2+IC^2 = " & y.Value & "^2+" & c.Value & "^2 = " & Round(Sqr(WorksheetFunction.power(y.Value, 2) + WorksheetFunction.power(c.Value, 2)), RN)
                            Exit Function
                        End If
                    Next a
                Next c
            Next x
        End If
    Next y

The problem is, if I have no value in this:
For Each x In Ws.Range("D12, H24:L24").Find("*", , xlValues) 'IR
For Each c In Ws.Range("D10, H23:L23").Find("*", , xlValues) 'IC
It won't return something.
It should return one off this:

    ElseIf x.Column > 0 And UCase(Ws.Range("F7").Value) = "YES" And UCase(Ws.Range("H7").Value) = "NO" Then
        FindI = "I = IV^2+IR^2 = " & y.Value & "^2+" & x.Value & "^2 = " & Round(Sqr(WorksheetFunction.power(y.Value, 2) + WorksheetFunction.power(x.Value, 2)), RN)
        Exit Function
    ElseIf UCase(Ws.Range("H7").Value) = "YES" And UCase(Ws.Range("F7").Value) = "NO" And c.Column > 0 Then
        FindI = "I = IV^2+IC^2 = " & y.Value & "^2+" & c.Value & "^2 = " & Round(Sqr(WorksheetFunction.power(y.Value, 2) + WorksheetFunction.power(c.Value, 2)), RN)
        Exit Function
    End If

If I remove On Error Resume Next, I got this error: Run-time error '424': Object requried and For Each c In Ws.Range("D10, H23:L23").Find("*", , xlValues) 'IC got this error.

Hope some can help me.
Thanks and Merry Christmas

 

[bebo021999]

Run each cell in BOTH ranges (rng1 and rng2)_, then return the first match

Option Explicit
Public Function FindI() As String
Dim rng1, rng2
Dim x&, y&
Const RN = 3
On Error Resume Next
For y = 8 To 12
    For x = 8 To 12
        With WorksheetFunction
            rng1 = .IfError(Cells(29, y).Value / Cells(20, x).Value, 0)
            rng2 = .IfError(Cells(30, y).Value / Cells(34, x).Value, 0)
            If rng1 <> 0 Then
                FindI = Cells(29, y).Value & "/" & Cells(20, x).Value & " = " & Round(rng1, RN)
                Exit Function
            Else
                If rng2 <> 0 Then
                    FindI = Cells(30, y).Value & "/" & Cells(34, x).Value & " = " & Round(rng2, RN)
                    Exit Function
                End If
            End If
        End With
    Next
Next
FindI = ""
End Function

 

[Nervatos]

Hello Bebo

Thanks for your post. It was helping a lot. But I'm not that good to VBA yet. So how can I get it to only search on Worksheets("Test")?

 

[bebo021999]

Sorry not at PC now. But you can do it by,
dim ws as worksheet
set ws =sheets("....
Then every cells()
, put ws before it
Ws.cells(...

 

[bebo021999]

Or you just put it in specific sheet("test") code window, chance public to private function

 

[Nervatos]

Thank you so much Bedo! It's working great!