Getting String from cell and inserting it into a formula

**Davtho1983** · 06-19-2015, 05:25 AM

Hi in Excel I have a column of unique server names, a column of servers and a column of patches applied to servers. It looks something like the picture below.

First I need to go through each servername in the first column, and count the occurrences of that servername in the second column.

Then I need to put each servername into a COUNTIFS formula that counts the occurrences of a particular patch per servername - the one on my spreadsheet looks like this:

=COUNTIFS(A:A,"servername",C:C,"Patch 1")+COUNTIFS(A:A,"servername",C:C,"Patch 2")

I cannot seem to find a way to make VBA go down a list of undefined length of servernames, putting the servername into the COUNTIFS formula each time? I think I need to get the cell value as a string and insert that string into a formula, but I have no idea how.

**JoeFoot** · 06-19-2015, 06:05 PM

see if this is what you want:


Sub apply_patches()

Dim n_server As Integer
Dim n_patch As Integer
Dim i As Integer, j As Integer
Dim current_server As String
Dim current_patch As String
Dim server_formulas(100) As String          'assuming less than 100 serves

For i = 1 To 100        'assuming you have less than 100 servers. Can also use row count feature

        If Cells(i, 1) = "" Then Exit For   'assuming your list starts at row 1. If no then just adjust i = 1 to whatever row you need
        
        n_server = n_server + 1
        
        current_server = Cells(i, 1)
        
        n_patch = 0
        
        For j = 1 To 100        'assuming less than 100 patches. Can also use row count feature
                
                If Cells(j, 2) = "" Then Exit For   'assuming your patch  list starts at row 1. If no then just adjust j = 1 to whatever row you need
                
                
                
                If Cells(j, 2) = current_server Then
                'found a server match in the patch list
                                
                        n_patch = n_patch + 1
                                
                        current_patch = Cells(j, 3)
                        
                        If n_patch = 1 Then
                        'start the formula
                                
                                server_formulas(n_server) = "=COUNTIFS(B:B," & Chr(34) & current_server & Chr(34) & ",C:C," & Chr(34) & current_patch & Chr(34) & ")"
                        
                        Else
                        'continue the formula
                        
                                server_formulas(n_server) = server_formulas(n_server) & "+COUNTIFS(B:B," & Chr(34) & current_server & Chr(34) & ",C:C," & Chr(34) & current_patch & Chr(34) & ")"
                        
                        End If
                
                
                End If
                
                
        Next j
                
                
        'now output the formula to column E of the same sheet
        
        Cells(i, 5) = server_formulas(n_server)
                
Next i




End Sub

I might be confusing your request but it seems you want to count the number of patch occurences in each unique server. If so should your COUNTIFS be referencing "B:B" instead of "A:A"? Isn't columnA where you keep your unique names?

Or I might be missing something. See if this helps.