asked on

amend function in vba

I have this function but it needs a slight amendment.

It fails on these strings:

strcurin= "a3 110"
strtestfieldin=a3

cur in=A3 S LINE BLACK EDITION (138)
testfield=A3 S LINE BLACK EDITION

each of the five words appears in cur

this one will fail

cur=A3 S LINE BLACK EDITION QUATTRO (170
testfield=A3 S LINE BLACK EDITION QUATTRO (

the first 6 words are in but the seventh word"(" is not a word in cur, it is a substring of (170" but i dont want that comparison.

it should pass as a3 in test field is a word in curin

I remmed out the piece that tests elements on both sides are there as the strtestfieldin could have or be equal too the elements of strcurin.

so cur will never be less than the test string

here i want to test if a3 exists as a word in strtestfieldin but not as an instring word for word.

the test fields are having things extracted from them. the remaining words should appear in cur somewhere, unless an extraction routine left a part word.

Function word4wordCheck(StrCURIn As String, StrTESTFieldin As String) As Boolean

'    Dim ExtractWordsPhrase1() As String
'    Dim ExtractWordsPhrase2() As String
'    Dim WordCountPhrase1, WordCountPhrase2 As Integer
'    Dim intLoop As Integer
'
'    ExtractWordsPhrase1 = Split(Trim(StrCURIn), " ")
'    ExtractWordsPhrase2 = Split(Trim(StrTESTFieldin), " ")
'
''    'If element count of arrays differ return false
''    If UBound(ExtractWordsPhrase1) <> UBound(ExtractWordsPhrase2) Then
''        word4wordCheck = False
''        Exit Function
''    End If
'
'    'Check each element of Array
'    For intLoop = LBound(ExtractWordsPhrase1) To UBound(ExtractWordsPhrase1)
'        If ExtractWordsPhrase1(intLoop) <> ExtractWordsPhrase2(intLoop) Then
'            word4wordCheck = False
'            Exit Function
'        End If
'    Next
'
'    word4wordCheck = True

End Function

Open in new window

Gustav Brock

Couldn't you just apply a replace for left-right paranthesis with a Space on the string before the comparison?

strToCompare = Replace(Replace("A3 S LINE BLACK EDITION QUATTRO (170", "(", " "), ")", " ")

/gustav

PeterBaileyUk

ASKER

here is my concept:
CUR        BHP Out      Litres Out      Valves Out      C02 Out        Door Out
A3 1.8 SE        A3 1.8 SE        A3 SE        A3 1.8 SE        A3 1.8 SE        A3 1.8 SE

I have some regualr expression rules on the five columns from bhp to door, if you look at litres out the 1.8 has beein removed. the others remain the same because there was nothing to extract so the original string is cur.

if i now say which of the outputs is not equal to the [cur] field i can deduce that the litres removed something (hopefully litres), to validate the litres string I need to see if each word (a3 then se) are in the original string. if they are its a valid string.

It could be that one of the other tests bleeds over
ie like the bracket it took the 170 but left the bracket

so i can now determine that the string is not valid as it had a complete word that was not in the original string.

The overall goal is to remove technical elements from the string which can only leave the actual descriptive element of the vehicle. so the original string A3 1.8 SE      will become A3 SE.

Gustav Brock

Oops, I can't get my head around this a quiet Saturday morning ...
Perhaps it would be easier to turn it upside down and extract valid words like A3 and SE?

/gustav

PeterBaileyUk

ASKER

too many of them whereas we know the footprints of things like litres bhp doors.

as i said i only need to get the words checked off.

PeterBaileyUk

ASKER

I think ive done it just by changing over the for loop

Function word4wordCheck(StrCURIn As String, StrTESTFieldin As String) As Boolean

    Dim ExtractWordsPhrase1() As String
    Dim ExtractWordsPhrase2() As String
    Dim WordCountPhrase1, WordCountPhrase2 As Integer
    Dim intLoop As Integer

    ExtractWordsPhrase1 = Split(Trim(StrCURIn), " ")
    ExtractWordsPhrase2 = Split(Trim(StrTESTFieldin), " ")



    'Check each element of Array
    For intLoop = LBound(ExtractWordsPhrase2) To UBound(ExtractWordsPhrase2)
        If ExtractWordsPhrase1(intLoop) <> ExtractWordsPhrase2(intLoop) Then
            word4wordCheck = False
            Exit Function
        End If
    Next

    word4wordCheck = True

End Function

Open in new window

Gustav Brock

Great!

/gustav

PeterBaileyUk

ASKER

ok so points, maybe you can confirm all is good in my code so i can award

ASKER CERTIFIED SOLUTION

Gustav Brock

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial