Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
method "range" of object "_worksheet" fails
line ** generates the above error. Please advise.
code: Sub clean() Dim mtsumc As Range Dim mtsumn As Range Dim psumc As Range Dim psumn As Range Dim curcell As Range Dim sht As Worksheet Dim el1 As Integer Dim el2 As Integer Dim c As Integer Dim P, M As Boolean For Each sht In ActiveWorkbook.Worksheets c = sht.UsedRange.Columns.Count For Each curcell In sht.UsedRange.Columns(2).Cells el1 = curcell.Row Select Case Trim(curcell.Offset(0, -1).Value) Case Is = "Motor" M = True Select Case Trim(curcell.Value) Case Is = "A" Set mtsumc = sht.Range(Cells(el1, 3), Cells(el1, c)) Case Is = "B" Set mtsumn = sht.Range(Cells(el1, 3), Cells(el1, c)) Case Is = "A+B" MsgBox "file already processed" Exit Sub End Select Case Is = "Property" P = True Select Case Trim(curcell.Value) Case Is = "A" Set psumc = sht.Range(Cells(el1, 3), Cells(el1, c)) Case Is = "B" Set psumn = sht.Range(Cells(el1, 3), Cells(el1, c)) Case Is = "A+B" MsgBox "file already processed" Exit Sub End Select End Select Next curcell If M = True Then With sht ..Range("mtsumn").Copy** ..Range("mtsumc").PasteSpecial operation:=xlAdd End With Application.CutCopyMode = False Else: End If If P = True Then With sht ..Range("psumn").Copy ..Range("psumc").PasteSpecial operation:=xlAdd End With Application.CutCopyMode = False Else: End If Next sht End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
method "range" of object "_worksheet" fails
Is "mtsumn" named range defined to the worksheet level or to the
workbook level ? In the second case, you should not use it with a worksheet. Regards. Daniel line ** generates the above error. Please advise. code: Sub clean() Dim mtsumc As Range Dim mtsumn As Range Dim psumc As Range Dim psumn As Range Dim curcell As Range Dim sht As Worksheet Dim el1 As Integer Dim el2 As Integer Dim c As Integer Dim P, M As Boolean For Each sht In ActiveWorkbook.Worksheets c = sht.UsedRange.Columns.Count For Each curcell In sht.UsedRange.Columns(2).Cells el1 = curcell.Row Select Case Trim(curcell.Offset(0, -1).Value) Case Is = "Motor" M = True Select Case Trim(curcell.Value) Case Is = "A" Set mtsumc = sht.Range(Cells(el1, 3), Cells(el1, c)) Case Is = "B" Set mtsumn = sht.Range(Cells(el1, 3), Cells(el1, c)) Case Is = "A+B" MsgBox "file already processed" Exit Sub End Select Case Is = "Property" P = True Select Case Trim(curcell.Value) Case Is = "A" Set psumc = sht.Range(Cells(el1, 3), Cells(el1, c)) Case Is = "B" Set psumn = sht.Range(Cells(el1, 3), Cells(el1, c)) Case Is = "A+B" MsgBox "file already processed" Exit Sub End Select End Select Next curcell If M = True Then With sht .Range("mtsumn").Copy** .Range("mtsumc").PasteSpecial operation:=xlAdd End With Application.CutCopyMode = False Else: End If If P = True Then With sht .Range("psumn").Copy .Range("psumc").PasteSpecial operation:=xlAdd End With Application.CutCopyMode = False Else: End If Next sht End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
method "range" of object "_worksheet" fails
my intention is for all the named ranges to be set and used at the sheet
level. What code changes are needed to make this work? "Daniel.C" wrote: Is "mtsumn" named range defined to the worksheet level or to the workbook level ? In the second case, you should not use it with a worksheet. Regards. Daniel line ** generates the above error. Please advise. code: Sub clean() Dim mtsumc As Range Dim mtsumn As Range Dim psumc As Range Dim psumn As Range Dim curcell As Range Dim sht As Worksheet Dim el1 As Integer Dim el2 As Integer Dim c As Integer Dim P, M As Boolean For Each sht In ActiveWorkbook.Worksheets c = sht.UsedRange.Columns.Count For Each curcell In sht.UsedRange.Columns(2).Cells el1 = curcell.Row Select Case Trim(curcell.Offset(0, -1).Value) Case Is = "Motor" M = True Select Case Trim(curcell.Value) Case Is = "A" Set mtsumc = sht.Range(Cells(el1, 3), Cells(el1, c)) Case Is = "B" Set mtsumn = sht.Range(Cells(el1, 3), Cells(el1, c)) Case Is = "A+B" MsgBox "file already processed" Exit Sub End Select Case Is = "Property" P = True Select Case Trim(curcell.Value) Case Is = "A" Set psumc = sht.Range(Cells(el1, 3), Cells(el1, c)) Case Is = "B" Set psumn = sht.Range(Cells(el1, 3), Cells(el1, c)) Case Is = "A+B" MsgBox "file already processed" Exit Sub End Select End Select Next curcell If M = True Then With sht .Range("mtsumn").Copy** .Range("mtsumc").PasteSpecial operation:=xlAdd End With Application.CutCopyMode = False Else: End If If P = True Then With sht .Range("psumn").Copy .Range("psumc").PasteSpecial operation:=xlAdd End With Application.CutCopyMode = False Else: End If Next sht End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
method "range" of object "_worksheet" fails
Could you post the formula which defines "mtsumn" ?
Daniel my intention is for all the named ranges to be set and used at the sheet level. What code changes are needed to make this work? "Daniel.C" wrote: Is "mtsumn" named range defined to the worksheet level or to the workbook level ? In the second case, you should not use it with a worksheet. Regards. Daniel line ** generates the above error. Please advise. code: Sub clean() Dim mtsumc As Range Dim mtsumn As Range Dim psumc As Range Dim psumn As Range Dim curcell As Range Dim sht As Worksheet Dim el1 As Integer Dim el2 As Integer Dim c As Integer Dim P, M As Boolean For Each sht In ActiveWorkbook.Worksheets c = sht.UsedRange.Columns.Count For Each curcell In sht.UsedRange.Columns(2).Cells el1 = curcell.Row Select Case Trim(curcell.Offset(0, -1).Value) Case Is = "Motor" M = True Select Case Trim(curcell.Value) Case Is = "A" Set mtsumc = sht.Range(Cells(el1, 3), Cells(el1, c)) Case Is = "B" Set mtsumn = sht.Range(Cells(el1, 3), Cells(el1, c)) Case Is = "A+B" MsgBox "file already processed" Exit Sub End Select Case Is = "Property" P = True Select Case Trim(curcell.Value) Case Is = "A" Set psumc = sht.Range(Cells(el1, 3), Cells(el1, c)) Case Is = "B" Set psumn = sht.Range(Cells(el1, 3), Cells(el1, c)) Case Is = "A+B" MsgBox "file already processed" Exit Sub End Select End Select Next curcell If M = True Then With sht .Range("mtsumn").Copy** .Range("mtsumc").PasteSpecial operation:=xlAdd End With Application.CutCopyMode = False Else: End If If P = True Then With sht .Range("psumn").Copy .Range("psumc").PasteSpecial operation:=xlAdd End With Application.CutCopyMode = False Else: End If Next sht End Sub |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
method "range" of object "_worksheet" fails
With sht
..Range("mtsumn").Copy** If any sheet does not have this local name defined on it you'll get an error when the macro gets to that sheet. -- Jim "PBcorn" wrote in message ... | line ** generates the above error. Please advise. | | | code: | Sub clean() | | Dim mtsumc As Range | Dim mtsumn As Range | Dim psumc As Range | Dim psumn As Range | | | Dim curcell As Range | Dim sht As Worksheet | Dim el1 As Integer | Dim el2 As Integer | Dim c As Integer | | Dim P, M As Boolean | | | For Each sht In ActiveWorkbook.Worksheets | | c = sht.UsedRange.Columns.Count | | For Each curcell In sht.UsedRange.Columns(2).Cells | | el1 = curcell.Row | | Select Case Trim(curcell.Offset(0, -1).Value) | | Case Is = "Motor" | | M = True | | Select Case Trim(curcell.Value) | | Case Is = "A" | Set mtsumc = sht.Range(Cells(el1, 3), Cells(el1, c)) | | Case Is = "B" | Set mtsumn = sht.Range(Cells(el1, 3), Cells(el1, c)) | | Case Is = "A+B" | MsgBox "file already processed" | Exit Sub | | End Select | | Case Is = "Property" | | P = True | | Select Case Trim(curcell.Value) | | Case Is = "A" | Set psumc = sht.Range(Cells(el1, 3), Cells(el1, c)) | | Case Is = "B" | Set psumn = sht.Range(Cells(el1, 3), Cells(el1, c)) | | Case Is = "A+B" | MsgBox "file already processed" | Exit Sub | | End Select | | End Select | Next curcell | | | | If M = True Then | With sht | .Range("mtsumn").Copy** | .Range("mtsumc").PasteSpecial operation:=xlAdd | End With | Application.CutCopyMode = False | Else: End If | | If P = True Then | With sht | .Range("psumn").Copy | .Range("psumc").PasteSpecial operation:=xlAdd | End With | Application.CutCopyMode = False | Else: End If | | | | | Next sht | End Sub | |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
method "range" of object "_worksheet" fails
ok what am trying to acheive is have the macro cycle through all the sheets
in the workbook and assign new ranges to the named ranges for each sheet, then use this for operations within the sheet. How can this be done? "Jim Rech" wrote: With sht ..Range("mtsumn").Copy** If any sheet does not have this local name defined on it you'll get an error when the macro gets to that sheet. -- Jim "PBcorn" wrote in message ... | line ** generates the above error. Please advise. | | | code: | Sub clean() | | Dim mtsumc As Range | Dim mtsumn As Range | Dim psumc As Range | Dim psumn As Range | | | Dim curcell As Range | Dim sht As Worksheet | Dim el1 As Integer | Dim el2 As Integer | Dim c As Integer | | Dim P, M As Boolean | | | For Each sht In ActiveWorkbook.Worksheets | | c = sht.UsedRange.Columns.Count | | For Each curcell In sht.UsedRange.Columns(2).Cells | | el1 = curcell.Row | | Select Case Trim(curcell.Offset(0, -1).Value) | | Case Is = "Motor" | | M = True | | Select Case Trim(curcell.Value) | | Case Is = "A" | Set mtsumc = sht.Range(Cells(el1, 3), Cells(el1, c)) | | Case Is = "B" | Set mtsumn = sht.Range(Cells(el1, 3), Cells(el1, c)) | | Case Is = "A+B" | MsgBox "file already processed" | Exit Sub | | End Select | | Case Is = "Property" | | P = True | | Select Case Trim(curcell.Value) | | Case Is = "A" | Set psumc = sht.Range(Cells(el1, 3), Cells(el1, c)) | | Case Is = "B" | Set psumn = sht.Range(Cells(el1, 3), Cells(el1, c)) | | Case Is = "A+B" | MsgBox "file already processed" | Exit Sub | | End Select | | End Select | Next curcell | | | | If M = True Then | With sht | .Range("mtsumn").Copy** | .Range("mtsumc").PasteSpecial operation:=xlAdd | End With | Application.CutCopyMode = False | Else: End If | | If P = True Then | With sht | .Range("psumn").Copy | .Range("psumc").PasteSpecial operation:=xlAdd | End With | Application.CutCopyMode = False | Else: End If | | | | | Next sht | End Sub | |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Error Help - Method "Range" of object "_Worksheet" failed. | Excel Programming | |||
Error in Macro: "Method 'Paste' of object '_Worksheet' failed" | Excel Programming | |||
What is Error "Method "Paste" of object "_Worksheet" failed? | Excel Programming | |||
"method 'Copy' of object '_Worksheet' failed" | Excel Programming | |||
METHOD "SELECT" OF OBJECT '_WORKSHEET' FAILED when opening a file | Excel Programming |