VBA vlookup/If condition missing

[excel01noob]

Hi

with these two files, Rawdata (changes each month) and commission table (no change) how can I add this additional condition to the below code:

if customer ref has more than 5 characters, take the first 5 left characters and compare with commission table array. If there is no match then result is "Commission setup missing"

my other conditions are working fine:
-if customer ref has more than 5 characters, take the first 5 left characters and compare with commission table array. On a match, result is % commission from commission table array B column;
-if customer ref is equal to 5 characters, commission is 0,5% (hard-coded)
-if customer ref is less than 5 characters, result is "Account setup missing"

My code



Option Explicit

Sub COMMISSIONS_check_2()

Application.ScreenUpdating = False

'Raw is the rawdata file
'Comm is commission table with variables comm %

Dim wbComm As Workbook, wbRaw As Workbook
Dim wsComm As Worksheet, wsRaw As Worksheet
Dim CommLR As Long, RawLR As Long, x As Long
Dim CommRng As Range

Set wbComm = Workbooks("commission table.xlsx")
Set wsComm = wbComm.Sheets(1)
CommLR = wsComm.Range("A" & Rows.Count).End(xlUp).Row

Set wbRaw = ActiveWorkbook
Set wsRaw = wbRaw.Sheets(1)
RawLR = wsRaw.Range("A" & Rows.Count).End(xlUp).Row

Set CommRng = wsComm.Range("A2:B" & CommLR)

For x = 2 To RawLR

On Error Resume Next

If Len(wsRaw.Range("A" & x)) > 5 Then

wsRaw.Range("C" & x).Value = Application.WorksheetFunction.VLookup(Left(wsRaw.Range("A" & x).Value, 5), CommRng, 2, False)
Else
If Len(wsRaw.Range("A" & x)) = 5 Then
wsRaw.Range("C" & x).Value = "0,5%"

Else
If Len(wsRaw.Range("A" & x)) < 5 Then
wsRaw.Range("C" & x).Value = "Account setup missing"
End If
End If

End If

Next x

Application.ScreenUpdating = True

End Sub

 

[excel01noob]

In this part of the code

++++++++++++++++++++++++++++++++
If Len(wsRaw.Range("A" & x)) > 5 Then

wsRaw.Range("C" & x).Value = Application.WorksheetFunction.VLookup(Left(wsRaw.Range("A" & x).Value, 5), CommRng, 2, False)
Else

+++++++++++++++++++++++
how would I include in this part of the code this additional criteria

"if customer ref has more than 5 characters, take the first 5 left characters and compare with commission table array. If there is no match then result is "Commission setup missing""