0

I am trying to convert a sub procedure to a function. This sub generate number based on some conditions. It's working as expected but when I try to convert it as a function, it only generate "0". Can anyone please help? It will be great appreciated! Here is my sub named "GenerateNumber"

Public Sub GenerateNumber()
    Dim evenRange As Range
    Dim oddRange As Range
    Dim i As Integer
Set evenRange = Range("A2:Z2,A4:Z4,A6:Z6,A8:Z8,A10:Z10,A12:Z12,A14:Z14,A16:Z16,A18:Z18 ")
    Set oddRange = Range("A1:Z1,A3:Z3,A5:Z5,A7:Z7,A9:Z9,A11:Z11,A13:Z13,A15:Z15,A17:Z17,A19:Z19")

    If Not Intersect(Activecell, evenRange) Is Nothing Then
        If Activecell.Interior.Color = Activecell.Offset(0, -1).Interior.Color Then
            Activecell.Value = Activecell.Offset(0, -1).Value + 1

            For i = 1 To Selection.Cells.Count - 1
            Activecell.Offset(0, i).Value = Activecell.Value + i
            Next i
        Else
            Activecell.Value = 1

            For i = 1 To Selection.Cells.Count - 1
            Activecell.Offset(0, i).Value = Activecell.Value + i
            Next i

        End If
        'MsgBox "Active Cell In Even Range!"
    Else
'      MsgBox "Active Cell In Odd Range!"
      If Activecell.Interior.Color = Activecell.Offset(0, 1).Interior.Color Then
        Activecell.Value = Activecell.Offset(0, 1).Value + 1

        For i = 1 To Selection.Cells.Count - 1
        Activecell.Offset(0, -i).Value = Activecell.Value + i
        Next i
     Else
        Activecell.Value = 1

        For i = 1 To Selection.Cells.Count - 1
        Activecell.Offset(0, -i).Value = Activecell.Value + i
        Next i

      End If

End If

End Sub

And my function code like this,

Public Function GenNumber() As Variant

    Dim evenRange As Range
    Dim oddRange As Range
    Dim rng As Range
    Dim i As Integer

    Set evenRange = Range("A2:Z2,A4:Z4,A6:Z6,A8:Z8,A10:Z10,A12:Z12,A14:Z14,A16:Z16,A18:Z18 ")
    Set oddRange = Range("A1:Z1,A3:Z3,A5:Z5,A7:Z7,A9:Z9,A11:Z11,A13:Z13,A15:Z15,A17:Z17,A19:Z19")

    If Not Intersect(Activecell, evenRange) Is Nothing Then
    If Activecell.Interior.Color = Activecell.Offset(0, -1).Interior.Color Then
        Activecell.Value = Activecell.Offset(0, -1).Value + 1

        For i = 1 To Selection.Cells.Count - 1
        Activecell.Offset(0, i).Value = Activecell.Value + i

        Next i
     Else
      Activecell.Value = 1

        For i = 1 To Selection.Cells.Count - 1
        Activecell.Offset(0, i).Value = Activecell.Value + i

        Next i

      End If
   'MsgBox "Active Cell In Even Range!"
    Else
'      MsgBox "Active Cell In Odd Range!"
      If Activecell.Interior.Color = Activecell.Offset(0, 1).Interior.Color Then
        Activecell.Value = Activecell.Offset(0, 1).Value + 1

        For i = 1 To Selection.Cells.Count - 1
        Activecell.Offset(0, -i).Value = Activecell.Value + i

        Next i
     Else
      Activecell.Value = 1

        For i = 1 To Selection.Cells.Count - 1
        Activecell.Offset(0, -i).Value = Activecell.Value + i

        Next i

      End If

End If

    GenNumber = Activecell.Value
End Function

Can anyone say where is the wrong and how to solve it. Thank you.

Mostak
  • 1
  • 1
  • Do you call it from a sheet? – GSerg May 26 '20 at 07:59
  • 2
    Why are you using `ActiveCell` in a Function? (There *are* sometimes good reasons, but they are very rare!) This falls under [How to Avoid using `Select` in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) – Chronocidal May 26 '20 at 08:04
  • GSerg, I am trying to use in worksheet like "=GenNumber()" in cell. – Mostak May 26 '20 at 16:02
  • Chronocidal, cell value depends on other adjacent cell value. So I used ActiveCell. – Mostak May 26 '20 at 16:08
  • 1
    If you are attempting to get a value from the Worksheet like this then you should generally be using Application.Caller not ActiveCell. https://excelfox.com/forum/showthread.php/937-Application-Caller – Tragamor May 26 '20 at 16:34
  • @Mostak If I use the function in Cell B2, followed by clicking in Cell E7, then the active Cell will be E7, while `Application.Caller` (as @Tragamor already mentioned) will still be B2 - which of these results sound more like what you want? – Chronocidal May 28 '20 at 08:48

0 Answers0