Update cell based on several criteria using VBA

I tried under the formula forum and it was suggested that the best solution would be VBA... so I'm going to try here...

I'm trying to automate a column for the users where a "Type" is updated based on the criterion below... This would really save them a lot of time as they deal with over 200 rows per file and they update the type per each manually - this is daily as its changes frequently... please refer to the attachment

Criteria:
IF columns E or F have a total greater than $0, enter MDE in the corresponding row in column D
IF column D already has MDE in the cell because columns E or F calculated 1st to update it, and any column from G - N has a total less than or greater than $0, leave as MDE
IF column D has no MDE because columns E or F equals to $0, than update as CR if row 4 has a CR number (the CR number is unique)
IF column D has no MDE because columns E or F equals to $0, than update as Other if row 4 has an Other (Other may appear more than 1 time)
If any of the columns have $0 and row 4 is empty (in this case J - N, do nothing


Note: columns E or F need to be precede the calculation (be calculated 1st) for column D before the other 4...

For your second criteria, what would the other option be (e.g. "D" already contains MDE, but all of the other columns are 0)?

Also what you you mean by

IF column D has no MDE because columns E or F equals to $0, than update as CR if row 4 has a CR number (the CR number is unique)
IF column D has no MDE because columns E or F equals to $0, than update as Other if row 4 has an Other (Other may appear more than 1 time)

Doesn't your row 4 contain "Other" as well as 2 CR Numbers?

I understand the goal is to update column "D", and how you can update it based on the values in columns "E" and "F" in the same row (e.g. criteria 1). What is confusing me is how you would update a cell in column "D" based on what is in row 4 (do you search through all of row 4? Do you look at the heading of row 4 for the non-blank entry in the row of interest?).

Since the code will 1st look at columns E or F and the each respective column has a total greater than $0, it will enter MDE in column D in those rows. Next, it will scan row 4 and column G is next. If that is a numerical (a CR number), than on that column if there are total less / greater than $0, enter CR in column D only on empty cells, those that do not have an MDE already populated. However, if when it scans row 4 and it's Other, do the same... that same will continue thru column N. From G - N, row for will either have a CR# or Other... hope this clarifies my goal...thx

If I'm understanding you correctly, this should do what you're asking.

Sub sort()
Dim last_row As Integer
Dim i As Integer
Dim j As Integer
last_row = Cells(Rows.Count, 2).End(xlUp).Row
For i = 5 To last_row
If Cells(i, 5).Value + Cells(i, 6).Value > 0 Then Cells(i, 3).Value = "MDE"
Next i
For j = 7 To 14
If IsNumeric(Cells(4, j)) Then
For i = 5 To last_row
If Cells(i, j).Value <> 0 And Cells(i, 3).Value <> MDE Then Cells(i, 3).Value = "CR"
Next i
End If
If Cells(4, j).Value = "Other" Then
For i = 5 To last_row
If Cells(i, j).Value <> 0 And Cells(i, 3).Value <> MDE Then Cells(i, 3).Value = "Other"
Next i
End If
Next j
End Sub

My last question: I simply ignored your last criteria, as it is not currently affecting the code. However, is it possible that in your data you have a situation where row 4 is blank but not all the values in the column are 0? And if yes, what should the outcome be in that situation?

Daniel -
No, that situation will and should not happen... I did however, forget one more "DDE". I would think it's would I would just copy the from "Next i" to "End if", correct? thx

That's correct just like this

Sub sort()
Dim last_row As Integer
Dim i As Integer
Dim j As Integer
last_row = Cells(Rows.Count, 2).End(xlUp).Row
For i = 5 To last_row
If Cells(i, 5).Value + Cells(i, 6).Value > 0 Then Cells(i, 3).Value = "MDE"
Next i
For j = 7 To 14
If IsNumeric(Cells(4, j)) Then
For i = 5 To last_row
If Cells(i, j).Value <> 0 And Cells(i, 3).Value <> MDE Then Cells(i, 3).Value = "CR"
Next i
End If
If Cells(4, j).Value = "Other" Then
For i = 5 To last_row
If Cells(i, j).Value <> 0 And Cells(i, 3).Value <> MDE Then Cells(i, 3).Value = "Other"
Next i
End If
If Cells(4, j).Value = "DDE" Then
For i = 5 To last_row
If Cells(i, j).Value <> 0 And Cells(i, 3).Value <> MDE Then Cells(i, 3).Value = "DDE"
Next i
End If
Next j
End Sub

Also, it seems like MDE is "Priority #1" (you will not change this status no matter what). Is it possible you will have values in multiple other headings (e.g. Other AND CR)? If so, is there a priority system for these?

Oops just realized this was supposed to change column D not C. Use this instead.

Sub sort()
Dim last_row As Integer
Dim i As Integer
Dim j As Integer
last_row = Cells(Rows.Count, 2).End(xlUp).Row
For i = 5 To last_row
If Cells(i, 5).Value + Cells(i, 6).Value > 0 Then Cells(i, 4).Value = "MDE"
Next i
For j = 7 To 14
If IsNumeric(Cells(4, j)) Then
For i = 5 To last_row
If Cells(i, j).Value <> 0 And Cells(i, 4).Value <> MDE Then Cells(i, 4).Value = "CR"
Next i
End If
If Cells(4, j).Value = "Other" Then
For i = 5 To last_row
If Cells(i, j).Value <> 0 And Cells(i, 4).Value <> MDE Then Cells(i, 4).Value = "Other"
Next i
End If
If Cells(4, j).Value = "DDE" Then
For i = 5 To last_row
If Cells(i, j).Value <> 0 And Cells(i, 4).Value <> MDE Then Cells(i, 4).Value = "DDE"
Next i
End If
Next j
End Sub

I will follow this priority order:

1. MDE
2. CR #'s (these are unique)
3. Other (could be more than 1)
4. Lastly DDE

At the end, column D will have MDE, CR, Other or DDE populated in all cells up to where there is data in column C....

Ok give this a shot

Sub sort()
Dim last_row As Integer
Dim i As Integer
Dim j As Integer
last_row = Cells(Rows.Count, 2).End(xlUp).Row
For i = 5 To last_row
If Cells(i, 5).Value + Cells(i, 6).Value > 0 Then Cells(i, 4).Value = "MDE"
Next i
For j = 7 To 14
If IsNumeric(Cells(4, j)) Then
For i = 5 To last_row
If Cells(i, j).Value <> 0 And Cells(i, 4).Value <> "MDE" Then Cells(i, 4).Value = "CR"
Next i
End If
If Cells(4, j).Value = "Other" Then
For i = 5 To last_row
If Cells(i, j).Value <> 0 And Cells(i, 4).Value <> "MDE" And Cells(i, 4).Value <> "CR" Then Cells(i, 4).Value = "Other"
Next i
End If
If Cells(4, j).Value = "DDE" Then
For i = 5 To last_row
If Cells(i, j).Value <> 0 And Cells(i, 4).Value <> "MDE" And Cells(i, 4).Value <> "CR" And Cells(i, 4).Value <> "Other" Then Cells(i, 4).Value = "DDE"
Next i
End If
Next j
End Sub