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
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
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
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

Unknown Unknown · Accepted Answer · 2015-02-24T14:27:44

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

Method 'range' of object' _Global' failed

1 Answers