VBA Help with Array of text

[decadence]

Hi, Is it possible to shorten this code as I have a hell of lot more I need to remove. My data will consist of 0.001 to 0.999, 1.01 to 9.99 and 1 to 50 with a specific word after which may or may not have a space, Any help would be greatly appreciated

    Dim RngSelection, RngUsedRange, RngIntersect As range, x As Variant, Arr() As Variant
    Set RngSelection = Selection
    Set RngUsedRange = ActiveSheet.UsedRange
    Set RngIntersect = Intersect(RngSelection, RngUsedRange)
    If RngIntersect Is Nothing Then Set RngIntersect = RngUsedRange

        Arr = Array("0.001 WATTS ", "0.001WATTS ", "0.002 WATTS ", "0.002WATTS ", "0.003 WATTS ", "0.003WATTS ", "0.004 WATTS ", "0.004WATTS ", "0.005 WATTS ", "0.005WATTS ", "0.006 WATTS ", "0.006WATTS ", "0.007 WATTS ", "0.007WATTS ", "0.008 WATTS ", "0.008WATTS ", "0.009 WATTS ", "0.009WATTS ", "0.010 WATTS ", "0.010WATTS ", "0.011 WATTS ", "0.011WATTS ", "0.012 WATTS ", "0.012WATTS ", "0.013 WATTS ", "0.013WATTS ", "0.014 WATTS ", "0.014WATTS ", "0.015 WATTS ", "0.015WATTS ", "0.016 WATTS ", "0.016WATTS ", "0.017 WATTS ", "0.017WATTS ", "0.018 WATTS ", "0.018WATTS ", "0.019 WATTS ", "0.019WATTS ", "0.020 WATTS ", "0.020WATTS ", "0.021 WATTS ", "0.021WATTS ", "0.022 WATTS ", "0.022WATTS ", "0.023 WATTS ", "0.023WATTS ", "0.024 WATTS ", "0.024WATTS ", "0.025 WATTS ", "0.025WATTS ", "0.026 WATTS ", "0.026WATTS ", "0.027 WATTS ", "0.027WATTS ", "0.028 WATTS ", "0.028WATTS ", "0.029 WATTS ", "0.029WATTS ", "0.030 WATTS ", "0.030WATTS ", _
                    "0.031 WATTS ", "0.031WATTS ", "0.032 WATTS ", "0.032WATTS ", "0.033 WATTS ", "0.033WATTS ", "0.034 WATTS ", "0.034WATTS ", "0.035 WATTS ", "0.035WATTS ", "0.036 WATTS ", "0.036WATTS ", "0.037 WATTS ", "0.037WATTS ", "0.038 WATTS ", "0.038WATTS ", "0.039 WATTS ", "0.039WATTS ", "0.040 WATTS ", "0.040WATTS ", "0.041 WATTS ", "0.041WATTS ", "0.042 WATTS ", "0.042WATTS ", "0.043 WATTS ", "0.043WATTS ", "0.044 WATTS ", "0.044WATTS ", "0.045 WATTS ", "0.045WATTS ", "0.046 WATTS ", "0.046WATTS ", "0.047 WATTS ", "0.047WATTS ", "0.048 WATTS ", "0.048WATTS ", "0.049 WATTS ", "0.049WATTS ", "0.050 WATTS ", "0.050WATTS ", "0.051 WATTS ", "0.051WATTS ", "0.052 WATTS ", "0.052WATTS ", "0.053 WATTS ", "0.053WATTS ", "0.054 WATTS ", "0.054WATTS ", "0.055 WATTS ", "0.055WATTS ", "0.056 WATTS ", "0.056WATTS ", "0.057 WATTS ", "0.057WATTS ", "0.058 WATTS ", "0.058WATTS ", "0.059 WATTS ", "0.059WATTS ", "0.060 WATTS ", "0.060WATTS ", _
                    "0.061 WATTS ", "0.061WATTS ", "0.062 WATTS ", "0.062WATTS ", "0.063 WATTS ", "0.063WATTS ", "0.064 WATTS ", "0.064WATTS ", "0.065 WATTS ", "0.065WATTS ", "0.066 WATTS ", "0.066WATTS ", "0.067 WATTS ", "0.067WATTS ", "0.068 WATTS ", "0.068WATTS ", "0.069 WATTS ", "0.069WATTS ", "0.070 WATTS ", "0.070WATTS ", "0.071 WATTS ", "0.071WATTS ", "0.072 WATTS ", "0.072WATTS ", "0.073 WATTS ", "0.073WATTS ", "0.074 WATTS ", "0.074WATTS ", "0.075 WATTS ", "0.075WATTS ", "0.076 WATTS ", "0.076WATTS ", "0.077 WATTS ", "0.077WATTS ", "0.078 WATTS ", "0.078WATTS ", "0.079 WATTS ", "0.079WATTS ", "0.080 WATTS ", "0.080WATTS ", "0.081 WATTS ", "0.081WATTS ", "0.082 WATTS ", "0.082WATTS ", "0.083 WATTS ", "0.083WATTS ", "0.084 WATTS ", "0.084WATTS ", "0.085 WATTS ", "0.085WATTS ", "0.086 WATTS ", "0.086WATTS ", "0.087 WATTS ", "0.087WATTS ", "0.088 WATTS ", "0.088WATTS ", "0.089 WATTS ", "0.089WATTS ", "0.090 WATTS ", "0.090WATTS ", _
                    "0.091 WATTS ", "0.091WATTS ", "0.092 WATTS ", "0.092WATTS ", "0.093 WATTS ", "0.093WATTS ", "0.094 WATTS ", "0.094WATTS ", "0.095 WATTS ", "0.095WATTS ", "0.096 WATTS ", "0.096WATTS ", "0.097 WATTS ", "0.097WATTS ", "0.098 WATTS ", "0.098WATTS ", "0.099 WATTS ", "0.099WATTS ", "0.100 WATTS ", "0.100WATTS ", "0.101 WATTS ", "0.101WATTS ", "0.102 WATTS ", "0.102WATTS ", "0.103 WATTS ", "0.103WATTS ", "0.104 WATTS ", "0.104WATTS ", "0.105 WATTS ", "0.105WATTS ", "0.106 WATTS ", "0.106WATTS ", "0.107 WATTS ", "0.107WATTS ", "0.108 WATTS ", "0.108WATTS ", "0.109 WATTS ", "0.109WATTS ", "0.110 WATTS ", "0.110WATTS ", "0.111 WATTS ", "0.111WATTS ", "0.112 WATTS ", "0.112WATTS ", "0.113 WATTS ", "0.113WATTS ", "0.114 WATTS ", "0.114WATTS ", "0.115 WATTS ", "0.115WATTS ", "0.116 WATTS ", "0.116WATTS ", "0.117 WATTS ", "0.117WATTS ", "0.118 WATTS ", "0.118WATTS ", "0.119 WATTS ", "0.119WATTS ", "0.120 WATTS ", "0.120WATTS ", _
                    "0.121 WATTS ", "0.121WATTS ", "0.122 WATTS ", "0.122WATTS ", "0.123 WATTS ", "0.123WATTS ", "0.124 WATTS ", "0.124WATTS ", "0.125 WATTS ", "0.125WATTS ", "0.126 WATTS ", "0.126WATTS ", "0.127 WATTS ", "0.127WATTS ", "0.128 WATTS ", "0.128WATTS ", "0.129 WATTS ", "0.129WATTS ", "0.130 WATTS ", "0.130WATTS ", "0.131 WATTS ", "0.131WATTS ", "0.132 WATTS ", "0.132WATTS ", "0.133 WATTS ", "0.133WATTS ", "0.134 WATTS ", "0.134WATTS ", "0.135 WATTS ", "0.135WATTS ", "0.136 WATTS ", "0.136WATTS ", "0.137 WATTS ", "0.137WATTS ", "0.138 WATTS ", "0.138WATTS ", "0.139 WATTS ", "0.139WATTS ", "0.140 WATTS ", "0.140WATTS ", "0.141 WATTS ", "0.141WATTS ", "0.142 WATTS ", "0.142WATTS ", "0.143 WATTS ", "0.143WATTS ", "0.144 WATTS ", "0.144WATTS ", "0.145 WATTS ", "0.145WATTS ", "0.146 WATTS ", "0.146WATTS ", "0.147 WATTS ", "0.147WATTS ", "0.148 WATTS ", "0.148WATTS ", "0.149 WATTS ", "0.149WATTS ", "0.150 WATTS ", "0.150WATTS ", _
                    "0.151 WATTS ", "0.151WATTS ", "0.152 WATTS ", "0.152WATTS ", "0.153 WATTS ", "0.153WATTS ", "0.154 WATTS ", "0.154WATTS ", "0.155 WATTS ", "0.155WATTS ", "0.156 WATTS ", "0.156WATTS ", "0.157 WATTS ", "0.157WATTS ", "0.158 WATTS ", "0.158WATTS ", "0.159 WATTS ", "0.159WATTS ", "0.160 WATTS ", "0.160WATTS ", "0.161 WATTS ", "0.161WATTS ", "0.162 WATTS ", "0.162WATTS ", "0.163 WATTS ", "0.163WATTS ", "0.164 WATTS ", "0.164WATTS ", "0.165 WATTS ", "0.165WATTS ", "0.166 WATTS ", "0.166WATTS ", "0.167 WATTS ", "0.167WATTS ", "0.168 WATTS ", "0.168WATTS ", "0.169 WATTS ", "0.169WATTS ", "0.170 WATTS ", "0.170WATTS ", "0.171 WATTS ", "0.171WATTS ", "0.172 WATTS ", "0.172WATTS ", "0.173 WATTS ", "0.173WATTS ", "0.174 WATTS ", "0.174WATTS ", "0.175 WATTS ", "0.175WATTS ", "0.176 WATTS ", "0.176WATTS ", "0.177 WATTS ", "0.177WATTS ", "0.178 WATTS ", "0.178WATTS ", "0.179 WATTS ", "0.179WATTS ", "0.180 WATTS ", "0.180WATTS ", _
                    "0.181 WATTS ", "0.181WATTS ", "0.182 WATTS ", "0.182WATTS ", "0.183 WATTS ", "0.183WATTS ", "0.184 WATTS ", "0.184WATTS ", "0.185 WATTS ", "0.185WATTS ", "0.186 WATTS ", "0.186WATTS ", "0.187 WATTS ", "0.187WATTS ", "0.188 WATTS ", "0.188WATTS ", "0.189 WATTS ", "0.189WATTS ", "0.190 WATTS ", "0.190WATTS ", "0.191 WATTS ", "0.191WATTS ", "0.192 WATTS ", "0.192WATTS ", "0.193 WATTS ", "0.193WATTS ", "0.194 WATTS ", "0.194WATTS ", "0.195 WATTS ", "0.195WATTS ", "0.196 WATTS ", "0.196WATTS ", "0.197 WATTS ", "0.197WATTS ", "0.198 WATTS ", "0.198WATTS ", "0.199 WATTS ", "0.199WATTS ", "0.200 WATTS ", "0.200WATTS ", "0.201 WATTS ", "0.201WATTS ", "0.202 WATTS ", "0.202WATTS ", "0.203 WATTS ", "0.203WATTS ", "0.204 WATTS ", "0.204WATTS ", "0.205 WATTS ", "0.205WATTS ", "0.206 WATTS ", "0.206WATTS ", "0.207 WATTS ", "0.207WATTS ", "0.208 WATTS ", "0.208WATTS ", "0.209 WATTS ", "0.209WATTS ", "0.210 WATTS ", "0.210WATTS ", _
                    "0.211 WATTS ", "0.211WATTS", "0.212 WATTS ", "0.212WATTS", "0.213 WATTS ", "0.213WATTS", "0.214 WATTS ", "0.214WATTS", "0.215 WATTS ", "0.215WATTS", "0.216 WATTS ", "0.216WATTS", "0.217 WATTS ", "0.217WATTS", "0.218 WATTS ", "0.218WATTS", "0.219 WATTS ", "0.219WATTS", "0.220 WATTS ", "0.220WATTS", "0.221 WATTS ", "0.221WATTS", "0.222 WATTS ", "0.222WATTS", "0.223 WATTS ", "0.223WATTS", "0.224 WATTS ", "0.224WATTS", "0.225 WATTS ", "0.225WATTS", "0.226 WATTS ", "0.226WATTS", "0.227 WATTS ", "0.227WATTS", "0.228 WATTS ", "0.228WATTS", "0.229 WATTS ", "0.229WATTS", "0.230 WATTS ", "0.230WATTS", "0.231 WATTS ", "0.231WATTS", "0.232 WATTS ", "0.232WATTS", "0.233 WATTS ", "0.233WATTS", "0.234 WATTS ", "0.234WATTS", "0.235 WATTS ", "0.235WATTS", "0.236 WATTS ", "0.236WATTS", "0.237 WATTS ", "0.237WATTS", "0.238 WATTS ", "0.238WATTS", "0.239 WATTS ", "0.239WATTS", "0.240 WATTS ", "0.240WATTS", _
                    "0.241 WATTS ", "0.241WATTS", "0.242 WATTS ", "0.242WATTS", "0.243 WATTS ", "0.243WATTS", "0.244 WATTS ", "0.244WATTS", "0.245 WATTS ", "0.245WATTS", "0.246 WATTS ", "0.246WATTS", "0.247 WATTS ", "0.247WATTS", "0.248 WATTS ", "0.248WATTS", "0.249 WATTS ", "0.249WATTS", "0.250 WATTS ", "0.250WATTS", "0.251 WATTS ", "0.251WATTS", "0.252 WATTS ", "0.252WATTS", "0.253 WATTS ", "0.253WATTS", "0.254 WATTS ", "0.254WATTS", "0.255 WATTS ", "0.255WATTS", "0.256 WATTS ", "0.256WATTS", "0.257 WATTS ", "0.257WATTS", "0.258 WATTS ", "0.258WATTS", "0.259 WATTS ", "0.259WATTS", "0.260 WATTS ", "0.260WATTS", "0.261 WATTS ", "0.261WATTS", "0.262 WATTS ", "0.262WATTS", "0.263 WATTS ", "0.263WATTS", "0.264 WATTS ", "0.264WATTS", "0.265 WATTS ", "0.265WATTS", "0.266 WATTS ", "0.266WATTS", "0.267 WATTS ", "0.267WATTS", "0.268 WATTS ", "0.268WATTS", "0.269 WATTS ", "0.269WATTS", "0.270 WATTS ", "0.270WATTS", _
                    "0.271 WATTS ", "0.271WATTS", "0.272 WATTS ", "0.272WATTS", "0.273 WATTS ", "0.273WATTS", "0.274 WATTS ", "0.274WATTS", "0.275 WATTS ", "0.275WATTS", "0.276 WATTS ", "0.276WATTS", "0.277 WATTS ", "0.277WATTS", "0.278 WATTS ", "0.278WATTS", "0.279 WATTS ", "0.279WATTS", "0.280 WATTS ", "0.280WATTS", "0.281 WATTS ", "0.281WATTS", "0.282 WATTS ", "0.282WATTS", "0.283 WATTS ", "0.283WATTS", "0.284 WATTS ", "0.284WATTS", "0.285 WATTS ", "0.285WATTS", "0.286 WATTS ", "0.286WATTS", "0.287 WATTS ", "0.287WATTS", "0.288 WATTS ", "0.288WATTS", "0.289 WATTS ", "0.289WATTS", "0.290 WATTS ", "0.290WATTS", "0.291 WATTS ", "0.291WATTS", "0.292 WATTS ", "0.292WATTS", "0.293 WATTS ", "0.293WATTS", "0.294 WATTS ", "0.294WATTS", "0.295 WATTS ", "0.295WATTS", "0.296 WATTS ", "0.296WATTS", "0.297 WATTS ", "0.297WATTS", "0.298 WATTS ", "0.298WATTS", "0.299 WATTS ", "0.299WATTS", "0.300 WATTS ", "0.300WATTS", _
                    "0.301 WATTS ", "0.301WATTS", "0.302 WATTS ", "0.302WATTS", "0.303 WATTS ", "0.303WATTS", "0.304 WATTS ", "0.304WATTS", "0.305 WATTS ", "0.305WATTS", "0.306 WATTS ", "0.306WATTS", "0.307 WATTS ", "0.307WATTS", "0.308 WATTS ", "0.308WATTS", "0.309 WATTS ", "0.309WATTS", "0.310 WATTS ", "0.310WATTS", "0.311 WATTS ", "0.311WATTS", "0.312 WATTS ", "0.312WATTS", "0.313 WATTS ", "0.313WATTS", "0.314 WATTS ", "0.314WATTS", "0.315 WATTS ", "0.315WATTS", "0.316 WATTS ", "0.316WATTS", "0.317 WATTS ", "0.317WATTS", "0.318 WATTS ", "0.318WATTS", "0.319 WATTS ", "0.319WATTS", "0.320 WATTS ", "0.320WATTS", "0.321 WATTS ", "0.321WATTS", "0.322 WATTS ", "0.322WATTS", "0.323 WATTS ", "0.323WATTS", "0.324 WATTS ", "0.324WATTS", "0.325 WATTS ", "0.325WATTS", "0.326 WATTS ", "0.326WATTS", "0.327 WATTS ", "0.327WATTS", "0.328 WATTS ", "0.328WATTS", "0.329 WATTS ", "0.329WATTS", "0.330 WATTS ", "0.330WATTS", _
                    "0.331 WATTS ", "0.331WATTS", "0.332 WATTS ", "0.332WATTS", "0.333 WATTS ", "0.333WATTS", "0.334 WATTS ", "0.334WATTS", "0.335 WATTS ", "0.335WATTS", "0.336 WATTS ", "0.336WATTS", "0.337 WATTS ", "0.337WATTS", "0.338 WATTS ", "0.338WATTS", "0.339 WATTS ", "0.339WATTS", "0.340 WATTS ", "0.340WATTS", "0.341 WATTS ", "0.341WATTS", "0.342 WATTS ", "0.342WATTS", "0.343 WATTS ", "0.343WATTS", "0.344 WATTS ", "0.344WATTS", "0.345 WATTS ", "0.345WATTS", "0.346 WATTS ", "0.346WATTS", "0.347 WATTS ", "0.347WATTS", "0.348 WATTS ", "0.348WATTS", "0.349 WATTS ", "0.349WATTS", "0.350 WATTS ", "0.350WATTS", "0.351 WATTS ", "0.351WATTS", "0.352 WATTS ", "0.352WATTS", "0.353 WATTS ", "0.353WATTS", "0.354 WATTS ", "0.354WATTS", "0.355 WATTS ", "0.355WATTS", "0.356 WATTS ", "0.356WATTS", "0.357 WATTS ", "0.357WATTS", "0.358 WATTS ", "0.358WATTS", "0.359 WATTS ", "0.359WATTS", "0.360 WATTS ", "0.360WATTS", _
                    "0.361 WATTS ", "0.361WATTS", "0.362 WATTS ", "0.362WATTS", "0.363 WATTS ", "0.363WATTS", "0.364 WATTS ", "0.364WATTS", "0.365 WATTS ", "0.365WATTS", "0.366 WATTS ", "0.366WATTS", "0.367 WATTS ", "0.367WATTS", "0.368 WATTS ", "0.368WATTS", "0.369 WATTS ", "0.369WATTS", "0.370 WATTS ", "0.370WATTS", "0.371 WATTS ", "0.371WATTS", "0.372 WATTS ", "0.372WATTS", "0.373 WATTS ", "0.373WATTS", "0.374 WATTS ", "0.374WATTS", "0.375 WATTS ", "0.375WATTS", "0.376 WATTS ", "0.376WATTS", "0.377 WATTS ", "0.377WATTS", "0.378 WATTS ", "0.378WATTS", "0.379 WATTS ", "0.379WATTS", "0.380 WATTS ", "0.380WATTS", "0.381 WATTS ", "0.381WATTS", "0.382 WATTS ", "0.382WATTS", "0.383 WATTS ", "0.383WATTS", "0.384 WATTS ", "0.384WATTS", "0.385 WATTS ", "0.385WATTS", "0.386 WATTS ", "0.386WATTS", "0.387 WATTS ", "0.387WATTS", "0.388 WATTS ", "0.388WATTS", "0.389 WATTS ", "0.389WATTS", "0.390 WATTS ", "0.390WATTS", _
                    "0.391 WATTS ", "0.391WATTS", "0.392 WATTS ", "0.392WATTS", "0.393 WATTS ", "0.393WATTS", "0.394 WATTS ", "0.394WATTS", "0.395 WATTS ", "0.395WATTS", "0.396 WATTS ", "0.396WATTS", "0.397 WATTS ", "0.397WATTS", "0.398 WATTS ", "0.398WATTS", "0.399 WATTS ", "0.399WATTS", "0.400 WATTS ", "0.400WATTS")
    Application.ScreenUpdating = False
    For Each x In Arr
    With RngIntersect
        .Replace What:=x, Replacement:="", LookAt:=xlPart
            End With
                Next x

 

