Hi,
I'm trying to write the code to get certain rows of data on one sheet to transfer to another sheet. The data sheet is denoted "Sheet1" and the compiled sheet is denoted "Sheet2". The compiled sheet has one row of input that includes values as well as 8 categories/classifications. If all of those 8 categories match an entry in the data sheet, then the entire row will be transferred to the compiled sheet. All of the data fields match up in each sheet; that is, the qualifications are in columns 19-26 in both. The rows to be transferred will include columns 2-26, and will go into the second sheet starting at row 3.
I'm using nested looping structures to do this, and what I have seems as if it'll make sense, but when I go to run it, there is a type mismatch. The part that gets highlighted is at the very beginning: Private Sub Transfer()
What am I doing wrong?
-------------------------------------------------------
Private Sub Transfer()
' Set variable types
Dim Lastrow As Long
Dim j As Integer
Dim k As Integer
Dim m As Integer
Dim r As Integer
Dim Check As Boolean
'Find the last row
Lastrow = Sheets("Sheet1").Cells(Rows.Count, 1).End(x1Up)
' j is used to test the columns containing qualifications
j = 19
' k is used for columns when transferring an entire row from Sheet1 to Sheet2
k = 2
' m is used for rows when transferring an entire row from Sheet1 to Sheet2
m = 3
' Loop through the rows in the data sheet (Sheet1)
For r = 2 To Lastrow
' Loop through the qualification columns on both sheets, marking True or False if they match
For j = 19 To j = 26
If Sheets("Sheet1").Cells(r, j) = Sheets("Sheet2").Cells(2, j) Then
j = j + 1
Check = True
Else
Check = False
End
' If the qualifications match, transfer the row
If Check Is True Then
For k = 2 To k = 26
Sheets("Sheet2").Cells(m, k) = Sheets("Sheet1").Cells(r, k)
k = k + 1
m = m + 1
End If
End Sub
Bookmarks