VBA search function

[lionginass]

Hello,


I' am trying to create a formula to find a first value in a row with VBA.

My code:

Function Secret(Qty As Range)

x = Qty.Find("*", , xlValues, xlWhole, xlByRows, xlNext, False, False, False).Value

Secret = x

End Function

Lets say i select range A1:G1 (Qty = A1:G1)

Values are: A1 = 5; D1 = 2; G1 = 8

My formula returns 2 instead of 5. Anyone has an idea why?
It is something wrong with A1, because if i delete 5 from A1 and enter it in B1, then formula returns 5 which is correct.
I had an idea that search function doesn't count the first cell in a range, but if I delete all other values and enter only 5 in A1; then formula returns 5.

If i want to search the last value in a row, then i change xlNext to xlPrevious, and then formula returns 8, which is correct,
so this issue is only when i try to find first value and something is strange with first cell

Cant find any help regarding this in internet.. seems like obvious thing but is to difficult for beginner

thanks for help

 

[steve the fish]

From what i gather you just want the first cell in the target range. You could use vba but there are perfectly simple native functions such as

=INDEX(range,1)

 

[lionginass]

I know this function.

Thing is that i creating a much more complex function, and finding the first value in selected range is one of many steps in this formula, so thats why i use VBA.

Found on internet:
[FONT=&quot]"Notice that [/FONT]After [FONT=&quot]must be a single cell in the range. Remember that the search begins after this cell; the specified cell isn't searched until the method wraps back around to this cell. If you do no specify this argument, the search starts after the cell in the upper-left corner of the range."

According to that Find function starts to search from B1 in my case, and A1 is actually the last cell to formula searches in.

Any ideas how to out-work this?
Maybe there is another methods apart from search function?
i need to find out value of first cell in selected range[/FONT]

 

[steve the fish]

Ok but still cant really see why you use .Find. You could just use:

[I]rng[/I].Cells(1).Value

 

[lionginass]

Because in some cases firs cell is blank and i need first non blank.

Anyway, found out solution in other sites.
here is the code for future generations
Function - First non blank value in a range


Function Secret(Qty As Range)

x = Qty.Find("*", , xlValues, xlWhole, xlByRows, xlPrevious, False, False, False).Value
rangex = Qty.Find("*", , xlValues, xlWhole, xlByRows, xlPrevious, False, False, False).Address
y = Qty.Find("*", Range(rangex), xlValues, xlWhole, xlByRows, xlNext, False, False, False).Value
Secret = y

End Function

 

[steve the fish]

Ah ok see if you can see what this is doing:

Function findfirst(rng As Range) As String
With rng
    findfirst = .Find(What:="*", SearchDirection:=xlByRows, After:=.Cells(.Rows.Count, .Columns.Count), LookIn:=xlValues).Value
End With
End Function

 

[Fluff]

Another option

Function Secret(Qty As Range)

If Qty.Cells(1, 1) <> "" Then
   Secret = Qty.Cells(1, 1)
Else
   Secret = Qty.find("*", Qty.Cells(1, 1), xlValues, xlWhole, xlByRows, xlNext, False, False, False).Value
End If
End Function