Simple Macro, What am I not seeing??

[FranktheBank]

I have the below Code, it simply is supposed to compare 'ThisWorkbook' Cells 2B, 2G and 2H in a loop with "IT_Expenditure" Worksheet "IT_Expenditure_Wks" Cells 2B, 2P and 2Q then 3B, 3P and 3Q etc.

When it gets to: If Worksheets("IT_ITGov_Expenses").Cells(lngI, 2) = Worksheets("IT_Expenditure_Wks").Cells(lng2, 2) I get the error:Runtime Error:13 Type mismatch.

I've replaced the"B", "G","H", "P" & "Q" with their corresponding number (2,7,8,16 & 17) and I get the same Error. I'm missing something simple can someone point it out?? Thanks

Sub Definitions()
    Application.ScreenUpdating = True
    Dim Vendor_Code As String
    Dim Vendor_Name As String
    Dim Account As String
    Dim IT_Expenditure As Workbook
    Dim IT_Expenditure_Wks As Worksheet
    Dim IT_ITGov As Workbook
    Dim IT_ITGov_Expense As Worksheet
'
    Set IT_ITGov = ThisWorkbook
    Set IT_ITGov_Expenses = IT_ITGov.Sheets("IT_ITGov_Expenses")
    Set IT_Expenditure = Workbooks.Open("G:\Planning\Branches\ITT\2015-16\IT Expenditure\IT Expenditure Workbook_20150817.xls")
    Set IT_Expenditure_Wks = IT_Expenditure.Sheets("Workbook")
    ThisWorkbook.Activate
    Dim lngI As Long
    Dim lng2 As Long
    For lngI = 2 To Worksheets("IT_ITGov_Expenses").UsedRange.Rows.Count Step 1
        With Cells(lngI, "B")
            For lng2 = 2 To IT_Expenditure_Wks.UsedRange.Rows.Count Step 1
                With Worksheets("IT_ITGov_Expenses").Cells(lngI, "B")
                    If Worksheets(IT_ITGov_Expenses).Cells(lngI, 2) = Worksheets("IT_Expenditure_Wks").Cells(lng2, 2) Then
                    If Worksheets(IT_ITGov_Expenses).Cells(lngI, 7) = Worksheets("IT_Expenditure_Wks").Cells(lng2, 16) Then
                    If Worksheets(IT_ITGov_Expenses).Cells(lngI, 8) = Worksheets("IT_Expenditure_Wks").Cells(lng2, 17) Then
                    Worksheets(IT_ITGov_Expenses).Cells(lngI, "N") = Worksheets("IT_Expenditure_Wks").Cells(lng2, "S")
                    End If
                    End If
                    End If
                End With
            Next
        End With
    Next
                End Sub

Post Moved by Mod

 

[vletm]

Set IT_Expenditure_Wks = IT_Expenditure.Sheets("Workbook")
...
Where/What/Why is You use "IT_Expenditure_Wks"?
If Worksheets(IT_ITGov_Expenses).Cells(lngI, 2) = Worksheets("IT_Expenditure_Wks").Cells(lng2, 2) Then ...
Would be one idea...

PS why do You those with ... and just Next (without which Next)?

 

[Marc L]

Hi !

Error 13 means bad data type as you can read in help !
For example a number is expected but data is a string …
So just check your data !
​

 

[FranktheBank]

Set IT_Expenditure_Wks = IT_Expenditure.Sheets("Workbook")
...
Where/What/Why is You use "IT_Expenditure_Wks"?
If Worksheets(IT_ITGov_Expenses).Cells(lngI, 2) = Worksheets("IT_Expenditure_Wks").Cells(lng2, 2) Then ...
Would be one idea...
PS why do You those with ... and just Next (without which Next)?

Thanks for your input vletm,

I used "IT_Expenditure_Wks" because the two workbooks I'm using are in two different directorates.

I'm using 'With' instead of 'Next' because that's what I did in another Macro and it worked fine??? Should I be using another 'For..Next' loop??

 

[FranktheBank]

Hi !

Error 13 means bad data type as you can read in help !
For example a number is expected but data is a string …
So just check your data !
​

O.K. so Columns 'B' in both worksheets is always numerical, but 'G' & 'H' and 'P' & 'Q' may be either numerical or String so how do I compare those??

 

[vletm]

for x = 2 to 3
    with sheets("A")
        for y = 2 to y_max
            .cells(y,x)
        next y
    end with
next x

What would be values of
Worksheets("IT_ITGov_Expenses").UsedRange.Rows.Count and
IT_Expenditure_Wks.UsedRange.Rows.Count
from You code?

'With & For - Next': If something works somewhere, that's good but do that code need those ... that was a question?