CODEWITHSUNDEEP
vbaexcelexcel-formulaexcel-2010
bdpolinsky
bdpolinsky

Reputation: 491

Worksheet Function in Excel Vba with concatenated values

I have the following formula:

 dim functionString as String

 functionString = "IFERROR(AND(MATCH(" & FirstName.Value & ",C2:C1048576, 0)>0, (MATCH(" & LastName.Value & ",B2:B1048576, 0)>0)), MAX(A2:A1048576)+1)"

What I want to be able to do is call it from the VBA code so it would look like.

 application.WorksheetFunction(functionString)

I know that I can place it on the worksheet at some cell that's never going to be used: IE:

 Activesheet.range("ZZ1000").formula = "="& functionString

and then reference that cell without worrying whether the program would inadvertently crash; but is there a way to do such a formula from VBA directly?

Basically I'm looking to see whether FirstName.Value and LastName.Value (which are defined elsewhere in the code) together are in the worksheet in column B and column C. As I'm writing this, I realized I need to make sure that they are both in the same row as well and not in different rows.

Upvotes: 0

Views: 353

Answers (1)

David Zemens
David Zemens

Reputation: 53663

You could try Application.Evaluate(functionString) but depending on complexity it may be better to use VBA functions instead of WorksheetFunctions.

The Application.Match function will return an error type if the value is not found in the range/array, so we Dim first, last as variant type to allow for this (without raising an error).

Dim first, last
' find the row where FirstName.Value appears in column C
first = Application.Match(FirstName.Value, Columns(3), False))
' find the row where LastName.Value appears in column B
last = Application.Match(LastName.Value, Columns(2), False))

If Not IsError(first) And Not IsError(last) Then
    If first = last Then
        ' match was found in both columns and on same row
        ' do something else...

    End If
End If

Upvotes: 1

Related Questions