Loop through an Array, change values

Darth269

New Member
Joined
Oct 4, 2018
Messages
15
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.

10 Very Easy
10 Demonstrated Well
10 Very Satisfied
1 Not Demonstrated
1 Not Satisfied
1 Not Easy


<colgroup><col></colgroup><tbody>
</tbody>


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

Some videos you may like

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
36,133
Office Version
365
Platform
Windows
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

New Member
Joined
Oct 4, 2018
Messages
15
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

Active Member
Joined
Mar 22, 2019
Messages
459
Office Version
365
Platform
Windows
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)
 
Last edited:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
36,133
Office Version
365
Platform
Windows
Maybe something like
Code:
   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

New Member
Joined
Oct 4, 2018
Messages
15
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
36,133
Office Version
365
Platform
Windows
Did you see my suggestion in post#5?
 

Darth269

New Member
Joined
Oct 4, 2018
Messages
15
Maybe something like
Code:
   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 :

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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
36,133
Office Version
365
Platform
Windows
That sounds like you have a 2d array in which case try
Code:
   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
 

Watch MrExcel Video

Forum statistics

Threads
1,090,444
Messages
5,414,546
Members
403,532
Latest member
mikexcel12

This Week's Hot Topics

Top