none
Find and replace with VBA XL 2007 RRS feed

  • Question

  • Hi

    I have built several VBA subs that substitute  x values for Y in a spreadsheet (based on one array). It can be several x for each Y, thereby the array for x. My first thought was to setup two arrays and use Lbound and UBOUND to "scroll" down the arrays making the code quick to write. But I dont know wheter it is possible to setup the array for x using several values for each "element" that should correnspond to each Y (if element is the correct term to be use) ?
    I got an error when I tried using this definition crit=array(1,2,(3,4)) where I defined 3 and 4 belonging to the same element.

    if not - is there any ways to define something similiar to an array to let the program scroll down the elements?

    I would be very happy for some ideas/clues to work on. I can provide some code examples if necessarily!
    Many thanks in advance!

    Best Sverker


    Sverreberre

    Wednesday, July 3, 2013 2:26 PM

Answers

  • I think you need to look for numbers, maybe? Try it this way, to cut down on looping:

        

    Sub findrep2()

    Dim target As Range
    Dim cell As Range
    Dim crit As Variant
    Dim crit2 As Variant
    Dim varMatch As Variant
    Dim iOffset As Integer

    'Use numbers, not strings

    crit2 = Array(10000, 10001, 10002, 10091, 10366, 10731, 11096, 11826, 13651)
    crit = Array("tom 3 mån", "tom 3 mån", "tom 3 mån", "tom 3 mån", _
    "ö 3 m - 1 år", "1 - 3 år", "1 - 3 år", "3 - 5 år", "över 5 år", "över 5 år")

    iOffset = 0
    If LBound(crit2) = 0 Then iOffset = 1

    Set target = Sheets("utlåning nya").Range(Range("G1"), Range("G65536").End(xlUp))

    For Each cell In target
        varMatch = Application.Match(cell.Value, crit2, False)
        If Not IsError(varMatch) Then cell.Value = crit(varMatch - iOffset)
    Next cell

    End Sub


    Thursday, July 4, 2013 2:28 PM

All replies

  • I think you should use one-to-one correspondence, so that if you have two X values that need to be replaced by the same Y value, have that Y value appear twice in the array.

    So, for your example:

    X = Array(1,2,3,4)

    Y = Array(7,8,9,9)

    Wednesday, July 3, 2013 2:44 PM
  • Hi again

    My code does not seem to work. Something is wrong but I dont get any error messages. It does not find the values stored in array crit2. If I replace crit2(i) with "hello" (in the if-clause part) and also put this value in col G the code works but of course returns only one value from array crit.

    Sub findrep2()


    Dim target, cell As Range
    Dim i As Integer
    Dim crit As Variant
    Dim crit2 As Variant

    crit2 = Array("10000", "10001", "10002", "10091", "10366", "10731", "11096", "11826", "13651")
    crit = Array("tom 3 mån", "tom 3 mån", "tom 3 mån", "tom 3 mån", "ö 3 m - 1 år", "1 - 3 år", "1 - 3 år", "3 - 5 år", "över 5 år", "över 5 år")
       
        For i = LBound(crit2) To UBound(crit2)
       
            Set target = Sheets("utlåning nya").Range(Range("G1"), Range("G65536").End(xlUp))
            For Each cell In target
                If cell.Value = crit2(i) Then cell.Value = crit(i)
            Next cell
        Next i

    End Sub

    Any ideas of what to do?

    Best regards Sverker


    Sverreberre

    Thursday, July 4, 2013 1:01 PM
  • I think you need to look for numbers, maybe? Try it this way, to cut down on looping:

        

    Sub findrep2()

    Dim target As Range
    Dim cell As Range
    Dim crit As Variant
    Dim crit2 As Variant
    Dim varMatch As Variant
    Dim iOffset As Integer

    'Use numbers, not strings

    crit2 = Array(10000, 10001, 10002, 10091, 10366, 10731, 11096, 11826, 13651)
    crit = Array("tom 3 mån", "tom 3 mån", "tom 3 mån", "tom 3 mån", _
    "ö 3 m - 1 år", "1 - 3 år", "1 - 3 år", "3 - 5 år", "över 5 år", "över 5 år")

    iOffset = 0
    If LBound(crit2) = 0 Then iOffset = 1

    Set target = Sheets("utlåning nya").Range(Range("G1"), Range("G65536").End(xlUp))

    For Each cell In target
        varMatch = Application.Match(cell.Value, crit2, False)
        If Not IsError(varMatch) Then cell.Value = crit(varMatch - iOffset)
    Next cell

    End Sub


    Thursday, July 4, 2013 2:28 PM
  • Tnx, that solved the mystery and saved me a lot of time! Numbers, not text-strings.

    Best Sverker


    Sverreberre

    Friday, July 5, 2013 6:28 PM