VBA String Comparison Issues

[bl4met]

I have a seemingly simple comparison that will only work part of the time. I have searched this and other forums and I just cant seem to find the issue. I'm using the following VBA code to compare and copy the value of the cell into the cell next to it if it matches. It is in a loop but the code on either side of this executes so there shouldn't be any reason for the if statement not to be evaluated.

If ActiveCell.Offset(0, -9).Value Like "*[10-20]/*[0-20].*[0-1]/0" Then ActiveCell.Offset(0, -8).Value = ActiveCell.Offset(0, -9).Value

The values for ActiveCell.Offset(0, -9) and whether or not they match are as follows:

10/0.0/0 Match
10/1.0/0 Match
10/2.0/0 Match
10/4.0/0 No Match
10/6.0/0 No Match
10/6.1/0 No Match
10/7.0/0 No Match
10/10.0/0 Match
10/11.0/0 Match
10/11.1/0 Match
10/12.0/0 Match
10/14.0/0 No Match
10/14.1/0 No Match
10/15.0/0 Match

From what I can tell there seem to be no rhyme or reason to it matching or not matching. Any help would be much appreciated.

 

[Rick Rothstein]

I was actually wanting that last zero as a zero that along with the slashes and the period were the only constants. in this case i was going more for the format of the data rather then the actual contents. It probably would have been easier if I had gone the other way and did my copy when the data did not match the other possible entry but I didn't want to run into issues down the road if the format of the overall entered data changed.

Using the list you posted originally as a guide for the structure of the text you are attempting to evaluate, here is how I would write the line of code you showed in your initial message...

If ActiveCell.Offset(0, -9).Value Like "[12]#/#.[01]/0" Or ActiveCell.Offset(0, -9).Value Like "[12]#/[12]#.[01]/0" Then ActiveCell.Offset(0, -8).Value = ActiveCell.Offset(0, -9).Value

Here is core of what I posted as a function (which could be used as a UDF is desired) which is less cluttered and shows the logic more clearly...

Function IsRightShape(S As String) As Boolean
  IsRightShape = S Like "[12]#/#.[01]/0" Or S Like "[12]#/[12]#.[01]/0"
End Function

 

[bl4met]

Thank you for all the good info, I will keep this in mind next time I am doing comparisons like this.