Need modification in code. New Condition & New Logic.

V

VBABEGINER

Well-known Member
Joined
Jun 15, 2011
Messages
1,232
I need help on this code..Need some modification in it..and addition of some more condition...

I really work hard to understand, but really I'm not that much, to insert any new condition..

Code: 
Dim R As Long, X As Long, Cnt As Long, Data As Variant
Data = Range("B3", Cells(Rows.Count, "B").End(xlUp)).Resize(, 2)
For R = 1 To UBound(Data)
  Cnt = 0
  Data(R, 2) = ""
  For X = Len(Data(R, 1)) To 1 Step -1
    If Cnt < 4 Then
      If IsNumeric(Mid(Data(R, 1), X, 1)) Then
        Cnt = Cnt + 1
        Data(R, 2) = Mid(Data(R, 1), X, 1) & Data(R, 2)
      End If
    ElseIf Cnt = 4 Then
      Data(R, 2) = Format(Data(R, 2), "@@/01/@@")
      Data(R, 1) = Left(Data(R, 1), X)
      Cnt = 5
    ElseIf IsNumeric(Mid(Data(R, 1), X, 1)) Then
      Data(R, 1) = Left(Data(R, 1), X)
      Exit For
    End If
  Next
Next
Range("F3").Resize(UBound(Data)).NumberFormat = "mm/dd/yyyy"
Range("E3").Resize(UBound(Data), 2) = Data
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
Sort by date Sort by votes
add new conditions this way:
Case NewCondition 'add another condition here

note: the case will only execute 1 (the 1st that is true) and NONE of the others.


Code: 
Dim R As Long, X As Long, Cnt As Long, Data As Variant
Data = Range("B3", Cells(Rows.Count, "B").End(xlUp)).Resize(, 2)
For R = 1 To UBound(Data)
  Cnt = 0
  Data(R, 2) = ""
  For X = Len(Data(R, 1)) To 1 Step -1
        Select Case True
            Case Cnt < 4
                If IsNumeric(Mid(Data(R, 1), X, 1)) Then
                  Cnt = Cnt + 1
                  Data(R, 2) = Mid(Data(R, 1), X, 1) & Data(R, 2)
                End If
            Case Cnt = 4
                Data(R, 2) = Format(Data(R, 2), "@@/01/@@")
                Data(R, 1) = Left(Data(R, 1), X)
                Cnt = 5
            Case IsNumeric(Mid(Data(R, 1), X, 1))
                Data(R, 1) = Left(Data(R, 1), X)
                Exit For


            Case NewCondition                 'add another condition here
            
         End Select
  Next
Next
Range("F3").Resize(UBound(Data)).NumberFormat = "mm/dd/yyyy"
Range("E3").Resize(UBound(Data), 2) = Data
 
Last edited:
Upvote 0
Thanks for your reply. But I really don't understand the logic of the code also..I got this from MrExcel only, with proper explanation every time. but even I'm not that much, to write in this.

If possible for you, can i give my condition, so you can write a code for me..?

Thanks..

ranman256 said:
add new conditions this way:
Case NewCondition 'add another condition here

note: the case will only execute 1 (the 1st that is true) and NONE of the others.


Code: 
Dim R As Long, X As Long, Cnt As Long, Data As Variant
Data = Range("B3", Cells(Rows.Count, "B").End(xlUp)).Resize(, 2)
For R = 1 To UBound(Data)
  Cnt = 0
  Data(R, 2) = ""
  For X = Len(Data(R, 1)) To 1 Step -1
        Select Case True
            Case Cnt < 4
                If IsNumeric(Mid(Data(R, 1), X, 1)) Then
                  Cnt = Cnt + 1
                  Data(R, 2) = Mid(Data(R, 1), X, 1) & Data(R, 2)
                End If
            Case Cnt = 4
                Data(R, 2) = Format(Data(R, 2), "@@/01/@@")
                Data(R, 1) = Left(Data(R, 1), X)
                Cnt = 5
            Case IsNumeric(Mid(Data(R, 1), X, 1))
                Data(R, 1) = Left(Data(R, 1), X)
                Exit For


            Case NewCondition                 'add another condition here
            
         End Select
  Next
Next
Range("F3").Resize(UBound(Data)).NumberFormat = "mm/dd/yyyy"
Range("E3").Resize(UBound(Data), 2) = Data
Click to expand...
 
Upvote 0
ranman256 said:
yes, what is the condition? (you could have stated it)
Click to expand...

Story was, previously i have limited "Form number with Date". Now some new "Form numbers" are seen.

Hence, need to incorporate this all..
In first i.e. in Col B having "Form number with Date".
Output Columns are-
Col E - Form Numbers only
Col F - Date only

