macro to copy text

marshalxxl

New Member
Joined
Nov 17, 2018
Messages
12
Hello at work we are using one program that basicly tells us what kind of variants to use for some specific objects for example :
ug8OcaT
ug8OcaT
https://imgur.com/a/ug8OcaT

The macro needs to copy from column : AO(V1) to CP(V54) [AO,AP,AQ,AR,AS....ETC] only the last three numbers and then only the last letter and for AO(v1) it will be # cuz it has no letters as you can see in the example:

VAR 246:#,F,N,P,Q

Also some other objects can have more rows of variants as you can see in this example:
https://imgur.com/a/tXZ5Ssp
And now for this object there will be :
Var 162:#,A,B,D,F,G
Var 181:#,A,C,D,F,G
VAR 325:#
VAR 327:A,B,D,E,G,H,N,P,Q,R
Var 920:#,E,F,H

Is there any way to copy that specific text ?
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
As your data syntax and business rules are very consistent, have you considered using Excel's powerful Advanced Filter (AF) functionality (on the Data / Sort & Filter ribbon menu) to quickly produce an extract of the rows and columns from the master database where the key field (the Object Name) matches a criterion (selected Object)?

AF will copy the required data (in columns AF & AO:CP) for the matching row/s (the target Object Name) to a separate area/sheet
You could then use Text related functions (Left, Mid, Right, Find, Search, Substitute, etc.) in formulas in a second (Final Output) table to "reduce" the original variant ID in the extract range to the string/s you want.

For the "Copy to another location" option, the Advanced Filter uses three ranges:

  1. List range (= the variant database, or just a contiguous range thereof holding the required fields)
  2. Crtieria range (= a single column in an out-of-the-way area with a heading ("kurzname"?) exactly matching the Object Name field heading (AF1) in the Variant database + a blank ow in which you enter the target Object Name, like "kb_1039")
  3. Copy to range (= a range in an out-of-the-way area with column headings ("kurzname", "V1", "V2", etc.) exactly matching those of the fields in the Variant database holding the variant IDs you seek (AF1, AO1:CP1)

These ranges would be best arranged as dynamic "Defined Names" / "Named Ranges" so re-specifying them is not require, and referencing them in any code is easy.

The only code you need (if any) is to run the AF rather than manually doing so.
 
Upvote 0
As your data syntax and business rules are very consistent, have you considered using Excel's powerful Advanced Filter (AF) functionality (on the Data / Sort & Filter ribbon menu) to quickly produce an extract of the rows and columns from the master database where the key field (the Object Name) matches a criterion (selected Object)?

AF will copy the required data (in columns AF & AO:CP) for the matching row/s (the target Object Name) to a separate area/sheet
You could then use Text related functions (Left, Mid, Right, Find, Search, Substitute, etc.) in formulas in a second (Final Output) table to "reduce" the original variant ID in the extract range to the string/s you want.

For the "Copy to another location" option, the Advanced Filter uses three ranges:

  1. List range (= the variant database, or just a contiguous range thereof holding the required fields)
  2. Crtieria range (= a single column in an out-of-the-way area with a heading ("kurzname"?) exactly matching the Object Name field heading (AF1) in the Variant database + a blank ow in which you enter the target Object Name, like "kb_1039")
  3. Copy to range (= a range in an out-of-the-way area with column headings ("kurzname", "V1", "V2", etc.) exactly matching those of the fields in the Variant database holding the variant IDs you seek (AF1, AO1:CP1)

These ranges would be best arranged as dynamic "Defined Names" / "Named Ranges" so re-specifying them is not require, and referencing them in any code is easy.

The only code you need (if any) is to run the AF rather than manually doing so.

I tried this but i can`t manage to make it to work....it is too complex for me
 
Upvote 0
There are a couple of tricks when the three AF ranges are not all on the same sheet, but other than that it's pretty straight forward (though I must admit I struggled when I first tried to use it many years ago).

I could try to build a proof of concept solution with some sample data from your linked files - but need a few days to do so as I've got a bit on over the weekend.
 
Upvote 0
A few more questions:

I noticed a pattern in some (but not all) of the alphabetic variant suffixes - they're in alphabetical order and in separate columns (i.e. AP = A, AQ = B, AR = C, etc.)
Q1. Is this pattern meant to be the same across all objects and all variants (noticing that KB_1002 and the last entry for KB_1039 doesn't follow this pattern)?

Q2. What fields are required and what is the format of the ultimate output - just a simple listing like for KB_1039 in your OP?

  • Var 162:#,A,B,D,F,G
  • Var 181:#,A,C,D,F,G
  • VAR 325:#
  • VAR 327:A,B,D,E,G,H,N,P,Q,R
  • Var 920:#,E,F,H

Q3. Will you be running this string extract for one object at a time or for multiples (if so how many?)

Q4. What's the maximum No. of variants for any one entry (row) for an object?
 
Upvote 0
Answer Q1:No the letter dosen`t correspond in alphapetical order whit columns as you can see whit object:kb_1039 at AR(V4) at 181 once you have C and at 920 you have E.
Q2:Yea that simple list is perfect i literally have to write that down for every specific variants and we have thousands.
Q3:For one object at a time the one that i search at AF(Kurzname)
Q4:It dosen`t matter the maximum letter it would be good if when one entire row of variants the next one to be below example:Var 162:#,A,B,D,F,G those are all variants so next row will be below Var 181:#,A,C,D,F,G
  • Var 162:#,A,B,D,F,G

  • Var 181:#,A,C,D,F,G
  • (in general we have maximum of 30 variants for one string for example for other object at Var 162:#,A,B,D,F,G,BA,BB,BC,AF,AG,AH ETC i never seen a object whit more
Thank you so much
 
Upvote 0

Forum statistics

Threads
1,213,552
Messages
6,114,278
Members
448,559
Latest member
MrPJ_Harper

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top