Performing a Macro within a range
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
|