+ Reply to Thread
Results 1 to 5 of 5

ByRef argument type mismatch error?

  1. #1
    sermest
    Guest

    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. #2
    BAC
    Guest

    RE: 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. #3
    sermest
    Guest

    Re: 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. #4
    Jim Cone
    Guest

    Re: 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" <[email protected]> wrote in message
    news:[email protected]...
    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. #5
    sermest
    Guest

    Re: ByRef argument type mismatch error?

    Jim Thank you very much, It works now.

    Best,
    sermest


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1