Any help would be appreciated.
40 ActiveCell.Offset(1, 4).Select
Do Until x > 0
ActiveCell = 0
ActiveCell.Offset(-1, 0).Select
y = ActiveCell.Select
Do Until ActiveCell.EntireRow.Hidden = False
If ActiveCell.EntireRow.Hidden = True Then
ActiveCell.Offset(-1, 0).Select
End If
Loop
y = ActiveCell
If -x < y Then
ActiveCell = y + x
End If
x = x + y
Loop
y = ActiveCell.Value
"Matt" <Ma...@discussions.microsoft.com> wrote in message
news:ACB8628C-C0AF-4BBD...@microsoft.com...
y = ActiveCell.Value
--
Rick (MVP - Excel)
"Matt" <Ma...@discussions.microsoft.com> wrote in message
news:ACB8628C-C0AF-4BBD...@microsoft.com...
y = ActiveCell.Select
What is it supposed to yield? Value? Address? Just move pointer?
"Matt" <Ma...@discussions.microsoft.com> wrote in message
news:ACB8628C-C0AF-4BBD...@microsoft.com...
the error is occuring because you are in some looop where you are
either trying to access a row number less than 1 or greater than the
number of rows on a worksheet (65,536) because one of your conditions
aren't being met. There are better ways of writing this type code but I
can't figure out what you are really trying to do.
--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=169202
[url="http://www.thecodecage.com"]Microsoft Office Help[/url]
Dim x As Long
Dim y As Long
Dim cell As Range
Sheets("summary").Select
x = Range("c11")
i = Range("c8")
Sheets("pyendLAYERS").Select
Range("a1").Activate
Selection.End(xlDown).Select
z = Range("COMPANY")
Selection.AutoFilter field:=1, Criteria1:=z
v = Range("TAX")
Selection.AutoFilter field:=2, Criteria1:=v
w = Range("RESALE")
Selection.AutoFilter field:=3, Criteria1:=w
If x > 0 Then GoTo 20
GoTo 40
20 ActiveCell.Offset(1, 0).Select
y = ActiveCell.Select
ActiveCell = Range("company")
ActiveCell.Offset(0, 1).Select
y = ActiveCell.Select
ActiveCell = Range("tax")
ActiveCell.Offset(0, 1).Select
y = ActiveCell.Select
ActiveCell = Range("RESALE")
ActiveCell.Offset(0, 1).Select
y = ActiveCell.Select
ActiveCell = Range("year")
ActiveCell.Offset(0, 1).Select
ActiveCell = x
ActiveCell.Offset(0, 1).Select
ActiveCell = i
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "=rc[-2]*rc[-1]"
40 ActiveCell.Offset(1, 4).Select
Do Until x > 0
ActiveCell = 0
ActiveCell.Offset(-1, 0).Select
y = ActiveCell.Select
Do Until ActiveCell.EntireRow.Hidden = False
If ActiveCell.EntireRow.Hidden = True Then
ActiveCell.Offset(-1, 0).Select
End If
Loop
y = ActiveCell
If -x < y Then
ActiveCell = y + x
End If
x = x + y
Loop
Range("layertotal").Select
Selection.Copy
Sheets("summary").Select
Range("taxdec").Activate
Selection.PasteSpecial Paste:=xlValues
"joel" wrote:
> [url="http://www.thecodecage.com"]Microsoft Office Help[/url]
>
> .
>
1) Autofilter is not turned on
2) the data you are autofilter isn't found, or the combination of
the 3 conditions
3) You can end up if your criteria isn't met reading from row 0
This code is pretty Idiot proof and you won't get the same errors you
were getting before.
Dim x As Long
Dim y As Long
Dim cell As Range
With Sheets("summary")
x = .Range("c11")
i = .Range("c8")
With Sheets("pyendLAYERS")
'turn off autofilter
If .AutoFilterMode = True Then
.Columns.AutoFilter
End If
LastRow = .Range("a1").End(xlDown).Row
companyName = Range("COMPANY")
.Columns("A:C").AutoFilter
Set c = .Columns("A").Find(what:=companyName, _
LookIn:=xlValues, lookat:=xlWhole)
If c Is Nothing Then
MsgBox ("Cannot find Company : " & companyName & vbCrLf & _
"Exiting Macro")
Exit Sub
End If
.Column("A").AutoFilter field:=1, Criteria1:=companyName
Tax = Range("TAX")
Set c = .Columns("B").Find(what:=Tax, _
LookIn:=xlValues, lookat:=xlWhole)
If c Is Nothing Then
MsgBox ("Cannot find Tax : " & Tax & vbCrLf & _
"Exiting Macro")
Exit Sub
End If
.Columns("A:C").AutoFilter field:=2, Criteria1:=Tax
Resale = Range("RESALE")
Set c = .Columns("C").Find(what:=Resale, _
LookIn:=xlValues, lookat:=xlWhole)
If c Is Nothing Then
MsgBox ("Cannot find Tax : " & Resale & vbCrLf & _
"Exiting Macro")
Exit Sub
End If
.Columns("A:C").AutoFilter field:=3, Criteria1:=Resale
NewRow = LastRow + 1
If x <= 0 Then
.Range("A" & NewRow) = Range("company")
.Range("B" & NewRow) = Range("tax")
.Range("C" & NewRow) = Range("RESALE")
.Range("D" & NewRow) = Range("year")
.Range("E" & NewRow) = x
.Range("F" & NewRow) = i
.Range("G" & NewRow).FormulaR1C1 = "=rc[-2]*rc[-1]"
Else
For RowCount = LastRow To 1 Step -1
If .Rows(RowCount).EntireRow.Hidden = True Then
y = .Range("E" & RowCount)
If -x < y Then
.Range("E" & RowCount) = y + x
End If
x = x + y
End If
Next RowCount
End If
End With
End With
Range("layertotal").Copy
Range("taxdec").PasteSpecial Paste:=xlValues
--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=169202
[url="http://www.thecodecage.com"]Microsoft Office Help[/url]
.Column("A").AutoFilter field:=1, Criteria1:=companyName
"joel" wrote:
Columns("A").AutoFilter Field:=1, Criteria1:=companyName
Collections are always plural.
"Matt" <Ma...@discussions.microsoft.com> wrote in message
news:819B5A43-500E-49D1...@microsoft.com...