Loop Problem

[PAPNIK]

Hello everyone,

I'm using the following to validate entries on a spreadsheet before saving:

If IsEmpty(Range("A2").Value) = False Then
If IsEmpty(Range("B2").Value) = True Or IsEmpty(Range("C2").Value) = True Or IsDate(Range("D2").Value) = False Or IsDate(Range("E2").Value) = Fale Or IsEmpty(Range("G2").Value) = True Or IsEmpty(Range("H2").Value) = True Or IsNumeric(Range("I2").Value) = False Then
MsgBox "One or more incorrect entries found in row 2."
Cancel = True
End If
End If

However, I cannot find the way to make a loop - Need this to run for each row.

I though of the following but it's not very practical..

If IsEmpty(Range("A2").Value) = False Then
If IsEmpty(Range("B2").Value) = True Or IsEmpty(Range("C2").Value) = True Or IsDate(Range("D2").Value) = False Or IsDate(Range("E2").Value) = Fale Or IsEmpty(Range("G2").Value) = True Or IsEmpty(Range("H2").Value) = True Or IsNumeric(Range("I2").Value) = False Then
MsgBox "One or more incorrect entries found in row 2."
Cancel = True
End If
End If

If IsEmpty(Range("A3").Value) = False Then
If IsEmpty(Range("B3").Value) = True Or IsEmpty(Range("C3").Value) = True Or IsDate(Range("D3").Value) = False Or IsDate(Range("E3").Value) = Fale Or IsEmpty(Range("G3").Value) = True Or IsEmpty(Range("H3").Value) = True Or IsNumeric(Range("I3").Value) = False Then
MsgBox "One or more incorrect entries found in row 3."
Cancel = True
End If
End If

If IsEmpty(Range("A4").Value) = False Then
If IsEmpty(Range("B4").Value) = True Or IsEmpty(Range("C4").Value) = True Or IsDate(Range("D4").Value) = False Or IsDate(Range("E4").Value) = Fale Or IsEmpty(Range("G4").Value) = True Or IsEmpty(Range("H4").Value) = True Or IsNumeric(Range("I4").Value) = False Then
MsgBox "One or more incorrect entries found in row 4."
Cancel = True
End If
End If

Any help would be really appreciated. Sorry I just started looking into this today, for this project.

Best,
Nick

 

[Joe4]

Welcome to the Board!

For rows with data, is there some column we can use to determine where the last row of data resides?
Since you seem to be checking columns A, B, and C to see if they are empty, I am guessing that those columns could have blanks in them for rows with data, so they may not be a good candidates to use.

 

[PAPNIK]

Hello and thank you!

I actually need B & C with data. What I need to check is that if A is not empty, all cells, from B to I, on the same row are filled out correctly.

The last row of data could be the 50th or so. I will lock the file once I figure this out so there won't be any issues on this.

 

[Joe4]

No, that is not what I am asking.
We want our code to dynamically find our last row we want to apply this against.
Typically, we do that by identifying some column that will ALWAYS have data in it for every row of that we want to check.
I am asking you what column that is.

For example, for every row with data that we want to check, maybe columns A, B, or C may be blank, but column D will ALWAYS have data.
Then column D would be the best column for us to use to dynamically identify exactly what our last row of data is.

 

[PAPNIK]

Hello,

Thank your for your reply and patience, lol

No, there isn't a column that will always have data.

This is something like a logbook, where users will need to fill out the rows. I just want to make sure that if they start filling in an entry (row), they will not be able to finish/save the file unless all required fields are filled out. If A% has values then B%:I% must also have values. However, the end user will also need to fill in A%.

Hope that gives you a better understanding of what I am trying to do here.

 

[Joe4]

Hello,

Thank your for your reply and patience, lol

No, there isn't a column that will always have data.

This is something like a logbook, where users will need to fill out the rows. I just want to make sure that if they start filling in an entry (row), they will not be able to finish/save the file unless all required fields are filled out. If A% has values then B%:I% must also have values. However, the end user will also need to fill in A%.

Hope that gives you a better understanding of what I am trying to do here.

Would they/could they ever start filling out a row skipping over column A and starting in another column?

Big picture, seems like an Entry Form might be a better option here, where you can force the user to fill out all necessary fields before submitting and populating data on worksheet.

 

[Joe4]

See if something like this works for you:

    Dim lr As Long
    Dim r As Long
    
'   Find last row in column with Data
    lr = ActiveSheet.UsedRange.Rows(ActiveSheet.UsedRange.Rows.Count).Row
    
'   Loop through all rows
    For r = 2 To lr
        If IsEmpty(Cells(r, "A").Value) = False Then
            If IsEmpty(Cells(r, "B").Value) = True Or IsEmpty(Cells(r, "C").Value) = True _
                    Or IsDate(Cells(r, "D").Value) = False Or IsDate(Cells(r, "E").Value) = False _
                    Or IsEmpty(Cells(r, "G").Value) = True Or IsEmpty(Cells(r, "H").Value) = True _
                    Or IsNumeric(Cells(r, "I").Value) = False Then
                MsgBox "One or more incorrect entries found in row " & r
                Cancel = True
            End If
        End If
    Next r

 

[PAPNIK]

Thanks so much! that was life-saving haha.

I though of an entry form too, but this was they will be able to copy-paste the data required from other sources - and will also be forced to ensure accuracy.

Thanks again!

Best,
Nick

 

[Joe4]

You are welcome.
Glad I was able to help!