CAE5942
asked on
Revise macro that expands data across multiple rows
Hi everyone,
I have a spreadsheet with rows of data. Each row represents a separate product and column G contains all the colours for the product in each row. Each of the colours is separated by the pipe symbol. I need a macro to look at column G in each row and then create copies of each row depending on how many colours there are for the product.
I have attached an excel file with sample data. Sheet 1 contains 2 rows (excluding the header row). The first row contains 4 colours in Column G and the second row contains 3 colours in Column G. If you look at Sheet 2, you can see that there are now 7 rows, ie. 4 for the first SKU (in sheet 1) and 3 for the second SKU (in sheet 1) and each of the colours from column G is now separated out onto its own row.
I have the following VBA script that works if the colours are in column B but because mine are in column G it doesn't seem to work:
I wondered if someone could help me revise the above script so that it works with my spreadsheet configuration, ie. where the colours are in column G?
Would really appreciate any help.
Thanks in advance.
test-data3.xlsx
I have a spreadsheet with rows of data. Each row represents a separate product and column G contains all the colours for the product in each row. Each of the colours is separated by the pipe symbol. I need a macro to look at column G in each row and then create copies of each row depending on how many colours there are for the product.
I have attached an excel file with sample data. Sheet 1 contains 2 rows (excluding the header row). The first row contains 4 colours in Column G and the second row contains 3 colours in Column G. If you look at Sheet 2, you can see that there are now 7 rows, ie. 4 for the first SKU (in sheet 1) and 3 for the second SKU (in sheet 1) and each of the colours from column G is now separated out onto its own row.
I have the following VBA script that works if the colours are in column B but because mine are in column G it doesn't seem to work:
Sub macro()
For Idx = Range("A" & Rows.Count).End(xlUp).Row To 2 Step -1
Set c = Cells(Idx, 2)
If InStr(1, c, "|") > 0 Then
Colors = Split(c, "|")
NrColors = UBound(Colors)
c.EntireRow.Copy
Rows(c.Row + 1 & ":" & c.Row + NrColors).Insert Shift:=xlDown
For Idx1 = 0 To NrColors
c.Offset(Idx1) = Colors(Idx1)
Next
End If
Next
End Sub
I wondered if someone could help me revise the above script so that it works with my spreadsheet configuration, ie. where the colours are in column G?
Would really appreciate any help.
Thanks in advance.
test-data3.xlsx
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Hi
Try this you just need to change the c to g and reference g at 7 instead of b at 2
Try this you just need to change the c to g and reference g at 7 instead of b at 2
Sub macro()
For Idx = Range("A" & Rows.Count).End(xlUp).Row To 2 Step -1
Set g = Cells(Idx, 7)
If InStr(1, g, "|") > 0 Then
Colors = Split(g, "|")
NrColors = UBound(Colors)
g.EntireRow.Copy
Rows(g.Row + 1 & ":" & g.Row + NrColors).Insert Shift:=xlDown
For Idx1 = 0 To NrColors
g.Offset(Idx1) = Colors(Idx1)
Next
End If
Next
End Sub
ASKER
Thanks so much for that. I made the change and it's all working well now which is great. Could I ask just one further related question: I need to paste in data from another excel spreadsheet into the spreadsheet with the macro. To do this, I need to create a new worksheet but when I do that the macro is not within that new worksheet, ie. I have to continually go into the editor and paste the code into the new worksheet in order to run the macro.
Is there a way to have the code automatically popular each new worksheet that I create?
Is there a way to have the code automatically popular each new worksheet that I create?
"Try this you just need to change the c to g and reference g at 7 instead of b at 2"
Sorry, but the name of the variable 'c' does not need to be changed at all.
Sorry, but the name of the variable 'c' does not need to be changed at all.
ASKER
I just changed this:
Set c = Cells(Idx, 2)
to this as you suggested:
Set c = Cells(Idx, 7)
Set c = Cells(Idx, 2)
to this as you suggested:
Set c = Cells(Idx, 7)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Hi
Sorry I acutally deleted my 1st comment, but somehow it stayed. The above one it now tested.
Sorry I acutally deleted my 1st comment, but somehow it stayed. The above one it now tested.
@gwh2:
---
I just changed this:
Set c = Cells(Idx, 2)
to this as you suggested:
Set c = Cells(Idx, 7)
---
Yes, that's fine.
My other responses (apart from the one replying to your subsequent question) were to Kanti Prasad.
---
I just changed this:
Set c = Cells(Idx, 2)
to this as you suggested:
Set c = Cells(Idx, 7)
---
Yes, that's fine.
My other responses (apart from the one replying to your subsequent question) were to Kanti Prasad.
ASKER
@Kanti Prasad - thank you for your input but the first response has solved my problem.
@[ fanpages ] - thanks again for the additional information.
@[ fanpages ] - thanks again for the additional information.
No problem at all.
Thanks for closing the question so promptly.
Thanks for closing the question so promptly.
Hi gwh2
no problem but just change that c to g as a matter of good practice as you don't refer column g with a c which also avoids confusion.
Set c = Cells(Idx, 2) to Set g = Cells(Idx, 2) and change all the c. to g.
no problem but just change that c to g as a matter of good practice as you don't refer column g with a c which also avoids confusion.
Set c = Cells(Idx, 2) to Set g = Cells(Idx, 2) and change all the c. to g.
Kanti Prasad - Expert Comment 2015-07-26 at 11:41:15 ID: 40899041
---
no problem but just change that c to g as a matter of good practice as you don't refer column g with a c which also avoids confusion.
Set c = Cells(Idx, 2) to Set g = Cells(Idx, 2) and change all the c. to g.
---
^ I presume the original author of the code used 'c' simply as an abbreviation of 'cell(s)'.
Are you suggesting the author should have used 'b' (as column [ B ] was the original column being interrogated)?
---
no problem but just change that c to g as a matter of good practice as you don't refer column g with a c which also avoids confusion.
Set c = Cells(Idx, 2) to Set g = Cells(Idx, 2) and change all the c. to g.
---
^ I presume the original author of the code used 'c' simply as an abbreviation of 'cell(s)'.
Are you suggesting the author should have used 'b' (as column [ B ] was the original column being interrogated)?
Hi
I suggested using g just to remove confusion and to show it picks values from column g as it is best to declare variables meaningfully which is a good practice!
Also as gwh2 mentioned it is changing the B pipes correctly so the author is aware that Set c = Cells(Idx, 2) means b values.
I suggested using g just to remove confusion and to show it picks values from column g as it is best to declare variables meaningfully which is a good practice!
Also as gwh2 mentioned it is changing the B pipes correctly so the author is aware that Set c = Cells(Idx, 2) means b values.
Kanti Prasad - Expert Comment 2015-07-26 at 12:00:09 ID: 40899046
---
I suggested using g just to remove confusion and to show it picks values from column g as it is best to declare variables meaningfully which is a good practice!
Also as gwh2 mentioned it is changing the B pipes correctly so the author is aware that Set c = Cells(Idx, 2) means b values.
---
With respect, that is nonsense.
Yes, naming variables to avoid confusion is good practice.
Your justification for misunderstanding the question is making your potential solution confusing.
---
I suggested using g just to remove confusion and to show it picks values from column g as it is best to declare variables meaningfully which is a good practice!
Also as gwh2 mentioned it is changing the B pipes correctly so the author is aware that Set c = Cells(Idx, 2) means b values.
---
With respect, that is nonsense.
Yes, naming variables to avoid confusion is good practice.
Your justification for misunderstanding the question is making your potential solution confusing.
Hi
Like wise with respect refer point 3
http://codebuild.blogspot.ie/2012/02/15-best-practices-of-variable-method.html
Like wise with respect refer point 3
http://codebuild.blogspot.ie/2012/02/15-best-practices-of-variable-method.html
Kanti Prasad - Expert Comment 2015-07-26 at 12:39:51 ID: 40899058
---
Like wise with respect refer point 3
http://codebuild.blogspot.ie/2012/02/15-best-practices-of-variable-method.html
---
(To avoid your comment being removed, I have transposed the content of the page below)
---
15 Best Practices of Variable & Method Naming
1. Use short enough and long enough variable names in each scope of code. Generally length may be 1 char for loop counters, 1 word for condition/loop variables, 1-2 words for methods, 2-3 words for classes, 3-4 words for globals.
2. Use specific names for variables, for example "value", "equals", "data", ... are not valid names for any case.
3. Use meaningful names for variables. Variable name must define the exact explanation of its content.
4. Don't start variables with o_, obj_, m_ etc. A variable does not need tags stating that it is a variable.
5. Obey company naming standards and write variable names consistently in application: e.g. txtUserName, lblUserName, cmbSchoolType, ... Otherwise readability will reduce and find/replace tools will be unusable.
6. Obey programming language standards and don't use lowercase/uppercase characters inconsistently: e.g. userName, UserName, USER_NAME, m_userName, username, ...
o For example for Java,
use Camel Case (aka Upper Camel Case) for classes: VelocityResponseWriter
use Lower Case for packages: com.company.project.ui
use Mixed Case (aka Lower Camel Case) for variables: studentName
use Upper Case for constants : MAX_PARAMETER_COUNT = 100
use Camel Case for enum class names and Upper Case for enum values.
don't use '_' anywhere except constants and enum values (which are constants).
7. Don't reuse same variable name in the same class in different contexts: e.g. in method, constructor, class. So you can provide more simplicity for understandability and maintainability.
8. Don't use same variable for different purposes in a method, conditional etc. Create a new and different named variable instead. This is also important for maintainability and readability.
9. Don't use non-ASCII chars in variable names. Those may run on your platform but may not on others.
10. Don't use too long variable names (e.g. 50 chars). Long names will bring ugly and hard-to-read code, also may not run on some compilers because of character limit.
11. Decide and use one natural language for naming, e.g. using mixed English and German names will be inconsistent and unreadable.
12. Use meaningful names for methods. The name must specify the exact action of the method and for most cases must start with a verb. (e.g. createPasswordHash)
13. Obey company naming standards and write method names consistently in application: e.g. getTxtUserName(), getLblUserName(), isStudentApproved(), ... Otherwise readability will reduce and find/replace tools will be unusable.
14. Obey programming language standards and don't use lowercase/uppercase characters inconsistently: e.g. getUserName, GetUserName, getusername, ...
o For example for Java,
use Mixed Case for method names: getStudentSchoolType
use Mixed Case for method parameters: setSchoolName(String schoolName)
15. Use meaningful names for method parameters, so it can documentate itself in case of no documentation.
---
3. Use meaningful names for variables. Variable name must define the exact explanation of its content.
Yes, use meaningful names.
No, they do not have to be an exact explanation of their usage/content.
If code interrogates four different columns, fourteen, or even forty columns, for example, there is no need to define multiple separate & distinct variables.
Just one; 'c' (or 'rngValue', or 'objCell', or something similar) is adequate.
---
Like wise with respect refer point 3
http://codebuild.blogspot.ie/2012/02/15-best-practices-of-variable-method.html
---
(To avoid your comment being removed, I have transposed the content of the page below)
---
15 Best Practices of Variable & Method Naming
1. Use short enough and long enough variable names in each scope of code. Generally length may be 1 char for loop counters, 1 word for condition/loop variables, 1-2 words for methods, 2-3 words for classes, 3-4 words for globals.
2. Use specific names for variables, for example "value", "equals", "data", ... are not valid names for any case.
3. Use meaningful names for variables. Variable name must define the exact explanation of its content.
4. Don't start variables with o_, obj_, m_ etc. A variable does not need tags stating that it is a variable.
5. Obey company naming standards and write variable names consistently in application: e.g. txtUserName, lblUserName, cmbSchoolType, ... Otherwise readability will reduce and find/replace tools will be unusable.
6. Obey programming language standards and don't use lowercase/uppercase characters inconsistently: e.g. userName, UserName, USER_NAME, m_userName, username, ...
o For example for Java,
use Camel Case (aka Upper Camel Case) for classes: VelocityResponseWriter
use Lower Case for packages: com.company.project.ui
use Mixed Case (aka Lower Camel Case) for variables: studentName
use Upper Case for constants : MAX_PARAMETER_COUNT = 100
use Camel Case for enum class names and Upper Case for enum values.
don't use '_' anywhere except constants and enum values (which are constants).
7. Don't reuse same variable name in the same class in different contexts: e.g. in method, constructor, class. So you can provide more simplicity for understandability and maintainability.
8. Don't use same variable for different purposes in a method, conditional etc. Create a new and different named variable instead. This is also important for maintainability and readability.
9. Don't use non-ASCII chars in variable names. Those may run on your platform but may not on others.
10. Don't use too long variable names (e.g. 50 chars). Long names will bring ugly and hard-to-read code, also may not run on some compilers because of character limit.
11. Decide and use one natural language for naming, e.g. using mixed English and German names will be inconsistent and unreadable.
12. Use meaningful names for methods. The name must specify the exact action of the method and for most cases must start with a verb. (e.g. createPasswordHash)
13. Obey company naming standards and write method names consistently in application: e.g. getTxtUserName(), getLblUserName(), isStudentApproved(), ... Otherwise readability will reduce and find/replace tools will be unusable.
14. Obey programming language standards and don't use lowercase/uppercase characters inconsistently: e.g. getUserName, GetUserName, getusername, ...
o For example for Java,
use Mixed Case for method names: getStudentSchoolType
use Mixed Case for method parameters: setSchoolName(String schoolName)
15. Use meaningful names for method parameters, so it can documentate itself in case of no documentation.
---
3. Use meaningful names for variables. Variable name must define the exact explanation of its content.
Yes, use meaningful names.
No, they do not have to be an exact explanation of their usage/content.
If code interrogates four different columns, fourteen, or even forty columns, for example, there is no need to define multiple separate & distinct variables.
Just one; 'c' (or 'rngValue', or 'objCell', or something similar) is adequate.
If you wish to discuss 'best practices', naming standards, & coding conventions I would suggest you begin a new thread.
Many already exist on this topic, in any respect.
Many already exist on this topic, in any respect.
Hi
Just to make myself clear I am not saying that using c is wrong . I suggested g as initially c was reading b range of values and then the changed code was reading g range values. Hence, a more meaning full name would g which will avoid confusion.
Just to make myself clear I am not saying that using c is wrong . I suggested g as initially c was reading b range of values and then the changed code was reading g range values. Hence, a more meaning full name would g which will avoid confusion.
---
Hi
Try this
Sub macro()
For Idx = Range("A" & Rows.Count).End(xlUp).Row To 2 Step -1
Set g = Cells(Idx, 2)
If InStr(1, g, "|") > 0 Then
Colors = Split(g, "|")
NrColors = UBound(Colors)
g.EntireRow.Copy
Rows(g.Row + 1 & ":" & g.Row + NrColors).Insert Shift:=xlDown
For Idx1 = 0 To NrColors
g.Offset(Idx1) = Colors(Idx1)
Next
End If
Next
End Sub
---
I think you may have misunderstood the requirements of the question, Kanti Prasad.
Simply renaming the variable 'c' to 'g' is going to produce the same result (that being an interrogation of column [ B ], not any action on column [G] as requested).