variable range: l just can't get there!
 

Hello to all, this is the scenario: l have 60 sheets in a workbook, 56
of them are named S1, S2, S3 etc etc up to S56, what l want to do is
check a range on each sheet for any blank cells, my problem is that
the range changes on each sheet... this is what l have so far (some
code borrowed from a post by Nick Hodge, thanks Nick)...the start
address of the range will be the same on each sheet which is "A3" the
end of the range will be the last used cell in col AS..
this is where l am beat... the last cell in col AS changes on each
sheet...
it would be nice if it did find any cell with lost data to tell me
what the cell address was, as some of these sheets carry a lot of
data...

Thans for any help....seeya ste

Public Sub checkforlostdata()

Dim myCell As Range
Dim endofrange As Variant

For sheetnumber = 1 To 2

sheetname = "S" & Format(sheetnumber, "##0")
Sheets(sheetname).Select


ActiveSheet.Range("AS1").End(xlDown).Select
ActiveCell.cell.Address = endofrange

For Each myCell In ActiveSheet.Range("A3:endofrange")
If IsEmpty(myCell.Value) Then
MsgBox "There is lost data on sheet " & sheetname
Cancel = True
Exit Sub
End If
Next myCell
Next
End Sub

variable range: l just can't get there!
 

Public Sub checkforlostdata()

Dim myCell As Range
Dim endofrange As Variant

For sheetnumber = 1 To 2

sheetname = "S" & Format(sheetnumber, "##0")
Sheets(sheetname).Select


ActiveSheet.Range("AS1").End(xlDown).Select
endofrange = ActiveCell.cell.Address '<==

For Each myCell In ActiveSheet.Range("A3:" & endofrange) ' <==
If IsEmpty(myCell.Value) Then
MsgBox "There is lost data on sheet " & sheetname
Cancel = True
Exit Sub
End If
Next myCell
Next
End Sub


