Total Newb to VB for Applications. I've got an Excel 2007 spreadsheet that is Huge and I am manually sorting a lot of stuff. Functions have failed me. I need Code! I'm familiar with VBScript and AutoIt3.
As the subject states, I need to compare Sheet2, Column 1, Cell 1 to every cell in Sheet 7, Column 1 to see if there is a match. If there is NOT a match, I need to take the whole row (12 columns) and put it into the next available row in sheet 6.
So far, I've opened the Visual Basic editor at Sheet6 and started on Code. Its not exactly what I want... but...
I get a 400 error when running this... what is wrong? And Is there an easier/quicker/better way to do this?
?
As the subject states, I need to compare Sheet2, Column 1, Cell 1 to every cell in Sheet 7, Column 1 to see if there is a match. If there is NOT a match, I need to take the whole row (12 columns) and put it into the next available row in sheet 6.
So far, I've opened the Visual Basic editor at Sheet6 and started on Code. Its not exactly what I want... but...
Code:
' start with (A1) in (Sheet 2) and compare
' to (Sheet 7) to see if it matches any cell in (A:A)
' If there is not a match, write A1 to next row in (Sheet 6)
' Repeat for (sheets 3-5)
Sub Compare()
Dim active_sheet As Worksheet
Dim CurCell
Dim CompCell
Dim Matched As Variant
Dim RealLastRow As Long
Set active_sheet = ActiveSheet
' Loop through Sheet 2, column A - every cell that has something in it
For Each CurCell In Sheet2.Range("A:A").Cells
If IsEmpty(CurCell) Then Exit For
' Compare current cell to each cell in sheet 7
Matched = "No"
For Each CompCell In Sheet7.Range("A:A").Cells
If IsEmpty(CompCell) Then Exit For
If CompCell.Value = CurCell.Value Then
Matched = "Yes"
Exit For
End If
Next
' Check to see if there was a match
If Matched = "No" Then
' Find the Last row, move one down and put the value of matched cell into it
RealLastRow = Cells.Find("*", Range("A1"), xlFormulas, , xlByRows, xlPrevious).Row
Cells(RealLastRow + 1, A).Value = CurCell.Value
End If
Next
' Repeat for Sheet 3, 4 and 5
End Sub
I get a 400 error when running this... what is wrong? And Is there an easier/quicker/better way to do this?
?