This is a formula that sits in a cell.
You'll have to put the formula in a cell in each worksheet.
sorry, this is the code I am reffering to: and I would like this code to work
for all worksheets: X340, X342n and X642e,
thnaks,
Dim cell As Range
For Each cell In Sheets("X340").Range("L2,L58")
If Len(Trim(cell.Text)) = 0 Then
MsgBox "Please make sure that column B, K & L are filled"
Application.Goto cell
Cancel = True
Exit For
End If
Next cell
> This is a formula that sits in a cell.
>
[quoted text clipped - 25 lines]
> > >
> > > Dave Peterson
Dave Peterson - 24 Feb 2006 18:39 GMT
Are you trying to look at all the rows 2:58 or just row 2 and row 58?
I'm gonna guess that you want all the rows:
Dim cell As Range
dim myRow as long
with sheets("x340")
For Each cell In .Range("a2:a58").cells 'just define the row
myrow = cell.row
if trim(.cells(myrow,"B")) = "" _
or trim(.cells(myrow,"K")) = "" _
or trim(.cells(myrow,"L")) = "" then
MsgBox "Please make sure that column B, K & L are filled on this row"
Application.Goto cell
Cancel = True
Exit For
End If
Next cell
end with
But you could use "L2,L58" if that's what you wanted.
As a user, I think I'd rather receive that immediate feedback that the worksheet
formula gives me--rather than waiting until I was saving the workbook.
> sorry, this is the code I am reffering to: and I would like this code to work
> for all worksheets: X340, X342n and X642e,
[quoted text clipped - 43 lines]
> >
> > Dave Peterson

Signature
Dave Peterson
Dave Peterson - 24 Feb 2006 18:43 GMT
And if you wanted to loop through those worksheets...
Dim wks As Worksheet
Dim cell As Range
Dim myRow As Long
Dim FoundAnError As Boolean
FoundAnError = False
For Each wks In Worksheets(Array("x340", "x342n", "x642e"))
If FoundAnError Then
Exit For
End If
With wks
For Each cell In .Range("a2:a58").Cells 'just define the row
myRow = cell.Row
If Trim(.Cells(myRow, "B")) = "" _
Or Trim(.Cells(myRow, "K")) = "" _
Or Trim(.Cells(myRow, "L")) = "" Then
MsgBox "Please make sure that column B, K & L are filled on this row"
Application.Goto cell
'Cancel = True
FoundAnError = True
Exit For
End If
Next cell
End With
Next wks
> sorry, this is the code I am reffering to: and I would like this code to work
> for all worksheets: X340, X342n and X642e,
[quoted text clipped - 43 lines]
> >
> > Dave Peterson

Signature
Dave Peterson