Check cell range contains data if adjacent cell is not blank

[Panda]

Hi There,

Basically I have a spreadsheet that a user enters in a serial number and inspection result and when they click a button it gets added to an archive. However, I want to try and ensure that the user can not enter a serial number in (G8) without having entered an inspection result (H8 to AA8). I have tried writing an if statement but think I have got the syntax incorrect;

[vba]If Range("G8").Value = vbNullString Or WorksheetFunction.CountA(Range("H8:AA8")) = 0 Then
MsgBox "You Have Not Entered an Inspection Result!", vbCritical, "Missing Data"
Range("G8").Select
Exit Sub
ElseIf Range("G9").Value = vbNullString Or WorksheetFunction.CountA(Range("H9:AA9")) = 0 Then
MsgBox "You Have Not Entered an Inspection Result!", vbCritical, "Missing Data"
Range("G9").Select
Exit Sub[/vba]
etc etc

However this only works if all 15 Serial Numbers have been entered into G8 to G22 and a corresponding inspection result has been entered into the adjacent columns (see attached spreadsheet). What I need it to do is only check that an inspection result has been entered if a Serial Number has been entered. If no Serial Number has been entered into the cell, then ignore the fact that an inspection result has not been entered.

Attached is a copy of the sheet that I am trying to add this to. Ignore the other codes, I haven’t attached the whole workbook as it is a fairly large file.

Sorry for the large explanation but can anybody help...please?

Thanks

[mbarron]

You could do something like this:
[vba]sub ButtonClick()
Dim i As Integer, strMiss As String
For i = 8 To 22
If Cells(i, 7) <> "" Then
If WorksheetFunction.CountA(Range(Cells(i, 8), Cells(i, 27))) = 0 Then
strMiss = strMiss & i & ", "
End If
End If
Next
If strMiss <> "" Then
MsgBox "You Have Not Entered an Inspection Result!" & vbLf & _
"Missing data in row(s) " & Left(strMiss, Len(strMiss) - 2), vbCritical, "Missing Data"
Else
MsgBox "You have NO missing data", vbInformation, "All required data is present"
End If
End Sub[/vba]

[Zack Barresse]

Why not just use data validation? Why go straight to VBA?

[Panda]

You could do something like this:
[vba]sub ButtonClick()
Dim i As Integer, strMiss As String
For i = 8 To 22
If Cells(i, 7) <> "" Then
If WorksheetFunction.CountA(Range(Cells(i, 8), Cells(i, 27))) = 0 Then
strMiss = strMiss & i & ", "
End If
End If
Next
If strMiss <> "" Then
MsgBox "You Have Not Entered an Inspection Result!" & vbLf & _
"Missing data in row(s) " & Left(strMiss, Len(strMiss) - 2), vbCritical, "Missing Data"
Else
MsgBox "You have NO missing data", vbInformation, "All required data is present"
End If
End Sub[/vba]

Thanks this works a treat, but i now have the problem of being able to log an inspection result without a serial number. Can this code be modified to check that a serial number has been added when an inspection result has been logged?

[Panda]

Why not just use data validation? Why go straight to VBA?

I thought about data validation but couldnt work out what to put in the custom formula box so thought that maybe this can only be done via vba. Can it it done via data validation?

[mbarron]

For the button.
[VBA]Sub Copy_New_Data_to_Archive()
Dim i As Integer, strMiss As String, strMiss2 As String
Dim strOut As String

For i = 8 To 22
If Cells(i, 7) <> "" Then
If WorksheetFunction.CountA(Range(Cells(i, 8), Cells(i, 27))) = 0 Then
strMiss = strMiss & i & ", "
End If
End If
Next
If strMiss <> "" Then
strOut = "You Have Not Entered an Inspection Result!" & vbLf & _
"Missing data in row(s) " & Left(strMiss, Len(strMiss) - 2) & vbLf & vbLf
End If

For i = 8 To 22
If Cells(i, 7) = "" Then
If WorksheetFunction.CountA(Range(Cells(i, 8), Cells(i, 27))) <> 0 Then
strMiss2 = strMiss2 & i & ", "
End If
End If
Next

If strMiss2 <> "" Then
strOut = strOut & "You Have an Inspection Result with no Serial Number!" & vbLf & _
"Missing Serial in row(s) " & Left(strMiss2, Len(strMiss2) - 2)
End If

If strOut <> "" Then
MsgBox strOut, vbCritical, "Missing Data"
Else
MsgBox "You have NO missing data", vbInformation, "All required data is present"
End If
End Sub
[/VBA]

Or with data validation where Serial number has to be filled in before the Inspection data can be entered:
Highlight G8:AA22 (make sure G8 is the active cell)
Custom validation of: =COUNTA($G8)<>0

As a change event where Serial number has to be filled in before the Inspection data can be entered. Right click on the tab as choose View Code. Paste the following:
[VBA]Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("H8:AA22")) Is Nothing And Target <> "" Then
If Range("G" & Target.Row) = "" Then
MsgBox "Please enter a serial number first", vbCritical, "Serial number please"
End If
End If
End Sub[/VBA]

[Panda]

Thank you so much for your help mbarron = used the code assigned to the button in the end and it works perfectly.

Thanks again for your time