Excel (VBA) question - confirm if string appears in particular column.

Factual Questions
1

Hi, folks. I need an excel hero – I’m a bit out of my depths.

I’ve designed a spreadsheet that creates a postage schedule report based on raw input from a .csv file produced by our postage meter.

Basically, I cut and paste the .csv file into a blank sheet, and use SUMIF to add up the charges for individual clients. My .csv file is typically about 1500 lines like this, dropped into a sheet named “meter file”:



1596	I	2/1/2007 10:56	2/1/2007 10:57	vr 1521	49	Letterpost	66	USA Std Letterpost			4	3.72	40	gm	0	3.72	$
1597	I	2/1/2007 10:57	2/1/2007 10:57	lms 578	49	Letterpost	66	USA Std Letterpost			1	0.93	10	gm	0	0.93	$

(The meter records jobs as they are done, so each property might have 50 separate entries.)

I get the total for each billed property with a formula like this:


=SUMIF('meter file'!E1:E3000,"=lms 578",'meter file'!M1:M3000)

Theoretically, I should just be able to drop my raw data in there and my report is done.

Unfortunately, I’m running into a slight problem – my total is out by a very small amount.

It appears that there is at least one “customer ID” in the meter file that isn’t referenced in the spreadsheet. Trying to locate it is driving me up the wall, and I know that this will happen in the future as new clients are added to the meter.

What I would like to do is to run a macro that looks at each row in the “meter file” sheet and checks to see if the text in column “E” appears in the formula in column “D” of the sheet with my report spreadsheet in it, and make it clear if it fails the test. (Either put a mark in a cell on that row, or change the colour of the row, or hide every row that passes the test, whatever, it doesn’t matter.)

Unfortunately, I am wet-behind-the-ears with VBA, and don’t really know how to approach string manipulation.

Is that clear? I need to get the contents of Cell “E” of each row in the sheet “meter file,” (call it “Customer_ID”) and then confirm that there is a cell in column “D” of the sheet called “schedule” which contains the text =SUMIF(‘meter file’!E1:E3000,"=Customer_ID",‘meter file’!M1:M3000)

So I should be able to drop my data in, check to see if the total on the sheet matches the total from the meter file, and if it doesn’t — run the macro, see which properties aren’t referenced in the sheet, fix it up, and print.

I know this should be easy, but I’m getting a headache trying to find out how to do it through online tutorials and whatnot.

Can anyone give me a clue?

Thanks!

2

Don’t beat yourself up over that. I have well over a decade of VB programming experience and Excel VBA is like Greek to me. The help file is a joke; if it weren’t for the exceptionally helpful and easily searchable Knowledge Base I’d never be able to do any Excel coding.

Here’s a primitive solution for your problem. To make this work, you need a third, blank sheet in the workbook. The macro will create a list of all “orphaned” properties (plus their corresponding row number from the meter sheet) in the third (blank) sheet. As I said, I can’t stand dealing with Excel so the code is pretty ugly, and it isn’t optimized at all. Fair warning: I didn’t bother to group the results, so if an orphan property has 50 meter readings, it will show up 50 times in the orphans list.

I used a metric assload of variables in order to simplify things on your end. In the beginning of the macro you’ll see a “User defined parameters” section where you can supply the necessary information. Set the names of all three sheets, plus the first cell in the search ranges on the meter and report sheets. (I didn’t know if you had header rows or not, and the code is so ugly I didn’t want you to have to go digging around in the guts of it.)

One last thing: This will undoubtedly be very slow, and I couldn’t figure out how to make the mouse cursor go hourglass while it’s working, so you’ll just have to give it a minute or so. If it doesn’t find anything, I suggest adding a new line item to the very end of the meter sheet (by copying and pasting from an existing row) and fudging the property cell to ensure you have at least one orphan. Then run again to verify it can actually find it.

Anyway, hope this helps, and post back if you have any problems.



