Type Mismatch

[pastorkc]

If I keep getting a type mismatch error when I run my macro and it has never had it before, could it be the new data that I am importing and if so, how do I know where it is erroring out?

 

[Joe4]

It is very likely that it could be your data.
When you get the error, do you get a "Debug" button option?
If so, click on it and see which line of code it highlights. That is where it is having issues.

Other than that, without seeing your code and sample data, there probably isn't much else we could advise you on.

 

[pastorkc]

No I do not get a debug option, that was what I was hoping for so it would point me to the error. It has to be my data because the previous months ran fine. In researching I found an accounting entry this month that hasn't been done in the past so I am trying to figure out how to rectify the situation.

 

[Joe4]

How about setting a break point at the first line of the code, then stepping through your code one line at a time while watching what happens in the workbook, and see where it errors?

 

[pastorkc]

I have a good idea were it is erroring, just thinking about how to fix it. It's the first time that we split an invoice onto two checks and they way the data comes over is confusing the macro when it copies the data.

 

[Joe4]

Without the details, we really cannot advise you on it, since we don't know what you are working with or trying to accomplish (in detail).

 

[pastorkc]

Transit Invoice Sheet.xlsm
	A	B	C	D	E	F	G
359							0.00
360			091923	Invoice	091923	9/19/23	218.71
361				Payment	0932	10/12/23	-218.71
362				Payment	0932V	10/16/23	218.71
363				Payment	21948	10/17/23	-218.71
364
365							0.00
Vendor Transaction History

Transit Invoice Sheet.xlsm
	A	B	C	D	E	F	G
553			89318JS23	Invoice	89318JS23	9/6/23	410.00
554				Payment	1007	9/15/23	-205.00
555				Payment	0916	9/15/23	-205.00
Vendor Transaction History

ThisWorkbook.Sheets("Vendor Transaction History").Activate
    Dim rA As Range
  
    Application.ScreenUpdating = False
    For Each rA In Range("D2", Range("D" & Rows.Count).End(xlUp)).SpecialCells(xlConstants).Areas
    With rA.Offset(, -3).Resize(, 2)
      If IsEmpty(.Cells(1, 1).Value) Then .Value = .Offset(-3).Value
    End With
    rA.Offset(, -1).Resize(, 5).Sort Key1:=rA.Columns(1), Order1:=xlAscending, Header:=xlNo
    Next rA
    Application.ScreenUpdating = True
    
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

The above is were it is erroring out. The code is the section that is supposed to look and copy information, but it puts a #NA in the blank cells in the highlighted areas. Hope this helps.

 

[Joe4]

I see one potential issue. With this line here:

For Each rA In Range("D2", Range("D" & Rows.Count).End(xlUp)).SpecialCells(xlConstants).Areas

it looks like you are starting in cell D2.

But then part of your code here:

     If IsEmpty(.Cells(1, 1).Value) Then .Value = .Offset(-3).Value

is trying to move up 3 rows from that starting point.
If you are starting on row 2, you CANNOT move up three rows!

You can add message boxes to your code to see the addresses of the ranges while testing, i.e.
Dim rA As Range

Application.ScreenUpdating = False

    For Each rA In Range("D2", Range("D" & Rows.Count).End(xlUp)).SpecialCells(xlConstants).Areas
        With rA.Offset(, -3).Resize(, 2)
            MsgBox .Address
            MsgBox .Offset(-3).Address
            If IsEmpty(.Cells(1, 1).Value) Then .Value = .Offset(-3).Value
        End With
        rA.Offset(, -1).Resize(, 5).Sort Key1:=rA.Columns(1), Order1:=xlAscending, Header:=xlNo
    Next rA
    
    Application.ScreenUpdating = True
    
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual