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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.