Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How change cell to another character, add comma, & continue next c
It is unclear whether where you want this output at. Since you posted to the
programming group, one could assume you are looking for VBA code; but, if you are, where do you want the outcome placed... in an array, in another cell, somewhere else? Personally, I think you are looking for a worksheet formula (microsoft.public.excel.worksheet.functions would have been a better newsgroup to use if that is the case); but again, where do you want the output... in a new column or to replace the contents of C:J for each row? Anyway, assuming you want a formula, and using a new column (same row as the data), this formula can be used as a starting point... =A2&" "&TEXT(B2,"mm/dd/yyyy")&" "&SUBSTITUTE(TRIM(IF(C2="T","0","")& " "&IF(D2="T",1,"")&" "&IF(E2="T",2,"")&" "&IF(F2="T",3,"")&" "&IF( G2="T",4,"")&" "&IF(H2="T",5,"")&" "&IF(I2="T",6,"")&" "&IF(J2="T", 7,""))," ",",") Copy it down as needed. -- Rick (MVP - Excel) "ILoveMyCorgi" wrote in message ... "ILoveMyCorgi" wrote: I have an Excel spreadsheet that has as a header row: #, Date, Per0, Per1, Per2, Per3, Per4, Per5, Per6, Per7. The rows below have the number, date, then for Per0 through Per7 may have a T in the cell. Sometimes the cell may have another character other than a T. What I need to do is take the T and change it to, let's say a 3 [for Per3] then add a comma and continue to the next cell [if there is another T] and add, hypothetically a 5 for Per5... and so on until the end of the rows with a number and date. The outcome shoudl look like: 12345 09/25/2008 3,5 If anyone can help me, I'd appreciate it. Hope you have a great day and a good weekend.... ILoveMyCorgi |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How change cell to another character, add comma, & continue ne
Thansk for the formula, but I was hoping for VBA code and have the output on
a separate worksheet within the file. Output would be first column: number, second: date, third column: 2, 3, 5... etc. "Rick Rothstein" wrote: It is unclear whether where you want this output at. Since you posted to the programming group, one could assume you are looking for VBA code; but, if you are, where do you want the outcome placed... in an array, in another cell, somewhere else? Personally, I think you are looking for a worksheet formula (microsoft.public.excel.worksheet.functions would have been a better newsgroup to use if that is the case); but again, where do you want the output... in a new column or to replace the contents of C:J for each row? Anyway, assuming you want a formula, and using a new column (same row as the data), this formula can be used as a starting point... =A2&" "&TEXT(B2,"mm/dd/yyyy")&" "&SUBSTITUTE(TRIM(IF(C2="T","0","")& " "&IF(D2="T",1,"")&" "&IF(E2="T",2,"")&" "&IF(F2="T",3,"")&" "&IF( G2="T",4,"")&" "&IF(H2="T",5,"")&" "&IF(I2="T",6,"")&" "&IF(J2="T", 7,""))," ",",") Copy it down as needed. -- Rick (MVP - Excel) "ILoveMyCorgi" wrote in message ... "ILoveMyCorgi" wrote: I have an Excel spreadsheet that has as a header row: #, Date, Per0, Per1, Per2, Per3, Per4, Per5, Per6, Per7. The rows below have the number, date, then for Per0 through Per7 may have a T in the cell. Sometimes the cell may have another character other than a T. What I need to do is take the T and change it to, let's say a 3 [for Per3] then add a comma and continue to the next cell [if there is another T] and add, hypothetically a 5 for Per5... and so on until the end of the rows with a number and date. The outcome shoudl look like: 12345 09/25/2008 3,5 If anyone can help me, I'd appreciate it. Hope you have a great day and a good weekend.... ILoveMyCorgi |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How change cell to another character, add comma, & continue ne
Thanks. This will work, but I was looking for a VBA solution so that when I
extract the data, the clerk can run the extract. "Rick Rothstein" wrote: It is unclear whether where you want this output at. Since you posted to the programming group, one could assume you are looking for VBA code; but, if you are, where do you want the outcome placed... in an array, in another cell, somewhere else? Personally, I think you are looking for a worksheet formula (microsoft.public.excel.worksheet.functions would have been a better newsgroup to use if that is the case); but again, where do you want the output... in a new column or to replace the contents of C:J for each row? Anyway, assuming you want a formula, and using a new column (same row as the data), this formula can be used as a starting point... =A2&" "&TEXT(B2,"mm/dd/yyyy")&" "&SUBSTITUTE(TRIM(IF(C2="T","0","")& " "&IF(D2="T",1,"")&" "&IF(E2="T",2,"")&" "&IF(F2="T",3,"")&" "&IF( G2="T",4,"")&" "&IF(H2="T",5,"")&" "&IF(I2="T",6,"")&" "&IF(J2="T", 7,""))," ",",") Copy it down as needed. -- Rick (MVP - Excel) "ILoveMyCorgi" wrote in message ... "ILoveMyCorgi" wrote: I have an Excel spreadsheet that has as a header row: #, Date, Per0, Per1, Per2, Per3, Per4, Per5, Per6, Per7. The rows below have the number, date, then for Per0 through Per7 may have a T in the cell. Sometimes the cell may have another character other than a T. What I need to do is take the T and change it to, let's say a 3 [for Per3] then add a comma and continue to the next cell [if there is another T] and add, hypothetically a 5 for Per5... and so on until the end of the rows with a number and date. The outcome shoudl look like: 12345 09/25/2008 3,5 If anyone can help me, I'd appreciate it. Hope you have a great day and a good weekend.... ILoveMyCorgi |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How change cell to another character, add comma, & continue ne
How about this macro then (change the two worksheet references in the two
With Worksheets(...) statements to the worksheets where your data is and where you output should go... Sub ProcessPerColumns() Dim X As Long Dim Z As Long Dim LenOutput As Long Dim LastRow As Long Dim LastOutputRow As Long Dim Output As String With Worksheets("DataSheet") LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row For X = 2 To LastRow Output = .Cells(X, "A") & " " & .Cells(X, "B") & " " LenOutput = Len(Output) For Z = 3 To 10 'Columns C thru J If .Cells(X, Z).Value = "T" Then If Len(Output) LenOutput Then Output = Output & ", " Output = Output & .Cells(X, Z).Value End If Next With Worksheets("OutputSheet") LastOutputRow = .Cells(.Rows.Count, "A").End(xlUp).Row .Cells(LastOutputRow + 1, "A").Value = Output End With Next End With End Sub -- Rick (MVP - Excel) "ILoveMyCorgi" wrote in message ... Thanks. This will work, but I was looking for a VBA solution so that when I extract the data, the clerk can run the extract. "Rick Rothstein" wrote: It is unclear whether where you want this output at. Since you posted to the programming group, one could assume you are looking for VBA code; but, if you are, where do you want the outcome placed... in an array, in another cell, somewhere else? Personally, I think you are looking for a worksheet formula (microsoft.public.excel.worksheet.functions would have been a better newsgroup to use if that is the case); but again, where do you want the output... in a new column or to replace the contents of C:J for each row? Anyway, assuming you want a formula, and using a new column (same row as the data), this formula can be used as a starting point... =A2&" "&TEXT(B2,"mm/dd/yyyy")&" "&SUBSTITUTE(TRIM(IF(C2="T","0","")& " "&IF(D2="T",1,"")&" "&IF(E2="T",2,"")&" "&IF(F2="T",3,"")&" "&IF( G2="T",4,"")&" "&IF(H2="T",5,"")&" "&IF(I2="T",6,"")&" "&IF(J2="T", 7,""))," ",",") Copy it down as needed. -- Rick (MVP - Excel) "ILoveMyCorgi" wrote in message ... "ILoveMyCorgi" wrote: I have an Excel spreadsheet that has as a header row: #, Date, Per0, Per1, Per2, Per3, Per4, Per5, Per6, Per7. The rows below have the number, date, then for Per0 through Per7 may have a T in the cell. Sometimes the cell may have another character other than a T. What I need to do is take the T and change it to, let's say a 3 [for Per3] then add a comma and continue to the next cell [if there is another T] and add, hypothetically a 5 for Per5... and so on until the end of the rows with a number and date. The outcome shoudl look like: 12345 09/25/2008 3,5 If anyone can help me, I'd appreciate it. Hope you have a great day and a good weekend.... ILoveMyCorgi |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How change cell to another character, add comma, & continue ne
Thanks. I copied the code and tried to run but I got a run-time error '9':
Subscript out of range. "Rick Rothstein" wrote: How about this macro then (change the two worksheet references in the two With Worksheets(...) statements to the worksheets where your data is and where you output should go... Sub ProcessPerColumns() Dim X As Long Dim Z As Long Dim LenOutput As Long Dim LastRow As Long Dim LastOutputRow As Long Dim Output As String With Worksheets("DataSheet") LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row For X = 2 To LastRow Output = .Cells(X, "A") & " " & .Cells(X, "B") & " " LenOutput = Len(Output) For Z = 3 To 10 'Columns C thru J If .Cells(X, Z).Value = "T" Then If Len(Output) LenOutput Then Output = Output & ", " Output = Output & .Cells(X, Z).Value End If Next With Worksheets("OutputSheet") LastOutputRow = .Cells(.Rows.Count, "A").End(xlUp).Row .Cells(LastOutputRow + 1, "A").Value = Output End With Next End With End Sub -- Rick (MVP - Excel) "ILoveMyCorgi" wrote in message ... Thanks. This will work, but I was looking for a VBA solution so that when I extract the data, the clerk can run the extract. "Rick Rothstein" wrote: It is unclear whether where you want this output at. Since you posted to the programming group, one could assume you are looking for VBA code; but, if you are, where do you want the outcome placed... in an array, in another cell, somewhere else? Personally, I think you are looking for a worksheet formula (microsoft.public.excel.worksheet.functions would have been a better newsgroup to use if that is the case); but again, where do you want the output... in a new column or to replace the contents of C:J for each row? Anyway, assuming you want a formula, and using a new column (same row as the data), this formula can be used as a starting point... =A2&" "&TEXT(B2,"mm/dd/yyyy")&" "&SUBSTITUTE(TRIM(IF(C2="T","0","")& " "&IF(D2="T",1,"")&" "&IF(E2="T",2,"")&" "&IF(F2="T",3,"")&" "&IF( G2="T",4,"")&" "&IF(H2="T",5,"")&" "&IF(I2="T",6,"")&" "&IF(J2="T", 7,""))," ",",") Copy it down as needed. -- Rick (MVP - Excel) "ILoveMyCorgi" wrote in message ... "ILoveMyCorgi" wrote: I have an Excel spreadsheet that has as a header row: #, Date, Per0, Per1, Per2, Per3, Per4, Per5, Per6, Per7. The rows below have the number, date, then for Per0 through Per7 may have a T in the cell. Sometimes the cell may have another character other than a T. What I need to do is take the T and change it to, let's say a 3 [for Per3] then add a comma and continue to the next cell [if there is another T] and add, hypothetically a 5 for Per5... and so on until the end of the rows with a number and date. The outcome shoudl look like: 12345 09/25/2008 3,5 If anyone can help me, I'd appreciate it. Hope you have a great day and a good weekend.... ILoveMyCorgi |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
How change cell to another character, add comma, & continue ne
Hi Rick and OP
I'm trying to understand how to convert the questions into the proper logic, so I'm examining your code, Thanks for sharing your knowledge, Rick. I copied the code and it ran after I changed the names of the worksheets to DataSheet and OutputSheet. Also, It didn't return any values but the number and date for each row until I changed the line If .Cells(X, Z).Value = "T" Then to If .Cells(X, Z).Value = "t" Then because I had used lowercase t. I made two more changes because the code was returning data like 45879 10/10/2007 t 45880 10/11/2007 t 45881 10/12/2007 t,t First, I changed Output = Output & .Cells(X, Z).Value to Output = Output & Z But the numbers didn't match Per0, Per1, etc so i added Output = Output & (Z - 3) That's my experience with this code, Dan |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
How change cell to another character, add comma, & continue ne
That might mean you data is located in different columns than I assumed. I
figured you used columns A thru J for you data and that data started on Row 2. Did you? -- Rick (MVP - Excel) "ILoveMyCorgi" wrote in message ... Thanks. I copied the code and tried to run but I got a run-time error '9': Subscript out of range. "Rick Rothstein" wrote: How about this macro then (change the two worksheet references in the two With Worksheets(...) statements to the worksheets where your data is and where you output should go... Sub ProcessPerColumns() Dim X As Long Dim Z As Long Dim LenOutput As Long Dim LastRow As Long Dim LastOutputRow As Long Dim Output As String With Worksheets("DataSheet") LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row For X = 2 To LastRow Output = .Cells(X, "A") & " " & .Cells(X, "B") & " " LenOutput = Len(Output) For Z = 3 To 10 'Columns C thru J If .Cells(X, Z).Value = "T" Then If Len(Output) LenOutput Then Output = Output & ", " Output = Output & .Cells(X, Z).Value End If Next With Worksheets("OutputSheet") LastOutputRow = .Cells(.Rows.Count, "A").End(xlUp).Row .Cells(LastOutputRow + 1, "A").Value = Output End With Next End With End Sub -- Rick (MVP - Excel) "ILoveMyCorgi" wrote in message ... Thanks. This will work, but I was looking for a VBA solution so that when I extract the data, the clerk can run the extract. "Rick Rothstein" wrote: It is unclear whether where you want this output at. Since you posted to the programming group, one could assume you are looking for VBA code; but, if you are, where do you want the outcome placed... in an array, in another cell, somewhere else? Personally, I think you are looking for a worksheet formula (microsoft.public.excel.worksheet.functions would have been a better newsgroup to use if that is the case); but again, where do you want the output... in a new column or to replace the contents of C:J for each row? Anyway, assuming you want a formula, and using a new column (same row as the data), this formula can be used as a starting point... =A2&" "&TEXT(B2,"mm/dd/yyyy")&" "&SUBSTITUTE(TRIM(IF(C2="T","0","")& " "&IF(D2="T",1,"")&" "&IF(E2="T",2,"")&" "&IF(F2="T",3,"")&" "&IF( G2="T",4,"")&" "&IF(H2="T",5,"")&" "&IF(I2="T",6,"")&" "&IF(J2="T", 7,""))," ",",") Copy it down as needed. -- Rick (MVP - Excel) "ILoveMyCorgi" wrote in message ... "ILoveMyCorgi" wrote: I have an Excel spreadsheet that has as a header row: #, Date, Per0, Per1, Per2, Per3, Per4, Per5, Per6, Per7. The rows below have the number, date, then for Per0 through Per7 may have a T in the cell. Sometimes the cell may have another character other than a T. What I need to do is take the T and change it to, let's say a 3 [for Per3] then add a comma and continue to the next cell [if there is another T] and add, hypothetically a 5 for Per5... and so on until the end of the rows with a number and date. The outcome shoudl look like: 12345 09/25/2008 3,5 If anyone can help me, I'd appreciate it. Hope you have a great day and a good weekend.... ILoveMyCorgi |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
How change cell to another character, add comma, & continue ne
That's right,
But I think the OP wanted something like if there is a T in Per0, the data would look like ####, MM/DD/YY 0 Does that make any sense? Dan |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
How change cell to another character, add comma, & continue ne
First of all, I take your posting to mean you did not get the error message
that the OP got (in other words, my code ran "error" free on your system). Okay, yes, I did forgot to change my test code so that the "T" values would output their numerical equivalents when creating the output for the output sheet (thanks for spotting that problem in my code). The modification you made to my code there was the correct method to get the numbers rather than the letters "T". As for the "T" versus "t" test... I used "T" because the is what the OP said was being used. However, to account for either an upper or lower case "T" (rather than only one or the other), I would use this test line instead... If .Cells(X, Z).Value Like "[Tt]" Then -- Rick (MVP - Excel) "dan dungan" wrote in message ... Hi Rick and OP I'm trying to understand how to convert the questions into the proper logic, so I'm examining your code, Thanks for sharing your knowledge, Rick. I copied the code and it ran after I changed the names of the worksheets to DataSheet and OutputSheet. Also, It didn't return any values but the number and date for each row until I changed the line If .Cells(X, Z).Value = "T" Then to If .Cells(X, Z).Value = "t" Then because I had used lowercase t. I made two more changes because the code was returning data like 45879 10/10/2007 t 45880 10/11/2007 t 45881 10/12/2007 t,t First, I changed Output = Output & .Cells(X, Z).Value to Output = Output & Z But the numbers didn't match Per0, Per1, etc so i added Output = Output & (Z - 3) That's my experience with this code, Dan |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
How change cell to another character, add comma, & continue ne
Hi Rick
First of all, I take your posting to mean you did not get the error message that the OP got (in other words, my code ran "error" free on your system). I got the same error: 9 Subscript out of range until I used the same worksheet names in your code. Now it runs error free. Thanks for your time and input. Dan |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
How change cell to another character, add comma, & continue ne
Ah, I see. Actually, the intent was for the you, and the OP, to change the
example worksheet names I used in my code to whatever actual worksheet name you are using in your own workbook. In other words, where my code had.this... With Worksheets("DataSheet") the you, and the OP, were supposed to change "DataSheet" to whatever name your own worksheet has. So, for example, if your data was located on "MyDataSheet3", then the above code line was to be changed to this... With Worksheets("MyDataSheet3") and similar change should be made where I used this... With Worksheets("OutputSheet") in my code also (using the actual name you gave to your output worksheet in place of my example worksheet name of "OutputSheet"). The key thing to note is the code was supposed to be changed to match your actual conditions, *not* the other way around. If the OP had posted the worksheet names for us in the original posting, I would have used them in my code; since the OP didn't, I simply made up example names instead. -- Rick (MVP - Excel) "dan dungan" wrote in message ... Hi Rick First of all, I take your posting to mean you did not get the error message that the OP got (in other words, my code ran "error" free on your system). I got the same error: 9 Subscript out of range until I used the same worksheet names in your code. Now it runs error free. Thanks for your time and input. Dan |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
How change cell to another character, add comma, & continue ne
Aside from the columns being located in different places, Dan has reminded
me in one of his postings that if you forgot to change the worksheet names from the example names of "DataSheet" and "OutputSheet" that I used (I had to use something as you didn't tell us what your actual worksheet names were), to what your actual worksheet names are, you would also get the same error message that you posted. -- Rick (MVP - Excel) "ILoveMyCorgi" wrote in message ... Thanks. I copied the code and tried to run but I got a run-time error '9': Subscript out of range. "Rick Rothstein" wrote: How about this macro then (change the two worksheet references in the two With Worksheets(...) statements to the worksheets where your data is and where you output should go... Sub ProcessPerColumns() Dim X As Long Dim Z As Long Dim LenOutput As Long Dim LastRow As Long Dim LastOutputRow As Long Dim Output As String With Worksheets("DataSheet") LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row For X = 2 To LastRow Output = .Cells(X, "A") & " " & .Cells(X, "B") & " " LenOutput = Len(Output) For Z = 3 To 10 'Columns C thru J If .Cells(X, Z).Value = "T" Then If Len(Output) LenOutput Then Output = Output & ", " Output = Output & .Cells(X, Z).Value End If Next With Worksheets("OutputSheet") LastOutputRow = .Cells(.Rows.Count, "A").End(xlUp).Row .Cells(LastOutputRow + 1, "A").Value = Output End With Next End With End Sub -- Rick (MVP - Excel) "ILoveMyCorgi" wrote in message ... Thanks. This will work, but I was looking for a VBA solution so that when I extract the data, the clerk can run the extract. "Rick Rothstein" wrote: It is unclear whether where you want this output at. Since you posted to the programming group, one could assume you are looking for VBA code; but, if you are, where do you want the outcome placed... in an array, in another cell, somewhere else? Personally, I think you are looking for a worksheet formula (microsoft.public.excel.worksheet.functions would have been a better newsgroup to use if that is the case); but again, where do you want the output... in a new column or to replace the contents of C:J for each row? Anyway, assuming you want a formula, and using a new column (same row as the data), this formula can be used as a starting point... =A2&" "&TEXT(B2,"mm/dd/yyyy")&" "&SUBSTITUTE(TRIM(IF(C2="T","0","")& " "&IF(D2="T",1,"")&" "&IF(E2="T",2,"")&" "&IF(F2="T",3,"")&" "&IF( G2="T",4,"")&" "&IF(H2="T",5,"")&" "&IF(I2="T",6,"")&" "&IF(J2="T", 7,""))," ",",") Copy it down as needed. -- Rick (MVP - Excel) "ILoveMyCorgi" wrote in message ... "ILoveMyCorgi" wrote: I have an Excel spreadsheet that has as a header row: #, Date, Per0, Per1, Per2, Per3, Per4, Per5, Per6, Per7. The rows below have the number, date, then for Per0 through Per7 may have a T in the cell. Sometimes the cell may have another character other than a T. What I need to do is take the T and change it to, let's say a 3 [for Per3] then add a comma and continue to the next cell [if there is another T] and add, hypothetically a 5 for Per5... and so on until the end of the rows with a number and date. The outcome shoudl look like: 12345 09/25/2008 3,5 If anyone can help me, I'd appreciate it. Hope you have a great day and a good weekend.... ILoveMyCorgi |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
How change cell to another character, add comma, & continue ne
Ok. I liked your names, though.
And I didn't want to mess it up before I understood it better. Dan |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
How change cell to another character, add comma, & continue ne
Thanks! I changed the code renaming the worksheets and the code ran!
Unfortunately, instead of having the result in one cell, I wanted to have the output in three separate columns; i.e., cola 1234 colb 08/25/2008 COLC 1,2,3. I also wanted the "1,3,5" if columns Per1, Per3, and Per5 had a T in it... I hope you can help me with this. Thanks, everyone! "Rick Rothstein" wrote: Ah, I see. Actually, the intent was for the you, and the OP, to change the example worksheet names I used in my code to whatever actual worksheet name you are using in your own workbook. In other words, where my code had.this... With Worksheets("DataSheet") the you, and the OP, were supposed to change "DataSheet" to whatever name your own worksheet has. So, for example, if your data was located on "MyDataSheet3", then the above code line was to be changed to this... With Worksheets("MyDataSheet3") and similar change should be made where I used this... With Worksheets("OutputSheet") in my code also (using the actual name you gave to your output worksheet in place of my example worksheet name of "OutputSheet"). The key thing to note is the code was supposed to be changed to match your actual conditions, *not* the other way around. If the OP had posted the worksheet names for us in the original posting, I would have used them in my code; since the OP didn't, I simply made up example names instead. -- Rick (MVP - Excel) "dan dungan" wrote in message ... Hi Rick First of all, I take your posting to mean you did not get the error message that the OP got (in other words, my code ran "error" free on your system). I got the same error: 9 Subscript out of range until I used the same worksheet names in your code. Now it runs error free. Thanks for your time and input. Dan |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
How change cell to another character, add comma, & continue ne
I dd change the code but the output I wanted was more on the lines of the
formula: =A2&" "&TEXT(B2,"mm/dd/yyyy")&" "&SUBSTITUTE(TRIM(IF(C2="T","0","")& " "&IF(D2="T",1,"")&" "&IF(E2="T",2,"")&" "&IF(F2="T",3,"")&" "&IF( G2="T",4,"")&" "&IF(H2="T",5,"")&" "&IF(I2="T",6,"")&" "&IF(J2="T", 7,""))," ",",") . I was hoping for a quick and easy RUN once I extract my data. "Rick Rothstein" wrote: Ah, I see. Actually, the intent was for the you, and the OP, to change the example worksheet names I used in my code to whatever actual worksheet name you are using in your own workbook. In other words, where my code had.this... With Worksheets("DataSheet") the you, and the OP, were supposed to change "DataSheet" to whatever name your own worksheet has. So, for example, if your data was located on "MyDataSheet3", then the above code line was to be changed to this... With Worksheets("MyDataSheet3") and similar change should be made where I used this... With Worksheets("OutputSheet") in my code also (using the actual name you gave to your output worksheet in place of my example worksheet name of "OutputSheet"). The key thing to note is the code was supposed to be changed to match your actual conditions, *not* the other way around. If the OP had posted the worksheet names for us in the original posting, I would have used them in my code; since the OP didn't, I simply made up example names instead. -- Rick (MVP - Excel) "dan dungan" wrote in message ... Hi Rick First of all, I take your posting to mean you did not get the error message that the OP got (in other words, my code ran "error" free on your system). I got the same error: 9 Subscript out of range until I used the same worksheet names in your code. Now it runs error free. Thanks for your time and input. Dan |
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
How change cell to another character, add comma, & continue ne
Okay, try this code then (do not forget to change the worksheet names)...
Sub ProcessPerColumns() Dim X As Long Dim Z As Long Dim LastRow As Long Dim LastOutputRow As Long Dim Output1 As String Dim Output2 As String Dim Output3 As String Dim NumberDate As Range With Worksheets("DataSheet") LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row For X = 2 To LastRow Set NumberDate = .Range("A" & LastRow & ":B" & LastRow) Output1 = .Cells(X, "A").Value Output2 = .Cells(X, "B").Value Output3 = "" For Z = 3 To 10 'Columns C thru J If .Cells(X, Z).Value Like "[Tt]" Then If Len(Output3) 0 Then Output3 = Output3 & ", " Output3 = Output3 & (Z - 3) End If Next With Worksheets("OutputSheet") LastOutputRow = .Cells(.Rows.Count, "A").End(xlUp).Row If LastOutputRow = 1 And .Range("A1").Value = "" Then LastOutputRow = 0 End If .Cells(LastOutputRow + 1, "A").Value = Output1 .Cells(LastOutputRow + 1, "B").Value = Output2 .Cells(LastOutputRow + 1, "C").Value = "'" & Output3 End With Next End With End Sub -- Rick (MVP - Excel) "ILoveMyCorgi" wrote in message ... Thanks! I changed the code renaming the worksheets and the code ran! Unfortunately, instead of having the result in one cell, I wanted to have the output in three separate columns; i.e., cola 1234 colb 08/25/2008 COLC 1,2,3. I also wanted the "1,3,5" if columns Per1, Per3, and Per5 had a T in it... I hope you can help me with this. Thanks, everyone! "Rick Rothstein" wrote: Ah, I see. Actually, the intent was for the you, and the OP, to change the example worksheet names I used in my code to whatever actual worksheet name you are using in your own workbook. In other words, where my code had.this... With Worksheets("DataSheet") the you, and the OP, were supposed to change "DataSheet" to whatever name your own worksheet has. So, for example, if your data was located on "MyDataSheet3", then the above code line was to be changed to this... With Worksheets("MyDataSheet3") and similar change should be made where I used this... With Worksheets("OutputSheet") in my code also (using the actual name you gave to your output worksheet in place of my example worksheet name of "OutputSheet"). The key thing to note is the code was supposed to be changed to match your actual conditions, *not* the other way around. If the OP had posted the worksheet names for us in the original posting, I would have used them in my code; since the OP didn't, I simply made up example names instead. -- Rick (MVP - Excel) "dan dungan" wrote in message ... Hi Rick First of all, I take your posting to mean you did not get the error message that the OP got (in other words, my code ran "error" free on your system). I got the same error: 9 Subscript out of range until I used the same worksheet names in your code. Now it runs error free. Thanks for your time and input. Dan |
#17
Posted to microsoft.public.excel.programming
|
|||
|
|||
How change cell to another character, add comma, & continue ne
PERFECT!! Thanks a million for helping me out with this... I really
appreciate it. Have a great weekend. Susan "Rick Rothstein" wrote: Okay, try this code then (do not forget to change the worksheet names)... Sub ProcessPerColumns() Dim X As Long Dim Z As Long Dim LastRow As Long Dim LastOutputRow As Long Dim Output1 As String Dim Output2 As String Dim Output3 As String Dim NumberDate As Range With Worksheets("DataSheet") LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row For X = 2 To LastRow Set NumberDate = .Range("A" & LastRow & ":B" & LastRow) Output1 = .Cells(X, "A").Value Output2 = .Cells(X, "B").Value Output3 = "" For Z = 3 To 10 'Columns C thru J If .Cells(X, Z).Value Like "[Tt]" Then If Len(Output3) 0 Then Output3 = Output3 & ", " Output3 = Output3 & (Z - 3) End If Next With Worksheets("OutputSheet") LastOutputRow = .Cells(.Rows.Count, "A").End(xlUp).Row If LastOutputRow = 1 And .Range("A1").Value = "" Then LastOutputRow = 0 End If .Cells(LastOutputRow + 1, "A").Value = Output1 .Cells(LastOutputRow + 1, "B").Value = Output2 .Cells(LastOutputRow + 1, "C").Value = "'" & Output3 End With Next End With End Sub -- Rick (MVP - Excel) "ILoveMyCorgi" wrote in message ... Thanks! I changed the code renaming the worksheets and the code ran! Unfortunately, instead of having the result in one cell, I wanted to have the output in three separate columns; i.e., cola 1234 colb 08/25/2008 COLC 1,2,3. I also wanted the "1,3,5" if columns Per1, Per3, and Per5 had a T in it... I hope you can help me with this. Thanks, everyone! "Rick Rothstein" wrote: Ah, I see. Actually, the intent was for the you, and the OP, to change the example worksheet names I used in my code to whatever actual worksheet name you are using in your own workbook. In other words, where my code had.this... With Worksheets("DataSheet") the you, and the OP, were supposed to change "DataSheet" to whatever name your own worksheet has. So, for example, if your data was located on "MyDataSheet3", then the above code line was to be changed to this... With Worksheets("MyDataSheet3") and similar change should be made where I used this... With Worksheets("OutputSheet") in my code also (using the actual name you gave to your output worksheet in place of my example worksheet name of "OutputSheet"). The key thing to note is the code was supposed to be changed to match your actual conditions, *not* the other way around. If the OP had posted the worksheet names for us in the original posting, I would have used them in my code; since the OP didn't, I simply made up example names instead. -- Rick (MVP - Excel) "dan dungan" wrote in message ... Hi Rick First of all, I take your posting to mean you did not get the error message that the OP got (in other words, my code ran "error" free on your system). I got the same error: 9 Subscript out of range until I used the same worksheet names in your code. Now it runs error free. Thanks for your time and input. Dan |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How change cell to another character, add comma, & continue nextc | Excel Programming | |||
How can I change the color of the cell when someone enter any number or character? | Charts and Charting in Excel | |||
Change first character in a cell | Excel Discussion (Misc queries) | |||
255 Character limit / Comma Delimited Import Problem | Excel Discussion (Misc queries) | |||
Change Event.....Cell character count | Excel Programming |