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
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