PDA

View Full Version : Compare Two Strings via Len Function



YellowLabPro
11-09-2007, 06:11 AM
I need to select the terms that have the same base value to perform my next instruction. These are sequential in my list.

Data Example: 3256305BW is in cell B4, the inital cell in the range.
This will match the next value and would return a true. On the offset of 4, this should return false. It still returns true.

3256305BW
3256305BW10
3256305BW11
3256LASBGR
3256LASBGR10
3256LASBGR11
3256LASBGR7
3256LASBGR
83256LASBGR9
3256LASBGR95
3256LASBK

I am using the Len Function. I am attempting to use the index value's len in this case 3256305BW, a length of 9, and subtract this number from the next value in the list, if string matches, based on the number of characters specificed by the function then we have a true scenario.

I am setting up a test condition currently, so I am only using hardcoded values at the moment to keep it simple.

My code is returning inaccurate results. This should fail, but does not. I must have something crisscrossed on the Len and Value here.


If Len(ActiveSheet.Range("B4").Offset(4, 0).Value) - Len(ActiveSheet.Range("B4").Value) Then
ActiveSheet.Range("B4").Offset(4, 0).Activate
End If

figment
11-09-2007, 06:31 AM
Data Example: 3256305BW is in cell B4, the inital cell in the range.
This will match the next value and would return a true. On the offset of 4, this should return false. It still returns true.

if your trying to compare the Cell to the value below it, you would need to use and offset of 1 not 4, and offset of 4 compares it to the value 4 cells below it.

if you are trying to get the length of the cells value you could use range.characters.count rather then using the len function.

rory
11-09-2007, 06:34 AM
You appear to be subtracting 9 from 10, yielding 1, which is then treated as True (because it's non-zero). Is that not happening or is that not what you expected?

Bob Phillips
11-09-2007, 06:41 AM
Any comparison that is not equal will produce a non-zero result, which will evaluate as True. Try

?CBool(-1) or
?CBool(3)

so you should check for zero explicitly



With ActiveSheet
If (Len(.Range("B4").Offset(4, 0).Value) - Len(.Range("B4").Value)) = 0 Then
.Range("B4").Offset(4, 0).Activate
End If
End With


But should you not test for likeness anyway?



With ActiveSheet
If .Range("B4").Offset(4, 0).Value Like .Range("B4").Value & "*" Then
.Range("B4").Offset(4, 0).Activate
End If
End With

YellowLabPro
11-09-2007, 07:06 AM
Ok...
Figment, I am using Offset 4 to look for a non-true result. I already tested Offset 1 and know it is true. I am doing this manually to iron out the flaws in my formula.

Rory,
You raise a good point, one I was trying to get myself out of, comparing numerical values. I want to compare data strings to match, not return a numerical value.
So it is not what I expected nor wanted. I want to convert my len back to a value comparison. By using length to setup the comparison, in this case if the index value is 9 characters in length we want to compare the first 9 characters of the subsequent value, if the first 9 characters match then do the next instruction. If the first 9 characters do not match then move on to the next record.

Bob,
I think you and Rory hit on my first error of my test condition. Since it is true, then the next instruction executed.
However, in my explanation to Rory's, I really want to compare the string from the offset value to the index value by way of the length of the index value.
Your second answer is one I had not ever thought of, it directly compares w/out having to perform a separate calculation.
I will test this out now.
But for knowledge sake, can you assist me w/ solving for the approach I have initially begun? Though if your answer works I like this very much instead.

YellowLabPro
11-09-2007, 07:16 AM
But should you not test for likeness anyway?



With ActiveSheet
If .Range("B4").Offset(4, 0).Value Like .Range("B4").Value & "*" Then
.Range("B4").Offset(4, 0).Activate
End If
End With


Bob- Bingo... this answer was excellent, thank you!
I am going to proceed w/ my next part of this. If you have the time, I know is a funny thing to say, I would like to revisit my inital approach. I think my logic is valid, only the instructions are lacking.

Cheers,

rory
11-09-2007, 07:27 AM
It's a guess, but I think you mean:
If Left(ActiveSheet.Range("B4").Offset(4, 0).Value, Len(ActiveSheet.Range("B4").Value)) = ActiveSheet.Range("B4").Value Then
ActiveSheet.Range("B4").Offset(4, 0).Activate
End If

Bob Phillips
11-09-2007, 07:37 AM
Bob- Bingo... this answer was excellent, thank you!
I am going to proceed w/ my next part of this. If you have the time, I know is a funny thing to say, I would like to revisit my inital approach. I think my logic is valid, only the instructions are lacking.

Cheers,

That would be by comparing the leftmost n characters of the new string against the old string, where n is the length of the old string.



With ActiveSheet
If Left$(.Range("B4").Offset(4, 0).Value, Len(.Range("B4").Value)) = .Range("B4").Value Then
.Range("B4").Offset(4, 0).Activate
End If
End With

Norie
11-09-2007, 07:46 AM
Doug

What is it you are actually trying to achieve here?

Perhaps filters are a better idea.

YellowLabPro
11-09-2007, 07:59 AM
Norie,
I have a "Parent" record number for every item I sell, this number defines a product but not its sizes, for instance a shoe w/ a record number of 3256BW is the "Parent", but the actual sizes would be 3256BW10 for the "Child" a size 10 or 3256BW11 for a size 11 and so on.

But my web program will only return the "Parent" record, not the "Children" records. I have to identify which records have a qty greater than zero from another source.

Norie
11-09-2007, 08:04 AM
Doug

So why can't you use filters?

And/or worksheet functions like SUMIF/COUNTIF?

Or perhaps even a pivot table?

YellowLabPro
11-09-2007, 08:11 AM
Norie,
Those are definitely good approaches, but in this case not feasible. And to explain it would take too much effort, you would need to see it. The information is vast and in different locations.

Norie
11-09-2007, 08:20 AM
Doug

If you can't explain it then how do you expect us to help with it?

Obviously you can ask specific questions bu then we can only give specific answers to those questions.

Perhaps if we had a better idea of the 'big picture' we could make more helpful suggestions.

YellowLabPro
11-09-2007, 08:29 AM
Norie,
Respectfully, I have an approach I am following. I chose not to offer a long winded explanation. You came in on the tail end of this, after it had been solved. I replied w/ a careful explanation of what I am doing per your post and w/out you seeing the project in its entirety would be too confusing. There are just some things that need to be seen. But maybe you are right, I could be deficient in explaining things..... but it is the best I can do, or best approach I can take.