vendredi 23 août 2019

Looking for advice on which loop to use?

I've built a tool that produces another spreadsheet when the macro runs. In order for that sheet to be uploaded succesfully into a system, it needs to be formatted a particular way. To prevent people from not using the tool properly and messing up the formatting on the new spreadsheet, I've build a series of checks.

Each one checks if a cell has a particular string ("Please Set!") and if so, pops a msgbox and stops the macro. The idea being that it passes each check until it either hits one it fails, or passes them all and runs the code to produce the new sheet. It's a bit long-winded at the moment and I'm looking for a loop to make it neater.

If Worksheets("Input").Range("F7").Value = "Please Set!" Then MsgBox "Please complete all the fields!", vbCritical Exit Sub End If

If Worksheets("Input").Range("F9").Value = "Please Set!" Then MsgBox "Please complete all the fields!", vbCritical Exit Sub End If

If Worksheets("Input").Range("F13").Value = "Please Set!" Then MsgBox "Please complete all the fields!", vbCritical Exit Sub End If

If Worksheets("Input").Range("F17").Value = "Please Set!" Then MsgBox "Please complete all the fields!", vbCritical Exit Sub End If

If Worksheets("Input").Range("F21").Value = "Please Set!" Then MsgBox "Please complete all the fields!", vbCritical Exit Sub End If

If Worksheets("Input").Range("L9").Value = "Please Set!" Then MsgBox "Please complete all the fields!", vbCritical Exit Sub End If

If Worksheets("Input").Range("L13").Value = "Please Set!" Then MsgBox "Please complete all the fields!", vbCritical Exit Sub End If

If Worksheets("Input").Range("L17").Value = "Please Set!" Then MsgBox "Please complete all the fields!", vbCritical Exit Sub End If

If Worksheets("Input").Range("L21").Value = "Please Set!" Then MsgBox "Please complete all the fields!", vbCritical Exit Sub

There's an else after this and the code to produce the sheet.

Everything is working as intended but the above code is bulky as all hell and I know there's a better way. I've looked into different loops but I'm new to VBA so I'm not sure which one would be the best to condense this down into. Any help would be great! :)

Aucun commentaire:

Enregistrer un commentaire