Name (Value) replacement in XL 2003 VBA

[MassSpecGuru]

I would like to develop some VBA code for the evaluation, and if "improperly entered"the replacement, of certain values in the first column of a worksheet.

Ultimately, I would like to test for four different situations, however, if pointed in the right direction, I could probably expand upon the one-case scenario myself (fingers crossed), but an example of what I would like to accomlish is this:

If the value in a cell is a number then return that number, otherwise check to see if it is, say, "QL"* (case sensitive). If it is only "QX" both capital letters), then return that value, too.

The "problem" comes in where I want to evaluate other possible entries such as: "Q l" (or q l) "Qlow", "QCL", "QCLOW", "qc_low", "QC-low" (or in this instance, any other variations/combinations you can dream up using the letters, both upper and lower case, q, c, b, l, a, n, k, as well as any "space-type" characters like <SPACE>, hyphen, dash.)

Basically, what I see as the key to this task is whether or not there is a "b", "l" (el), "m" or "h" somwhere in the cell. The "difficulty" increases though because the possibility if a cell containing, say, (ignoring case and just focusing on the letters involved) "qc blank", or "qcblk" or "qcbl" versus a cell containing "qcl" or "qc low" of just "q low". I guess you could check for the "b" first here and then move on, so maybe a Case construction would be in order?

Can anybody come up with some suggestions, or code , to getting this going?

Thanks for your input!


* where "X" would be either "B", "L", "M" or "H", thus the other three instances we're checking for are: "QB", "QM" and "QH".

 

[MassSpecGuru]

As a follow-up, an example of what the data could look like is:

qcl
190001
qMed
190002
190003
190004
QCHIGH
190005
190006
blk
190007
190008
190009
ql
190010
190011
Q-M
.
.
.

and what the corrected data should look like:

QL
190001
QM
190002
190003
190004
QH
190005
190006
QB
190007
190008
190009
QL
190010
190011
QM
.
.
.

Somehting I guess I forgot to note earlier was that the only acceptable non-numeric values are "QB", "QL", "QM" and "QH". The rest of the values should be purely numeric (the length of which, are unimportant).

 

[shg]

I don't see the rationale for the changes you want to the alpha entries.

 

[MassSpecGuru]

I don't see the rationale for the changes you want to the alpha entries.

When you say, "the rationale", do you mean wanting to go from lower to upper case even if the correct two-digit code is entered or something else?

Like I wrote in my follow-up, the ONLY acceptable non-numeric enteries are: "QB", "QL", "QM" or "QH". That's it. The "rationale" is those are the only four allowable 'codes' we are allowed to use. I didn't set up the reporting system, I'm only trying to do what is asked of me, which is to take years of old data and compile it in the format requested, and instead of going through thousands of cells and manually changing, say, "QC Blank" to "QB", or "qc_high" to "QH", I want to do it through some VBA code.

And since there are more than 7 possible permutations of the letters and characters, I can't use a nested IF statement to do everything needed.

As an aside, the non-numeric values aren't going to be anything like "car" or "dog" or "house", they are in almost every instance going to be something like "QC..." or "q..."; however, I'm not guaranteeing that they always start with a "q" (lower or upper case). Like I think I mentioned, it could be simply "blank" or "low" or "high", which is why I initially mentioned that probably looking for a "b" or an "l" or an "m" or an "h" would suffice, as long as the "l" case came after the "b" case because of the possibility the word was "blank" or "blk" (again, ignoring the case).

Please let me know if this isn't any clearer, and thanks for looking.

 

[xenou]

Is this a one-time cleanup job or will you be receiving and cleaning up such data on a regular basis?

I would start by hitting all the obvious replacements or the easy ones - then see what you have left. You say there are more than 7 possible combinations of letters (more than meaning 8? 9? 10?). That wouldn't be hard to set up with a simple case statement for a one-time clean up job - can you give a complete list of these possibilities?

 

[shg]

How would you know to change "blk" to "QL"?

 

[MassSpecGuru]

Is this a one-time cleanup job or will you be receiving and cleaning up such data on a regular basis?

I would start by hitting all the obvious replacements or the easy ones - then see what you have left. You say there are more than 7 possible combinations of letters (more than meaning 8? 9? 10?). That wouldn't be hard to set up with a simple case statement for a one-time clean up job - can you give a complete list of these possibilities?

Hopefully, this will be a one-time cleanup.

