Programming Forums > Application Development > Visual Basic

TheAberrant · Jul 31st, 2008, 6:42 AM

I apologize in advance if my information is vague, but I'll try to be as concise as possible. This is for work and not a school project, and isn't something I typically do (just something to make my life a little easier)

Task:
Write a macro to get case #'s from another sheet and insert in the Master sheet. Basically I have a list of items that needs to be covered, and each row has a list of those items depending on what that case covers. I want to track which item doesn't have an associated case, and if it does have one which cases (can be multiple).

Problem:
I'll post the current code in it's entireity at the end. The issue deals with false positives and solution is giving false negatives. Some items are things like:
hello_goodbye_goodday
hello_goodbye

The initial problem was that hello_goodbye would count towards hello_goodbye_goodday. That was solved with the following line (J loops through all the rows relevant for the cases):

(Toggle Plain Text)

If InStr(Sheets("cases").Range("F" & J).Value, currentAction & "_") Then GoTo NextPublicIteration

which bypasses the inner for loop and goes to the next line. However, now if I have both hello_goodbye AND hello_goodbye_goodday in the same case (row), it doesn't count hello_goodbye.

Potential Solution?
I'm thinking something like checking for a newline - but my attempt to use currentAction & vbNewLine didn't work

(Toggle Plain Text)

    For I = firstRow To lastPublicActionRow
        currentAction = Range("B" & I).Value
        'For each test case that contains the action, mark the test case #
        For J = 8 To lastTestRow
            If InStr(Sheets("cases").Range("F" & J).Value, currentAction) Then
                ' Causing false positive - skips if hello_goodbye AND hello_goodbye_goodday exist
                If InStr(Sheets("cases").Range("F" & J).Value, currentAction & "_") Then GoTo NextPublicIteration
                If Range("D" & I).Value = "No Case" Then
                    Range("D" & I).Value = Sheets("Public").Range("A" & J).Value
                ElseIf Range("D" & I).Value <> Empty Then
                    Range("D" & I).Value = Range("D" & I).Value & ", " & Sheets("Public").Range("A" & J).Value
                Else
                    Range("D" & I).Value = Sheets("cases").Range("A" & J).Value
                End If
            End If
NextPublicIteration:
        Next J
        If (Range("C" & I).Value = "DEPRECATED" Or Range("C" & I).Value = "UNUSED") And (Range("D" & I).Value = "No Case") Then Range("D" & I).Value = "N/A"
    Next I

Jul 31st, 2008, 6:42 AM	#1
TheAberrant Newbie Join Date: Jan 2008 Posts: 10 Rep Power: 0	Excel macro: False Positives on instr I apologize in advance if my information is vague, but I'll try to be as concise as possible. This is for work and not a school project, and isn't something I typically do (just something to make my life a little easier) Task: Write a macro to get case #'s from another sheet and insert in the Master sheet. Basically I have a list of items that needs to be covered, and each row has a list of those items depending on what that case covers. I want to track which item doesn't have an associated case, and if it does have one which cases (can be multiple). Problem: I'll post the current code in it's entireity at the end. The issue deals with false positives and solution is giving false negatives. Some items are things like: hello_goodbye_goodday hello_goodbye The initial problem was that hello_goodbye would count towards hello_goodbye_goodday. That was solved with the following line (J loops through all the rows relevant for the cases): (Toggle Plain Text) If InStr(Sheets("cases").Range("F" & J).Value, currentAction & "_") Then GoTo NextPublicIteration If InStr(Sheets("cases").Range("F" & J).Value, currentAction & "_") Then GoTo NextPublicIteration which bypasses the inner for loop and goes to the next line. However, now if I have both hello_goodbye AND hello_goodbye_goodday in the same case (row), it doesn't count hello_goodbye. Potential Solution? I'm thinking something like checking for a newline - but my attempt to use currentAction & vbNewLine didn't work (Toggle Plain Text) For I = firstRow To lastPublicActionRow currentAction = Range("B" & I).Value 'For each test case that contains the action, mark the test case # For J = 8 To lastTestRow If InStr(Sheets("cases").Range("F" & J).Value, currentAction) Then ' Causing false positive - skips if hello_goodbye AND hello_goodbye_goodday exist If InStr(Sheets("cases").Range("F" & J).Value, currentAction & "_") Then GoTo NextPublicIteration If Range("D" & I).Value = "No Case" Then Range("D" & I).Value = Sheets("Public").Range("A" & J).Value ElseIf Range("D" & I).Value <> Empty Then Range("D" & I).Value = Range("D" & I).Value & ", " & Sheets("Public").Range("A" & J).Value Else Range("D" & I).Value = Sheets("cases").Range("A" & J).Value End If End If NextPublicIteration: Next J If (Range("C" & I).Value = "DEPRECATED" Or Range("C" & I).Value = "UNUSED") And (Range("D" & I).Value = "No Case") Then Range("D" & I).Value = "N/A" Next I For I = firstRow To lastPublicActionRow currentAction = Range("B" & I).Value 'For each test case that contains the action, mark the test case # For J = 8 To lastTestRow If InStr(Sheets("cases").Range("F" & J).Value, currentAction) Then ' Causing false positive - skips if hello_goodbye AND hello_goodbye_goodday exist If InStr(Sheets("cases").Range("F" & J).Value, currentAction & "_") Then GoTo NextPublicIteration If Range("D" & I).Value = "No Case" Then Range("D" & I).Value = Sheets("Public").Range("A" & J).Value ElseIf Range("D" & I).Value <> Empty Then Range("D" & I).Value = Range("D" & I).Value & ", " & Sheets("Public").Range("A" & J).Value Else Range("D" & I).Value = Sheets("cases").Range("A" & J).Value End If End If NextPublicIteration: Next J If (Range("C" & I).Value = "DEPRECATED" Or Range("C" & I).Value = "UNUSED") And (Range("D" & I).Value = "No Case") Then Range("D" & I).Value = "N/A" Next I

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Easy Question on VB for Excel	Aussiemex	Visual Basic	0	Feb 7th, 2006 11:51 PM
appending an excel file	glevine	PHP	1	Nov 29th, 2005 5:33 PM
entering data into excel from a form	glevine	Perl	1	Nov 18th, 2005 5:03 PM
Reading Excel files on the web	sagi	Other Web Development Languages	3	May 2nd, 2005 10:24 AM
How to run a macro in MSProject from Excel?	jiancheng80	Visual Basic	2	Jan 19th, 2005 6:49 PM

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)

Programming Forums > Application Development > Visual Basic

Excel macro: False Positives on instr