CODEWITHSUNDEEP
vbaexcelrange
Fred Young
Fred Young

Reputation: 13

Method 'range' of object' _Global' failed

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

Upvotes: 1

Views: 1243

Answers (1)

user4039065
user4039065

Reputation:

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

Upvotes: 1

Related Questions