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.