[decadence]

I am getting Invalid Next control variable reference on Next x.

 

[RoryA]

Sorry - that should be For x = ...

 

[Rick Rothstein]

Have now tried it and nothing happens, Perhaps it's because what I am looking for is within a string of words.

I have read through your posts and responses to others and am confused as to what it is your code is supposed to be doing. Can you describe in words what you are trying to accomplish?

 

[decadence]

Just tried it and it seems to be intermittent with removing some but leaving some, any ideas why this might be the case?

 

[RoryA]

That would suggest that the text doesn't actually match.

 

[decadence]

Hi Rick, my apologies for any confusion, Basically I am trying to remove characters out of a string, my first post with code does what i need it to but way too many search criteria in the array and I have a lot more I wish to remove as each string may or may not contain between 0.001 WATTS to 50 WATTS.

 

[Rick Rothstein]

Hi Rick, my apologies for any confusion, Basically I am trying to remove characters out of a string, my first post with code does what i need it to but way too many search criteria in the array and I have a lot more I wish to remove as each string may or may not contain between 0.001 WATTS to 50 WATTS.

Just for clarification, can you show us two or three examples... show us (varying if possible) examples of the text before anything is done to them and then show us what each should look like after the code runs.

Also, a second question... could there amounts of WATTS (such as, say, 60 WATTS) which will not be removed from any text containing them?

 

