423,682 Members | 1,622 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 423,682 IT Pros & Developers. It's quick & easy.

Excel vba problem help!!!

P: 12
Hello,

I have combobox list with product options:
Z1
Z2
Z3 etc.

and I want to to repeat the same macro (copy and paste range) every time when sb choose product from the list.
Attached you will find a .xls with the recorded macro I don't know how to relate the functioning of the macro from the choosing of the value from Combobox

I know that I should use Select Case but I don't know how.
Thank you for your help in advance.

Here is the recorded macro:
Expand|Select|Wrap|Line Numbers
  1. Sub PROD_Z()
  2. '
  3. ' PROD_Z Macro
  4. '
  5.  
  6. '
  7.     Columns("I:L").Select
  8.     Range("I2").Activate
  9.     Selection.Copy
  10.     Columns("N:N").Select
  11.     Range("N2").Activate
  12.     ActiveSheet.Paste
  13.     Range("Q1").Select
  14.     Application.CutCopyMode = False
  15.     ActiveCell.FormulaR1C1 = "Z2"
  16.     Range("Q3:Q18").Select
  17.     Selection.ClearContents
  18. End Sub
  19.  
Attached Files
File Type: xlsx CAT_Z.xlsx (31.6 KB, 69 views)
Jan 4 '18 #1
Share this Question
Share on Google+
7 Replies


GazMathias
Expert 100+
P: 189
Hi,

I don't quite understand what you are trying to do with your macro itself but if you are trying to pass the value from the combo box into the macro then:

Assuming you are using a combo box inserted from the Developer menu and assuming your values Z1,Z2 and Z3 are fed into the 'Input Range' property from a worksheet range then do the following:

Set aside two cells for later use, I shall refer to them as InputCell and ReturnCell.

1) Point your combo box's 'Cell Link' property at InputCell. This will put the index of the combo box into the cell when you change the selection.

2) In ReturnCell input the formula( =Index([the range for Z1,Z2, etc],InputCell). This will convert the number in InputCell into the real Z1, Z2, Z3 value.

3) Right Click the combobox and select Assign Macro.

4) Select your macro and click OK.

5) In your macro, read the value from ReturnCell:

Expand|Select|Wrap|Line Numbers
  1. Dim foo As String
  2. foo = Range([ReturnCell]).Value2
If you put ReturnCell on a different sheet then adjust accordingly.

Then (if I understand what you are trying to do)

Expand|Select|Wrap|Line Numbers
  1. ActiveCell.FormulaR1C1 = "Z2"
Becomes

Expand|Select|Wrap|Line Numbers
  1. ActiveCell.FormulaR1C1 = foo
Gaz

P.S Think up a better name for your variable than foo!
Jan 5 '18 #2

P: 12
Thank you for your reply.

I change the macro and now it looks like underneath:

If I click the button ADD PRODUCT it add products one time.
But I want to relate the functioning of the macro to the button.
Everytime when I click the button it will copy and paste range offset(0,5)
and add Z2,Z3,Z4... name in cell Q1(Z1), V1(Z2) etc.

Here is the code:
Expand|Select|Wrap|Line Numbers
  1. Sub CopyPaste()
  2. '
  3. ' CopyPaste Macro
  4. '
  5. Dim i As Long
  6. For i = 1 To 492
  7. 'Copies the specified selection to the Clipboard
  8. Range("I:L").Select
  9. Selection.Copy
  10. 'Paste the specified selection to the specified range
  11. Range("I1").Offset(0, 5 * i).Select
  12. ActiveSheet.Paste
  13. 'Unhidde the hidden columns
  14. Selection.EntireColumn.Hidden = False
  15. Next i
  16. 'Cancels Cut or Copy mode and removes the moving border
  17. Application.CutCopyMode = False
  18. End Sub
  19.  
How to change the code to use by CommandButton.

Everytime when I click the button Add copied range to offset(0,5)
to max 492 times.
Now when I click the button Excel copy and paste range one time (492 times)

Thanks in advance.
Attached Files
File Type: xlsx CAT_Z2.xlsx (44.8 KB, 63 views)
Jan 5 '18 #3

GazMathias
Expert 100+
P: 189
So from what I understand you want:

A) a command to replicate the product blocks out to the right for each of the Z1, Z2 entries and you want each blue cell to contain the Z entry for that position in the index.

And

B) a command to add one new block to the end when you click it?

Gaz
Jan 5 '18 #4

P: 12
Yes add new block and in blue cell add Z1 Z2 Z3 .. AHEAD until z492

everytime when I click the button copy the block and add next product name Z1
click
Z2
click
Z3
until
z492
Jan 5 '18 #5

P: 12
When I click the button Copy the range I:M one time to right offset (0,5) and input in cell Q1 "Z1"
and click the button
Copy the range I:M one time to right offset (0,5) and input in cell V1 "Z2"

loop to x492 times

My macro do it but one time all replciation (x492) not one every time when I click the button

I hope this makes sense.

I'm new in VBA so I have no idea how to change my code to what I want to achieve.
Jan 5 '18 #6

NeoPa
Expert Mod 15k+
P: 31,084
Another similar thread was posted as EXCEL VBA copy paste loop macro.

Anyone should feel free to PM me directly if they see that a member has posted what amounts to the same question more than once so I can ensure one is removed before it gets too messy.
Jan 8 '18 #7

P: 12
Please delete my threads as I found the solution.
Jan 8 '18 #8

Post your reply

Sign in to post your reply or Sign up for a free account.