Macro to look at list in sheet1, if not in sheet2, pull in

[preseb]

For simplicity purpose, say in Sheet 1, column A I have numbers:
1
3
5
7
8
10
In Sheet2, column A I have numbers:
1
2
3
4
5
6
7
8
9
10
I need a macro to loop through sheet1 to see if that number is in sheet2. If not to pull insert a row in numerical order and insert just the number, not the entire row.
So the end result would have numbers 1-10 list in sheet1 column A

[Bob Phillips]

[vba]

Public Sub ProcessData()
Dim Lastrow As Long
Dim InsertAfter As Long
Dim i As Long

Application.ScreenUpdating = False

With Worksheets("Sheet2")

Lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
InsertAfter = 0
For i = 1 To Lastrow

If IsError(Application.Match(.Cells(i, "A").Value2, Worksheets("Sheet1").Columns(1), 0)) Then

Worksheets("Sheet1").Rows(InsertAfter + 1).Insert
Worksheets("Sheet1").Cells(InsertAfter + 1, "A").Value2 = .Cells(i, "A").Value2
Else

InsertAfter = i
End If
Next i
End With

Application.ScreenUpdating = True
End Sub
[/vba]

[preseb]

that worked well, but I have one dilemma. My numbers start is leading 0's. so in my example below the numbers are actually: 00001, 00002, etc.

When your macro ran it pulled the numbers over, but I lost the leading zeros. How can I correct this?

Thanks

[Kenneth Hobs]

Numberformat:
[VBA]Public Sub ProcessData()
Dim Lastrow As Long
Dim InsertAfter As Long
Dim i As Long

Application.ScreenUpdating = False

With Worksheets("Sheet2")

Lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
InsertAfter = 0
For i = 1 To Lastrow

If IsError(Application.Match(.Cells(i, "A").Value2, Worksheets("Sheet1").Columns(1), 0)) Then

Worksheets("Sheet1").Rows(InsertAfter + 1).Insert
Worksheets("Sheet1").Cells(InsertAfter + 1, "A").Value2 = .Cells(i, "A").Value2
Worksheets("Sheet1").Cells(InsertAfter + 1, "A").NumberFormat = .Cells(i, "A").NumberFormat
Else

InsertAfter = i
End If
Next i
End With

Application.ScreenUpdating = True
End Sub[/VBA]

[preseb]

Kenneth Hobs - thank you for your help, but it is still dropping the leading zeros.

any other suggestions?

[CatDaddy]

copy as string?

[Kenneth Hobs]

If it is then your numberformat for sheet2 cells is not set to leading 0's. What you may want to do is to set a format for all the range in sheet1 where it uses say Sheet2's cell A1's numberformat.
[vba]
Public Sub ProcessData()
Dim Lastrow As Long
Dim InsertAfter As Long
Dim i As Long

Application.ScreenUpdating = False

With Worksheets("Sheet2")
Lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
InsertAfter = 0
For i = 1 To Lastrow
If IsError(Application.Match(.Cells(i, "A").Value2, Worksheets("Sheet1").Columns(1), 0)) Then
Worksheets("Sheet1").Rows(InsertAfter + 1).Insert
Worksheets("Sheet1").Cells(InsertAfter + 1, "A").Value2 = .Cells(i, "A").Value2
Else
InsertAfter = i
End If
Next i
Worksheets("Sheet1").Range("A1", Worksheets("Sheet1").Range("A" & Rows.Count).End(xlUp)).NumberFormat = _
.Range("A1").NumberFormat
End With

Application.ScreenUpdating = True
End Sub[/vba]

[preseb]

hummm. it's still not capturing the leading zeros.

what about adding an apostrophe before the zeros?
Take both sheet 1 and 2 and concatenate the numbers with a leading apostrophe, then run your macro. you think that would work?

[Kenneth Hobs]

Set your numberformat in Sheet2's cell A1 and it should work properly. Otherwise, post your workbook so that we can see what is going on.

[Bob Phillips]

[vba]

Public Sub ProcessData()
Dim Lastrow As Long
Dim InsertAfter As Long
Dim i As Long

Application.ScreenUpdating = False

With Worksheets("Sheet2")

Lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
InsertAfter = 0
For i = 1 To Lastrow

If IsError(Application.Match(.Cells(i, "A").Value2, Worksheets("Sheet1").Columns(1), 0)) Then

Worksheets("Sheet1").Rows(InsertAfter + 1).Insert
Worksheets("Sheet1").Cells(InsertAfter + 1, "A").Value2 = .Cells(i, "A").Value2
Worksheets("Sheet1").Cells(InsertAfter + 1, "A").NumberFormat = "00000"
Else

InsertAfter = i
End If
Next i
End With

Application.ScreenUpdating = True
End Sub [/vba]

[preseb]

xld - yours visually worked but in the text box at the top it will did not show the leading zeros

what about bringing the number over at text?

[Bob Phillips]

[vba]Public Sub ProcessData()
Dim Lastrow As Long
Dim InsertAfter As Long
Dim i As Long

Application.ScreenUpdating = False

With Worksheets("Sheet2")

Lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
InsertAfter = 0
For i = 1 To Lastrow

If IsError(Application.Match(.Cells(i, "A").Value2, Worksheets("Sheet1").Columns(1), 0)) Then

Worksheets("Sheet1").Rows(InsertAfter + 1).Insert
Worksheets("Sheet1").Cells(InsertAfter + 1, "A").Value2 = Format(.Cells(i, "A").Value2, "00000")
Else

InsertAfter = i
End If
Next i
End With

Application.ScreenUpdating = True
End Sub [/vba]

[preseb]

I used the above code and added some lines to go to both sheets and make both "A" columns to be text. that seem to have worked.

Thanks everyone