VBA Nested If Statements to ensure Valid Input

[Roberto2712]

Hi all,


I have been working on a dashboard which will, dependent on a yes/no indicator, require updates from a given user.


The dependent variable is in column B, with about 2000 rows. These cells have drop downs with two choices: "No Changes" and "Update Result". "No Changes" is the default.


If a user selects "Update Results" in a given row (lets say "B9"), they must populate the data in Cells: "J9", "K9", "L9", "M9".


To enforce this, I have placed a "Validate My Input" button at the top of the spreadsheet, before the user uploads the spreadsheet, they must click the button. I want the button to loop through column B and locate any cells containing "Update Results". If a cell containing this string is located, the macro should check the corresponding cells (in columns J-N) to ensure the user has populated the cells.


If they have not done so, I would like a message to be generated in cell "D4" stating "For any updated result, please populate all mandatory fields".


The code I have is here:


Dim deleteRow as Long
Dim ws as Worksheet
Dim ws = Sheets ("Rules Results Template")
For deleteRow = ws.Range("B" & Rows.Count).End(xlUp).Row to 8 Step -1
If ws.Range("B" & deleteRow).Value = "No Change" Then
ws.Range("D4") = ("Data Entered is Valid - Please Upload the Template")
Elseif ws.Range("B" & deleteRow).Value<>"No Change" Then
ws.Range("D4") = ("Please update all mandatory fields").
End if
Next deleteRow
End Sub




I'm not far off, however, I have not managed to create functionality which would check the corresponding rows (J-N) for correct data.


Does anybody have any ideas?(Workarounds would be appreciated either!). Tis a tricky one!


Thanks

 

[Nine Zero]

Hello,

How about something like this

[COLOR=#333333]Dim deleteRow as Long[/COLOR]
[COLOR=#333333]Dim ws as Worksheet[/COLOR]
[COLOR=#333333]Dim ws = Sheets ("Rules Results Template")[/COLOR]
[COLOR=#333333]For deleteRow = ws.Range("B" & Rows.Count).End(xlUp).Row to 8 Step -1 [/COLOR]
[COLOR=#333333]If ws.Range("B" & deleteRow).Value = "No Change" Then[/COLOR]
[COLOR=#333333]ws.Range("D4") = ("Data Entered is Valid - Please Upload the Template")[/COLOR]
[COLOR=#333333]Elseif ws.Range("B" & deleteRow).Value<>"No Change" Then[/COLOR]
[COLOR=#333333]ws.Range("D4") = ("Please update all mandatory fields").
[/COLOR][COLOR=#ff0000][B]Elseif ws.Range("B" & deleteRow).Offset(,8).Value="" Or _
ws.Range("B" & deleteRow).Offset(,9).Value="" Or _
[/B][B]ws.Range("B" & deleteRow).Offset(,10).Value="" Or _
[/B][B]ws.Range("B" & deleteRow).Offset(,11).Value="" Or _
[/B][B]ws.Range("B" & deleteRow).Offset(,12).Value="" Then
[/B][B]ws.Range("D4").Value = "Update Required Fields"[/B][/COLOR]
[COLOR=#333333]End if[/COLOR]
[COLOR=#333333]Next deleteRow[/COLOR]
[COLOR=#333333]End Sub[/COLOR]

 

[Roberto2712]

Hey - thanks for coming back. This is along the lines of what I need. Unfortunately, there is one setback:

If B8 = "Update Result" and fields J8-M8 are not populated. This gives the desired result.

However, if for instance, B9 = "Update Result", the macro does not seem to look at J9-M9 to ensure these fields are populated.

Is there anyway I could expand this method to the entire table and not just to row 8?

Thanks very much for the help