Copy parts of table based on cell value

Hi all,
I need help with a macro.

In sheet "List" I have a table with data.
In sheet "Pickup data" I have table with standard month range.

I need when the macro will run, a message box to appear asking for the month length that need to be entered.
Based on the entered value, macro will search the closest smaller value in column "E" in sheet "List", based on the standard month value in sheet "Pickup data".
All rows (cells B-H) for which this apply to be copied at the end of the existing data, and instead the existing value in column "E", the newly entered value to be copied.

e.g.: For the month period of 36 in column "E" (NOPR), I have the below value.

MODE CONF CHRS NOPR MAXRESVV MAXRESVP MINRESVP
AAA 2x 300 36 943 0.34 0.02
AAA 2x 500 36 8467 0.8 0.02
AAA 3x 300 36 2922 0.26 0.02
AAA 3x 500 36 3232 0.84 0.02
BBB 1x 300 36 2342 0.58 0.02
BBB 1x 500 36 10037 0.46 0.02
CCC 1x 300 36 2554 0.46 0.02
CCC 1x 500 36 5843 0.82 0.02
CCC 2x 300 36 6495 0.64 0.02
CCC 2x 500 36 7749 0.99 0.02


If the user in the message box insert value of 38, the data that will be copied at the end of the table need to be as shown below.
MODE CONF CHRS NOPR MAXRESVV MAXRESVP MINRESVP
AAA 2x 300 38 943 0.34 0.02
AAA 2x 500 38 8467 0.8 0.02
AAA 3x 300 38 2922 0.26 0.02
AAA 3x 500 38 3232 0.84 0.02
BBB 1x 300 38 2342 0.58 0.02
BBB 1x 500 38 10037 0.46 0.02
CCC 1x 300 38 2554 0.46 0.02
CCC 1x 500 38 5843 0.82 0.02
CCC 2x 300 38 6495 0.64 0.02
CCC 2x 500 38 7749 0.99 0.02



Also, optional, it will be great if more than one value can be entered per time.

Thank you in advance.

Igor


VBA Copy parts of array.xlsb

Hi igormigor,
I am sorry but i do not understand what you want.
Also the file you gave was difficult to open in my Excel.

Could you please supply two normal ( .xlsx or .xls ) Excel files . Call one File the “After”, and call the other the “Before”

( or alternatively supply clearer screenshots )

The “Before” should look exactly as your File does before the running of any macro
The “After” should be hand filled by you to show the situation after running of the macro, based on your input data in the “Before”
And try to explain again what it is you want.

Make sure there is just enough data to demonstrate your need. Remember to desensitize the data if necessary, but use the typical Format type and choose your data carefully so that it can be used to test all possible scenarios.
Alan
Germany

P.s . Notes for supplying the “Before” and “After” ( Most people use method _ .3) )
_ . 1) – post screenshots ( see notes for how to do that in my signature –Please do not post an image as we cannot copy that to a spreadsheet!)
_ . or
_ . 2) - Up left in the Thread editor is a Table icon. Click that, create an appropriately sized table and fill it in. (To get the table icon up in the Reply window you need to click on the “Go Advanced” Button next to the Reply Button)
_ . or
_ .3 ) – attach two files or one File with at least two sheets, - a “Before” and an “After” . You need to click on the “Go Advanced” Button next to the Reply Button to get the Paperclip Icon:
http://www.excelforum.com/members/da...ch-a-file.html )

