vba "evaluate" code not working
Hello, I am making a logbook, where I enter flight details in the AllData sheet, and when I select "ok" in column L, the code should automatically copy the information to the pilots sheets and make some adjustments.
The code works good for this part.
Now if I select CLR in AllData sheet,let's say in row 6, the code should go to the pilot sheet (pilot3) and check which row has the same information as this one. (in this case it is "row 5" in "pilot3" )
Then it should send a message box with the value of the row number.
But the code is not working for this part, it always returns a message box with the number 0...I think the error is in the " (destRow3 = Evaluate...) part " perhaps there is something wrong or something missing, and I am not able to continue, so your help is much appreciated, thanks.
I uploaded a sample of the work.
| Attachment | Size |
|---|---|
| sample11.xlsm | 25.93 KB |
I've stepped through the code
I've stepped through the code and there is nothing wrong with it, it just isn't giving you there answer you expect :)
The eveaulate function is legacy from excel 4 so there are very few situations in which it should be used.
There are two posible solutions, the first is to use somthing like
sub Main
Dim r1 As Range
Set r1 = Range("Sheet1!C8:C12")
Dim r2 As Range
Set r2 = Range("Sheet1!F8:F12")
Dim result As Double
result = WorksheetFunction.SumProduct(r1, r2)
end sub
however a better solution would to write your own version of SUMPRODUCT as VBA function
Function SumProduct(rA As Range, rB As Range) As Double
Dim result As Double
Dim rowOffset As Long
Dim cellA As Range
For Each cellA In rA
rowOffset = rowOffset + 1
result = result + _
cellA.Value * rB.Cells(rowOffset).Value
Next cellA
SumProduct = result
End Function
As this will allow you to put any if/elseif/else logic inside the loop.
HTH
Phineas