Excel Macro Question 2

[SysAdminMike]

I have a macro I wrote to sort through some data I have (see below)

Sub FreeGVMCounter()

Dim s1 As Worksheet
Dim s2 As Worksheet
Dim row, host, count, total As Integer

Set s1 = ActiveWorkbook.ActiveSheet
Set s2 = ActiveWorkbook.Sheets("Overview")

For i = 2 To 600 Step 1
    host = 0
    count = 0
    Do While s1.Cells(i + host, 1) <> ""
        If s1.Cells(i + host, 4) = "gvm-free" Then
            count = count + 1
        End If
        host = host + 1
    Loop
    
    
    If host <> 0 Then
        s1.Cells(i + host, 4) = "Available GVMs"
        s1.Cells(i + host, 5) = count
        i = i + host
    End If
Next i

If s1 = ActiveWorkbook.Sheets("Loc1") Then
    s2.Cells(4, 5) = total
ElseIf ActiveWorkbook.Sheets("Loc2") Then
    s2.Cells(5, 5) = total
End If


End Sub

Everything worked perfectly UNTIL I added

If s1 = ActiveWorkbook.Sheets("Loc1") Then
    s2.Cells(4, 5) = total
ElseIf ActiveWorkbook.Sheets("Loc2") Then
    s2.Cells(5, 5) = total
End If

at the end. Any ideas?

Thanks,

Michael

 

[SkipVought]

hi,

Dim s1 As Worksheet
Dim s2 As Worksheet
Dim row as integer, host as integer, count as integer, total As Integer

'...
'[b]you NEVER assign total!!![/b]

If s1.name = "Loc1" Then
    s2.Cells(4, 5).value = total
ElseIf s1.name = "Loc2" Then
    s2.Cells(5, 5).value = total
End If

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[combo]

Skip's method is simplest, but coming back to your code, it fails due to wrong operator applied for objects comparison , should be:

If s1 [!]is[/!] ActiveWorkbook.Sheets("Loc1") Then

Are you sure that the 'For' and 'Do While' loops work properly? For some data structures the result is overwritten and final 'count' is 1. All can be counted in one 'For...Next' loop.


combo

 

[SysAdminMike]

thanks guys

Thanks,

Michael