CODEWITHSUNDEEP
vbaexcelfind
Patrick S
Patrick S

Reputation: 325

Excel VBA Find method goes wrong?

My Sheet

I have during some time developped some sheets, which relies on the same macro. It will find the headers, and copy the columns from the selected columns.

The sheet is made to compare two different sets of data, whereas the Find function within VBA is used very often.

My Problem

The problem lies within the Find function, whereas it references the same column, with two different names, which have a slight comparison.

It happens with two columns, which has a comparison in name, to some extent, as shown below:

Sub CopyPasteDataLookingForHeader()

    Dim sht As Worksheet
    Set sht = Sheets("Put in data")

    'I'm trying to find the column header Total Lineamount within my sheet
    FindTotalLineVAT = sht.Range("1:1").Find("Total Lineamount").Address(False, False, xlA1)
    TotalLineVAT = Application.WorksheetFunction.Substitute(FindTotalLineVAT, 1, "")

    'FindTotalLineVAT will return Cell "J1"
    'TotalLineVAT will return column "J"

    'I'm trying to find the column header Total Lineamount excl VAT within my sheet
    FindTotalLineXVAT = sht.Range("1:1").Find("Total Lineamount excl VAT").Address(False, False, xlA1)
    TotalLineXVAT = Application.WorksheetFunction.Substitute(FindTotalLineXVAT, 1, "")

    'FindTotalLineXVAT will return Cell "J1"
    'TotalLineXVAT will return column "J"

End Sub

My Goal

I want this formula to return two different results of cell and column. The "Find" function should return two different sets of cells, since the names are different to some extent.

Can someone explain why "Find" is not searching for the entire name, but only bits of it?

Please do let me know, your answer is very much appreciated :)

Upvotes: 3

Views: 807

Answers (1)

DevLuke
DevLuke

Reputation: 40

You get twice the same result as by default the LookAt parameter in the Find function is set to xlPart. Try setting the LookAt parameter to xlWhole in the Find function as follows:

FindTotalLineVAT = sht.Range("1:1").Find("Total Lineamount", Lookat:=xlWhole).Address(False, False, xlA1)

FindTotalLineXVAT = sht.Range("1:1").Find("Total Lineamount excl VAT", Lookat:=xlWhole).Address(False, False, xlA1)

It should now work as expected.

Upvotes: 1

Related Questions