PDA

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



Djblois
12-04-2008, 07:43 AM
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:

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

Bob Phillips
12-04-2008, 08:00 AM
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

Djblois
12-05-2008, 07:46 AM
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
12-05-2008, 09:00 AM
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
12-05-2008, 09:15 AM
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
12-05-2008, 09:19 AM
I just tried this code but it is giving me a compile error:

tmp = Mid(.Value, Find("(", .Value) + 1, Find(")", .Value) - Find("(", .Value) - 1)

RonMcK
12-05-2008, 09:22 AM
'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
12-05-2008, 09:22 AM
Const FORMULA_NUM As String = _
"MID(<cell>,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},<cell>&""0123456789"")),SUMPRODUCT(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

Djblois
12-05-2008, 09:24 AM
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
12-05-2008, 09:28 AM
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

Djblois
12-05-2008, 09:36 AM
That worked perfectly mdmack. Thank you very much