Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Setting Multiple Variables with a loop | Excel Discussion (Misc queries) | |||
Setting A variable name From Loop | Excel Programming | |||
Dynamically setting Dimensions in a VBA loop | Excel Programming | |||
Setting a range within a loop variable for copy/paste | Excel Programming | |||
TextToColumns delimiter setting ignored w/in VB loop | Excel Programming |