469,568 Members | 1,444 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,568 developers. It's quick & easy.

run-time error '1004' method 'range' of object '_global' failed

3
I have a problem.
The error in subject appear when i run the macro
Can u tell me why

Expand|Select|Wrap|Line Numbers
  1.  
  2.         Case "CENTRALIZATOR SURVEY"
  3.                 Dim SHT As Object
  4.                 Set SHT = Application.ThisWorkbook.Sheets("CENTRALIZATOR SURVEY")
  5.                 SHT.Cells(10, 1) = UCase(n)
  6.                 If IsEmpty(SHT.Cells(11, 1)) = False Then
  7.                 Do
  8.                     Range(SHT.Cells(11, 1), Cells(11, 8)).Select
  9.                     Selection.EntireRow.Delete
  10.                     Set SHT = Nothing
  11.                 Loop Until IsEmpty(SHT.Cells(11, 1))
  12.             End If
  13.                 nr_crt = 0
  14.                 suma = 0
  15.                 For i = 5 To 184
  16.                     For j = 3 To 6
  17.                         Select Case ThisWorkbook.Sheets("RAPORT ZILNIC").Cells(i, j).Value
  18.                             Case "SURVEY S1"
  19.                                 Set SHT = Application.ThisWorkbook.Sheets("CENTRALIZATOR SURVEY")
  20.                                 nr_crt = nr_crt + 1
  21.                                 suma = suma + 5
  22.                                 For k = 10 To 10 + nr_crt
  23.                                     If ThisWorkbook.Sheets("RAPORT ZILNIC").Cells(1, 3).Value = ThisWorkbook.Sheets("CENTRALIZATOR SURVEY").Cells(k, 1).Value Then
  24.                                         Range(SHT.Cells(k + nr_crt, 1), Cells(k + nr_crt, 8)).Select
  25.                                         Selection.EntireRow.Copy
  26.                                         Selection.Insert
  27.                                         Application.CutCopyMode = False
  28.                                         SHT.Cells(k + nr_crt, 1).Value = nr_crt
  29.                                         SHT.Cells(k + nr_crt, 2).Value = ThisWorkbook.Sheets("RAPORT ZILNIC").Cells(i, j + 1).Value
  30.                                         SHT.Cells(k + nr_crt, 3).Value = ThisWorkbook.Sheets("RAPORT ZILNIC").Cells(i, j + 2).Value
  31.                                         SHT.Cells(k + nr_crt, 4).Value = ThisWorkbook.Sheets("RAPORT ZILNIC").Cells(i, j + 3).Value
  32.                                         SHT.Cells(k + nr_crt, 5).Value = "S1"
  33.                                     End If
  34.  
  35.                                 Next k
  36.                                 Set SHT = Nothing
  37.                             Case "SURVEY S2"
  38.                                 Set SHT = Application.ThisWorkbook.Sheets("CENTRALIZATOR SURVEY")
  39.                                 nr_crt = nr_crt + 1
  40.                                 suma = suma + 40
  41.                                 For k = 10 To 10 + nr_crt
  42.                                     If ThisWorkbook.Sheets("RAPORT ZILNIC").Cells(1, 3).Value = SHT.Cells(k, 1).Value Then
  43.                                         Range(SHT.Cells(k + nr_crt, 1), Cells(k + nr_crt, 8)).Select
  44.                                         Selection.EntireRow.Copy
  45.                                         Selection.Insert
  46.                                         Application.CutCopyMode = False
  47.                                         SHT.Cells(k + nr_crt, 1).Value = nr_crt
  48.                                         SHT.Cells(k + nr_crt, 2).Value = ThisWorkbook.Sheets("RAPORT ZILNIC").Cells(i, j + 1).Value
  49.                                         SHT.Cells(k + nr_crt, 3).Value = ThisWorkbook.Sheets("RAPORT ZILNIC").Cells(i, j + 2).Value
  50.                                         SHT.Cells(k + nr_crt, 4).Value = ThisWorkbook.Sheets("RAPORT ZILNIC").Cells(i, j + 3).Value
  51.                                         SHT.Cells(k + nr_crt, 5).Value = "S2"
  52.                                     End If
  53.  
  54.                                 Next k
  55.                                 Set SHT = Nothing
  56.                             Case "ALTE SERVICII"
  57.                                 Set SHT = Application.ThisWorkbook.Sheets("CENTRALIZATOR SURVEY")
  58.                                 nr_crt = nr_crt + 1
  59.                                 suma = suma + 10
  60.                                 For k = 10 To 10 + nr_crt
  61.                                     If ThisWorkbook.Sheets("RAPORT ZILNIC").Cells(1, 3).Value = SHT.Cells(k, 1).Value Then
  62.                                         Range(SHT.Cells(k + nr_crt, 1), Cells(k + nr_crt, 8)).Select
  63.                                         Selection.EntireRow.Copy
  64.                                         Selection.Insert
  65.                                         Application.CutCopyMode = False
  66.                                         SHT.Cells(k + nr_crt, 1).Value = nr_crt
  67.                                         SHT.Cells(k + nr_crt, 2).Value = ThisWorkbook.Sheets("RAPORT ZILNIC").Cells(i, j + 1).Value
  68.                                         SHT.Cells(k + nr_crt, 3).Value = ThisWorkbook.Sheets("RAPORT ZILNIC").Cells(i, j + 2).Value
  69.                                         SHT.Cells(k + nr_crt, 4).Value = ThisWorkbook.Sheets("RAPORT ZILNIC").Cells(i, j + 3).Value
  70.                                         SHT.Cells(k + nr_crt, 5).Value = "A"
  71.                                     End If
  72.  
  73.                                 Next k
  74.                                 Set SHT = Nothing
  75.                             Case "RECUPERARE"
  76.                                 Set SHT = Application.ThisWorkbook.Sheets("CENTRALIZATOR SURVEY")
  77.                                 nr_crt = nr_crt + 1
  78.                                 suma = suma + 20
  79.                                 For k = 10 To 10 + nr_crt
  80.                                     If ThisWorkbook.Sheets("RAPORT ZILNIC").Cells(1, 3).Value = SHT.Cells(k, 1).Value Then
  81.                                         Range(SHT.Cells(k + nr_crt, 1), Cells(k + nr_crt, 8)).Select
  82.                                         Selection.EntireRow.Copy
  83.                                         Selection.Insert
  84.                                         Application.CutCopyMode = False
  85.                                         SHT.Cells(k + nr_crt, 1).Value = nr_crt
  86.                                         SHT.Cells(k + nr_crt, 2).Value = ThisWorkbook.Sheets("RAPORT ZILNIC").Cells(i, j + 1).Value
  87.                                         SHT.Cells(k + nr_crt, 3).Value = ThisWorkbook.Sheets("RAPORT ZILNIC").Cells(i, j + 2).Value
  88.                                         SHT.Cells(k + nr_crt, 4).Value = ThisWorkbook.Sheets("RAPORT ZILNIC").Cells(i, j + 3).Value
  89.                                         SHT.Cells(k + nr_crt, 5).Value = "D"
  90.  
  91.                                     End If
  92.  
  93.                                 Next k
  94.                                 Set SHT = Nothing
  95.                         End Select
  96.                 Set SHT = Application.ThisWorkbook.Sheets("CENTRALIZATOR SURVEY")
  97.                 SHT.Cells(nr_crt + 13, 6).Value = suma
  98.                 Set SHT = Nothing
  99.                 Next j
  100.                 Next i
  101.  
