CODEWITHSUNDEEP
vbaexcelloopsfor-loop
Rhyfelwr
Rhyfelwr

Reputation: 329

Trying to match some data across two sheets

I get

"Type Mismatch" error

I have the following code that loops through two sheets, matching data and fills out column "C" and "D" accordingly. The code works perfectly up until I put in the "And" statements, at which point I get a "Type mismatch" error, and the debugging highlights that line too. I cannot figure out what is wrong, any help would be appreciated. 

Sub ind_access_report()
Dim lastrow As Variant
Dim sh1 As Worksheet
Dim sh2 As Worksheet
Dim x As Variant
Dim iName As String

    Set sh1 = ActiveWorkbook.Sheets("Sheet1")
    Set sh2 = ActiveWorkbook.Sheets("Sheet2")

iName = sh2.Range("A2").Value
lastrow = sh1.Cells(Rows.Count, "A").End(xlUp).Row

For x = 2 To lastrow

    If sh1.Range("C" & x).Value = iName _
  Then sh2.Range("C" & x + 3).Value = sh1.Range("A" & x).Value _
   And sh2.Range("D" & x + 3) = "OWNER"

    If sh1.Range("D" & x).Value = iName _
  Then sh2.Range("C" & x + 3).Value = sh1.Range("A" & x).Value _
   And sh2.Range("D" & x + 3) = "BACKUP"

    If sh1.Range("E" & x).Value = iName _
  Then sh2.Range("C" & x + 3).Value = sh1.Range("A" & x).Value _
   And sh2.Range("D" & x + 3) = "BACKUP"


Next x

Upvotes: 0

Views: 38

Answers (2)

Sam
Sam

Reputation: 5721

You will have to rethink your line break strategy. It is the main reason of why it is failing. If you have a line break after Then, you will need an End If.
Try this:

Sub ind_access_report()
    Dim lastrow As Variant
    Dim sh1 As Worksheet
    Dim sh2 As Worksheet
    Dim x As Variant
    Dim iName As String

    Set sh1 = ActiveWorkbook.Sheets("Sheet1")
    Set sh2 = ActiveWorkbook.Sheets("Sheet2")

    iName = sh2.Range("A2").Value
    lastrow = sh1.Cells(Rows.Count, "A").End(xlUp).Row

    For x = 2 To lastrow

        If sh1.Range("C" & x).Value = iName Then
            sh2.Range("C" & x + 3).Value = sh1.Range("A" & x).Value
            sh2.Range("D" & x + 3) = "OWNER"
        End If

        If sh1.Range("D" & x).Value = iName Then
            sh2.Range("C" & x + 3).Value = sh1.Range("A" & x).Value
            sh2.Range("D" & x + 3) = "BACKUP"
        End If

        If sh1.Range("E" & x).Value = iName Then
            sh2.Range("C" & x + 3).Value = sh1.Range("A" & x).Value
            sh2.Range("D" & x + 3) = "BACKUP"
        End If

    Next x
End If

Upvotes: 3

braX
braX

Reputation: 11755

You are not using And correctly. You are probably trying to do multiple things in your If statement. Using And is not how you do it. Instead, use multiple lines and End If like this:

If sh1.Range("C" & x).Value = iName Then 
    sh2.Range("C" & x + 3).Value = sh1.Range("A" & x).Value
    sh2.Range("D" & x + 3) = "OWNER"
End If

Upvotes: 1

Related Questions