Loop through an Array, change values

[Darth269]

Hi there!

I want to loop through an array looking for the below values. IF it finds them, I want to change the value, if it doesn't, just move on.

[TABLE="width: 147"]
<colgroup><col></colgroup><tbody>[TR]
[TD]10 Very Easy[/TD]
[/TR]
[TR]
[TD]10 Demonstrated Well[/TD]
[/TR]
[TR]
[TD]10 Very Satisfied[/TD]
[/TR]
[TR]
[TD]1 Not Demonstrated[/TD]
[/TR]
[TR]
[TD]1 Not Satisfied[/TD]
[/TR]
[TR]
[TD]1 Not Easy

[/TD]
[/TR]
</tbody>[/TABLE]


The value will change to either 10 or 1 accordingly. Hope someone can help. The array is called FeedbackArray.

 

[Fluff]

Is this a VBA array & does it have values like "Very Easy", "Demonstrated Well" etc that need to be changed to either 10 or 1 accordingly?

 

[Darth269]

Hi there!

Yes, it's a VBA array, and has entries of NUMBER + TEXT, i need to remove the text leaving only the number, but only from the specific examples given (there are some NUMBER + TEXT entries that need to remain).

 

[VBE313]

Hello,

The Following UDF Grabs the number. https://www.extendoffice.com/documents/excel/1622-excel-extract-number-from-string.html

Function ExtractNumber(Cell As Range)
Dim i As Long
Dim ResultNum As Long
Dim InputString As String
InputString = Cell.Value
For i = 1 To Len(InputString)
If IsNumeric(Mid(InputString, i, 1)) = True Then
ResultNum = ResultNum & Mid(InputString, i, 1)
End If
Next
ExtractNumber = ResultNum
End Function

Copy this code into a module, and select the cell to the right of your text and type "=ExtractNumber(A1)" and Click the cell that you want to extract. (I used A1 as example)

 

[Fluff]

Maybe something like

   Dim FeedbackArray As Variant
   Dim Sp As Variant
   Dim i As Long
   
   For i = LBound(FeedbackArray) To UBound(FeedbackArray)
      Sp = Split(FeedbackArray(i))
      If Sp(0) = "1" Or Sp(0) = "10" Then FeedbackArray(i) = Sp(0)
   Next i

 

[Darth269]

Hello,

The Following UDF Grabs the number. https://www.extendoffice.com/documents/excel/1622-excel-extract-number-from-string.html

Function ExtractNumber(Cell As Range)
Dim i As Long
Dim ResultNum As Long
Dim InputString As String
InputString = Cell.Value
For i = 1 To Len(InputString)
If IsNumeric(Mid(InputString, i, 1)) = True Then
ResultNum = ResultNum & Mid(InputString, i, 1)
End If
Next
ExtractNumber = ResultNum
End Function

Copy this code into a module, and select the cell to the right of your text and type "=ExtractNumber(A1)" and Click the cell that you want to extract. (I used A1 as example)

Thank you so much for taking the time to do this, however I'm trying to edit the data in an Array, whilst leaving the src data in the worksheet untouched.

 

[Fluff]

Did you see my suggestion in post#5?

 

[Darth269]

Did you see my suggestion in post#5?

Yes, but only just, sorry. I'm thinking through it now... the code is very new to me, so i'm just going to test it now.

 

[Darth269]

Maybe something like

   Dim FeedbackArray As Variant
   Dim Sp As Variant
   Dim i As Long
   
   For i = LBound(FeedbackArray) To UBound(FeedbackArray)
      Sp = Split(FeedbackArray(i))
      If Sp(0) = "1" Or Sp(0) = "10" Then FeedbackArray(i) = Sp(0)
   Next i

Hmmm, i'm getting error 9, subscript out of range... specifically on the line :

[COLOR=#333333] Sp = Split(FeedbackArray(i))[/COLOR]

 

[Fluff]

That sounds like you have a 2d array in which case try

   Dim Sp As Variant
   Dim i As Long
   FeedbackArray = (Range("E3:E8").Value)
   For i = LBound(FeedbackArray) To UBound(FeedbackArray)
      Sp = Split(FeedbackArray(i, [COLOR=#ff0000]1[/COLOR]))
      If Sp(0) = "1" Or Sp(0) = "10" Then FeedbackArray(i, [COLOR=#ff0000]1[/COLOR]) = Sp(0)
   Next i