Setting Range with a Loop
 

I am trying to create a macro that is very similar to the one written by Dave Peterson in this thread http://www.excelbanter.com/showthread.php?t=207507

Dave's macro works great, but I needed to add a couple things to it and have tried to create a loop, but I am getting an error.

Here is what I have. I am getting the following error "Run-time error '91. Object variable or With block variable not set" It seems this has something to do with the range I am trying to set, but I don't know where I am going wrong. I am fairly new to macros - Can anyone help?

Option Explicit
Sub Set_Q1_Targets_Results()
Sheets("Unhide").Visible = True
Sheets("last").Visible = True
Sheets("last1").Visible = True
Sheets("Home Equity First Lien").Select
Do While ActiveSheet.Name < "Unhide"
Application.ScreenUpdating = False
Dim myRng As Range
Dim myCell As Range

Set myRng = Nothing

On Error Resume Next
With Worksheets(ActiveSheet)
Range("K14:K120").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Set myRng = .Range("Z14", .Cells(.Rows.Count, "Z").End(xlUp)) _
.SpecialCells(xlCellTypeConstants)
End With
On Error GoTo 0

If myRng Is Nothing Then
MsgBox "No Constants!"
Exit Sub
End If

For Each myCell In myRng.Cells
With myCell
.NumberFormat = "_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)"
.Formula = "=" & .Value
End With
Next myCell

Range("D14").Select
ActiveWindow.FreezePanes = True
ActiveWindow.ScrollColumn = 1
ActiveWindow.ScrollRow = 1
ActiveSheet.Outline.ShowLevels RowLevels:=1
ActiveSheet.Next.Select
Loop
ActiveWindow.SelectedSheets.Visible = False
End Sub

Setting Range with a Loop
 

On Friday, September 28, 2012 1:09:24 PM UTC-5, abbruno wrote:
I am trying to create a macro that is very similar to the one written by

Dave Peterson in this thread

http://www.excelbanter.com/showthread.php?t=207507



Dave's macro works great, but I needed to add a couple things to it and

have tried to create a loop, but I am getting an error.



Here is what I have. I am getting the following error "Run-time error

'91. Object variable or With block variable not set" It seems this has

something to do with the range I am trying to set, but I don't know

where I am going wrong. I am fairly new to macros - Can anyone help?



Option Explicit

Sub Set_Q1_Targets_Results()

Sheets("Unhide").Visible = True

Sheets("last").Visible = True

Sheets("last1").Visible = True

Sheets("Home Equity First Lien").Select

Do While ActiveSheet.Name < "Unhide"

Application.ScreenUpdating = False

Dim myRng As Range

Dim myCell As Range



Set myRng = Nothing



On Error Resume Next

With Worksheets(ActiveSheet)

Range("K14:K120").Select

Selection.Copy

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,

SkipBlanks _

:=False, Transpose:=False

Set myRng = .Range("Z14", .Cells(.Rows.Count, "Z").End(xlUp)) _

SpecialCells(xlCellTypeConstants)

End With

On Error GoTo 0



If myRng Is Nothing Then

MsgBox "No Constants!"

Exit Sub

End If



For Each myCell In myRng.Cells

With myCell

NumberFormat = "_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)"

Formula = "=" & .Value

End With

Next myCell



Range("D14").Select

ActiveWindow.FreezePanes = True

ActiveWindow.ScrollColumn = 1

ActiveWindow.ScrollRow = 1

ActiveSheet.Outline.ShowLevels RowLevels:=1

ActiveSheet.Next.Select

Loop

ActiveWindow.SelectedSheets.Visible = False

End Sub









--

abbruno
It appears that you have many errors in your code. If desired, send me your file dguillett1 @gmail.com with your coding efforts AND a complete explanation of what you want.