Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Loop through an Array, change values
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Oct 2018
    Posts
    15
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Loop through an Array, change values

    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


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

  2. #2
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    29,229
    Post Thanks / Like
    Mentioned
    483 Post(s)
    Tagged
    49 Thread(s)

    Default Re: Loop through an Array, change values

    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?
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  3. #3
    New Member
    Join Date
    Oct 2018
    Posts
    15
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Loop through an Array, change values

    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).

  4. #4
    Board Regular
    Join Date
    Mar 2019
    Posts
    81
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Loop through an Array, change values

    Hello,

    The Following UDF Grabs the number. https://www.extendoffice.com/documen...om-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 by VBE313; Oct 7th, 2019 at 10:47 AM.

  5. #5
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    29,229
    Post Thanks / Like
    Mentioned
    483 Post(s)
    Tagged
    49 Thread(s)

    Default Re: Loop through an Array, change values

    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
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  6. #6
    New Member
    Join Date
    Oct 2018
    Posts
    15
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Loop through an Array, change values

    Quote Originally Posted by VBE313 View Post
    Hello,

    The Following UDF Grabs the number. https://www.extendoffice.com/documen...om-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.

  7. #7
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    29,229
    Post Thanks / Like
    Mentioned
    483 Post(s)
    Tagged
    49 Thread(s)

    Default Re: Loop through an Array, change values

    Did you see my suggestion in post#5?
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  8. #8
    New Member
    Join Date
    Oct 2018
    Posts
    15
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Loop through an Array, change values

    Quote Originally Posted by Fluff View Post
    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.

  9. #9
    New Member
    Join Date
    Oct 2018
    Posts
    15
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Loop through an Array, change values

    Quote Originally Posted by Fluff View Post
    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:
     Sp = Split(FeedbackArray(i))

  10. #10
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    29,229
    Post Thanks / Like
    Mentioned
    483 Post(s)
    Tagged
    49 Thread(s)

    Default Re: Loop through an Array, change values

    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, 1))
          If Sp(0) = "1" Or Sp(0) = "10" Then FeedbackArray(i, 1) = Sp(0)
       Next i
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •