问题
大数据集在特定字段中有许多不当或不均匀输入日期。大约有19种不同的记录中,大约有19种不同的方式。有些条目是完全无效的,必须丢弃,其他条目必须正确格式化。我将范围加载到数组中,然后执行操作。我认为我需要写出操作,但是我需要一些帮助才能使它们实际工作。请参阅下面的代码;评论中的错误。
当前问题
对于" 54天"之类的条目,以下代码只能将字符带到空间的左侧,然后检查它们是否为数字。如果是,它会保留它们,如果不是它们,那就倒空数组元素。实际上,什么都没发生,我仍然在数组元素中有完整的条目?
ElseIf delType = "String" And Len(del(i, 1)) < 10 And InStrRev(del(i, 1), " ") Then 'Takes the number from entries like 2194 Days or 23 DPD
del(i, 1) = Left(del(i, 1), Len(del(i, 1)) - InStrRev(del(i, 1), " "))
If IsNumeric(del(i, 1)) = False Then 'If the characters to the left of the space are not numbers, discard
del(i, 1) = Empty
Else
del(i, 1) = Format((CLng(Range("E" & i + 1).Value) - Abs(del(i, 1))), "mm/dd/yy") 'Pull order date and subtract days from it for delinquency date
End If
输入模板
9月。25、20 (没有年,没有一年!删除。)
9月(没有一年,无用,删除。)
n/a (垃圾!删除。)
长时间Ag (Moron认为这是一个好主意,删除。)
6月30日,200 (显然,该字段只能容纳12个字符,删除。)
收费(无用,删除。)
94天(取之前的所有字符并从包含订单日期的其他字段中减去以获取拖欠日期。)
94 DPD (某人的dpd在我相信的几天中持续了几天。与上面相同。)
2008-7-15 12 (不确定是什么其他数字,在空间和转换之前将所有字符取。)
无效(删除。)
空白(无所事事。)
12282009 (使用左右嵌套,并与中/in in之间。)
9202011 (添加领先零,然后与上述相同。)
92410 (添加领先零,这将转换为09/24/10)
41261 (自1899年3月31日以来,这将转换为12/08/12)
1023 (自从犯罪以来,从订单日期减去以获取拖欠日期。)
452 (与上述相同。)
12 (与上述相同。)
1432.84 (货币价值,由低IQ Lackey误输入。删除。)
代码(正在进行的工作)
'Perform housekeeping on delinquency date
Columns("AH:AH").Select
Selection.NumberFormat = "0"
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
True, Transpose:=False
Dim del()
ReDim del(1 To importwsRowCount, 1 To 1)
del = Range("AH1:AH" & importwsRowCount).Value
Dim delChars As Long
Dim delType As String
For i = LBound(del, 1) To UBound(del, 1)
delChars = Len(del(i, 1)) 'Determine length of entry
If IsNumeric(del(i, 1)) = True Then 'Determine datatype of entry
delType = "Numeric"
Else
delType = "String"
End If
If InStr(del(i, 1), ".") Then 'Removes monetary entries like 142.84
del(i, 1) = Empty
ElseIf InStr(del(i, 1), "*") Then 'Removes ***INVALID*** entries
del(i, 1) = Empty
ElseIf delChars = 12 Then 'Removes all entries that extend beyond the 12 character limit of the field and get cut off
del(i, 1) = Empty
ElseIf delType = "String" And Len(del(i, 1)) < 10 And InStrRev(del(i, 1), " ") Then 'Takes the number from entries like 2194 Days or 23 DPD
del(i, 1) = Left(del(i, 1), Len(del(i, 1)) - InStrRev(del(i, 1), " "))
If IsNumeric(del(i, 1)) = False Then 'If the characters to the left of the space are not numbers, discard
del(i, 1) = Empty
Else
del(i, 1) = Format((CLng(Range("E" & i + 1).Value) - Abs(del(i, 1))), "mm/dd/yy") 'Pull order date and subtract days from it for delinquency date
End If
ElseIf delType = "Numeric" And Len(del(i, 1)) = 5 Then
If del(i, 1) > CLng(Date) Then 'Value is greater than todays date, improperly formated date that needs character manipulation and / added
del(i, 1) = Format(del(i, 1), "000000") 'Add leading zero
del(i, 1) = DateSerial(Right(del(i, 1), 2), Left(del(i, 1), 2), Right(Left(del(i, 1), 2), 4)) 'Grab year, then month, then day for serialize
Else
del(i, 1) = Format(del(i, 1), "mm/dd/yy") 'Properly formated date that just needs format conversion
End If
ElseIf delType = "Numeric" And (delChars = 7 Or delChars = 8) Then
If delChars = 7 Then
del(i, 1) = Format(del(i, 1), "00000000") 'Add leading zero
End If
del(i, 1) = DateSerial(Right(del(i, 1), 4), Left(del(i, 1), 2), Right(Left(del(i, 1), 2), 6)) 'Grab year, then month, then day for serialize
ElseIf delType = "Numeric" And delChars < 5 Then
del(i, 1) = Format((CLng(Range("E" & i + 1)) - Abs(del(i, 1))), "mm/dd/yy")
End If
Next i
Set delRange = Range("AJ1:AJ" & importwsRowCount)
iWS.names.Add Name:="dRange", RefersTo:=delRange
Range("dRange").Value = del 'Write array to worksheet
" 54天"包含7个字符,因此Len("54 Days")
是7
空间字符出现在位置3,InStrRev("54 Days", " ")
返回3
所以,我们有Left("54 Days", 7 - 3)
产生"54 D"
,显然不是数字
也许尝试Left(del(i, 1), InStrRev(del(i, 1), " ") - 1)
而不是