I have been using the forums for a couple of weeks now while learning VBA. But now I am stuck and need help. So here is my problem:
I have a workbook with several worksheets in it. For simplicity let's assume that I have two worksheets. One with a lot of raw data material and one which is nicely formatted and will be the receiving sheet.
The raw data table looks like this: I have 8 columns and I need to check for two conditions in each row (columns B and E) and sum up the values in column H. Column B contains different names and column E contains a working status, e.g. ready or talking. I do have about 3.000 rows and a set of about 20 different names with different status. So overall I have, let's say 100 rows with the name "Smith" and the status "talking" in no particular order. I now need to sum up all the numbers (column H) in each of those rows where the name "Smith" and the status "talking" are put in. After that, I would like to have the name (column B), the status (column E) and the sum (column H), to be written into cells of the same workbook in a dynamic range, starting at I3:K3. I have to admit that I am totaly in the dark on this one.. I really hope that you guys can help me with some code and/or point me into the right direction. Most of all I hope that you could also explain what your code does, so I can understand it properly and learn. Thanks in advance guys!
Here is my code so far:
Private Sub SumNumbers() Dim tbl As Worksheet Dim x As Integer Dim lrow As Long Dim lastname As String Dim astatus As String Dim number As Integer Dim counter As Integer Set wb = ThisWorkbook Set tbl = wb.Sheets("Sheet1") lrow = tbl.Cells(Rows.Count, "B").End(xlUp).Row lastname = tbl.Cells("C2:C" & lrow).Text astatus = tbl.Cells("E2:E" & lrow).Text number = tbl.Cells("H2:H" & lrow) For x = 2 To lrow If lastname = "Smith" And astatus = "Talking" Then counter = counter + number End If Next x End SubAnswer1:
You're close, but you need to test each cell individually inside your loop. Something like this:
Sub Macro1() Dim tbl As Worksheet Dim x As Integer Dim lrow As Long Dim lastname As String Dim astatus As String Dim number As Integer Dim counter As Integer: counter = 3 Set wb = ThisWorkbook Set tbl = wb.Sheets("Sheet1") lrow = tbl.Cells(Rows.Count, "B").End(xlUp).Row For x = 2 To lrow If tbl.Range("C" & x) = "Smith" And tbl.Range("E" & x) = "Talking" Then Range("I" & counter).Value = Range("C" & x) Range("J" & counter).Value = Range("E" & x) Range("K" & counter).Value = Range("H" & x) counter = counter + 1 End If Next x End Sub<hr />
Per @Tim comment you can use SumIfs to return the total of 'H' column that meet multiple criteria like this:
I found a way that does the job for me so far, but this seems pretty messed up since I have to do it for every case manually. So now I am looking for a simpler and more elegant way to do this. If you could provide me with some code or point me into the right direction, I would highly appreciate it.
Here is my code so far:
For Each cell In Range("H2:H" & lrow) cell.Offset(0, 1).Value = cell.Value + cell.Offset(-1, 1).Value If cell.Offset(0, -6).Value = "Smith" And cell.Offset(0, -3).Value = "Talking" Then cell.Offset(0, 2).Value = cell.Value End If If cell.Offset(0, -6).Value = "Smith" And cell.Offset(0, -3).Value = "Ready" Then cell.Offset(0, 3).Value = cell.Value End If If cell.Offset(0, -6).Value = "Smith" And cell.Offset(0, -3).Value = "Not Ready" Then cell.Offset(0, 4).Value = cell.Value End If Next
As mentioned before, it is a workaround which I would like have to be simplified. It looks for the condition "Smith" and the status "Talking", "Ready" and "Not Ready" and puts the value of the H Column into the respective cells of the columns to it's right to be summed in the later process. So, is there any way to make that more elegant?Answer3:
Sure thing Portland Runner! My logic is simple. I've got list of recorded activities for each name with certain statusses. e.g. "Smith" (column B) and "Talking" (column C) and a number (column H). And I need to sum up the numbers in column H for every row where the conditions name and status are equal. Let's say I have 10k rows. 2k of them have the name "Smith" in it. But only 500 of them have the name "Smith" and the status "Talking". So I need to figure out a simpler way to sum up those numbers. There are also rows where the name is "Smith" but the status is "Ready". The difficulty here is, that those lines are basically in a random order.
So the table would look like this:
Row Name Status Number 2 Smith Talking 105 3 Smith Talking 67 4 Smith Ready 75 5 Smith Talking 94 6 Jones Ready 89 7 Jones Talking 224 8 Jones Not Ready 75 9 Jones Talking 99
So in this case row number 1 would be blocked for the headline and the data would start in row number 2. For "Smith" and "Talking" the code would need to sum the numbers of rows 2,3 and 5 for the condition "Smith" and "Talking". Rows 4 for "Smith" and "Ready". Rows 7 and 9 for "Jones" and "Talking" and so on.
What I can do, is create a new worksheet and put a list of all names in column A and a list of all statusses into column B and reference to it. That would save me the trouble of using the exact names and statusses in the code, but wouldn't solve my problem.Answer4:
A. Get all unique Name and place in a new column: this will be your array used to search each row.
B. Set up another array for Status since you know what the status could be.
C. Set up outer loop for Name Array<br /> For each item in array
D. Nested loop look at each row and search on each status
C. When status matches then another nested loop to add time and place in a new column called total time.