can we do something..
Form Number with DateForm NumberDate (M/D/YYYY)
EPP-E122-0116EPP-E1221/1/2016
10-02-2543 (10/16 ed.)10/2/254310/1/2016---here "ed." not required
14-02-1350 (1/95 ed.)14-02-13501/1/1995---here "ed." not required
AXIS 102 A/C (06 15)AXIS 102 A/C6/1/2015
EP 4104 (06 13)EP 41046/1/2013
EP 1051-CA (07 13)EP 1051-CA7/1/2013
CC-1k11H (03/14)CC-1k11H3/1/2014
XSC-27266 (05/09)XSC-272665/1/2009
EXL 0001 0615EXL 00016/1/2015
05 PCD0071 00 03 1005 PCD0071 003/1/2010
00 ML0012 00 09 0400 ML0012 009/1/2004
IL 00 17 01 98IL 00 171/1/1998
HC00051212HC000512/1/2012
HC70010605HC70016/1/2005
IL P 001 01/04IL P 0011/1/2004
RHIC 6000 08/11RHIC 60008/1/2011
ACF-7007 Ed. 08-11ACF-70078/1/2011---here "Ed." not required
DX T1 00 11 12DX T1 0011/1/2012
DX 00 04 11 07DX 00 0411/1/2007
17-02-5205 2-9817-02-52052/1/1998
17-02-5205 7-0317-02-52057/1/2003
11-02-1220 (09/03)11-02-12209/1/2003
NOTS0021CA (09/03)NOTS0021CA9/1/2003
UTS-COVPG 7-03UTS-COVPG7/1/2003--before 7 there is one extra space…very rare it is coming
IL 00 17 02/2013IL 00 172/1/2013---here "Ed." not required
abcde (06 15)abcde6/1/2015
EPP-POL-0410EPP-POL4/1/2010
ABCDEF 02 - 01/20ABCDEF 021/1/2020
IL 1202IL 1202 --no date required
17-02-52 - 01/5717-02-52 - 01/57 --no date required
95863.285714283795863.2857142837 --no date required
95863.28571 - 01/5795863.28571 - 01/57 --no date required

<colgroup><col span="3"><col></colgroup><tbody>
</tbody>
Click to expand...
 
Upvote 0
Rick Sir..Can you please assist on this..That will help me to solve the problem..

Problem is with this only..
Code: 
[TABLE="width: 968"]
<colgroup><col span="3"><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD="colspan: 2"]wrong output genearting[/TD]
[TD="colspan: 2"]What it should be..[/TD]
[/TR]
[TR]
[TD]Form Number with Date[/TD]
[TD]Form Number Only[/TD]
[TD]Date only[/TD]
[TD]Form number[/TD]
[TD]Date only[/TD]
[/TR]
[TR]
[TD]UTS-COVPG  7-03[/TD]
[TD]UTS-COVPG  7-03[/TD]
[TD]12/03/1901[/TD]
[TD]UTS-COVPG[/TD]
[TD]07/01/2003[/TD]
[/TR]
[TR]
[TD]AXIS 102 A/C (06 15)[/TD]
[TD]AXIS 102[/TD]
[TD="align: right"]06/01/2015[/TD]
[TD]Form number should be "AXIS 102 A/C"[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]abcde (06 15)[/TD]
[TD]abcde ([/TD]
[TD="align: right"]06/01/2015[/TD]
[TD]abcde[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]EPP-POL-0410[/TD]
[TD]EPP-POL-[/TD]
[TD="align: right"]04/01/2010[/TD]
[TD]EPP-POL[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]IL 1202[/TD]
[TD]IL [/TD]
[TD="align: right"]12/01/2002[/TD]
[TD]IL 1202[/TD]
[TD]No date[/TD]
[/TR]
</tbody>[/TABLE]


VBABEGINER said:
I need help on this code..Need some modification in it..and addition of some more condition...

I really work hard to understand, but really I'm not that much, to insert any new condition..

Code: 
Dim R As Long, X As Long, Cnt As Long, Data As Variant
Data = Range("B3", Cells(Rows.Count, "B").End(xlUp)).Resize(, 2)
For R = 1 To UBound(Data)
  Cnt = 0
  Data(R, 2) = ""
  For X = Len(Data(R, 1)) To 1 Step -1
    If Cnt < 4 Then
      If IsNumeric(Mid(Data(R, 1), X, 1)) Then
        Cnt = Cnt + 1
        Data(R, 2) = Mid(Data(R, 1), X, 1) & Data(R, 2)
      End If
    ElseIf Cnt = 4 Then
      Data(R, 2) = Format(Data(R, 2), "@@/01/@@")
      Data(R, 1) = Left(Data(R, 1), X)
      Cnt = 5
    ElseIf IsNumeric(Mid(Data(R, 1), X, 1)) Then
      Data(R, 1) = Left(Data(R, 1), X)
      Exit For
    End If
  Next
Next
Range("F3").Resize(UBound(Data)).NumberFormat = "mm/dd/yyyy"
Range("E3").Resize(UBound(Data), 2) = Data
Click to expand...
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Solved
Update to existing Move data macro
2
Replies
11
Views
370
Alex Blakenburg
A
T
  • Solved
Remove numbers from dynamic range
Replies
5
Views
285
Georgiboy
Georgiboy
zookeepertx
  • Question
Having trouble "deciphering" some code so I can see how it works
Replies
7
Views
98
zookeepertx
zookeepertx
T
  • Solved
Multiple columns into 2?
Replies
6
Views
122
Georgiboy
Georgiboy
Keebler
  • Solved
clean code
5 6 7
Replies
66
Views
1K
Keebler
Keebler

Forum statistics

Threads
1,215,491
Messages
6,125,109
Members
449,205
Latest member
ralemanygarcia

Share this page

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