Maybe this to order it and reduce required calcs:
Option Explicit

Function pvalueconversion(pVal As Double)
    If pVal > 0.05 Then
        pvalueconversion = "1"
    ElseIf pVal > 0.01 Then
        pvalueconversion = "2"
    ElseIf pVal > 0.003 Then
        pvalueconversion = "3"
    ElseIf pVal > 0.0003 Then
        pvalueconversion = "4"
    ElseIf pVal > 0 Then
        pvalueconversion = "5"
    ElseIf pVal > -0.0003 Then
        pvalueconversion = "-5"
    ElseIf pVal > -0.003 Then
        pvalueconversion = "-4"
    ElseIf pVal > -0.01 Then
        pvalueconversion = "-3"
    ElseIf pVal > -0.05 Then
        pvalueconversion = "-2"
    Else
        pvalueconversion = "-1"
    End If
    
End Function