PDA

View Full Version : Solved: Insert Rows by taking the difference from another worksheet.



Shazam
06-07-2006, 08:47 AM
Hopefully this could be possible in vba. In my attachment below in worksheet tab “Download” start by looking in column C with the given part numbers you want to be able to count the number of instance of that part # appears with in that column, with that count in mind now look at the "Orders" worksheet and by looking in column B, again count the number of times with in that column by part # then take the difference of the number of counts found in worksheet “Download” and the counts from “Orders” use that number and insert that amount of rows after the last entries of that particular part # in “Orders”.


Example:

Worksheet “Download” in column C

Shazam
Shazam
Shazam
Shazam
Shazam



Worksheet “Orders” in column B

Shazam
Shazam


Take the difference ( Basic Math )
(“Download”)-(“Order Results”) =3
5 - 2 = 3


Now take that difference that will be 3 and insert 3 rows in worksheet “Orders”. Example:

Shazam
Shazam
Insert row….
Insert row….
Insert row….

There is a worksheet tab named "Orders Result" that gives a example how it should look after running the code.

Norie
06-07-2006, 11:50 AM
Shazam

Will all the different product nos be grouped together?

Shazam
06-07-2006, 11:52 AM
Shazam

Will all the different product nos be grouped together?


If you mean in order, yes it will be.

Norie
06-07-2006, 12:12 PM
Shazam

The Order Results sheet doesn't seem to tally.

Shazam
06-07-2006, 12:18 PM
Shazam

The Order Results sheet doesn't seem to tally.

The Order Results is an example how it should look after running a code. The code should be reading off of worksheet "Order" & "Download". Do you think this is possible?

Norie
06-07-2006, 12:22 PM
But the results don't seem to tally with the data and what you've described.

I tried this code but got different results from yours.

Sub test()
Dim wsOrders As Worksheet
Dim wsDownload As Worksheet
Dim rng As Range
Dim rngCount As Range
Dim NoProductsOrd As Long
Dim NoProductsDL As Long
Dim NoRows As Long
Dim LastRow As Long

Set wsOrders = Worksheets("Orders")
Set wsDownload = Worksheets("Download")

LastRow = wsDownload.Range("C" & Rows.Count).End(xlUp).Row

Set rngCount = wsDownload.Range("C2:C" & LastRow)
Set rng = wsOrders.Range("B6")

While rng.Value <> ""
NoProductsOrd = NoProductsOrd + 1
If rng.Value <> rng.Offset(1) Then
NoProductsDL = Application.WorksheetFunction.CountIf(rngCount, rng.Value)
NoRows = NoProductsDL - NoProductsOrd
If NoRows > 0 Then
rng.Offset(1).Resize(NoRows).EntireRow.Insert
Set rng = rng.Offset(NoRows)
End If
NoProductsOrd = 0

End If
Set rng = rng.Offset(1)
Wend
End Sub

Shazam
06-07-2006, 12:37 PM
My fault I thought I properly sort it but it looks like did not. Thanks norie it seems to work. I'll test several times and post back what I got. Here is the attachment below that is sorted.

Shazam
06-08-2006, 08:26 AM
I'm impress norie that code you provided is excellent.

Thanks!