ByRef argument type mismatch error?

[sermest]

Hi I have below functions. I did not get what is mismatching. Can
anybody help? Thanks,



Function TYLY() As Integer
Dim FFW, LHW, FDate As Date
Dim FWRow, HWRow, HWColumn, FP, HP As Integer
Dim DateRange, FWCell, HWCell As Range
FFW = Worksheets("F").Cells(22, 49).Value
FP = Worksheets("F").Cells(23, 49).Value
LHW = Worksheets("F").Cells(25, 49).Value
HP = Worksheets("F").Cells(26, 49).Value
Set DateRange = Worksheets("F").Range("F:F")
Set FWCell = DateRange.Find(DateValue(FFW), ,
LookIn:=xlFormulas)
Set HWCell = DateRange.Find(DateValue(LHW), ,
LookIn:=xlFormulas)
FWRow = FWCell.Row
HWRow = HWCell.Row
HWColumn = HWCell.Column
......
With Cells(y, 38)
.Font.ColorIndex = 52
.Value = TValue(HWRow, HP, 7, FWRow)
.Interior.ColorIndex = 6
.......


The TValue function that I call above is below:

Function TValue(THWRow, THP, TDColumn, TFWRow As Integer) As Long
Dim TTY, TLY As Double
TValue = 0
TTY = 0
TLY = 0
For x = THWRow - THP + 1 To THWRow
TTY = TTY + Cells(x, TDColumn).Value
Next x
For y = THWRow - THP + 1 - 52 To THWRow - 52
TLY = TLY + Cells(x, TDColumn).Value
Next y
TValue = (TTY / TLY) * Cells(TFWRow, TDColumn)

End Function

 

[Guest]

It looks like the Function TYLY() Declared as an integer is returning the
..value property of cell(y,38) from the TValue Function declared as a long

Try declaring both functions as the same type and the error should go away.

bac

 

[sermest]

Thanks for the reply.
I Declared:
TYLY() As Integer
TValue(..As Integer) As Integer

and I still get the error, Error highlights the FWRow at TValue(HWRow,
HP, 5, FWRow) in TYLY function

values at cell(y, 38) are also integers.

 

[Jim Cone]

sermest,

You have FWRow declared as a Variant.
You are trying to pass it to the function as an integer.
Excel won't let you convert variants to another data type,
unless they are declared ByVal.
The answer is to declare FWRow as a Long and TFWRow as a Long.

Also, you have not declared "y" in the first function and have
not declared "x" and "y" in the second function.

Regards,
Jim Cone
San Francisco, USA


Hi I have below functions. I did not get what is mismatching. Can
anybody help? Thanks,

Function TYLY() As Integer
Dim FFW, LHW, FDate As Date
Dim FWRow, HWRow, HWColumn, FP, HP As Integer
Dim DateRange, FWCell, HWCell As Range
FFW = Worksheets("F").Cells(22, 49).Value
FP = Worksheets("F").Cells(23, 49).Value
LHW = Worksheets("F").Cells(25, 49).Value
HP = Worksheets("F").Cells(26, 49).Value
Set DateRange = Worksheets("F").Range("F:F")
Set FWCell = DateRange.Find(DateValue(FFW), ,
LookIn:=xlFormulas)
Set HWCell = DateRange.Find(DateValue(LHW), ,
LookIn:=xlFormulas)
FWRow = FWCell.Row
HWRow = HWCell.Row
HWColumn = HWCell.Column
......
With Cells(y, 38)
.Font.ColorIndex = 52
.Value = TValue(HWRow, HP, 7, FWRow)
.Interior.ColorIndex = 6
.......

The TValue function that I call above is below:

Function TValue(THWRow, THP, TDColumn, TFWRow As Integer) As Long
Dim TTY, TLY As Double
TValue = 0
TTY = 0
TLY = 0
For x = THWRow - THP + 1 To THWRow
TTY = TTY + Cells(x, TDColumn).Value
Next x
For y = THWRow - THP + 1 - 52 To THWRow - 52
TLY = TLY + Cells(x, TDColumn).Value
Next y
TValue = (TTY / TLY) * Cells(TFWRow, TDColumn)
End Function

 

[sermest]

Jim Thank you very much, It works now.

Best,
sermest