PDA

View Full Version : Macro to look at list in sheet1, if not in sheet2, pull in



preseb
07-05-2011, 08:20 AM
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
07-05-2011, 08:38 AM
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

preseb
07-05-2011, 08:45 AM
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
07-05-2011, 10:35 AM
Numberformat:
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

preseb
07-05-2011, 10:55 AM
Kenneth Hobs - thank you for your help, but it is still dropping the leading zeros.

any other suggestions?

CatDaddy
07-05-2011, 11:09 AM
copy as string?

Kenneth Hobs
07-05-2011, 11:16 AM
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.

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

preseb
07-05-2011, 11:35 AM
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
07-05-2011, 11:39 AM
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
07-05-2011, 11:42 AM
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

preseb
07-05-2011, 11:53 AM
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
07-05-2011, 12:04 PM
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

preseb
07-05-2011, 01:56 PM
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