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?

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

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?

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 -