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/yyyy")
.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 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
