Solved check a column in each row and capture each unique value

April 29, 2019 at 18:03:57
Specs: Windows 7
I have the following code that loops through each row and executes code to change values. I also want it to capture the processor's name located in column C for each row but I don't want duplicate names. I will then use those names for an automated email code I have prepared. I don't know how to capture the names into a variable for each row without duplicates.
Dim x as Integer
Application.ScreenUpdating = False
      NumRows = Range("A2", Range("A2").End(xlDown)).Rows.Count
      Range("A2").Select
      For x = 2 To NumRows + 1
         Range("A" & x).Validation.Delete
         If ActiveSheet.Range("T" & x).Value > 1 Then ActiveSheet.Range("G" & x).Value = "YES" ' Else
         If ActiveSheet.Range("T" & x).Value < 1 Then ActiveSheet.Range("G" & x).Value = "NO" ' Else

'HERE IS WHERE I WANT TO ADD THE CODE I NEED HELP WITH
      ActiveCell.Offset(1, 0).Select
      Next
      Application.ScreenUpdating = True

message edited by mecerrato


See More: check a column in each row and capture each unique value

Reply ↓  Report •

#1
April 29, 2019 at 20:19:15
So many questions have I...

      Range("A2").Select

Why are you selecting A2?

         If ActiveSheet.Range("T" & x).Value > 1 Then ActiveSheet.Range("G" & x).Value = "YES" ' Else
         If ActiveSheet.Range("T" & x).Value < 1 Then ActiveSheet.Range("G" & x).Value = "NO" ' Else

What happens the value in Column T equals 1?

Why not use this: (one less test for VBA to perform and easier to read

           If ActiveSheet.Range("T" & x).Value > 1 Then
              ActiveSheet.Range("G" & x).Value = "YES"
           Else
              ActiveSheet.Range("G" & x).Value = "NO"
           End If

re: "I don't know how to capture the names into a variable for each row without duplicates."

Use an Array to store the names, then loop through the array, creating an email for each element in the array. The COUNTIF function can be used to prevent duplicates.

The code will essentially do the following by using the COUNTIF formula and looking "up" as it evaluates each name.

      C		
2    Tom      COUNTIF($C$2:C2,C2) = 1        Add name to Array
3    Bill     COUNTIF($C$2:C3,C3) = 1        Add name to Array
4    Sue      COUNTIF($C$2:C4,C4) = 1        Add name to Array
5    Tom      COUNTIF($C$2:C5,C5) = 2        Don't add name to Array
6    Beth     COUNTIF($C$2:C6,C6) = 1        Add name to Array

Sub SomeCode()
Dim x As Integer

'Declare dynamic array to hold names
Dim nameArray()

'Initialize variable for array
'The first element in the array should be 2 to match your first row.
pNum = 1

'Your uncommented code
    Application.ScreenUpdating = False
      NumRows = Range("A2", Range("A2").End(xlDown)).Rows.Count
'      Range("A2").Select 'Why?
      For x = 2 To NumRows + 1
         Range("A" & x).Validation.Delete
           If ActiveSheet.Range("T" & x).Value > 1 Then
              ActiveSheet.Range("G" & x).Value = "YES"
           Else
              ActiveSheet.Range("G" & x).Value = "NO"
           End If

'At each name, use COUNTIF to look upwards and see if the name already exists.
'If not, increment the array element number, resize the array and add the name
         If WorksheetFunction.CountIf(Range("C2:C" & x), Range("C" & x)) < 2 Then
             pNum = pNum + 1
             ReDim Preserve nameArray(pNum)
             nameArray(pNum) = Range("C" & x)
         End If
      Next

'Display the array (or create emails)
        For pName = 2 To pNum
          MsgBox nameArray(pName)
        Next
    Application.ScreenUpdating = True
End Sub

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

#2
April 30, 2019 at 04:54:05
Thank you, this worked great except I couldn't figure out how to capture the names in a list. Is there way to create 1 variable that captures the string of all names as it loops like this "name1; name2; name3; name4;". If I am able to do that I can assign that variable to the .to section of my email code.

With OutMail
    .To = "Mario Cerrato"
    '.cc = ProcessorNames
    .Subject = "FireDrill Report"
    .HTMLBody = "<BODY style=font-size:11pt;font-family:Calibri>" & "</p>" & _
    strbody & RangetoHTML(Rng) & Signature
    .Display
End With

message edited by mecerrato


Reply ↓  Report •

#3
April 30, 2019 at 07:54:34
✔ Best Answer
Your OP said:

I don't know how to capture the names into a variable for each row without duplicates.

"...a variable for each row" sounds to me like, well. a variable for each row, which is why I suggested the Array. Each element in the array is essentially a separate variable.

If you actually want "one variable that contains all unique names separated by a semi-colon", then you don't need an array.

Sub SomeCode()
Dim x As Integer

'Your uncommented code
    Application.ScreenUpdating = False
      NumRows = Range("A2", Range("A2").End(xlDown)).Rows.Count

      For x = 2 To NumRows + 1
         Range("A" & x).Validation.Delete
           If ActiveSheet.Range("T" & x).Value > 1 Then
              ActiveSheet.Range("G" & x).Value = "YES"
           Else
              ActiveSheet.Range("G" & x).Value = "NO"
           End If

'At each name, use COUNTIF to look upwards and see if the name already exists.
'If not, add name, semi-colon and space to temporary name list
         If WorksheetFunction.CountIf(Range("C2:C" & x), Range("C" & x)) < 2 Then
             nameList_tmp = nameList_tmp & Range("C" & x) & "; "
         End If
      Next
            
'Strip last semi-colon & space off of temp list
          nameList = Left(nameList_tmp, Len(nameList_tmp) - 2)
          
'Display the list (or create emails)
          MsgBox nameList
          
    Application.ScreenUpdating = True
End Sub

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

Related Solutions

#4
April 30, 2019 at 17:42:01
Thanks for the help it worked just beautifully :-)

Reply ↓  Report •

Ask Question