.Find not working

S

shanep

New Member
Joined
Mar 18, 2009
Messages
32
Hi

I have a list of hostnames, each of which has an associated Application ID, Environment (e.g. Prod, DEV, UAT etc.) and Monthly Cost.

The list contains multiple entries for some hosts (as they are shared across multiple applications). Where there are multiple entries for a host the Application ID is different in each row (as well as possibly the Environment and Monthly Cost).

I need to create a wsheet that shows each unique Application ID and for each unique Application ID the Monthly Cost of all machines for each unique Environment, something like the following:

Application ID | Production | DR | UAT | DEV
App1 | 3000.00 | 1000.00 | 1500.00 | 1000.00
App2 | 5000.00 | 2000.00 | 2000.00 | 3000.00

I have created a list of the unique Applicaiton ID's and have written the following code to attempt to acheive my goal, but it seems that the first time through the loop rGG is set correctly (i.e. it finds the first ocurence of ther application ID after cell "B1") but on each subsequent loop rGG remains set to the first occurence it found, e.g.

loop 2 rGG = B1, but it finds the cell.Value in cell B366 and sets rGG accordingly.
loop 2 rGG = B366, but it does not find the net occurence of cell.Value (even though there are definitely 3 occurences as I've manually checked this) and rGG still = B366.

Any ideas on why this is happening?

(Apologies for any sloppy coding but I haven't had a chance to clean it up yet...)

Cheers
Shane

Rich (BB code): 
'get each app id's cost per environment and place in Summary wsheet
Dim rGG As Range
Set rGG = Range("B1")
iProd = 0
iProdH = 0
iCont = 0
iContH = 0
iDev = 0
iDevH = 0
iUat = 0
iUatH = 0
iQa = 0
iQaH = 0
iArch = 0
iArchH = 0
iNone = 0
iNoneH = 0
For Each cell In rSum
MsgBox cell.Row
        tWS.Activate
        x = WorksheetFunction.CountIf(tWS.Columns("B:B"), cell.Value)
'MsgBox x
        'Set rX = Range("B1")
        For lCount = 1 To x
        MsgBox "lCount " & lCount
 
        MsgBox "rGG.address " & rGG.Address
            Set rGG = tWS.Columns("B:B").Find(What:=cell.Value, After:=rGG, LookIn _
                :=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection _
                :=xlNext, MatchCase:=False, SearchFormat:=False)
            MsgBox "rGG.address " & rGG.Address
 
            If rX Is Nothing Then
                iNone = iNone + rGG.Cells(1, 3).Value
                iNoneH = iNoneH + iNoneH
            Else
            'MsgBox lCount
                If Trim(rGG.Cells(1, 2).Value) = "Production" Then
                'MsgBox 1
                    iProd = iProd + rGG.Cells(1, 3).Value
                    iProdH = iProdH + 1
                ElseIf Trim(rGG.Cells(1, 2).Value) = "Pre-Production" Then
                'MsgBox 2
                    iProd = iProd + rGG.Cells(1, 3).Value
                    iProdH = iProdH + 1
                ElseIf Trim(rGG.Cells(1, 2).Value) = "Contingency" Then
                'MsgBox 3
                    iCont = iCont + rGG.Cells(1, 3).Value
                    iContH = iContH + 1
                ElseIf Trim(rGG.Cells(1, 2).Value) = "Pre-Contingency" Then
                'MsgBox 4
                    iCont = iCont + rGG.Cells(1, 3).Value
                    iContH = iContH + 1
                ElseIf Trim(rGG.Cells(1, 2).Value) = "Development" Then
                'MsgBox 5
                    iDev = iDev + rGG.Cells(1, 3).Value
                    iDevH = iDevH + 1
                ElseIf Trim(rGG.Cells(1, 2).Value) = "Pre-Development" Then
                'MsgBox 6
                    iDev = iDev + rGG.Cells(1, 3).Value
                    iDevH = iDevH + 1
                ElseIf Trim(rGG.Cells(1, 2).Value) = "UAT" Then
                'MsgBox 7
                    iUat = iUat + rGG.Cells(1, 3).Value
                    iUatH = iUatH + 1
                ElseIf Trim(rGG.Cells(1, 2).Value) = "Pre-UAT" Then
                'MsgBox 8
                    iUat = iUat + rGG.Cells(1, 3).Value
                    iUatH = iUatH + 1
                ElseIf Trim(rGG.Cells(1, 2).Value) = "Test/Contingency" Then
                'MsgBox 9
                    iUat = iUat + rGG.Cells(1, 3).Value
                    iUatH = iUatH + 1
                ElseIf Trim(rGG.Cells(1, 2).Value) = "Lab/Test" Then
                'MsgBox 10
                    iUat = iUat + rGG.Cells(1, 3).Value
                    iUatH = iUatH + 1
                ElseIf Trim(rGG.Cells(1, 2).Value) = "QA" Then
                'MsgBox 11
                    iQa = iQa + rGG.Cells(1, 3).Value
                    iQaH = iQaH + 1
                ElseIf Trim(rGG.Cells(1, 2).Value) = "QA/Contingency" Then
                'MsgBox 12
                    iQa = iQa + rGG.Cells(1, 3).Value
                    iQaH = iQaH + 1
                ElseIf Trim(rGG.Cells(1, 2).Value) = "Archive" Then
                'MsgBox 13
                    iArch = iArch + rGG.Cells(1, 3).Value
                    iArchH = iArchH + 1
                ElseIf Trim(rGG.Cells(1, 2).Value) = "" Then
                'MsgBox 14
                    iNone = iNone + rGG.Cells(1, 3).Value
                    iNoneH = iNoneH + 1
                End If
            End If
 
        Next lCount
 
        Set rGG = Range("B1")
 
        MsgBox "iProd " & iProd
        MsgBox "iProdH " & iProdH
        MsgBox "iCont " & iCont
        MsgBox "iContH " & iContH
        MsgBox "iDev " & iDev
        MsgBox "iDevH " & iDevH
        MsgBox "iUat " & iUat
        MsgBox "iUatH " & iUatH
        MsgBox "iQa " & iQa
        MsgBox "iQaH " & iQaH
        MsgBox "iArch " & iArch
        MsgBox "iArchH " & iArchH
        MsgBox "iNone " & iNone
        MsgBox "iNoneH " & iNoneH
 
        sWS.Cells(cell.Row, 3).Value = iProd
        sWS.Cells(cell.Row, 4).Value = iProdH
        sWS.Cells(cell.Row, 5).Value = iCont
        sWS.Cells(cell.Row, 6).Value = iContH
        sWS.Cells(cell.Row, 7).Value = iDev
        sWS.Cells(cell.Row, 8).Value = iDevH
        sWS.Cells(cell.Row, 9).Value = iUat
        sWS.Cells(cell.Row, 10).Value = iUatH
        sWS.Cells(cell.Row, 11).Value = iQa
        sWS.Cells(cell.Row, 12).Value = iQaH
        sWS.Cells(cell.Row, 13).Value = iArch
        sWS.Cells(cell.Row, 14).Value = iArchH
        sWS.Cells(cell.Row, 15).Value = iNone
        sWS.Cells(cell.Row, 16).Value = iNoneH
 
        iProd = 0
        iProdH = 0
        iCont = 0
        iContH = 0
        iDev = 0
        iDevH = 0
        iUat = 0
        iUatH = 0
        iQa = 0
        iQaH = 0
        iArch = 0
        iArchH = 0
        iNone = 0
        iNoneH = 0
 
    x = 0
 
Next cell
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Sort by date Sort by votes
I can't immediately fathom what that code is supposed to do. However, consider using the FindNext method. Here's an example: It finds all occurrences and highlights them in yellow:

Code: 
Sub FindHighlight()
Dim tempcell As Range, Found As Range, sTxt, FoundRange As Range, Response As Integer
Set Found = Range("A1")
sTxt = InputBox(prompt:="Enter value for search", Title:="VoG's Finder")
If sTxt = "" Then Exit Sub
Set tempcell = Cells.Find(What:=sTxt, After:=Found)
If tempcell Is Nothing Then
    MsgBox prompt:="Not found", Title:="VoG's Finder"
    Exit Sub
Else
    Set Found = tempcell
    Set FoundRange = Found
End If
Do
    Set tempcell = Cells.FindNext(After:=Found)
    If Found.Row >= tempcell.Row Then Exit Do
    Set Found = tempcell
    Set FoundRange = Application.Union(FoundRange, Found)
Loop
FoundRange.Interior.ColorIndex = 6
Response = MsgBox(prompt:="Clear highlighting", Title:="VoG's Finder", Buttons:=vbOKCancel + vbQuestion)
If Response = vbOK Then FoundRange.Interior.ColorIndex = xlNone
End Sub
 
Upvote 0
Hi Peter

I've added comments to the code below (in bold red) to descibe what I'm trying to ahceive with the code.

Is there a way of posting worksheets as images into these posts to maybe help explain a bit better than I can with text?

Cheers
Shane

Rich (BB code): 
'get each app id's cost per environment and place in Summary wsheet
Dim rGG As Range
Set rGG = Range("B1")
iProd = 0
iProdH = 0
iCont = 0
iContH = 0
iDev = 0
iDevH = 0
iUat = 0
iUatH = 0
iQa = 0
iQaH = 0
iArch = 0
iArchH = 0
iNone = 0
iNoneH = 0
rSum is a range of unique application ID's. I want to loop through each application ID in that range and...
For Each cell In rSum
MsgBox cell.Row
        tWS.Activate
count the number of occurences of each application ID in column B of a worksheet, because I want to...
 
        x = WorksheetFunction.CountIf(tWS.Columns("B:B"), cell.Value)
'MsgBox x
        'Set rX = Range("B1")
        For lCount = 1 To x
        MsgBox "lCount " & lCount
 
        MsgBox "rGG.address " & rGG.Address
find each occurence in column B of that temporary worksheet and...
            Set rGG = tWS.Columns("B:B").Find(What:=cell.Value, After:=rGG, LookIn _
                :=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection _
                :=xlNext, MatchCase:=False, SearchFormat:=False)
            MsgBox "rGG.address " & rGG.Address
 
            If rX Is Nothing Then
                iNone = iNone + rGG.Cells(1, 3).Value
                iNoneH = iNoneH + iNoneH
            Else
            'MsgBox lCount
identify which environment (Prod, UAT, DEV etc.) the hostname associated with that occurence of the application ID is in.
 
depending upon which environment it is in i want to add the monthly cost associated with that occurence (row, basically) to a summary sheet in the row associated with that application ID.
 
i want to do this for every occurence of the application ID in the temporary sheet (hence the x = WorksheetFunction.CountIf(tWS.Columns("B:B"), cell.Value) and then move on to the next unique application ID in the rSUM range.
 
at the same time I want to count the number of hostnames associated with that application ID and which environment they are in and past the total intot he summary sheet for that application ID.
 
                If Trim(rGG.Cells(1, 2).Value) = "Production" Then
                'MsgBox 1
                    iProd = iProd + rGG.Cells(1, 3).Value
                    iProdH = iProdH + 1
                ElseIf Trim(rGG.Cells(1, 2).Value) = "Pre-Production" Then
                'MsgBox 2
                    iProd = iProd + rGG.Cells(1, 3).Value
                    iProdH = iProdH + 1
                ElseIf Trim(rGG.Cells(1, 2).Value) = "Contingency" Then
                'MsgBox 3
                    iCont = iCont + rGG.Cells(1, 3).Value
                    iContH = iContH + 1
                ElseIf Trim(rGG.Cells(1, 2).Value) = "Pre-Contingency" Then
                'MsgBox 4
                    iCont = iCont + rGG.Cells(1, 3).Value
                    iContH = iContH + 1
                ElseIf Trim(rGG.Cells(1, 2).Value) = "Development" Then
                'MsgBox 5
                    iDev = iDev + rGG.Cells(1, 3).Value
                    iDevH = iDevH + 1
                ElseIf Trim(rGG.Cells(1, 2).Value) = "Pre-Development" Then
                'MsgBox 6
                    iDev = iDev + rGG.Cells(1, 3).Value
                    iDevH = iDevH + 1
                ElseIf Trim(rGG.Cells(1, 2).Value) = "UAT" Then
                'MsgBox 7
                    iUat = iUat + rGG.Cells(1, 3).Value
                    iUatH = iUatH + 1
                ElseIf Trim(rGG.Cells(1, 2).Value) = "Pre-UAT" Then
                'MsgBox 8
                    iUat = iUat + rGG.Cells(1, 3).Value
                    iUatH = iUatH + 1
                ElseIf Trim(rGG.Cells(1, 2).Value) = "Test/Contingency" Then
                'MsgBox 9
                    iUat = iUat + rGG.Cells(1, 3).Value
                    iUatH = iUatH + 1
                ElseIf Trim(rGG.Cells(1, 2).Value) = "Lab/Test" Then
                'MsgBox 10
                    iUat = iUat + rGG.Cells(1, 3).Value
                    iUatH = iUatH + 1
                ElseIf Trim(rGG.Cells(1, 2).Value) = "QA" Then
                'MsgBox 11
                    iQa = iQa + rGG.Cells(1, 3).Value
                    iQaH = iQaH + 1
                ElseIf Trim(rGG.Cells(1, 2).Value) = "QA/Contingency" Then
                'MsgBox 12
                    iQa = iQa + rGG.Cells(1, 3).Value
                    iQaH = iQaH + 1
                ElseIf Trim(rGG.Cells(1, 2).Value) = "Archive" Then
                'MsgBox 13
                    iArch = iArch + rGG.Cells(1, 3).Value
                    iArchH = iArchH + 1
                ElseIf Trim(rGG.Cells(1, 2).Value) = "" Then
                'MsgBox 14
                    iNone = iNone + rGG.Cells(1, 3).Value
                    iNoneH = iNoneH + 1
                End If
            End If
 
        Next lCount
 
        Set rGG = Range("B1")
 
        MsgBox "iProd " & iProd
        MsgBox "iProdH " & iProdH
        MsgBox "iCont " & iCont
        MsgBox "iContH " & iContH
        MsgBox "iDev " & iDev
        MsgBox "iDevH " & iDevH
        MsgBox "iUat " & iUat
        MsgBox "iUatH " & iUatH
        MsgBox "iQa " & iQa
        MsgBox "iQaH " & iQaH
        MsgBox "iArch " & iArch
        MsgBox "iArchH " & iArchH
        MsgBox "iNone " & iNone
        MsgBox "iNoneH " & iNoneH
 
        sWS.Cells(cell.Row, 3).Value = iProd
        sWS.Cells(cell.Row, 4).Value = iProdH
        sWS.Cells(cell.Row, 5).Value = iCont
        sWS.Cells(cell.Row, 6).Value = iContH
        sWS.Cells(cell.Row, 7).Value = iDev
        sWS.Cells(cell.Row, 8).Value = iDevH
        sWS.Cells(cell.Row, 9).Value = iUat
        sWS.Cells(cell.Row, 10).Value = iUatH
        sWS.Cells(cell.Row, 11).Value = iQa
        sWS.Cells(cell.Row, 12).Value = iQaH
        sWS.Cells(cell.Row, 13).Value = iArch
        sWS.Cells(cell.Row, 14).Value = iArchH
        sWS.Cells(cell.Row, 15).Value = iNone
        sWS.Cells(cell.Row, 16).Value = iNoneH
 
        iProd = 0
        iProdH = 0
        iCont = 0
        iContH = 0
        iDev = 0
        iDevH = 0
        iUat = 0
        iUatH = 0
        iQa = 0
        iQaH = 0
        iArch = 0
        iArchH = 0
        iNone = 0
        iNoneH = 0
 
    x = 0
 
Next cell
 
Upvote 0
Thanks Peter

I copied the code into another workbook and it ran fine, so i copied back into the original and it ran fine there too, go figure...... :confused:

thanks for your time though, you're a gent!
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,214,861
Messages
6,121,971
Members
449,059
Latest member
oculus

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top