A possibility (which should be faster


Dim sh As Worksheet, rng As Range, rng1 As Range
For Each sh In ThisWorkbook.Worksheets
If Left(sh.Name, 1) = "S" And _
IsNumeric(Right(sh.Name, Len(sh.Name) - 1)) Then
Set rng = sh.Range(sh.Cells(1, 1), _
sh.Cells(Rows.Count, "AS").End(xlUp))
On Error Resume Next
Set rng1 = rng.SpecialCells(xlBlanks)
On Error GoTo 0
If Not rng1 Is Nothing Then
MsgBox "There is lost data on sheet " _
& sh.Name
'Exit Sub
End If
End If
Next



or
Dim sh as Worksheet, rng as Range, rng1 as Range
Dim sStr as String
sStr = "There is lost data on sheets: " & vbNewLine
For each sh in Thisworkbook.Worksheets
if Left(sh.name,1)="S" and isnumeric(right(sh.name,len(sh.name)-1)) then
set rng = sh.Range(sh.Cells(1,1),sh.Cells(rows.count,"AS").E nd(xlup))
on Error Resume Next
set rng1 = rng.SpecialCells(xlBlanks)
on Error Goto 0
if not rng1 is nothing then
sStr = sStr & sh.Name & "," & vbNewLine
end if
End if
Next
MsgBox sStr

Code is untested and may contain typos, but should give you a general idea.

--
Regards,
Tom Ogilvy

"ste mac" wrote in message
om...
Hello to all, this is the scenario: l have 60 sheets in a workbook, 56
of them are named S1, S2, S3 etc etc up to S56, what l want to do is
check a range on each sheet for any blank cells, my problem is that
the range changes on each sheet... this is what l have so far (some
code borrowed from a post by Nick Hodge, thanks Nick)...the start
address of the range will be the same on each sheet which is "A3" the
end of the range will be the last used cell in col AS..
this is where l am beat... the last cell in col AS changes on each
sheet...
it would be nice if it did find any cell with lost data to tell me
what the cell address was, as some of these sheets carry a lot of
data...

Thans for any help....seeya ste

Public Sub checkforlostdata()

Dim myCell As Range
Dim endofrange As Variant

For sheetnumber = 1 To 2

sheetname = "S" & Format(sheetnumber, "##0")
Sheets(sheetname).Select


ActiveSheet.Range("AS1").End(xlDown).Select
ActiveCell.cell.Address = endofrange

For Each myCell In ActiveSheet.Range("A3:endofrange")
If IsEmpty(myCell.Value) Then
MsgBox "There is lost data on sheet " & sheetname
Cancel = True
Exit Sub
End If
Next myCell
Next
End Sub

variable range: l just can't get there!
 

Thanks a lot Tom, and so quick too! it did the trick no-problem..

once again thanks for your help...

seeya ste




"Tom Ogilvy" wrote in message ...
Public Sub checkforlostdata()

Dim myCell As Range
Dim endofrange As Variant

For sheetnumber = 1 To 2

sheetname = "S" & Format(sheetnumber, "##0")
Sheets(sheetname).Select


ActiveSheet.Range("AS1").End(xlDown).Select
endofrange = ActiveCell.cell.Address '<==

For Each myCell In ActiveSheet.Range("A3:" & endofrange) ' <==
If IsEmpty(myCell.Value) Then
MsgBox "There is lost data on sheet " & sheetname
Cancel = True
Exit Sub
End If
Next myCell
Next
End Sub


A possibility (which should be faster


Dim sh As Worksheet, rng As Range, rng1 As Range
For Each sh In ThisWorkbook.Worksheets
If Left(sh.Name, 1) = "S" And _
IsNumeric(Right(sh.Name, Len(sh.Name) - 1)) Then
Set rng = sh.Range(sh.Cells(1, 1), _
sh.Cells(Rows.Count, "AS").End(xlUp))
On Error Resume Next
Set rng1 = rng.SpecialCells(xlBlanks)
On Error GoTo 0
If Not rng1 Is Nothing Then
MsgBox "There is lost data on sheet " _
& sh.Name
'Exit Sub
End If
End If
Next



or
Dim sh as Worksheet, rng as Range, rng1 as Range
Dim sStr as String
sStr = "There is lost data on sheets: " & vbNewLine
For each sh in Thisworkbook.Worksheets
if Left(sh.name,1)="S" and isnumeric(right(sh.name,len(sh.name)-1)) then
set rng = sh.Range(sh.Cells(1,1),sh.Cells(rows.count,"AS").E nd(xlup))
on Error Resume Next
set rng1 = rng.SpecialCells(xlBlanks)
on Error Goto 0
if not rng1 is nothing then
sStr = sStr & sh.Name & "," & vbNewLine
end if
End if
Next
MsgBox sStr

Code is untested and may contain typos, but should give you a general idea.

--
Regards,
Tom Ogilvy

"ste mac" wrote in message
om...
Hello to all, this is the scenario: l have 60 sheets in a workbook, 56
of them are named S1, S2, S3 etc etc up to S56, what l want to do is
check a range on each sheet for any blank cells, my problem is that
the range changes on each sheet... this is what l have so far (some
code borrowed from a post by Nick Hodge, thanks Nick)...the start
address of the range will be the same on each sheet which is "A3" the
end of the range will be the last used cell in col AS..
this is where l am beat... the last cell in col AS changes on each
sheet...
it would be nice if it did find any cell with lost data to tell me
what the cell address was, as some of these sheets carry a lot of
data...

Thans for any help....seeya ste

Public Sub checkforlostdata()

Dim myCell As Range
Dim endofrange As Variant

For sheetnumber = 1 To 2

sheetname = "S" & Format(sheetnumber, "##0")
Sheets(sheetname).Select


ActiveSheet.Range("AS1").End(xlDown).Select
ActiveCell.cell.Address = endofrange

For Each myCell In ActiveSheet.Range("A3:endofrange")
If IsEmpty(myCell.Value) Then
MsgBox "There is lost data on sheet " & sheetname
Cancel = True
Exit Sub
End If
Next myCell
Next
End Sub