Solved: How do I do this without inputing it in a cell?

[Djblois]

I am testing to see if a Cell contains a 6 digit number within its string but currently the only way I have found to do it is to take the value out put it in another cell and then test that cell. I know there has to be a way to test if the string in a cell contains a 6 digit number. Here is what I have used:

[VBA]With Range("B9")
.FormulaR1C1 = _
"=MID(RC[-1],FIND(""("",RC[-1])+1,FIND("")"",RC[-1])-FIND(""("",RC[-1])-1)"
.Value = Range("B9").Value
If IsNumeric(.Value) And (Len(.Value) = 6 Or Len(.Value) = 5) Then
SaveSetting "BRT", "Current", "Company", "Atalanta"
Else
SaveSetting "BRT", "Current", "Company", "DeMedici"
End If
end with[/VBA]

[Bob Phillips]

[vba]

With Range("A9")
tmp = Replace(Replace(.Value, "(", ""), ")", "")
If IsNumeric(tmp) And (Len(tmp) = 6 Or Len(tmp) = 5) Then
SaveSetting "BRT", "Current", "Company", "Atalanta"
Else
SaveSetting "BRT", "Current", "Company", "DeMedici"
End If
End With
[/vba]

[Djblois]

xld,

I thank you for all your help over the years. You are very helpful. Your code here is only taken into account the Parenthesis but my issue is that there will be text and maybe some other numbers also in that cell. I only want to test the size of the numbers inbetween the Parenthesis. I want to disregard everything else in the cell.

[RonMcK]

Daniel,

Please post a file containing sample data that the code should be able to pick-up and evaluate, and some data representative of transactions that should fail the process.

Thanks,

[Djblois]

Thank you ron. I want to test any cell that contains data like this: 'BRESAOLA 9/3# (454900)
'CAMPOFRIO SERRANO JAMON 1/12 LBS (560010)

I just want to test the number in between the Parenthesis. I can't use right or left because the Parenthesis keep moving but they are always there in the data.

[Djblois]

I just tried this code but it is giving me a compile error:

[vba]tmp = Mid(.Value, Find("(", .Value) + 1, Find(")", .Value) - Find("(", .Value) - 1)[/vba]

[RonMcK]

'BRESAOLA 9/3# (454900)
'CAMPOFRIO SERRANO JAMON 1/12 LBS (560010)

So, Daniel, are the numbers (in parentheses) always the last item in the data?

Thanks,

[Bob Phillips]

[vba]

Const FORMULA_NUM As String = _
"MID(<cell>,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},<cell>&""0123456789"")),SUMPRO DUCT(LEN(<cell>)-LEN(SUBSTITUTE(<cell>,{0,1,2,3,4,5,6,7,8,9},""""))))"

With Range("A9")
tmp = .Parent.Evaluate(Replace(FORMULA_NUM, "<cell>", .Address))
If IsNumeric(tmp) And (Len(tmp) = 6 Or Len(tmp) = 5) Then
SaveSetting "BRT", "Current", "Company", "Atalanta"
Else
SaveSetting "BRT", "Current", "Company", "DeMedici"
End If
End With
[/vba]

[Djblois]

So, Daniel, are the numbers (in parentheses) always the last item in the data?

Thanks,

Yes but they can be different lengths. I need to test the length of the number in the Parenthesis.

[mdmackillop]

[vba]
Sub test()
Dim v
With Range("A9")
v = Split(Split(.Value, "(")(1), ")")(0)

If IsNumeric(v) And (Len(v) = 6 Or Len(v) = 5) Then
SaveSetting "BRT", "Current", "Company", "Atalanta"
Else
SaveSetting "BRT", "Current", "Company", "DeMedici"
End If
End With
End Sub

[/vba]

[Djblois]

That worked perfectly mdmack. Thank you very much