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

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
35,705
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
444
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
35,705
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
35,705
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
35,705
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,089,619
Messages
5,409,376
Members
403,260
Latest member
ssauk

This Week's Hot Topics

  • help please
    SORRY NOT ANY GOOD AT EXCEL SO HELP WOULD BE MUCH APPRECIATED this formula is in a sheet called ignore...
  • two formulas needed
    Hello, I'll try my best to explain this: First formula needed in Sheet1 cell A2: If Sheet1 cell B2 = Sheet2 cell B2 then return a 1. If not then...
  • Dynamic Counts
    Good afternoon, we are tidying up some data & the data seems to be growing quicker than we are tidying it up! What we confirm (by reviewing it...
  • Help Excel formula eliminate duplicate values and keep only 2 identical rows.
    as picture below column A has a duplicate value. but the values are not the same as the rule. sometimes 4 rows, sometimes 10 rows or 7 or 9...
  • Macro Compile Error Sub or Function not defined
    Hello, I am trying to run macros from a validation list, all macros have been created and run perfectly on there own but I'm getting a compile...
  • Last row combined with Current Region VBA
    I'm generally happy finding the last row of data through something like Lastrow = Cells(Rows.Count, "D").End(xlUp) but I don't always receive data...
Top