VBA - Search not returning the value - despite finding it

[ffialho]

Hi All,

If I could pick you brain for a bit. I have a spreadsheet attached with 2 sheets:

Sheet 1 -> Base_214
Sheet 2 --> Base_120_Ajustada

Sheet 1 is the main and Sheet 2 is a support that has only 1 info needed do the MAIN SHEET 1 ( Base 214)

My problem:
Sheet 2 has the PO payment date in column A
I need the payment date to be shown in Sheet 1

I created a key using customer ID + PO Number in Sheet 1
did the same in Sheet 2

BUT: PO number in Sheet 2 has more caracters then the PO number in sheet 1 ( main sheet)
ex:
PO Sheet 1: ABCD122
PO Sheet 2: ABCD122#1 ( there´s no pattern to how many caracters are added AFTER the PO #)

So what I did is a search to see if PO in Sheet 1 is contained in PO Sheet 2 to return the Payment for that PO found in Sheet 2.
Since this PO # can be repeated a few times in the list - I used the MIN function to obtain the oldest payment date.

point is at the end it just returns 1/1/1900

There is like 35000 rows,

For clarification

Key_I - lead key that needs to be found is in column AS of BASE214 ( Main sheet)
Key_II -the one the above key will see if it´s contained is is in column U of Base_120_Ajustada ( Support Sheet)
PO Payment Date - Is in column A of Base_120_Ajustada ( Support Sheet)
If found PO Payment is to be filled in column AT of BASE214 ( Main sheet)

any help would be great.

Sub Search_Key_Returns_Date()
 Dim UL As Long
  UWht L = Sheets("Base_120_Ajustada").Cells(Rows.Count, "U").End(xlUp).Row
  With Sheets("Base214")
   .Columns("AT") = ""
   .Range("AT2:AT" & .Cells(Rows.Count, "AS").End(xlUp).Row).Formula = _
     "=MIN(IF(ISNUMBER(SEARCH(AS2,Base_120_Ajustada!U$2:U$" & UL & ")),Base_120_Ajustada!A$2:A$" & UL & "))"
   .Columns("AT").Value = .Columns("AT").Value
   .Columns("AT").NumberFormat = "dd/mm/yyyy": .Columns("AT").AutoFit
  End With
End Sub

 

[rlv01]

First you need to sort out your formula manually, independent of the VBA, to get it to return the result you want for a test case. Then worry about the VBA component.

Temporarily remove this line

.Columns("AT").Value = .Columns("AT").Value

So you can examine the formula and determine why it is not returning the result you expect.

 

[steve the fish]

What is UL?

 

[ffialho]

What is UL?

Hi Steve,

UL = lastrow --> so I would look for the last filled row in column "U" - which has the Key_I that Im using as reference to return the value in column "A".

 

[Joe4]

Hi Steve,

UL = lastrow --> so I would look for the last filled row in column "U" - which has the Key_I that Im using as reference to return the value in column "A".

Is there a typo there?
Where:

 Dim UL As Long
  UWht L = Sheets("Base_120_Ajustada").Cells(Rows.Count, "U").End(xlUp).Row

should actually be:

 Dim UL As Long
  UL = Sheets("Base_120_Ajustada").Cells(Rows.Count, "U").End(xlUp).Row

 

[ffialho]

Is there a typo there?
Where:

 Dim UL As Long
  UWht L = Sheets("Base_120_Ajustada").Cells(Rows.Count, "U").End(xlUp).Row

should actually be:

 Dim UL As Long
  UL = Sheets("Base_120_Ajustada").Cells(Rows.Count, "U").End(xlUp).Row

you are correct. there was typo when I pasted here.

 

[steve the fish]

In that code you have given you arent assigning any value to the variable UL but presume that isnt the case in the actual macro. The prodlem with using MIN is that the false part of the IF will be producing 0 so the MIN will be 0. Put a large number in the false part of the IF.

 

[ffialho]

In that code you have given you arent assigning any value to the variable UL but presume that isnt the case in the actual macro. The prodlem with using MIN is that the false part of the IF will be producing 0 so the MIN will be 0. Put a large number in the false part of the IF.

UL isnt a value but a column. UL = lastrow from the column "U". If I misunderstood your explanation, sorry. Although I´ll revisit the MIN statement to include a large number. thanks!!