Dec 18 '06 #1
4 6941
Killer42
8,435 Expert 8TB
I have a problem.
The error in subject appear when i run the macro
Can u tell me why

Expand|Select|Wrap|Line Numbers
  1. Case "CENTRALIZATOR SURVEY"
  2.   Dim SHT As Object
  3.   Set SHT = Application.ThisWorkbook.Sheets("CENTRALIZATOR SURVEY")
  4.   SHT.Cells(10, 1) = UCase(n)
  5.   If IsEmpty(SHT.Cells(11, 1)) = False Then
  6.     Do
  7.       Range(SHT.Cells(11, 1), Cells(11, 8)).Select
  8.       Selection.EntireRow.Delete
  9.       Set SHT = Nothing
  10.     Loop Until IsEmpty(SHT.Cells(11, 1))
  11.   End If
  12.   nr_crt = 0
  13.   suma = 0
  14.   For i = 5 To 184
  15.     For j = 3 To 6
  16.       Select Case ThisWorkbook.Sheets("RAPORT ZILNIC").Cells(i, j).Value
  17.         Case "SURVEY S1"
  18.           Set SHT = Application.ThisWorkbook.Sheets("CENTRALIZATOR SURVEY")
  19.           nr_crt = nr_crt + 1
  20.           suma = suma + 5
  21.           For k = 10 To 10 + nr_crt
  22.             If ThisWorkbook.Sheets("RAPORT ZILNIC").Cells(1, 3).Value = ThisWorkbook.Sheets("CENTRALIZATOR SURVEY").Cells(k, 1).Value Then
  23.               Range(SHT.Cells(k + nr_crt, 1), Cells(k + nr_crt, 8)).Select
  24.               Selection.EntireRow.Copy
  25.               Selection.Insert
  26.               Application.CutCopyMode = False
  27.               SHT.Cells(k + nr_crt, 1).Value = nr_crt
  28.               SHT.Cells(k + nr_crt, 2).Value = ThisWorkbook.Sheets("RAPORT ZILNIC").Cells(i, j + 1).Value
  29.               SHT.Cells(k + nr_crt, 3).Value = ThisWorkbook.Sheets("RAPORT ZILNIC").Cells(i, j + 2).Value
  30.               SHT.Cells(k + nr_crt, 4).Value = ThisWorkbook.Sheets("RAPORT ZILNIC").Cells(i, j + 3).Value
  31.               SHT.Cells(k + nr_crt, 5).Value = "S1"
  32.             End If
  33.           Next k
  34.           Set SHT = Nothing
  35.         Case "SURVEY S2"
  36.           Set SHT = Application.ThisWorkbook.Sheets("CENTRALIZATOR SURVEY")
  37.           nr_crt = nr_crt + 1
  38.           suma = suma + 40
  39.           For k = 10 To 10 + nr_crt
  40.             If ThisWorkbook.Sheets("RAPORT ZILNIC").Cells(1, 3).Value = SHT.Cells(k, 1).Value Then
  41.               Range(SHT.Cells(k + nr_crt, 1), Cells(k + nr_crt, 8)).Select
  42.               Selection.EntireRow.Copy
  43.               Selection.Insert
  44.               Application.CutCopyMode = False
  45.               SHT.Cells(k + nr_crt, 1).Value = nr_crt
  46.               SHT.Cells(k + nr_crt, 2).Value = ThisWorkbook.Sheets("RAPORT ZILNIC").Cells(i, j + 1).Value
  47.               SHT.Cells(k + nr_crt, 3).Value = ThisWorkbook.Sheets("RAPORT ZILNIC").Cells(i, j + 2).Value
  48.               SHT.Cells(k + nr_crt, 4).Value = ThisWorkbook.Sheets("RAPORT ZILNIC").Cells(i, j + 3).Value
  49.               SHT.Cells(k + nr_crt, 5).Value = "S2"
  50.             End If
  51.           Next k
  52.           Set SHT = Nothing
  53.         Case "ALTE SERVICII"
  54.           Set SHT = Application.ThisWorkbook.Sheets("CENTRALIZATOR SURVEY")
  55.           nr_crt = nr_crt + 1
  56.           suma = suma + 10
  57.           For k = 10 To 10 + nr_crt
  58.             If ThisWorkbook.Sheets("RAPORT ZILNIC").Cells(1, 3).Value = SHT.Cells(k, 1).Value Then
  59.               Range(SHT.Cells(k + nr_crt, 1), Cells(k + nr_crt, 8)).Select
  60.               Selection.EntireRow.Copy
  61.               Selection.Insert
  62.               Application.CutCopyMode = False
  63.               SHT.Cells(k + nr_crt, 1).Value = nr_crt
  64.               SHT.Cells(k + nr_crt, 2).Value = ThisWorkbook.Sheets("RAPORT ZILNIC").Cells(i, j + 1).Value
  65.               SHT.Cells(k + nr_crt, 3).Value = ThisWorkbook.Sheets("RAPORT ZILNIC").Cells(i, j + 2).Value
  66.               SHT.Cells(k + nr_crt, 4).Value = ThisWorkbook.Sheets("RAPORT ZILNIC").Cells(i, j + 3).Value
  67.               SHT.Cells(k + nr_crt, 5).Value = "A"
  68.             End If
  69.           Next k
  70.           Set SHT = Nothing
  71.         Case "RECUPERARE"
  72.           Set SHT = Application.ThisWorkbook.Sheets("CENTRALIZATOR SURVEY")
  73.           nr_crt = nr_crt + 1
  74.           suma = suma + 20
  75.           For k = 10 To 10 + nr_crt
  76.             If ThisWorkbook.Sheets("RAPORT ZILNIC").Cells(1, 3).Value = SHT.Cells(k, 1).Value Then
  77.               Range(SHT.Cells(k + nr_crt, 1), Cells(k + nr_crt, 8)).Select
  78.               Selection.EntireRow.Copy
  79.               Selection.Insert
  80.               Application.CutCopyMode = False
  81.               SHT.Cells(k + nr_crt, 1).Value = nr_crt
  82.               SHT.Cells(k + nr_crt, 2).Value = ThisWorkbook.Sheets("RAPORT ZILNIC").Cells(i, j + 1).Value
  83.               SHT.Cells(k + nr_crt, 3).Value = ThisWorkbook.Sheets("RAPORT ZILNIC").Cells(i, j + 2).Value
  84.               SHT.Cells(k + nr_crt, 4).Value = ThisWorkbook.Sheets("RAPORT ZILNIC").Cells(i, j + 3).Value
  85.               SHT.Cells(k + nr_crt, 5).Value = "D"
  86.             End If
  87.           Next k
  88.           Set SHT = Nothing
  89.       End Select
  90.       Set SHT = Application.ThisWorkbook.Sheets("CENTRALIZATOR SURVEY")
  91.       SHT.Cells(nr_crt + 13, 6).Value = suma
  92.       Set SHT = Nothing
  93.     Next j
  94.   Next i
Hi.
I've put [c o d e] tags around your code, as it aids readability. I also reduced thge indenting a bit, just to reduce sideways scrolling a little here on TheScripts. Sorry about that.

Could you please give us a hint as to where in this code the error occurs?

Also, where is the code located? If it's in a module rather than a worksheet, perhaps it doesn't know what to apply the Range method to.
Dec 19 '06 #2
dilau
3
the error appear in first range at case "centralizator survey" and the same in case "centralizator instalari"
Dec 19 '06 #3
dilau
3
and YES is in a module
Dec 19 '06 #4
Killer42
8,435 Expert 8TB
and YES is in a module
Maybe you need to refer to CurrentSheet.Range or something? How is the Range function supposed to know what object sheet you're referring to?
Dec 20 '06 #5

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by James | last post: by
3 posts views Thread by leroybt.rm | last post: by
9 posts views Thread by shank | last post: by
8 posts views Thread by David Thielen | last post: by
26 posts views Thread by Chief | last post: by
3 posts views Thread by traceable1 | last post: by
8 posts views Thread by Sean DiZazzo | last post: by
16 posts views Thread by maya | last post: by
reply views Thread by suresh191 | last post: by
4 posts views Thread by guiromero | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.