maintaining the value of a variable outside of an if construct...

R

R Tanner

Sub test()
Dim rng As Range
Dim x As Integer
Dim i As Range


Sheets("Workforce Data Table").Select
ActiveSheet.ListObjects( _
"WDT").Range. _
AutoFilter Field:=2, Criteria1:="Grave"

Range("A1").Select
Set RANGEENTRIES = Range("A1", ActiveCell.End(xlDown))
RANGEENTRIES.Select
Do While x <= NUMENTRIES
For Each cell In Selection
If ActiveCell.EntireRow.Hidden = False Then
x = x + 1
Set i = ActiveCell
Sheets("Monthly Management Report").Select
Range("B46").Select
ActiveCell.Cells(x, 0).Select

ActiveCell.Value = i
End If
i.Select 'This line fails, I'm assuming because my
variable i loses it's

'value outside of the if construct...
ActiveCell.Offset(1, 0).Select
Next cell
Loop

End Sub
 
J

Jim Thomlinson

i si still valid. you are trying to select a cell that is not on the active
sheet. Not going to work for you...

Sub test()
Dim rng As Range
Dim x As Integer
Dim i As Range


Sheets("Workforce Data Table").Select
ActiveSheet.ListObjects( _
"WDT").Range. _
AutoFilter Field:=2, Criteria1:="Grave"

Range("A1").Select
Set RANGEENTRIES = Range("A1", ActiveCell.End(xlDown))
RANGEENTRIES.Select
Do While x <= NUMENTRIES
For Each cell In Selection
If ActiveCell.EntireRow.Hidden = False Then
x = x + 1
Set i = ActiveCell
Sheets("Monthly Management Report").Select
Range("B46").Select
ActiveCell.Cells(x, 0).Select

ActiveCell.Value = i
End If
i.parent.select 'Select the sheet it comes from
i.Select
ActiveCell.Offset(1, 0).Select
Next cell
Loop

End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top