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.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
30,568
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

Board Regular
Joined
Mar 22, 2019
Messages
89
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
30,568
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
30,568
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
30,568
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
 

Forum statistics

Threads
1,078,500
Messages
5,340,746
Members
399,393
Latest member
farlow

Some videos you may like

This Week's Hot Topics

  • Problem with Radio Button's format control
    I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
  • Last Display on userform to a Listbox
    [CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
  • Rename and move files to a new location
    Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
  • Help with True/False Formula
    Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
  • Clear extra characters from a provided range of cells
    Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
  • Help with Current and highest streaks
    Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...
Top