spanishnick
New Member
- Joined
- Nov 10, 2021
- Messages
- 12
- Platform
- Windows
Hi,
I'd like to split my data (column AS) into quintile groups, i.e. assign a value from 1 (lowest 20%) to 5 (highest 20%) in column AT. I've written the code below but get the following error message: "Sub of Function not defined", referring to dataout in row 14 (the first propvalue(r, 1).Value ). I was wondering if anyone could give me a hand.
Thank you!
I'd like to split my data (column AS) into quintile groups, i.e. assign a value from 1 (lowest 20%) to 5 (highest 20%) in column AT. I've written the code below but get the following error message: "Sub of Function not defined", referring to dataout in row 14 (the first propvalue(r, 1).Value ). I was wondering if anyone could give me a hand.
Thank you!
VBA Code:
Sub quintiles()
With Sheets("Geomapping Data")
.Range("au1:au102835") = ""
datout = .Range("au1:au102835") ' load all the data into a variant array
propvalue = .Range("as1:as102835")
For r = 2 To 102835
If propvalue(r, 1).Value <= WorksheetFunction.Percentile("AS:AS", 0.2) Then
dataout(r, 1).Value = 1
Else
If propvalue(r, 1).Value > WorksheetFunction.Percentile("AS:AS", 0.2) And propvalue(r, 1).Value <= WorksheetFunction.Percentile("AS:AS", 0.4) Then
dataout(r, 1).Value = 2
Else
If propvalue(r, 1).Value > WorksheetFunction.Percentile("AS:AS", 0.4) And propvalue(r, 1).Value <= WorksheetFunction.Percentile("AS:AS", 0.6) Then
dataout(r, 1).Value = 3
Else
If propvalue(r, 1).Value > WorksheetFunction.Percentile("AS:AS", 0.6) And propvalue(r, 1).Value <= WorksheetFunction.Percentile("AS:AS", 0.8) Then
dataout(r, 1).Value = 4
Else
If propvalue(r, 1).Value > WorksheetFunction.Percentile("AS:AS", 0.8) And propvalue(r, 1).Value <= WorksheetFunction.Percentile("AS:AS", 1) Then
dataout(r, 1).Value = 5
End If
End If
End If
End If
End If
Next r
End With
End Sub