Panda
09-06-2010, 12:07 PM
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;
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
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
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;
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
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