-1

FIXED: check user3964075's comment

Need help with my simple code below: it's basically a different version of vlookup where you can also specify which row to look for.

asda(fval, rng, fcol, rcol)

fval is what the user is looking for

rng is the range

fcol is by vlookup default, set to 1, now the user can pick which column to use as basis for the search

rcol is the column which will be returned if match is found

See code below:

Function asda(fval As Range, rng As Range, fCol As Integer, rCol As Integer)

    Dim row As Variant

    For Each row In rng.Rows
        If fval.Value = rng.Columns(fCol).Rows(row).Value Then
            result = rng.Columns(rCol).Rows(row).Value
            GoTo found
        End If
    Next

found:
    asda = result

End Function

Problem: It does not work and I don't know why. Inasmuch as I'd like to use other people's code, I'd want to start with mine and fix it.

Fixed code to anyone reading this in the future:

Function asda(fval As Range, rng As Range, fCol As Integer, rCol As Integer)
Dim row As Variant
Dim rowc As Integer

rowc = 1
For Each row In rng.Rows
    If fval.Value = rng.Cells(rowc, fCol).Value Then
        result = rng.Cells(rowc, rCol).Value
        Exit For
    End If
    rowc = rowc + 1
Next

asda= result

End Function

hakusai
  • 3
  • 2
  • How doesn't it work? Also, please don't get into the habbit of using goto. There are other, much better, ways of exiting loops. In this case, you can use the VBA Exit loop construct `Exit For` instead of the goto. Another option would be to set `asda = rng.Columns(rCol, row).Value` inside the loop and then using `Exit Function` – SBI May 29 '15 at 12:46
  • Hi, thanks for Exit For. It only returns #value, for some reason. – hakusai May 29 '15 at 12:46
  • 2
    You can't string together `.Columns(fCol).Rows(row)` try `rng.Cells(row, fCol).Value`. – CuberChase May 29 '15 at 12:48
  • Don't get me wrong. I also like to play around with custome functions. But in that case it would be so much easier and faster to use the Excel built-in functions `INDEX`and `MATCH` to get to the same result (if I understand your code correctly). You might want to look at this article: http://www.mbaexcel.com/excel/why-index-match-is-better-than-vlookup/ – Ralph May 29 '15 at 12:54
  • 1
    @CuberChase there's nothing wrong with `.Columns(fCol).Rows(row)`. The only problem with this line is that `row` is a `Range`, not an `Integer` – BrakNicku May 29 '15 at 12:55
  • Oh now it works. True, row is range and not an integer. To everyone, thanks for the reply. And @Ralph, I'm using that too, but I'm trying to teach myself UDFs and decided to try with some easy stuff before doing some complex ones. – hakusai May 29 '15 at 12:59
  • @user3964075 dang, you're right. I've never actually gone to use `Rows()` and `Columns()` like that. Lesson here, is to declare you variables as defined types not `Variants` – CuberChase May 29 '15 at 12:59

1 Answers1

0

Look at the comments for the first explaination.

And as you were using row as a variable you couldn't call the Row Property from the Range class, so I changed it to RowV to use RowV.row.

Compiling what have been said :

Function asda(fval As Range, rng As Range, fCol As Integer, rCol As Integer)

    Dim RowV As Range

    For Each RowV In rng.Rows
        If fval.Value <> rng.Cells(RowV.row, fCol).Value Then
        Else
            asda = rng.Columns(rCol).Rows(RowV.row).Value
            'Exit For
            Exit Function
        End If
    Next RowV

    asda = "Value not found"

End Function
R3uK
  • 14,417
  • 7
  • 43
  • 77
  • This is actually very clean and elegant. Thank you. – hakusai May 29 '15 at 13:08
  • Thanks and You are welcome! But remember to avoid using names that may already been use classically in VBA to avoid that kind of problems! ;) – R3uK May 29 '15 at 13:12