0

I'm new to VBA and am having some problems, especially with the syntax.

I'm trying to do a function that counts how many repeated cells I have in a passing range (for now, I assume that should pass only a range with 1 column).

I have the following:

Public Function countRepeated(ByVal pRange As range) As Integer
Dim numberOfRows As Integer
Dim numberOfColumns As Integer
Dim repeated As Integer
Dim localRange As range

Set localRange = pRange

numberOfRows = localRange.Rows.Count
numberOfColumns = localRange.columns.Count

If (numberOfColumns > 1) Then
    temp = MsgBox("Insira intervalos com apenas 1 coluna.", vbExclamation, "Erro")
    countRepeated = -1
    Exit Function
End If

repeated = 0

For i = 1 To numberOfRows
    temporary = localRange.Cells(i, 1).Value
    For j = i + 1 To numberOfRows
        If (temporary = localRange.Cells(j, 1).Value And temporary <> "") Then
            repeated = repeated + 1
            localRange.Cells(j, 1).Value = ""
            'after the previous instruction, i get thet #VALUE! error
            'i also try set localRange.Cells(j,1).Value = ""
            'and localRange.Cells(j, 1).Value = 0
        End If
    Next j

Next i

countRepeated = repeated

End Function

But I am getting a #VALUE! error after I try to change a value from the Range. Initially I tried to modify the parameter itself (pRange), by passing it 'ByVal', but I get the same error.

Jonah
  • 1,013
  • 15
  • 25
Victor Hugo
  • 25
  • 1
  • 5

2 Answers2

0

Here is what I can tell you about your code:

  1. Your problem is here:

    localRange.Cells(j, 1).Value = ""

The value cannot be "". Try with localRange.Cells(j, 1).Text = ""

  1. Do not use integers in VBA. Why Use Integer Instead of Long?

  2. If you are not able to rewrite your code with punkt 1, then give some example of input and output, and the people in StackOverflow will give you ideas.

That's all :) Cheers!

Community
  • 1
  • 1
Vityata
  • 42,633
  • 8
  • 55
  • 100
0

If you are calling this function from a formula in a cell Excel will not allow you to change the value of another cell from inside the function.

Using Set localRange=pRangedoes not create a copy of pRange that you can manipulate.

Probably what you should do is to get pRange into a variant array and manipulate that:

dim localRange as variant
localRange=pRange.value2

then instead of localRange.Cells(j,1) use localRange(j,1)

Charles Williams
  • 23,121
  • 5
  • 38
  • 38