Sub FindMissing()
    Dim strMeterSheet As String
    Dim strMeterStart As String
    Dim lngMeterCol As Long
    Dim lngMeterMin As Long
    Dim lngMeterMax As Long
    Dim strReportSheet As String
    Dim strReportStart As String
    Dim lngReportCol As Long
    Dim lngReportMin As Long
    Dim lngReportMax As Long
    Dim strOrphanSheet As String
    Dim m As Long
    Dim r As Long
    Dim o As Long
    Dim blnFound As Boolean
    Dim strProperty As String
    Dim strFormula As String
    
    ' User defined parameters - set these as appropriate
    
    strMeterSheet = "meter file"
    strMeterStart = "E1"
    
    strReportSheet = "report"
    strReportStart = "D1"
    
    strOrphanSheet = "orphans"
    
    ' Prep
    
    Sheets(strMeterSheet).Select
    ActiveCell.SpecialCells(xlLastCell).Select
    lngMeterCol = Asc(Left(strMeterStart, 1)) - 64
    lngMeterMin = Mid(strMeterStart, 2)
    lngMeterMax = ActiveCell.Row
    Sheets(strReportSheet).Select
    ActiveCell.SpecialCells(xlLastCell).Select
    lngReportCol = Asc(Left(strReportStart, 1)) - 64
    lngReportMin = Mid(strReportStart, 2)
    lngReportMax = ActiveCell.Row
    Sheets(strOrphanSheet).Select
    Range("A1").Select
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    Selection.ClearContents
    Range("A1").Select
    Sheets(strOrphanSheet).Cells(1, 1).Value = "Property"
    Sheets(strOrphanSheet).Cells(1, 2).Value = "Row"
    o = 2
    
    ' Main loop
    
    For m = lngMeterMin To lngMeterMax
        strProperty = Sheets(strMeterSheet).Cells(m, lngMeterCol).Value
        blnFound = False
        For r = lngReportMin To lngReportMax
            strFormula = Sheets(strReportSheet).Cells(r, lngReportCol).FormulaR1C1
            If InStr(1, strFormula, strProperty) > 0 Then
               blnFound = True
               Exit For
            End If
        Next
        If Not blnFound Then
            Sheets(strOrphanSheet).Cells(o, 1).Value = strProperty
            Sheets(strOrphanSheet).Cells(o, 2).Value = m
            o = o + 1
        End If
    Next
End Sub

Notes:[ul][li]The “orphans” sheet gets completely blanked out by the macro each time it runs, so be sure you don’t set strOrphanSheet to the wrong sheet name.[/li][*]The macro doesn’t distinguish between property codes that are a subset of one another. So if there is an orphan named “vr 152” and a bucketed property named “vr 1521”, the orphan won’t get picked up. (Since “vr 152” is contained within “vr 1521”)[/ul]

3

Holy crap, dude – that’s awesome. I would have been happy with a nudge in the right direction; I didn’t expect a finished solution. Thank you!

As for it taking a while to run, I’m not too worried about that! Before this month, this report was prepared manually.

A minute or so to run a macro or two (I have another macro that hides the rows for properties that aren’t billed that month) and insert a couple of rows to the sheet to get it to balance compares pretty favourably with going through the freaking meter file line-by-line and summing up the totals for 250 properties with a calculator, and then entering those numbers into Excel. :smiley:

The deal with it not catching “orphaned” properties that are part of listed properties isn’t a biggie, either. Theoretically, it could happen: For example, we have an account called “LMS 8” and one called “LMS 876” – if “8” came after “876,” it could get missed.

I thought about seeing if I could modifying the code to be more discrete myself, (since you’ve given it to me all nicely wrapped up, I would like to have the opportunity to tweak it a tiny bit,) but I think it’s easier just to give a naming convention to the mail-girl: In future, all Strata numbers will be set up in the postage meter with four digits and have leading zeros. (eg; “LMS 8” should be set up as “LMS 0008” and “LMS 876” should be set up as “LMS 0876”) Problem solved.

Once again, thank you for your help!

4

Oh, and thanks for the link to the knowledge base, too – I’m sure that will come in handy in the future.

5

Strong naming conventions are always a good idea; I would do that regardless. However, since your codes could potentially be subsets of one another, you should have the search term bounded by quotes to ensure proper results. In the OP you appear to be using double quotes (") to surround the property ID inside each formula, as opposed to single quotes (’). Assuming that is the case, change the following line of code:

strProperty = Sheets(strMeterSheet).Cells(m, lngMeterCol).Value

to this:

strProperty = Chr(34) & “=” & Sheets(strMeterSheet).Cells(m, lngMeterCol).Value & Chr(34)

This will also affect the output; an orphaned property will now appear as “=v 157” instead of v 157 in the results sheet. But every unique code will be processed independently, without the possibility of ambiguity.

My pleasure.

I forgot that in the OP you specifically mentioned string manipulation. Here’s the basic functions off the top of my head. To get details on them, type one into the VBA editor and hit F1. (If it isn’t found, try adding the open parenthesis.)

Formatting functions:
UCase()
LCase()
Format() (Format any kind of number or date in any way you like. Quite powerful.)

Extracting substrings:
Left()
Mid()
Right()

Searching:
Instr()
InstrRev() (may not exist in Excel VBA)

Character codes:
Asc()
Chr()

Miscellaneous:
Str()
Len()
Replace()
Space()
String()

Splitting strings into arrays for faster processing:
Split() (may not exist in Excel VBA)
Join() (may not exist in Excel VBA)

Finally, the ampersand (&) is the preferred method for string concatenation. It is superior to the plus sign (+) because the ampersand handles Null values, whereas the plus sign does not.

On preview: Yeah, the Knowledge Base kicks all kinds of ass. I was embarassingly ignorant of it until very recently.