I have unsucessfully tried to get this macro to stay within a range of a
worksheet ("E3:AU65535") to perform the following function/results. I have
columns of 0's with 1's and 2's in them. The 1 is a start date and the 2 is
an end date. I want to find each occurance and data fill with 1's inbetween
the 1's and 2's.

The rest of the worksheet should not be changed. I have tried several
different approaches....still looking....my find statement end up going
outside of the range. Can anyone help? Thanks...........

Sub FindOnes()

Dim DataWithOnes As Range
Dim Topcell As Variant
Dim Bottomcell As Variant

Set DataWithOnes = ActiveSheet.UsedRange

For Each c In ActiveSheet.UsedRange

Cells.Find(What:="1", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
If ActiveCell.Value = 1 Then
Set Topcell = ActiveCell

Cells.Find(What:="2", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
If ActiveCell.Value = 2 Then
Set Bottomcell = ActiveCell

If Topcell = 1 Then Set Topcell = Topcell
Range(Topcell, Bottomcell).Select
Selection.FillDown
Bottomcell.Select

Set DataWithOnes = ActiveSheet.UsedRange


End If
End If

If DataWithOnes Is Nothing Then
GoTo done
End If
Next

hi
here is your problem.
For Each c In ActiveSheet.UsedRange
used range consists of all of the sheet that has data.
change that....
dim r as range
set r = activesheet.range("E3:AU65535")
for each c in r
....
i see usedrange in several place. change them too.

regards
FSt1

"Carrie" wrote:

I have unsucessfully tried to get this macro to stay within a range of a
worksheet ("E3:AU65535") to perform the following function/results. I have
columns of 0's with 1's and 2's in them. The 1 is a start date and the 2 is
an end date. I want to find each occurance and data fill with 1's inbetween
the 1's and 2's.

The rest of the worksheet should not be changed. I have tried several
different approaches....still looking....my find statement end up going
outside of the range. Can anyone help? Thanks...........

Sub FindOnes()

Dim DataWithOnes As Range
Dim Topcell As Variant
Dim Bottomcell As Variant

Set DataWithOnes = ActiveSheet.UsedRange

For Each c In ActiveSheet.UsedRange

Cells.Find(What:="1", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
If ActiveCell.Value = 1 Then
Set Topcell = ActiveCell

Cells.Find(What:="2", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
If ActiveCell.Value = 2 Then
Set Bottomcell = ActiveCell

If Topcell = 1 Then Set Topcell = Topcell
Range(Topcell, Bottomcell).Select
Selection.FillDown
Bottomcell.Select

Set DataWithOnes = ActiveSheet.UsedRange


End If
End If

If DataWithOnes Is Nothing Then
GoTo done
End If
Next

Your are my hero....I have been stuck on this for days.......I never knew
that UsedRange applies to all data on the sheet. Makes perfect sense now.
Thanks!

"FSt1" wrote:

hi
here is your problem.
For Each c In ActiveSheet.UsedRange
used range consists of all of the sheet that has data.
change that....
dim r as range
set r = activesheet.range("E3:AU65535")
for each c in r
...
i see usedrange in several place. change them too.

regards
FSt1

"Carrie" wrote:

I have unsucessfully tried to get this macro to stay within a range of a
worksheet ("E3:AU65535") to perform the following function/results. I have
columns of 0's with 1's and 2's in them. The 1 is a start date and the 2 is
an end date. I want to find each occurance and data fill with 1's inbetween
the 1's and 2's.

The rest of the worksheet should not be changed. I have tried several
different approaches....still looking....my find statement end up going
outside of the range. Can anyone help? Thanks...........

Sub FindOnes()

Dim DataWithOnes As Range
Dim Topcell As Variant
Dim Bottomcell As Variant

Set DataWithOnes = ActiveSheet.UsedRange

For Each c In ActiveSheet.UsedRange

Cells.Find(What:="1", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
If ActiveCell.Value = 1 Then
Set Topcell = ActiveCell

Cells.Find(What:="2", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
If ActiveCell.Value = 2 Then
Set Bottomcell = ActiveCell

If Topcell = 1 Then Set Topcell = Topcell
Range(Topcell, Bottomcell).Select
Selection.FillDown
Bottomcell.Select

Set DataWithOnes = ActiveSheet.UsedRange


End If
End If

If DataWithOnes Is Nothing Then
GoTo done
End If
Next