Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
passing range to function: type of argument is byref incompatible
I'm trying to write some code to traverse a range of cells and, for
each one, to call a function, which returns the address of the nearest cell above the one passed in, with a lower outline level. In the main code, I have: For Each cTempj In rngCAshOP cTempj.Offset(0, 14).value = fncCellAddCASuperiorItem(cTempj) Next cTempj And then: Function fncCellAddCASuperiorItem(cInferior As Range) As String Dim cSuperior As Range Dim i As Integer i = 0 Do While cSuperior Is Nothing and i < 1000 If cInferior.Offset(-i, 0).EntireRow.OutlineLevel < cInferior.EntireRow.OutlineLevel Then cSuperior = cInferior.Offset(-i, 0) Else i = i + 1 End If Loop fncCellAddCASuperiorItem = cSuperior.Address End Function When i run this, i get an error saying the the type of the argument is byref incompatible. Could anyone please give me some help with where I'm going wrong? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
passing range to function: type of argument is byref incompatible
How did you declare cTempj (and rngCashOp)? (You didn't share!)
dim cTempj as Range dim rngCashOp as range 'and I like to be specific For Each cTempj In rngCAshOP.Cells mcgurkle wrote: I'm trying to write some code to traverse a range of cells and, for each one, to call a function, which returns the address of the nearest cell above the one passed in, with a lower outline level. In the main code, I have: For Each cTempj In rngCAshOP cTempj.Offset(0, 14).value = fncCellAddCASuperiorItem(cTempj) Next cTempj And then: Function fncCellAddCASuperiorItem(cInferior As Range) As String Dim cSuperior As Range Dim i As Integer i = 0 Do While cSuperior Is Nothing and i < 1000 If cInferior.Offset(-i, 0).EntireRow.OutlineLevel < cInferior.EntireRow.OutlineLevel Then cSuperior = cInferior.Offset(-i, 0) Else i = i + 1 End If Loop fncCellAddCASuperiorItem = cSuperior.Address End Function When i run this, i get an error saying the the type of the argument is byref incompatible. Could anyone please give me some help with where I'm going wrong? -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
passing range to function: type of argument is byref incompatible
Hi,
Make sure cTempj is declared as a range variable in your main code. i.e., you have this line in your main code: Dim cTempj As Range -- Hope that helps. Vergel Adriano "mcgurkle" wrote: I'm trying to write some code to traverse a range of cells and, for each one, to call a function, which returns the address of the nearest cell above the one passed in, with a lower outline level. In the main code, I have: For Each cTempj In rngCAshOP cTempj.Offset(0, 14).value = fncCellAddCASuperiorItem(cTempj) Next cTempj And then: Function fncCellAddCASuperiorItem(cInferior As Range) As String Dim cSuperior As Range Dim i As Integer i = 0 Do While cSuperior Is Nothing and i < 1000 If cInferior.Offset(-i, 0).EntireRow.OutlineLevel < cInferior.EntireRow.OutlineLevel Then cSuperior = cInferior.Offset(-i, 0) Else i = i + 1 End If Loop fncCellAddCASuperiorItem = cSuperior.Address End Function When i run this, i get an error saying the the type of the argument is byref incompatible. Could anyone please give me some help with where I'm going wrong? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
passing range to function: type of argument is byref incompatible
Hi guys,
Thanks very much for the advice. Your suggestions pointed me in the direction of the main function as the root of the problem. I've now changed the bit where I call the function to: For Each cTempj In rngCAshOP Set cSuperior = CellCASuperiorItem(cTempj) If cSuperior Is Nothing Then 'this is the top cell cTempj.Offset(0, 15).value = "$AA$7" Else cTempj.Offset(0, 15).value = cSuperior.AddressLocal End If Next cTempj And it works fine. Thanks very much for taking the time to help On May 3, 1:35 pm, Vergel Adriano wrote: Hi, Make sure cTempj is declared as a range variable in your main code. i.e., you have this line in your main code: Dim cTempj As Range -- Hope that helps. Vergel Adriano "mcgurkle" wrote: I'm trying to write some code to traverse a range of cells and, for each one, to call a function, which returns the address of the nearest cell above the one passed in, with a lower outline level. In the main code, I have: For Each cTempj In rngCAshOP cTempj.Offset(0, 14).value = fncCellAddCASuperiorItem(cTempj) Next cTempj And then: Function fncCellAddCASuperiorItem(cInferior As Range) As String Dim cSuperior As Range Dim i As Integer i = 0 Do While cSuperior Is Nothing and i < 1000 If cInferior.Offset(-i, 0).EntireRow.OutlineLevel < cInferior.EntireRow.OutlineLevel Then cSuperior = cInferior.Offset(-i, 0) Else i = i + 1 End If Loop fncCellAddCASuperiorItem = cSuperior.Address End Function When i run this, i get an error saying the the type of the argument is byref incompatible. Could anyone please give me some help with where I'm going wrong?- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Passing a range name as an argument to the Index Function | Excel Discussion (Misc queries) | |||
"ByRef argument type mismatch" Error | Excel Programming | |||
ByRef argument type mismatch error? | Excel Programming | |||
Passing range as an argument in a function | Excel Programming | |||
Passing an Array of User-Defined Type to an Argument of a Function | Excel Programming |