vba types incompatible while comparing two strings-CodePudding

I want to compare two strings from two different worksheets with vba in excel. Already wrote the following code:

Public Sub Vergleich_Arbeitsmappen()
Dim i As Long
Dim projectCounter As Integer
Dim strAngNr As String
Dim strCodename As String



Dim wks As Workbook

strAngNr = Range("C3").Value
strCodename = Range("C4").Value
projectCounter = 200

Set wks = Workbooks.Open("filename")

For i = 2 To projectCounter
    
    If CStr(Workbooks(wks).Worksheets("Tabelle2").Cells(i, 2).Value) = strAngNr Then
        MsgBox "FEHLER"
        
    End If
Next i

End Sub

But while debugging, excel always show the error "types incompatible" even if both values are strings, as defined.

CodePudding user response：

Your problem doesn't come from the 2 strings you are comparing, it comes from the invalid parameter into the Workbooks-collection.
As you have Workbook object already stored in wks, you simply can write

wks.Worksheets("Tabelle2").Cells(i, 2).Value

You use the Workbooks-Collection to access an open workbook either by name or by index so the parameter into Workbooks is either a String or a Number (In theory you could write Workbooks(wks.Name), but that would be completely useless).

You are passing an object of type Workbook as Parameter, and that fails and give you the "types incompatible"-error.