_ . or , only as a very last resort.
_ .4 ). – Private Message me, and I will reply with my E-mail address and you can contact me and attach a File.( To Private Message me: ---You must be logged in. ---Click on my name Doc.AElstein above the Picture in the left margin. ---Select Private Message. –The rest should be obvious

Hi Alan,

Sorry for my confusive explanation.

This is how the file looks before VBA Copy parts of array - MacroWorkbook Before.xlsm
This is how the file should look after VBA Copy parts of array - MacroWorkbook After.xlsm

I need when the "Run macro" button will be clicked, a popup menu to appear that will allow the user to insert number of months (month period).
The standard months that exist are shown in the "Standard Months range" sheet, in file "VBA Copy parts of array - MacroWorkbook After".

The conditions that the macro will need to follow are the following:
a) If the entered month value is equal to a value that already exist in the list, message should appear that will notify the user that the given value already exist in the list, and no future action will be taken.
b) If a value which is not in the list is chosen, then the macro should search the first lower value in column "E" and all rows (columns B, C, D, F, G, H) which have this value to be copied at the end of the table.
c) In the column E to be inserted the newly chosen month period value.
d) In column A to be filled the serial number for the row.


In the file "VBA Copy parts of array - MacroWorkbook After", the new inserted rows are at the end, marked with yellow.
The data for the value where in column E we have 22 corresponds with the data where for column E we have 12.
The data for the value where in column E we have 41 corresponds with the data where for column E we have 36.
The data for the value where in column E we have 75 corresponds with the data where for column E we have 72.


I've wrote a code that fulfills the macro conditions b) and d).

