Re: $$Excel-Macros$$ Splitting the values from 1 cell to 2 cells
May I ask why you did not like my solution? Sub TTC_SAS() Application.ScreenUpdating = False Application.DisplayAlerts = False Columns(g:o).Delete Columns(A).TextToColumns Destination:=Range(G1), DataType:=xlFixedWidth, _ FieldInfo:=Array(Array(0, 1), Array(4, 1)), TrailingMinusNumbers:=True Columns(C).TextToColumns Destination:=Range(I1), DataType:=xlFixedWidth, _ FieldInfo:=Array(Array(0, 3), Array(9, 1)), TrailingMinusNumbers:=True Columns(D).TextToColumns Destination:=Range(J1), DataType:=xlDelimited, _ TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _ Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar:=- Columns(E).TextToColumns Destination:=Range(N1), DataType:=xlDelimited, _ TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _ Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar:=X Application.DisplayAlerts = True Columns(f).Copy Range(p1) Columns.AutoFit Application.ScreenUpdating = True End Sub Don Guillett SalesAid Software dguille...@gmail.com From: Darwin Chan Sent: Tuesday, January 31, 2012 2:17 AM To: excel-macros@googlegroups.com Subject: Re: $$Excel-Macros$$ Splitting the values from 1 cell to 2 cells Dear group, 1. Based on the ideas from Paul, changing code can help me to get desired results. If IsEmpty(.Cells(R, E)) Then .Cells(R, N).Value = .Cells(R, O).Value = ElseIf (InStr(.Cells(R, E).Value, X) 0) Then .Cells(R, N).Value = Left(.Cells(R, E).Value, (InStr(.Cells(R, E).Value, X) - 1)) .Cells(R, O).Value = Mid(.Cells(R, E).Value, (InStr(.Cells(R, E).Value, X) + 1), 3) Else .Cells(R, N).Value = .Cells(R, E).Value .Cells(R, O).Value = End If .Cells(R, P).Value = Cells(R, F).Value Next R 2. A However, as users have changed their mind, so I have to change the code for other parts. For step 2 in my code, the sztyhg (Column B in file) can determine other values in the file. Company code go to HHKHKGHKHKGRTT with third digit of sztyhg =G go to HHKHKGHKHKGRT1 with third digit of sztyhg =R Remark same data as sztyhg Man Rate O go to 40 with third digit of sztyhg =G go to 48 with third digit of sztyhg =R 2.B But some other changes happened when I use original method to get damage code and repair code, it does not get the desired results as I want, could anyone help to find out what is the problem? 2012/1/31 Rajan_Verma rajanverma1...@gmail.com You can use Split method and Assign split values to range Range(“A1:C1”)=split(“A:B:C”,”:”) Rajan. From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of dguillett1 Sent: Jan/Mon/2012 08:14 To: excel-macros@googlegroups.com Subject: Re: $$Excel-Macros$$ Splitting the values from 1 cell to 2 cells oops. change o1 to p1 Application.DisplayAlerts = True Columns(f).Copy Range(p1) Don Guillett SalesAid Software dguille...@gmail.com From: Darwin Chan Sent: Sunday, January 29, 2012 11:03 PM To: excel-macros@googlegroups.com Subject: $$Excel-Macros$$ Splitting the values from 1 cell to 2 cells Dear group, I would like to get the value from 1 cell and split the value to 2 cells. Any VBA formula can help achieve this? Code starts from Private Sub CommandButton2_Click() Criteria: Depends on the position of X. If no value in original cell, just leave it blank. The value before X is length, the value after X is width. Attached can find sample for reference. -- Darwin Chan darwin.chankaw...@gmail.com kw42c...@yahoo.com.hk -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad
RE: $$Excel-Macros$$ Splitting the values from 1 cell to 2 cells
Hi Do you want this? , see the attached =TRANSPOSE(MID(A1,ROW(INDIRECT(1: LEN(A1))),1)) as CSE Rajan. From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of dguillett1 Sent: Jan/Tue/2012 07:40 To: excel-macros@googlegroups.com Subject: Re: $$Excel-Macros$$ Splitting the values from 1 cell to 2 cells May I ask why you did not like my solution? Sub TTC_SAS() Application.ScreenUpdating = False Application.DisplayAlerts = False Columns(g:o).Delete Columns(A).TextToColumns Destination:=Range(G1), DataType:=xlFixedWidth, _ FieldInfo:=Array(Array(0, 1), Array(4, 1)), TrailingMinusNumbers:=True Columns(C).TextToColumns Destination:=Range(I1), DataType:=xlFixedWidth, _ FieldInfo:=Array(Array(0, 3), Array(9, 1)), TrailingMinusNumbers:=True Columns(D).TextToColumns Destination:=Range(J1), DataType:=xlDelimited, _ TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _ Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar:=- Columns(E).TextToColumns Destination:=Range(N1), DataType:=xlDelimited, _ TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _ Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar:=X Application.DisplayAlerts = True Columns(f).Copy Range(p1) Columns.AutoFit Application.ScreenUpdating = True End Sub Don Guillett SalesAid Software dguille...@gmail.com From: Darwin Chan mailto:darwin.chankaw...@gmail.com Sent: Tuesday, January 31, 2012 2:17 AM To: excel-macros@googlegroups.com Subject: Re: $$Excel-Macros$$ Splitting the values from 1 cell to 2 cells Dear group, 1. Based on the ideas from Paul, changing code can help me to get desired results. If IsEmpty(.Cells(R, E)) Then .Cells(R, N).Value = .Cells(R, O).Value = ElseIf (InStr(.Cells(R, E).Value, X) 0) Then .Cells(R, N).Value = Left(.Cells(R, E).Value, (InStr(.Cells(R, E).Value, X) - 1)) .Cells(R, O).Value = Mid(.Cells(R, E).Value, (InStr(.Cells(R, E).Value, X) + 1), 3) Else .Cells(R, N).Value = .Cells(R, E).Value .Cells(R, O).Value = End If .Cells(R, P).Value = Cells(R, F).Value Next R 2. A However, as users have changed their mind, so I have to change the code for other parts. For step 2 in my code, the sztyhg (Column B in file) can determine other values in the file. Company code go to HHKHKGHKHKGRTT with third digit of sztyhg =G go to HHKHKGHKHKGRT1 with third digit of sztyhg =R Remark same data as sztyhg Man Rate O go to 40 with third digit of sztyhg =G go to 48 with third digit of sztyhg =R 2.B But some other changes happened when I use original method to get damage code and repair code, it does not get the desired results as I want, could anyone help to find out what is the problem? 2012/1/31 Rajan_Verma rajanverma1...@gmail.com You can use Split method and Assign split values to range Range(A1:C1)=split(A:B:C,:) Rajan. From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of dguillett1 Sent: Jan/Mon/2012 08:14 To: excel-macros@googlegroups.com Subject: Re: $$Excel-Macros$$ Splitting the values from 1 cell to 2 cells oops. change o1 to p1 Application.DisplayAlerts = True Columns(f).Copy Range(p1) Don Guillett SalesAid Software dguille...@gmail.com From: Darwin Chan mailto:darwin.chankaw...@gmail.com Sent: Sunday, January 29, 2012 11:03 PM To: excel-macros@googlegroups.com Subject: $$Excel-Macros$$ Splitting the values from 1 cell to 2 cells Dear group, I would like to get the value from 1 cell and split the value to 2 cells. Any VBA formula can help achieve this? Code starts from Private Sub CommandButton2_Click() Criteria: Depends on the position of X. If no value in original cell, just leave it blank. The value before X is length, the value after X is width. Attached can find sample for reference. -- Darwin Chan darwin.chankaw...@gmail.com kw42c...@yahoo.com.hk -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread
RE: $$Excel-Macros$$ Splitting the values from 1 cell to 2 cells
Hi Darwin, Your ElseIf Not. lines are redundant, you can simply say Else for those lines, since that is what the ElseIf line already does. In the line .Cells(r, 14).Value = VBA.Left(.Range(E r), (InStr(.Range(E r), X) - 1)) If the cell at .Range(E r) does not contain an X, then Instr() will return 0, and then you subtract 1 from that, and ask for -1 characters using Left(). That is most likely the problem. I also highly recommend putting OPTION EXPLICIT at the top of every module and declaring all variables that you use with Dim, Static, etc. as appropriate. Otherwise it can make debugging much more difficult if you have a small typo in a variable name, as well as create other surprising problems caused by using variant type variables (the default) for everything. You switch between using Cells() and Range() for your references. In a case like this where both seem to be being used interchangably, I recommend sticking to one for ease of code comprehension, debugging, and code speed. I would use Cells in this case as there is less work for Excel to do to interpret it and it is probably a little faster. There is almost never any need to reference the VBA library name in your code. You can, for example, just use Left(string, length) -- no need for VBA.Left(.). All the best! Asa From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of Darwin Chan Sent: Monday, January 30, 2012 12:11 AM To: excel-macros@googlegroups.com Subject: Re: $$Excel-Macros$$ Splitting the values from 1 cell to 2 cells Dear all, I have composed some code for this, however, there is error happened, can anyone suggest to solve? Highlight in yellow has problem. Private Sub CommandButton2_Click() 'Inserting columns and fill with strings, I basically get the idea from our group With Sheets(CHS EDI) .Cells(1, 2).Value = E/F .Cells(1, 7).Value = Unit Prefix .Cells(1, 8).Value = Unit No .Cells(1, 9).Value = Estimate Date .Cells(1, 10).Value = Component Code .Cells(1, 11).Value = Location Code .Cells(1, 12).Value = Damage Code .Cells(1, 13).Value = Repair Code .Cells(1, 14).Value = Length .Cells(1, 15).Value = Width .Cells(1, 16).Value = Num For r = 2 To .Cells(Rows.Count, C).End(xlUp).Row .Cells(r, 7).Value = VBA.Left(.Range(A r), 4) .Cells(r, 8).Value = VBA.Mid(.Range(A r), 5, 7) .Cells(r, 9).Value = VBA.Format(.Range(C r), dd/mm/) .Cells(r, 10).Value = VBA.Left(.Range(D r), 3) .Cells(r, 11).Value = VBA.Mid(.Range(D r), 5, 4) .Cells(r, 12).Value = VBA.Mid(.Range(D r), 10, 2) .Cells(r, 13).Value = VBA.Mid(.Range(D r), 13, 2) If IsEmpty(.Cells(r, 5)) Then .Cells(r, 14).Value = ElseIf Not IsEmpty(.Cells(r, 5).Value) Then .Cells(r, 14).Value = VBA.Left(.Range(E r), (InStr(.Range(E r), X) - 1)) End If If IsEmpty(.Cells(r, 5).Value) Then .Cells(r, 15).Value = ElseIf Not IsEmpty(.Cells(r, 5).Value) Then .Cells(r, 15).Value = VBA.Mid(.Range(E r), (InStr(.Range(E r), X) + 1), 3) End If .Cells(r, 16).Value = Cells(r, 6) Next r .UsedRange.Columns.AutoFit End With End Sub 2012/1/30 Darwin Chan darwin.chankaw...@gmail.com Dear group, I would like to get the value from 1 cell and split the value to 2 cells. Any VBA formula can help achieve this? Code starts from Private Sub CommandButton2_Click() Criteria: Depends on the position of X. If no value in original cell, just leave it blank. The value before X is length, the value after X is width. Attached can find sample for reference. -- Darwin Chan darwin.chankaw...@gmail.com kw42c...@yahoo.com.hk -- Darwin Chan darwin.chankaw...@gmail.com kw42c...@yahoo.com.hk -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge
Re: $$Excel-Macros$$ Splitting the values from 1 cell to 2 cells
Hi Asa, First thank you very much for suggesting why there is an error. As a newbie in VBA programming, I really dont know much about OPTION EXPLICIT, only to the extent it requires us to define variables in every procedure. When i put OPTION EXPLICIT in original code, I find there is error showing internal procedure. After revising the ElseIf Not... I use the following code.however, the problem is only solved by On error resume Next only, any other way out? Private Sub CommandButton2_Click() 'Inserting columns and fill with strings, I basically get the idea from our group With Sheets(CHS EDI) .Cells(1, 2).Value = E/F .Cells(1, 7).Value = Unit Prefix .Cells(1, 8).Value = Unit No .Cells(1, 9).Value = Estimate Date .Cells(1, 10).Value = Component Code .Cells(1, 11).Value = Location Code .Cells(1, 12).Value = Damage Code .Cells(1, 13).Value = Repair Code .Cells(1, 14).Value = Length .Cells(1, 15).Value = Width .Cells(1, 16).Value = Num For r = 2 To .Cells(Rows.Count, C).End(xlUp).Row .Cells(r, 7).Value = VBA.Left(.Range(A r), 4) .Cells(r, 8).Value = VBA.Mid(.Range(A r), 5, 7) .Cells(r, 9).Value = VBA.Format(.Range(C r), dd/mm/) .Cells(r, 10).Value = VBA.Left(.Range(D r), 3) .Cells(r, 11).Value = VBA.Mid(.Range(D r), 5, 4) .Cells(r, 12).Value = VBA.Mid(.Range(D r), 10, 2) .Cells(r, 13).Value = VBA.Mid(.Range(D r), 13, 2) On Error Resume Next If IsEmpty(.Cells(r, 5)) Then .Cells(r, 14).Value = Else .Cells(r, 14).Value = VBA.Left(.Range(E r), (InStr(.Range(E r), X) - 1)) End If If IsEmpty(.Cells(r, 5).Value) Then .Cells(r, 15).Value = Else .Cells(r, 15).Value = VBA.Mid(.Range(E r), (InStr(.Range(E r), X) + 1), 3) End If .Cells(r, 16).Value = Cells(r, 6) Next r .UsedRange.Columns.AutoFit End With End Sub 2012/1/30 Asa Rossoff a...@lovetour.info Hi Darwin, Your ElseIf Not… lines are redundant, you can simply say Else for those lines, since that is what the ElseIf line already does. ** ** In the line .Cells(r, 14).Value = VBA.Left(.Range(E r), (*InStr(.Range(E r), X) - 1*)) If the cell at .Range(E r) does not contain an X, then Instr() will return 0, and then you subtract 1 from that, and ask for -1 characters using Left(). That is most likely the problem. ** ** I also highly recommend putting OPTION EXPLICIT at the top of every module and declaring all variables that you use with Dim, Static, etc. as appropriate. Otherwise it can make debugging much more difficult if you have a small typo in a variable name, as well as create other surprising problems caused by using variant type variables (the default) for everything. ** ** You switch between using Cells() and Range() for your references. In a case like this where both seem to be being used interchangably, I recommend sticking to one for ease of code comprehension, debugging, and code speed. I would use Cells in this case as there is less work for Excel to do to interpret it and it is probably a little faster. ** ** There is almost never any need to reference the VBA library name in your code. You can, for example, just use Left(string, length) -- no need for VBA.Left(…). ** ** All the best! Asa ** ** *From:* excel-macros@googlegroups.com [mailto: excel-macros@googlegroups.com] *On Behalf Of *Darwin Chan *Sent:* Monday, January 30, 2012 12:11 AM *To:* excel-macros@googlegroups.com *Subject:* Re: $$Excel-Macros$$ Splitting the values from 1 cell to 2 cells ** ** Dear all, ** ** I have composed some code for this, however, there is error happened, can anyone suggest to solve? Highlight in yellow has problem. ** ** Private Sub CommandButton2_Click() 'Inserting columns and fill with strings, I basically get the idea from our group With Sheets(CHS EDI) .Cells(1, 2).Value = E/F .Cells(1, 7).Value = Unit Prefix .Cells(1, 8).Value = Unit No .Cells(1, 9).Value = Estimate Date .Cells(1, 10).Value = Component Code .Cells(1, 11).Value = Location Code .Cells(1, 12).Value = Damage Code .Cells(1, 13).Value = Repair Code .Cells(1, 14).Value = Length .Cells(1, 15).Value = Width .Cells(1, 16).Value = Num ** ** For r = 2 To .Cells(Rows.Count, C).End(xlUp).Row .Cells(r, 7).Value = VBA.Left(.Range(A r), 4) .Cells(r, 8).Value = VBA.Mid(.Range(A r), 5, 7) .Cells(r, 9).Value = VBA.Format(.Range(C r), dd/mm/) .Cells(r, 10).Value = VBA.Left(.Range(D r), 3) .Cells(r, 11).Value = VBA.Mid(.Range(D r), 5, 4) .Cells(r, 12).Value = VBA.Mid(.Range(D r), 10, 2) .Cells(r, 13).Value = VBA.Mid(.Range(D r), 13, 2) ** ** *If IsEmpty(.Cells(r, 5)) Then* *.Cells(r, 14).Value = * *ElseIf Not IsEmpty(.Cells(r, 5).Value) Then* *.Cells(r, 14).Value = VBA.Left(.Range(E r), (InStr(.Range(E r), X) - 1))* *End If* ** ** *If IsEmpty(.Cells
Re: $$Excel-Macros$$ Splitting the values from 1 cell to 2 cells
oops. change o1 to p1 Application.DisplayAlerts = True Columns(f).Copy Range(p1) Don Guillett SalesAid Software dguille...@gmail.com From: Darwin Chan Sent: Sunday, January 29, 2012 11:03 PM To: excel-macros@googlegroups.com Subject: $$Excel-Macros$$ Splitting the values from 1 cell to 2 cells Dear group, I would like to get the value from 1 cell and split the value to 2 cells. Any VBA formula can help achieve this? Code starts from Private Sub CommandButton2_Click() Criteria: Depends on the position of X. If no value in original cell, just leave it blank. The value before X is length, the value after X is width. Attached can find sample for reference. -- Darwin Chan darwin.chankaw...@gmail.com kw42c...@yahoo.com.hk -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com
Re: $$Excel-Macros$$ Splitting the values from 1 cell to 2 cells
Hi Paul, Thanks a lot for your explanation first on how to use option explicit. Let me try the code when I m back to office!! 2012/1/30 Paul Schreiner schreiner_p...@att.net First of all, I'll explain Option Explicit. In ANY programming language, you have the ability to define variables to represent values. In this case, you're using r to increment the Row number. Now, in all programming, the variables have to be Declared. that is, you have to tell the language Compiler what type of variable it is. text string, integer number, floating point number (Boolean (true/false)) In VBA, the compiler allows the variable to be Declared the first time it is used. Sometimes, that's not a good thing. Let's say that the values in a list of cells is being copied to a variable. the values are supposed to be numbers which are then used in calculations. The first value is read, but since it's the column heading, it is text, so the variable is declared as a String. then, when the next value is read, it is stored in a String variable, which doesn't work well in calculations. Also.. let's say that the variable you used is for the row number is: rNumber. But in one instance, you accidently type it as: rNubmer The first time the code uses rNubmer it will automatically Declare is, and the code continues with unexpected results. So, the best course of action is to ALWAYS declare the variable, just so you can control the TYPE of variable. Using Option Explicit tells the compiler that ALL variables must be declared. So if it encounters a variable that has not been declared, it assumes that it is an error (which, if you simply failed to declare it is really more of an oversight). --- Next. The problem is with: If IsEmpty(.Cells(r, 5)) Then .Cells(r, 14).Value = Else .Cells(r, 14).Value = VBA.Left(.Range(E r), (InStr(.Range(E r), X) - 1)) End If In your sample file, the values in column E are NOT blank (empty) they have a space in them. So, the script assumes that if it is not blank, then it MUST have a X in the string. The error occurs when the Instr() function runs, finds no X, returns 0. then, the Left() command results in: Left ( ,-1) which causes an error. I'd suggest using: If (InStr(.Cells(R, E).Value, X) 0) Then .Cells(R, N).Value = Left(.Cells(R, E).Value, (InStr(.Cells(R, E).Value, X) - 1)) Else .Cells(R, N).Value = End If *Paul* - *“Do all the good you can, By all the means you can, In all the ways you can, In all the places you can, At all the times you can, To all the people you can, As long as ever you can.” - John Wesley *- -- *From:* Darwin Chan darwin.chankaw...@gmail.com *To:* excel-macros@googlegroups.com *Sent:* Mon, January 30, 2012 4:25:57 AM *Subject:* Re: $$Excel-Macros$$ Splitting the values from 1 cell to 2 cells Hi Asa, First thank you very much for suggesting why there is an error. As a newbie in VBA programming, I really dont know much about OPTION EXPLICIT, only to the extent it requires us to define variables in every procedure. When i put OPTION EXPLICIT in original code, I find there is error showing internal procedure. After revising the ElseIf Not... I use the following code.however, the problem is only solved by On error resume Next only, any other way out? Private Sub CommandButton2_Click() 'Inserting columns and fill with strings, I basically get the idea from our group With Sheets(CHS EDI) .Cells(1, 2).Value = E/F .Cells(1, 7).Value = Unit Prefix .Cells(1, 8).Value = Unit No .Cells(1, 9).Value = Estimate Date .Cells(1, 10).Value = Component Code .Cells(1, 11).Value = Location Code .Cells(1, 12).Value = Damage Code .Cells(1, 13).Value = Repair Code .Cells(1, 14).Value = Length .Cells(1, 15).Value = Width .Cells(1, 16).Value = Num For r = 2 To .Cells(Rows.Count, C).End(xlUp).Row .Cells(r, 7).Value = VBA.Left(.Range(A r), 4) .Cells(r, 8).Value = VBA.Mid(.Range(A r), 5, 7) .Cells(r, 9).Value = VBA.Format(.Range(C r), dd/mm/) .Cells(r, 10).Value = VBA.Left(.Range(D r), 3) .Cells(r, 11).Value = VBA.Mid(.Range(D r), 5, 4) .Cells(r, 12).Value = VBA.Mid(.Range(D r), 10, 2) .Cells(r, 13).Value = VBA.Mid(.Range(D r), 13, 2) On Error Resume Next If IsEmpty(.Cells(r, 5)) Then .Cells(r, 14).Value = Else .Cells(r, 14).Value = VBA.Left(.Range(E r), (InStr(.Range(E r), X) - 1)) End If If IsEmpty(.Cells(r, 5).Value) Then .Cells(r, 15).Value = Else .Cells(r, 15).Value = VBA.Mid(.Range(E r), (InStr(.Range(E r), X) + 1), 3) End If .Cells(r, 16).Value = Cells(r, 6) Next r .UsedRange.Columns.AutoFit End With End Sub 2012/1/30 Asa Rossoff a...@lovetour.info Hi
RE: $$Excel-Macros$$ Splitting the values from 1 cell to 2 cells
You can use Split method and Assign split values to range Range(A1:C1)=split(A:B:C,:) Rajan. From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of dguillett1 Sent: Jan/Mon/2012 08:14 To: excel-macros@googlegroups.com Subject: Re: $$Excel-Macros$$ Splitting the values from 1 cell to 2 cells oops. change o1 to p1 Application.DisplayAlerts = True Columns(f).Copy Range(p1) Don Guillett SalesAid Software dguille...@gmail.com From: Darwin Chan mailto:darwin.chankaw...@gmail.com Sent: Sunday, January 29, 2012 11:03 PM To: excel-macros@googlegroups.com Subject: $$Excel-Macros$$ Splitting the values from 1 cell to 2 cells Dear group, I would like to get the value from 1 cell and split the value to 2 cells. Any VBA formula can help achieve this? Code starts from Private Sub CommandButton2_Click() Criteria: Depends on the position of X. If no value in original cell, just leave it blank. The value before X is length, the value after X is width. Attached can find sample for reference. -- Darwin Chan darwin.chankaw...@gmail.com kw42c...@yahoo.com.hk -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com
