Check values in range of cells

[KGee]

I want to add two separate command buttons to a worksheet, one for "team A" and one for "team B", but each button will essentially serve the same function. I need the buttons to check the contents of the cells in column L and build a variable that contains the contents from cells D, E, F, & G of the same row if a certain value exists. The data to be reviewed will always start in cell L5 but the end cell in column will vary in each file.

I have working code to get the last cell in the sheet and define the range of cells to test in column L. I'm having issues defining a loop to check each of the cells and build the variable.

For simplicity, if L5 = "A", then I want to write the contents from D5-G5 to my variable and check the next cell in the range. If L5 <> "A" I want to skip it and check the next cell in the range.

This is my rough code:

myTempVar = ""
myFinalVar = ""
 
For Each y in myRange
 If y = "A" Then
  ' do something to get data from cells D-G here (need to separate values with a comma or similar)
 
  ' append the temp variable to the final variable
  myFinalVar = myFinalVar & myTempVar & Chr(13)
 End If
Next y

 

[Jerry Sullivan]

Here is one way to build your string for each row that meets the criteria
and then append your final string.

Dim myTempVar, myFinalVar
Dim i As Long, lngOffset As Long
Dim lngOffsetStart As Long, lngOffsetEnd As Long
lngOffsetStart = Range("D1").Column - Range("L1").Column
lngOffsetEnd = Range("G1").Column - Range("L1").Column
For Each y In myRange
   If y = "A" Then
        'do something to get data from cells D-G here
        ' (need to separate values with a comma or similar)
        For lngOffset = lngOffsetStart To lngOffsetEnd
            myTempVar = myTempVar & y.Offset(0, lngOffset) & ","
        Next lngOffset
        If Len(myTempVar) > 0 Then
            myTempVar = Left(myTempVar, Len(myTempVar) - 1)
        End If
        ' append the temp variable to the final variable
        myFinalVar = myFinalVar & myTempVar & Chr(13)
        myTempVar = ""
    End If
Next y
MsgBox myFinalVar

Depending on what you want to do with this myFinalVar value,
you might consider using a 2 dimensional Array to hold the values
instead of Comma-Separated Columns and Chr(13) Separated Rows.

 

[KGee]

Hi Jerry,

Can you elaborate a bit on the array vs. this approach? The data being collected needs to be sent to a third department who will provide the backup detail needed in order for team A/B to complete their review. My plan was to send an email notification to the third department with the information being requested. And I would either put the information directly in the email or write it to a text file and attach to the email. I'm using code modified from Ron de Bruin's site for sending the emails from Excel.