Error 13 : Type Mismatch Error // Error not highlighted-CodePudding

I am currently getting an "Error 13: Type Mismatch " Error for the following code and I can't seem to find where the problem is coming from

I have added the Option Explicit code to try and assist but it still doesn't highlight anything when the program is executed.

Option Explicit
Public mainWB As Workbook
Public mainWS As Worksheet
Public newWS As Worksheet

Sub Main()

'Creating New Variables
Dim TranstactDate As String, AmountExcl As Single
Dim mainR As Long, mainC As Long, newR As Long, newC As Long
Dim randNumber As Long
Dim accHolder As String

newR = 2 'start of writing Row

Set mainWB = Workbooks("arrears-formatter.xlsx") 'Setting mainWB
Set mainWS = mainWB.Worksheets("arrears-formatter") ' set mainWS to the working Worksheet

mainWB.Activate 'Shows that were working in the mainWB workbook
randNumber = Int((99999 - 10000   1) * Rnd   10000)

TranstactDate = mainWS.Cells(1, 2)

For mainR = 9 To 1000
    If mainWS.Cells(mainR, 1) = "" Then GoTo exitthis:
    
    accHolder = Left(mainWS.Cells(mainR, 1), 3)
    
    AmountExcl = mainWS.Cells(mainR, 3)
    
    While Left(mainWS.Cells(mainR, 1), 3) = accHolder
        Set newWS = mainWB.Worksheets(accHolder   "-"   randNumber)
        
        newWS.Range("A2:A1000").NumberFormat = "@"
        'Determining new sheet values
        newWS.Cells(newR, 1) = TranstactDate
        newWS.Cells(newR, 1).NumberFormat = "dd/mm/yyy"
        newWS.Cells(newR, 2) = mainWS.Cells(mainR, 1)
        newWS.Cells(newR, 3) = "AR"
        newWS.Cells(newR, 4) = "Interest"
        newWS.Cells(newR, 5) = "0"
        newWS.Cells(newR, 6) = "7"
        newWS.Cells(newR, 7) = "Interest"
        newWS.Cells(newR, 8) = ""
        newWS.Cells(newR, 9) = AmountExcl
        newWS.Cells(newR, 10) = ""
        newWS.Cells(newR, 11) = ""
        newWS.Cells(newR, 12) = "0"
        newWS.Cells(newR, 13) = AmountExcl
        newWS.Cells(newR, 14) = "1"
        newWS.Cells(newR, 15) = AmountExcl
        newWS.Cells(newR, 16) = AmountExcl
        newWS.Cells(newR, 17) = "0"
        newWS.Cells(newR, 18) = "0"
        newWS.Cells(newR, 19) = ""
        newWS.Cells(newR, 20) = "0"
        newWS.Cells(newR, 21) = "0"
        newWS.Cells(newR, 22) = "0"
        newWS.Cells(newR, 23) = ""
        newWS.Cells(newR, 24) = ""
        newWS.Cells(newR, 25) = "0"
        newWS.Cells(newR, 26) = "0"
        newWS.Cells(newR, 27) = ""
        newWS.Cells(newR, 28) = "0"
        newWS.Cells(newR, 29) = "0"
        newWS.Cells(newR, 30) = "0"
        newWS.Cells(newR, 31) = "2750>050"
        newWS.Cells(newR, 32) = "0"
        newWS.Cells(newR, 33) = "0"
        
        newR = newR   1
    Wend
exitthis:
Next mainR

    newWS.Cells(1, 1) = "Trans Date"
    newWS.Cells(1, 2) = "Account"
    newWS.Cells(1, 3) = "Module"
    newWS.Cells(1, 4) = "Trans Code"
    newWS.Cells(1, 5) = "Post Date"
    newWS.Cells(1, 6) = "Reference"
    newWS.Cells(1, 7) = "Description"
    newWS.Cells(1, 8) = "Order Number"
    newWS.Cells(1, 9) = "Amount Excl"
    newWS.Cells(1, 10) = "Tax Type"
    newWS.Cells(1, 11) = "Tax Account"
    newWS.Cells(1, 12) = "Is Debit"
    newWS.Cells(1, 13) = "Amount Incl"
    newWS.Cells(1, 14) = "Exchange Rate"
    newWS.Cells(1, 15) = "Foreign Amount Excl"
    newWS.Cells(1, 16) = "Foreign Amount Incl"
    newWS.Cells(1, 17) = "Discount Percent"
    newWS.Cells(1, 18) = "Discount Amount Excl"
    newWS.Cells(1, 19) = "Discount Tax Type"
    newWS.Cells(1, 20) = "Discount Amount Incl"
    newWS.Cells(1, 21) = "Foreign Discount Amount Excl"
    newWS.Cells(1, 22) = "Foreign Discount Amount Incl"
    newWS.Cells(1, 23) = "Project Code"
    newWS.Cells(1, 24) = "Rep Code"
    newWS.Cells(1, 25) = "Split Group"
    newWS.Cells(1, 26) = "Split GL Account"
    newWS.Cells(1, 27) = "Split Description"
    newWS.Cells(1, 28) = "Split Amount"
    newWS.Cells(1, 29) = "Foreign Split Amount"
    newWS.Cells(1, 30) = "Split Project Code"
    newWS.Cells(1, 31) = "GL Contra Code"
    newWS.Cells(1, 32) = "Split Tax Type"
    newWS.Cells(1, 33) = "Split Tax Account"
    
   
    
With newWS.Sort
     .SortFields.Add Key:=Range("C2"), Order:=xlAscending
     .SetRange Range("A2:G1000")
     .Header = xlNo
     .Apply
End With


End Sub

Please kindly assist if you can see where this error comes from , its just used to re-format an excel sheet

CodePudding user response：

You are trying to create a new worksheet using a random number:

Set newWS = mainWB.Worksheets(accHolder   "-"   randNumber)

However, that will not create a worksheet. You need to first create the worksheet, and then rename the worksheet.

Set newWS = mainwb.Sheets.Add
newWS.Name = accholder & "-" & randNumber

Also, you were trying to join variables and strings using the " " symbol, you need to use an ampersand "&".