Method 'range' of object' _Global' failed

Question

Hi i am Very new to vba, first day using it, im trying to get the following code to read down the list and remove all the letter and spaces so i am just left with number in the column to the right. this code is something that i found on line and am trying to edit for my own purposes, any help would be greatly aprecated. i am still googling franticly to see what i can find. i get the error on line starting 'For each cell'

Sub Remove_Alphabets_SpecialChar_RetainDecimalNumber_Test()
   Dim RegX As Object
   Dim Rng As Range
   Set RegX = CreateObject("vbscript.regexp")
   With RegX
     .Global = True
     .Pattern = "[^-0-9-.]"
   End With
   Range(Range("d4").End(xlDown)).Select
   For Each cell In Range(Selected)
     Rng.Offset(, 1) = RegX.Replace(Rng, "")
   Next cell
 End Sub

`Selected` has not been defined. You need this to be a range object e.g.`Set rng = Range("A1:A10")` or `Range("D" & Range("D4").End(xlDown).Row` — Alex P, Feb 24 '15 at 13:21
Ah okay, that makes sence, I know this may sound stupid, where in the code would i define this? Thank you for your amazingly quick responce — Fred Young, Feb 24 '15 at 13:24
At the top put `Dim rng as Range`. Then you need to define the range object (e.g. `Set rng = Range("A1:A10")`) anywhere before the `for...each` loop. — Alex P, Feb 24 '15 at 13:30
That unfortunatly keeps giving me the same error. is is because the worksheet isn't defined correctly? i would like to avoid if possable setting a fixed range as the data that im importing could be anywhere between 1 and 500 lines long, is this a possabily or will i have to limit it? — Fred Young, Feb 24 '15 at 13:51

score 1 · Accepted Answer · 2015-02-24T14:36:41.990

There is no such thing as Selected unless you declare and assign a variable (propably type Range) called Selected. However, there is something called Selection which refers to the currently selected cells.

Range(Range("d4"), Range("d4").End(xlDown)).Select
For Each cell In Range(Selection)
  Rng.Offset(, 1) = RegX.Replace(Rng, "")
Next cell

I've also extended the range you selected from just the last cell in column D to include all cells from D4 to the last cell in column D.

BTW, it is better practise to go into the VBE's Tools ► Options ► Editor and check Require Variable Declaration. It may take a bit of getting used to but your code will benefit in the long run.

Addendum:

I would be remiss if I did not at least try to get you away from using .Select and Selection in your code writing. Relying upon what is currently selected is prone to errors and conflicts. Here is your routine written with direct cell referencing in mind.

Sub Remove_Alphabets_SpecialChar_RetainDecimalNumber_Test()
   Dim r As Long, RegX As Object
   Set RegX = CreateObject("vbscript.regexp")
   With RegX
     .Global = True
     .Pattern = "[^-0-9-.]"
   End With
   For r = 4 To Range("d4").End(xlDown).Row
     Cells(r, "D") = RegX.Replace(Cells(r, "D").Value, "")
   Next r
   Set RegX = Nothing
End Sub

[Here](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) is another great resource on how/why to avoid `Select` and `Activate` methods. — David Zemens, Feb 24 '15 at 14:55
Thank you very much. thank you for explaining the .select and selection issues that can happen when useing them, i will endevour to avoid them from now on, its good to kick bad habbits before they propergate. thank you for the changes you made they seem to be working really well for me. if i was to change the `RegX.Replace(Cells(r, "D")` too `RegX.Ofset(Cells(r, "E").Value, "") ` would it offset the result by one cell or not? — Fred Young, Feb 25 '15 at 08:09

Method 'range' of object' _Global' failed

1 Answers1