Filling a Range with data

Question

I am working with several Ranges which contain data, and out of these Ranges I have to calculate means and other stuff. But before I can do so I need to find outliers and mark them as such. Most of it works fine but I have a problem when trying to fill the Range where I want to mark the outliers as outliers. I have read before that I can't fill anything but the Cell where I call the function from with values. That is why I tried a sub instead.

Please see below a simple version of what i would like to achive.

The Error I receive is that there is an issue with a datatype

Function MySheet()
    'returns a String containing the Worksheet Name
    MySheet = Application.Caller.Worksheet.Name
End Function

Sub FillIt(Register As String, x As Long, y As Long, Val As String)
    Debug.Print Register, x, y, Val, TypeName(Val)
    ' I checked the passed Values which have made it until here and they seem fine
    Sheets(Register).Cells(x, y) = Val ' The problem must lie here but I have almost the same in another sub and there it works.
End Sub

Function Fill(myRange As Range) ' This is what will get called from the worksheet
    Dim Register As String
    Dim i As Long, j As Long
    Register = MySheet()
    For i = 1 To myRange.Rows.Count
        For j = 1 To myRange.Columns.Count
            Debug.Print i, j
            If i * j Mod 2 = 0 Then
                Call FillIt(Register, myRange.Row + i - 1, myRange.Column + j - 1, "o") ' there is an issue with this line
            End If
        Next j
    Next i
    Fill = 88 ' just as a return value to check if it works
End Function

user_12231984 · Answer

Functions, by definition, return a single value - they aren't allowed to affect other cells. For affecting multiple cells like this you'll need to do it in a Sub. I quickly adapted yours to the below for testing and it seemed to work ('work' = complete with no errors) - but you'll need some way of calling the sub obviously.

Sub FillIt(Register As String, x As Long, y As Long, Val As String)
    Debug.Print Register, x, y, Val, TypeName(Val)
    ' I checked the passed Values which have made it until here and they seem fine
    Sheets(Register).Cells(x, y) = Val ' The problem must lie here but I have almost the same in another sub and there it works.
End Sub

Sub Fill(myRange As Range) ' This is what will get called from the worksheet
    Dim Register As String
    Dim i As Long, j As Long
    Register = ActiveSheet.Name
    For i = 1 To myRange.Rows.Count
        For j = 1 To myRange.Columns.Count
            Debug.Print i, j
            If i * j Mod 2 = 0 Then
                Call FillIt(Register, myRange.Row + i - 1, myRange.Column + j - 1, "o") ' there is an issue with this line
            End If
        Next j
    Next i
    
End Sub

Sub Testing()
Call Fill(ActiveSheet.Range("C10:E15"))
End Sub

If you'll be wanting to do this on various different ranges I suggest putting in an Application.Inputbox at the start of the sub to set your Range variable.

Filling a Range with data

1 Answers