Insert Row For Value Not Found

**bdb1974** · 11-13-2009, 02:02 PM

Hi all,

I'm attempting to add to the following to insert a row for values it cannot find and insert the missing value into column A.

   
   OutPL = Sheets("Inventory_YARD")
     Sheets("Working_Yard").Activate
     For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row
     Set findit = OutPL.Range("A:A").Find(what:=Cells(i, 1).Value)
    If findit Is Nothing Then MsgBox "Item #" & i & " Not Found. " & i " will be added to Inventory Yard"

Therefore if the value "554500" from Column A on Sheet "Working_Yard" is not found on Sheet "Inventory_YARD" Then it will find on sheet 554499 or 554501 on Sheet "Inventory_YARD and Insert Below/Above and insert a row
with the value "554500" into column A for the repective row.

So I will end up with:
Column A:
554499
554500 ' new row added w/value
554501

Any help is appreciated.

Thanks,

BDB

**JBeaucaire** · 11-13-2009, 03:03 PM

Easiest way...when value is missing...add at the bottom of the data set and resort the data. Is the data set a single column only?

Something like this:

Sub ExampleInsertSort()
Dim OutPL As Worksheet, cell As Range, FindIt As Range

Set OutPL = Sheets("Inventory_YARD")

For Each cell In Sheets("Working_Yard").Range("A:A").SpecialCells(xlCellTypeConstants)
    Set FindIt = OutPL.Range("A:A").Find(what:=cell.Value)
    If FindIt Is Nothing Then
        MsgBox "Item #" & cell & " Not Found. " & cell & " will be added to Inventory Yard"
        Sheets("Inventory_Yard").Range("A" & Rows.Count).End(xlUp).Offset(1, 0) = cell
    End If
Next cell

Sheets("Inventory_Yard").Range("A:A").Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortTextAsNumbers
End Sub

**bdb1974** · 11-13-2009, 05:13 PM

JBeaucaire, Works Perfect !!
The data is no limited to a single column. but to each row. I'd like to bring data from other columns.

Could you show how I would do this using "For Each cell"?

I'd like to bring data in as follows:

from Working_Yard to Inventory_GUS
col. B to col. C
col. Y to col. D
col. Z to col. F

Thanks for all your help.

BDB

**JBeaucaire** · 11-13-2009, 05:24 PM

Sorry, I don't follow that. If this sheet is adding data to a data set that involves more than one column, you'll need to post up a sample workbook with enough data in it to represent the issues you want covered.

There are several columns to deal with?
All at once?
One at a time?

Include some before/after examples to make it clearest of all.

=======
Click GO ADVANCED and use the paperclip icon to post up your workbook.

**bdb1974** · 11-13-2009, 07:16 PM

Let me try & explain again.
For each row a Cell value is added to Column A on Inventory_Yard sheet,
I would like to have data from other columns for that Cell row also added
to the Inventory_Yard.

So if
Column A:
554499
554501
554500 is new value added to Inventory_Yard sheet
then for the row that 554500 is found on Working_Yard sheet
I'd like to have data from column B copied to C on Inventory Yard.
from column y to D and from Z to F.

Hopefull this clarify's what I'm asking.
If not, I'll try and post an example which will probably not be til Monday.
Thanks,

BDB

**JBeaucaire** · 11-14-2009, 01:07 PM

Maybe this:

Option Explicit

Sub ExampleInsertSort()
Dim OutPL As Worksheet, cell As Range, FindIt As Range
Dim NR As Long

Set OutPL = Sheets("Inventory_YARD")
NR = OutPL.Range("A" & Rows.Count).End(xlUp).Row + 1

For Each cell In Sheets("Working_Yard").Range("A:A").SpecialCells(xlCellTypeConstants)
    Set FindIt = OutPL.Range("A:A").Find(what:=cell.Value)
    If FindIt Is Nothing Then
        MsgBox "Item #" & cell & " Not Found. " & cell & " will be added to Inventory Yard"
        OutPL.Range("A" & NR) = cell
        OutPL.Range("C" & NR) = Cells(cell.Row, "B")
        OutPL.Range("D" & NR) = Cells(cell.Row, "Y")
        OutPL.Range("F" & NR) = Cells(cell.Row, "Z")
        NR = NR + 1
    End If
Next cell

Sheets("Inventory_Yard").Range("A:CC").Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortTextAsNumbers
End Sub