Worksheet Function across Range (VBA)

[expeo]

I am not sure what I am doing wrong here. It errors out when it reaches the worksheetfunction

Sub STwoFill()


Dim rng As Range: Set rng = Application.Range("STwo!C14:C1013")
Dim cel As Range


Dim NorMean As Integer
Dim NorSD As Integer


NorMean = Sheets("STwo").Range("C5").Value
NorSD = Sheets("STwo").Range("C6").Value

Randomize 'Not sure if this part is needed, but I have tried using with and without. 


For Each cel In rng.Cells
    With cel
        .Value = Round(WorksheetFunction.Norm_Inv(Rnd(), NorMean, NorSD))
    End With
Next cel
  
End Sub

 

[Leith Ross]

Hello expeo,

The Norm_Inv arguments all need to be Double, not Integer. Numbers in cells on the worksheet are always Double data typed.

Sub STwoFill()


Dim rng As Range: Set rng = Application.Range("STwo!C14:C1013")
Dim cel As Range




Dim NorMean As Double
Dim NorSD As Double




NorMean = Sheets("STwo").Range("C5").Value
NorSD = Sheets("STwo").Range("C6").Value


Randomize 'Not sure if this part is needed, but I have tried using with and without.




For Each cel In rng.Cells
    With cel
        .Value = Round(WorksheetFunction.Norm_Inv(Rnd(), NorMean, NorSD))
    End With
Next cel
  
End Sub