Need modification in code. New Condition & New Logic.

V

VBABEGINER

Well-known Member
Joined
Jun 15, 2011
Messages
1,284
Office Version
  1. 365
Platform
  1. Windows
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

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
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..
[TABLE="width: 720"]
<colgroup><col span="3"><col></colgroup><tbody>[TR]
[TD]Form Number with Date[/TD]
[TD]Form Number[/TD]
[TD]Date (M/D/YYYY)[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]EPP-E122-0116[/TD]
[TD]EPP-E122[/TD]
[TD="align: right"]1/1/2016[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]10-02-2543 (10/16 ed.)[/TD]
[TD]10/2/2543[/TD]
[TD="align: right"]10/1/2016[/TD]
[TD]---here "ed." not required[/TD]
[/TR]
[TR]
[TD]14-02-1350 (1/95 ed.)[/TD]
[TD]14-02-1350[/TD]
[TD="align: right"]1/1/1995[/TD]
[TD]---here "ed." not required[/TD]
[/TR]
[TR]
[TD]AXIS 102 A/C (06 15)[/TD]
[TD]AXIS 102 A/C[/TD]
[TD="align: right"]6/1/2015[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]EP 4104 (06 13)[/TD]
[TD]EP 4104[/TD]
[TD="align: right"]6/1/2013[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]EP 1051-CA (07 13)[/TD]
[TD]EP 1051-CA[/TD]
[TD="align: right"]7/1/2013[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]CC-1k11H (03/14)[/TD]
[TD]CC-1k11H[/TD]
[TD="align: right"]3/1/2014[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]XSC-27266 (05/09)[/TD]
[TD]XSC-27266[/TD]
[TD="align: right"]5/1/2009[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]EXL 0001 0615[/TD]
[TD]EXL 0001[/TD]
[TD="align: right"]6/1/2015[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]05 PCD0071 00 03 10[/TD]
[TD]05 PCD0071 00[/TD]
[TD="align: right"]3/1/2010[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]00 ML0012 00 09 04[/TD]
[TD]00 ML0012 00[/TD]
[TD="align: right"]9/1/2004[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]IL 00 17 01 98[/TD]
[TD]IL 00 17[/TD]
[TD="align: right"]1/1/1998[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]HC00051212[/TD]
[TD]HC0005[/TD]
[TD="align: right"]12/1/2012[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]HC70010605[/TD]
[TD]HC7001[/TD]
[TD="align: right"]6/1/2005[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]IL P 001 01/04[/TD]
[TD]IL P 001[/TD]
[TD="align: right"]1/1/2004[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]RHIC 6000 08/11[/TD]
[TD]RHIC 6000[/TD]
[TD="align: right"]8/1/2011[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]ACF-7007 Ed. 08-11[/TD]
[TD]ACF-7007[/TD]
[TD="align: right"]8/1/2011[/TD]
[TD]---here "Ed." not required[/TD]
[/TR]
[TR]
[TD]DX T1 00 11 12[/TD]
[TD]DX T1 00[/TD]
[TD="align: right"]11/1/2012[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]DX 00 04 11 07[/TD]
[TD]DX 00 04[/TD]
[TD="align: right"]11/1/2007[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]17-02-5205 2-98[/TD]
[TD]17-02-5205[/TD]
[TD="align: right"]2/1/1998[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]17-02-5205 7-03[/TD]
[TD]17-02-5205[/TD]
[TD="align: right"]7/1/2003[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]11-02-1220 (09/03)[/TD]
[TD]11-02-1220[/TD]
[TD="align: right"]9/1/2003[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]NOTS0021CA (09/03)[/TD]
[TD]NOTS0021CA[/TD]
[TD="align: right"]9/1/2003[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]UTS-COVPG 7-03[/TD]
[TD]UTS-COVPG[/TD]
[TD="align: right"]7/1/2003[/TD]
[TD]--before 7 there is one extra space…very rare it is coming[/TD]
[/TR]
[TR]
[TD]IL 00 17 02/2013[/TD]
[TD]IL 00 17[/TD]
[TD="align: right"]2/1/2013[/TD]
[TD]---here "Ed." not required[/TD]
[/TR]
[TR]
[TD]abcde (06 15)[/TD]
[TD]abcde[/TD]
[TD="align: right"]6/1/2015[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]EPP-POL-0410[/TD]
[TD]EPP-POL[/TD]
[TD="align: right"]4/1/2010[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]ABCDEF 02 - 01/20[/TD]
[TD]ABCDEF 02[/TD]
[TD="align: right"]1/1/2020[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]IL 1202[/TD]
[TD]IL 1202[/TD]
[TD] [/TD]
[TD]--no date required[/TD]
[/TR]
[TR]
[TD]17-02-52 - 01/57[/TD]
[TD]17-02-52 - 01/57[/TD]
[TD] [/TD]
[TD]--no date required[/TD]
[/TR]
[TR]
[TD]95863.2857142837[/TD]
[TD]95863.2857142837[/TD]
[TD] [/TD]
[TD]--no date required[/TD]
[/TR]
[TR]
[TD]95863.28571 - 01/57[/TD]
[TD]95863.28571 - 01/57[/TD]
[TD] [/TD]
[TD]--no date required[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...
 
Upvote 0
Hello, any possibility in this...pls revert...thanks in advance..
 
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

A
  • Question Question
Looking to improve procedure
Replies
4
Views
113
AlexanderBB
A
S
  • Question Question
VBA help
Replies
1
Views
209
Micron
M
K
  • Solved
Update to existing Move data macro
2
Replies
11
Views
746
Alex Blakenburg
A
A
  • Solved
VBA to list all links (external links AND hyperlinks) together with the cells containing the links
Replies
3
Views
2K
pbornemeier
pbornemeier
R
  • Question Question
VBA to add to data model when creating Power Query tables`
Replies
3
Views
644
Raddle
R

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

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