Check if one or the other cell is empty - essentially check for a mismatch 3

[MattGreer]

I'm parsing through a spreadsheet and I need to check if there's an instance when one cell has something in it but the other is blank. There are instances when both cells have something in them, and instances when both cells are blank. That's OK.

All I can come up with is something pretty ugly but it seems to work (I don't

If IsEmpty(Cell 1) And Not IsEmpty(Cell 2) OR IsEmpty(Cell 2) And Not IsEmpty(Cell 1)

What's order of operations for AND and OR statements? This works but I would have though I needed brackets around the two AND comparisons, right?

I mean, there has to be an easier or more logical way, right? This works, but is there some kind of "mismatch" function?

Thanks!!


Matt

 

[strongm]

> to do in regex.

OK, it can be done in regexp, but requires a teeny bit of arcane knowledge e- which is that we can write a custom replacer function that regexp will use, rather than the default (which, means there are all sorts of neat tricks we can play). Oh, and there’s one other thing. You need to be able to set a default method for a class – easy in VB6 , little harder in VBA

So, create a class module (C las1) and add the following code:

[blue]Option Explicit
[COLOR=green]' function template:
' replaceFunc(matchedString [, subMatch1 [, ...]] , matchPos, source)
' this is your custom replacer function. You can put your own code in here ...
' the template above should help you to figure out which parameter means what[/color]
Public Function ReplacerFunction(ParamArray a()) As String
   ReplacerFunction = Replace(a(3), ",,", a(1) & ", " & a(2) & ", ")
End Function[/blue]

Make this method the default (lots of google articles on how to do this – basically involves exporting the class module as a text file, editing it in a text editor to add the necessary attribute, then (re)importing the class text file

Now you just need a form with a multiline textbox and a command button. Copy and paste the lines below (shamelessly stolen from Andy above) into the textbox

[tt]VP-MOD-112X
5-RW-40016-001
,,11,"5FS3S(MODIFIED)", 1,5FS3S(MODIFIED),1
,,7,"5S2CV-4""", 8,5S2CV-4"",1
,,8,"52SCV-5""", 8,52SCV-5"",1
,,16,""5S2CV-3 7/8"" 6 5S2CV-3", 7/8"",,1
ABCD
XYZ
,,11,"5FS3S(MODIFIED)", 1,5FS3S(MODIFIED),1
,,7,"5S2CV-4""", 8,5S2CV-4"",1
,,8,"52SCV-5""", 8,52SCV-5"",1
,,16,""5S2CV-3 7/8"" 6 5S2CV-3", 7/8"",,1[/tt]

And then use the following code in your command button (may need to modify to match you textbox name and your command button name, as this is from a VB6 project, rather than VBA, an d the default naming conventions are not quite the same)

[blue]Private Sub Command1_Click()
    Dim re As New RegExp
    Dim myReplacer As New Class1

    With re
        .MultiLine = True
        .Global = True
        .Pattern = "(.*)\r\n(.*)\r\n((^,,.*\r\n)+)" [COLOR=green]' could probably be improved if I spent time on it[/color]
        MsgBox .Replace(Text1.Text, myReplacer)
    End With
End Sub[/blue]

And that’s it (note there will be a minor oddity, because your fourth line has ,,1 at the end, which this example code happens to consider needs replacing – but this is just illustrative of how we might do it with regexp, not a full solution

Bearing that in mind, for the example input, the output I get is:

[tt]VP-MOD-112X, 5-RW-40016-001, 11,"5FS3S(MODIFIED)", 1,5FS3S(MODIFIED),1
VP-MOD-112X, 5-RW-40016-001, 7,"5S2CV-4""", 8,5S2CV-4"",1
VP-MOD-112X, 5-RW-40016-001, 8,"52SCV-5""", 8,52SCV-5"",1
VP-MOD-112X, 5-RW-40016-001, 16,""5S2CV-3 7/8"" 6 5S2CV-3", 7/8""VP-MOD-112X, 5-RW-40016-001, 1
ABCD, XYZ, 11,"5FS3S(MODIFIED)", 1,5FS3S(MODIFIED),1
ABCD, XYZ, 7,"5S2CV-4""", 8,5S2CV-4"",1
ABCD, XYZ, 8,"52SCV-5""", 8,52SCV-5"",1
ABCD, XYZ, 16,""5S2CV-3 7/8"" 6 5S2CV-3", 7/8""ABCD, XYZ, 1[/tt]

 

[Andrzejek]

To Matt - See? I would NOT bet against strongm.
He knows what he is talking about...

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[MattGreer]

LOLOL, There are so many smart folks around here. I'm very grateful!

Thanks!!


Matt