Use a cell value to several decimal pfrom a worksheet named range in a formula in a macro

[chappy]

I would like to define "commPS" as cell E47 on worksheet "INSTRUCTIONS" (excel named range "COMM_PER_SHARE") in the code below (appliedCommision = quantity * commPS ). Ideally I would like to reference the excel named range "COMM_PER_SHARE" rather than cell reference E47. The value is to several decimal places so it should not be treated as a whole number or rounded up.

I anyone could help me define this so I can test it in the code it would be very much appreciated. Thanks in advance.

Private Sub calculateCommissionPayable(actvSheet As Worksheet)

Dim rw As Range
Dim col As Range
Dim commPS As Double
Dim actvSheet As Worksheet
Dim instructSheet As Worksheet
Dim quantity As Integer 'Current Quantity

Set actvSheet = ActiveWorkbook.Sheets("DAS_DATA")
Set instructSheet = ActiveWorkbook.Sheets("INSTRUCTIONS")

commPS = instructSheet.Range("E47")

For Each rw In actvSheet.UsedRange.Rows 'Looping on each row of Sheet1'

If rw.row <> 1 Then 'Skip first header row'

Dim appliedCommision As Double 'Calculated commission'

quantity = convertToInt(Cells(rw.row, 5).Value)

appliedCommision = quantity * commPS

actvSheet.Cells(rw.row, 14).Value = appliedCommision 'applies commission per ticket

End If 'Check if not First Row'

Next rw
Exit Sub

End Sub

 

[chappy]

Managed to fix this with a different workaround

 

[jasonb75]

Not sure if I follow exactly what you were asking for, as far as I can see you just need to change the commPS definition to

commPS = Range("COMM_PER_SHARE")

You would only need to qualify it with a reference to the sheet if the named range is scoped to worksheet level instead of workbook level.

 

[chappy]

Not sure if I follow exactly what you were asking for, as far as I can see you just need to change the commPS definition to

commPS = Range("COMM_PER_SHARE")

You would only need to qualify it with a reference to the sheet if the named range is scoped to worksheet level instead of workbook level.

Thanks for posting a reply Jason. Much appreciated!