Sub test()
Dim rcnt As Long
rcnt = Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To rcnt
If Range("E" & i).Value = "12" Then      '   change the number (22) which represents the month period

    Range("B" & i).Copy
    Range("B" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteAll

    Range("C" & i).Copy
    Range("C" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteAll
    
    Range("D" & i).Copy
    Range("D" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteAll
                        
    Range("E" & i).Value = 12      '   change the number (22) which represents the month period
                        
    Range("F" & i).Copy
    Range("F" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteAll

    Range("G" & i).Copy
    Range("G" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteAll
                        
    Range("H" & i).Copy
    Range("H" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteAll
                         
    End If
    Next i
Application.CutCopyMode = False

' Add numbers in column A
    For x = 1 To Range("a6").CurrentRegion.Rows.Count - 1
        Range("A" & x + 1) = x
    Next x

End Sub

But I need the macro to be able to fulfill the conditions a) and c).

When you click the "Run macro" button, the input form will appear in which the user will need to insert the month range value.

Hope this was clearer that the previous post.
Please let me know in case I need better to explain.
Thank you in advance.

Igor

Hi igormigor,

_ Thanks for the extra info. I have had a detailed look at the requirement. I think i can see fairly clearly what you want.

_ It is an interesting learning experience for me as i have no experience what so ever with User Forms!! . But that does not appear to be a problem as i can see , I think, how to modify the codes attempts you have already made to meet your full requirements.

_ I have made a start and think i can make an initial attempt.

_. I will post again when i have an initial full solution

Alan
Germany

Hi igormigor,

_ So i have an intial Code. It was actually simpler than I thought As your last instructions and the were very clear. And having all the UserForm stuff already up and running was good as i still have no idea how you set up all that stuff – looks very useful and “nice user friendly interface stuff! “

_ So basically all i have done is to modify the code i found by double clicking on your “UserForm1”, in particular the code:
Private Sub CommandButton1_Click()
As well as in addition adding some code lines just above that code.

_ Then everything seems to work nicely, ( But how, that is to say, what is going on in that Userform1 when you click stuff there or on the “Run Macro” Button is still a mystery to me!! )

_ I achieve exactly the results you gave as sample After, based on your instructions and your Before. It could still be that some subtlety means it does not do exactly what you want with more data or whatever, but it is a good start i think



_ General Notes:
_ 1) I assume your Standard Months range is in all your files ( Second Sheet, cells F1 to F11 ) as this was missing in your, Before , but it is very trivial to change the code to access it from wherever it may be in the future )
_ 2) The code i have done is much bigger and complicated than necessary. I have many explain ‘Green Comments as well as extra Declared variables not really needed. It is trivial to remove the comments and simplify by, for example substituting in the values given to many variable in the part of the code where the variables are used, subsequently removing the need for, and allowing the removal of, those declarations. I can easily do that for you if you wish, but i find personally in doing that yourself it can be very helpful in understanding where things are coming from, what they are, what they do, and exactly what is going on in the program

_....
So: I have the start sample data you gave

Using Excel 2007

Row\Col	A	B	C	D	E	F	G	H	I
1	#	MODE	CONF	CHRS	NOPR	MAXRESVV	MAXRESVP	MINRESVP
2	1	AAA	2x	300	12	4850	0.61	0.02
3	2	AAA	2x	300	24	2494	0.1	0.02
4	3	AAA	2x	300	36	943	0.34	0.02
5	4	AAA	2x	300	48	10575	0.2	0.02
6	5	AAA	2x	300	60	163	0.28	0.02
7	6	AAA	2x	300	72	454	0.17	0.02
8	7	AAA	2x	300	84	7526	0.95	0.02
9	8	AAA	2x	300	96	6584	0.72	0.02
10	9	AAA	2x	300	108	3971	0.99	0.02
11	10	AAA	2x	300	120	5851	0.97	0.02
12	11	AAA	2x	500	12	7745	0.56	0.02
13	12	AAA	2x	500	24	1398	0.42	0.02
14	13	AAA	2x	500	36	8467	0.8	0.02
15	14	AAA	2x	500	48	3629	0.22	0.02
16	15	AAA	2x	500	60	4882	0.81	0.02
17	16	AAA	2x	500	72	1294	0.01	0.02
18	17	AAA	2x	500	84	4717	0.96	0.02
19	18	AAA	2x	500	96	4361	0.3	0.02
20	19	AAA	2x	500	108	5930	0.57	0.02
21	20	AAA	2x	500	120	6455	0.79	0.02
22

After

_ I then follow your instructions as given in Post #3:

_(i) I click on the “Run Macro” Button in the File you Supplied . Then I see this
igormigorUserForm1.JPG

I then insert the numbers 22 into the UserForm1 , then hit OK.
My code springs then on and adds two new lines.
Then similarly I type in 41 , then after two more lines are added by the code, I type in 75.

The end result is that the data is modified thus:

Using Excel 2007

Row\Col	A	B	C	D	E	F	G	H	I
20	19	AAA	2x	500	108	5930	0.57	0.02
21	20	AAA	2x	500	120	6455	0.79	0.02
22	21	AAA	2x	300	22	4850	0.61	0.02
23	22	AAA	2x	500	22	7745	0.56	0.02
24	23	AAA	2x	300	41	943	0.34	0.02
25	24	AAA	2x	500	41	8467	0.8	0.02
26	25	AAA	2x	300	75	454	0.17	0.02
27	26	AAA	2x	500	75	1294	0.01	0.02
28

After

_..
I believe that is identical to your sample After File

Here Is the code:

http://www.excelforum.com/developmen...ml#post4251649

Let me know how you get on please and if you require more help.

Alan

Hi Igor,

Originally Posted by igormigor

.... I need the macro to be able to recognize the month values that are in the E column in the "After" sheet, instead of the Standard Months range ( file " VBA Copy parts of array - MacroWorkbook After", Second Sheet, cells F1 to F11). I've tried to make some changes, but non of them work. ....

I suspect the problem is not in Getting the correct Range that you want.. – You may have that right in the code mod you did depending on how / when you do the rcnt determination )
(.....To check that - When going through the code in F8 debug mode, highlight any occurrence of arrSM() In your modified code and hit
Shift + F9
_--- then select Insert.

And you will see that you get arrSm() shown in the Watch window

Then as the code passes the point where the Array is filled you will see that it is filled correctly
....)


But:-

I expect the problem is in my code line

    Let MonthValueNxtSmallest = arrSM(MntMtchNxt, 1) 'The nearest next Month match is obtained by the "position along" ( MntMtchNxt ) in the Grid ( Array in this case ) arrSM()