[decadence]

Just for clarification, can you show us two or three examples... show us (varying if possible) examples of the text before anything is done to them and then show us what each should look like after the code runs.

From This

Products	Specifications
Product1	Does Not Contain 0.001 WATTS so Something else
Product2	Contains 0.063 WATTS something
Product3	Does Not Contain 2.1WATTS so this is something
Product4	Does Not Contain 36.5WATTS this is not something
Product5	May Contain 0.12 WATTS
Product6	Does Not Contain 7 WATTS is something
Product7	May not Contain 30 WATTS could be nothing
Product8	Does Not Contain 50WATTS might be something
Product9	Possibly Contains 0.1WATTS might be nothing

<tbody>
</tbody>

To This

Products	Specifications
Product1	Does Not Contain so Something else
Product2	Contains something
Product3	Does Not Contain so this is something
Product4	Does Not Contain this is not something
Product5	May Contain
Product6	Does Not Contain is something
Product7	May not Contain could be nothing
Product8	Does Not Contain might be something
Product9	Possibly Contains might be nothing

<tbody>
</tbody>

Answer to the second question is there might be above 50+ WATTS but rarely, the amount of WATTS is not required in the string

 

[Rick Rothstein]

If your goal is to remove the word "WATTS" and whatever number is in front of it, then select the cells you want to do this to and then give this macro a try (note, no array is needed)...

[table="width: 500"]
[tr]
	[td]Sub RemoveWATTSandValueInFrontOfIt()
  Dim R As Long, C As Long, Data As Variant, Parts() As String
  Data = Selection.Value
  Application.ScreenUpdating = False
  For R = 1 To UBound(Data, 1)
    For C = 1 To UBound(Data, 2)
      Parts = Split(Data(R, C), "WATTS")
      Parts(0) = Left(RTrim(Parts(0)), InStrRev(RTrim(Parts(0)), " ") - 1)
      Data(R, C) = Join(Parts, "")
    Next
  Next
  Selection = Data
  Application.ScreenUpdating = True
End Sub[/td]
[/tr]
[/table]

 

[decadence]

Yes that is the goal Rick.

However

I am getting Run-time Error 13: Type Mismatch if I select a single cell

  For R = 1 To UBound(Data, 1)