PDA

View Full Version : [SOLVED] .pattern help please



qqsteven
04-26-2017, 11:12 PM
i am newbie in vba, and trying to write a program can get values automatically, like the table below

Column A:
D1234/9525845/480.5/48/A --->column B: 480.5 ---->column C: 48
peter/D12457(6)/50/$477/F --->column B: 477 --->column C: 50
sam/520/6528457/$52 --->column B: 520 --->column C: 52
B5214/50.5 --->column C: 50.5
B52145/500 --->column B: 500

what i want is to put the value under 1000 to column B, and the value under 100 to column C.
i tried a lot with something like .pattern "[0-9]{3}", but it keeps give me the wrong value that i dont want, can someone please help, thanks.

mdmackillop
04-28-2017, 01:57 AM
Sub Test()
Dim arr, a
Dim cel as Range
For Each cel In Cells(1, 1).CurrentRegion
arr = Split(Replace(cel, "$", ""), "/")
On Error Resume Next
For Each a In arr
If a < 1000 And a >= 100 Then cel.Offset(, 1) = a
If a < 100 Then cel.Offset(, 2) = a
Next
Next cel
End Sub

qqsteven
04-28-2017, 02:27 AM
solved. thanks a lot !