_ There are certain rules as to how the second Argument ( matrix ) should look like for the
VBA .Match Function
when using the
+1
Option for the last argument:
See Alan’s reply @ Alan here:
http://www.excelforum.com/excel-prog...ml#post4157064

To redo the code for you to work need to know something:

Exactly what range is to be used at the start of each code run. ?
Is it the current Column E ? at the outset of each running of my code. ( That is to say the last one updated by the last running of the code
Or
Is it the Column E at the first running of the code ( That is to say as it stands in the Before file you gave me

Alan


P.s. Email Notifications are not working for Replies currently, as often the case at ExcelForum!!
, so I PM’ed you about this reply – hope you get it
A good Tip at ExcelForum is to visit the site from time to time and hit the Settings, or User CP ( User Control Panel , UCP) Button
http://www.excelforum.com/usercp.php
to check out if you have replies. Or just check out the thread from time to time as the UCP doesn’t work either ......

P.P.s
Did you get an Email Notification of this reply and / or a Email Notification of my PM to you ?

Hi
I did get your PM...

So it sounds like from what you say that this from my post #7 is what you want

Originally Posted by Doc.AElstein

.....
Exactly what range is to be used at the start of each code run. ?
Is it the current Column E ? at the outset of each running of my code. ( That is to say the last one updated by the last running of the code
Or.........

_ - So effectively, the current E column ( or rather the sorted Unique values therefrom ) are the Stanfard Months for the next run of the code?
Correct?

Alan

Hi Igor,

So i assume for now my assumption in Post # 9 is correct.

I have rewritten the code appropriately:

Following on from what i said in post # 7, we need to get a unique set of Standard month values from column E at the start of each code run, and in addition this Unique set of values must be re-sorted into ascending order to meet the requirements of the VBA .Match Function with third argument set to 1

In the new code:

section 2) works on column E using a “By - product” of using a Microsoft Scripting Runtime Dictionary for storing those values from Column E. That “By-product” being that only unique values are allowed.

Section 3) uses a standard Bubble sort to arrange those unique values in the required ascending order.


I changed your test data ( the "before" data ) to check the functioning of the code thus:

Using Excel 2007

Row\Col	A	B	C	D	E	F	G	H
1	#	MODE	CONF	CHRS	NOPR	MAXRESVV	MAXRESVP	MINRESVP
2	1	AAA	2x	300	36	4850	0.61	0.02
3	2	AAA	2x	300	24	2494	0.1	0.02
4	3	AAA	2x	300	12	943	0.34	0.02
5	4	AAA	2x	300	48	10575	0.2	0.02
6	5	AAA	2x	300	120	163	0.28	0.02
7	6	AAA	2x	300	72	454	0.17	0.02
8	7	AAA	2x	300	84	7526	0.95	0.02
9	8	AAA	2x	300	96	6584	0.72	0.02
10	9	AAA	2x	300	108	3971	0.99	0.02
11	10	AAA	2x	300	60	5851	0.97	0.02
12	11	AAA	2x	500	36	7745	0.56	0.02
13	12	AAA	2x	500	24	1398	0.42	0.02
14	13	AAA	2x	500	12	8467	0.8	0.02
15	14	AAA	2x	500	48	3629	0.22	0.02
16	15	AAA	2x	500	120	4882	0.81	0.02
17	16	AAA	2x	500	84	1294	0.01	0.02
18	17	AAA	2x	500	72	4717	0.96	0.02
19	18	AAA	2x	500	96	4361	0.3	0.02
20	19	AAA	2x	500	108	5930	0.57	0.02
21	20	AAA	2x	500	60	6455	0.79	0.02

After

I then repeated the steps done in testing the first code. I achieved then identical results

Let me know how you get on with the new code please ( and while you’re at it, update me on if you got an Email notification of my replies , thanks)

Alan

New Code here ( .... Note this time it is the complete
Private Sub CommandButton1_Click() __ thing
_ .... there are no “global variables things” up above it – delete the previous ones i gave ...)

http://www.excelforum.com/developmen...ml#post4253730