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.
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.
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
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.
VBA Code:
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
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.
VBA Code:
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