I'd have to say, from the application, the letters to be encountered will be:

Q, C, B, L, A, N, K, O, W, H, I, and G. Then throw in the space character, (which didn't show up in my original post because I used the "less than" and "greater than" signs around the word "space"), the dash character and the hyphen character and you probably have 99.999999% of the characters involved, so that's, what, 15 characters?

What this all revolves around, is taking non-uniform laboratory data, pulling the sample "names" (unknown samples are run with a lab id (always numeric, no alpha or other characters!)) and the four Quality Control samples (thus the "QC" part of the sample id term) and making them uniform.

Like I mentioned, there are four types of quality control samples: a blank QC, a low QC, a medium QC and a high QC, where "blank", "low", "medium" and "high" refer to the target analyte's concentration. What I need to do is try and figure out what the then-analyst ran and change it to the desired two-letter code.

So, for instance, if they used "qc low" as the Sample ID I need to change it to "QL". But another analyst might have entered the same Sample ID as "QLOW". In both cases, along with many potential others when you start including spaces and dashes and hyphens as possible spearators between QC and whatever follows, I need the final result to be "QL".

I need to then do the same thing for the other three types of QC samples: "QB", "QM" and "QH".

Does this make any more sense? (I sure hope so because I don't know that I can explain it any more clearly. )

Thanks!

 

[MassSpecGuru]

How would you know to change "blk" to "QL"?

Actually, if you saw "blk" you should change it to "QB", so probably some code like:

if you find "b" or "B" (anywhere in the cell) then = "QB"
Similarly, if you find "l" or "L" but not also "b" or "B" then = "QL", or
if you find "m" or "M" then = "QM", or
if you find "h" or "H" then = "QH".

Actually, looking at it written outlike this, it's probably as simple as that.

 

[xenou]

I'm afraid I have not tested this so it probably has some error in it.
I tried to set this up to be a "stringent" test. We won't assign a QH if there is an M in the string, and vice versa. We will assign a "B" if there is an "L", but an "L" only if there is no "B".

The output is to a new workbook to prevent any problems with your original data (I don't know if there's more on the spreadsheet. Presumably, you can just copy it back if it looks right.

There's is a line to set the range to process, which you must adjust for your actual range:

    '//Set the range to be processed
    a = .[COLOR="Blue"]Range("A1:A10000")[/COLOR].Value

Fingers crossed! (one suspects that you'll find more "exceptions" once you run through all the data. If it works, I would then sort or filter the results for anything not QB,QL,QM,QH and see what didn't make it).

Sub Foo()
Dim sTemp As String
Dim i As Long
Dim a, b
Dim ws As Worksheet
Dim wbOutput As Workbook

Set ws = ActiveSheet

With ws
    '//Set the range to be processed
    a = .Range("A1:A10000").Value

    Redim b(1 to Ubound(a,1),1)
    
    For i = 1 to Ubound(a,1)

        '-------------------------------------
        If IsNumeric(a(i,1)) Then
            
            b(i,1) = a(i,1)

        Else
        
            b(i,1) = UCase(a(i,1))

            '--------------------------------------------------------
            '//Attempt to match: (L can exist with B)
            If InStr(1,b(i,1),"B") > 0 Then
                b(i,1) = "QB"
            ElseIf InStr(1,b(i,1),"L") > 0 Then
                 b(i,1) = "QL"
            End If
 
            '//M cannot exist with H and vice versa
	    If b(i,1) <> "QB" And b(i,1) <> "QL" Then
                If InStr(1,b(i,1),"M") > 0 XOr InStr(1,b(i,1),"H") > 0 Then
                        If Instr(1, b(i,1), "M") > 0 Then
                            b(i, 1) = "QM"
			Else
			    b(i, 1) = "QH"
		        End If
		End If
	    End If

        End If
    
    Next i

End With

'//Output processed values
Set wbOutput = Workbooks.Add
wbOutput.Worksheets(1).Cells(1,1).Resize(Ubound(a,1)).Value = a
wbOutput.Worksheets(1).Cells(1,2).Resize(Ubound(a,1)).Value = b

'//Warning
Msgbox "Values for codes are blank where no match or more than one match was found.  Please scan/sort for blanks and correct these codes."

End Sub

 

[xenou]

Note: Lol fixed about 6 errors just now looking at this after I posted. Wrote it in a text editor - can you imagine what it was like when code was written in 1's and 0's!