Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
ByRef argument type mismatch error?
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
ByRef argument type mismatch error?
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" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
ByRef argument type mismatch error?
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
ByRef argument type mismatch error?
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 "sermest" wrote in message oups.com... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
ByRef argument type mismatch error?
Jim Thank you very much, It works now.
Best, sermest |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Visual Basic Error Run Time Error, Type Mismatch | Excel Discussion (Misc queries) | |||
Type Mismatch Error | Excel Discussion (Misc queries) | |||
Befuddled with For Next Loop ------ Run - Time Error '13' Type Mismatch Error | Excel Programming | |||
Type Mismatch Error | Excel Programming | |||
Type Mismatch Error Need Help | Excel Programming |