Set reference, find it, find where text matches, bring across value

Im trying to bring across the value of a cell after it finds the set reference and matches some text

Thanks to the help of this forum we were able to create a macro to bring across my desired cells using .SpecialCells

Example: email.xlsm

Its working beautifully but now i’m trying to use the “reference numbers” eg. 01806220120914/0195

Then search/find for the occurrence where the “reference numbers” - 01806220120914/0195 equals that set reference number 01806220120914/0195 in column “C” of my “Bulked Letters” sheet and where the first 7 letters of "AC" equal "PAYDATE".

Then bring across this value into my other workbook.

The move to the next reference number...

Any ideas welcome please

end result.JPG
letters bulked.jpg

Hi

If there is only going to be one result per reference number then try

=MID(INDEX('Letters - Bulked'!AC:AC,SUMPRODUCT(--('Letters - Bulked'!$C$2:$C$834=Current!C7),--(LEFT('Letters - Bulked'!$AC$2:$AC$834,7)="PAYDATE"),ROW('Letters - Bulked'!$J$2:$J$834))),22,11)

rylo

rylo you've done it again mate thats great gives me something to work with, but do you or anyone know how to make this happen via vba so theres no formula on the sheet?

Reason being, i get the "letters - bulked" spreadsheet emailed seperatly daily, bring across my selected information into my master then delete the letters bulked.

So if there were a forumla it would break the next day, when a new bunch of information and "reference numbers" come through.

Thanks again

Hi

Sub aaa()
  lastrow = Sheets("Letters - Bulked").Cells(Rows.Count, 2).End(xlUp).Row
  
  Sheets("Current").Activate
  
  Range("L7").Formula = "=MID(INDEX('Letters - Bulked'!AC:AC,SUMPRODUCT(--('Letters - Bulked'!$C$2:$C$" & lastrow & "=Current!C7),--(LEFT('Letters - Bulked'!$AC$2:$AC$" & lastrow & ",7)=""PAYDATE""),ROW('Letters - Bulked'!$J$2:$J$" & lastrow & "))),22,11)"
  Range("L7").AutoFill Destination:=Range("L7:L" & Cells(Rows.Count, 1).End(xlUp).Row)
  Range(Range("L7"), Range("L7").End(xlDown)).Value = Range(Range("L7"), Range("L7").End(xlDown)).Value
End Sub

Something I did notice is that you don't have a valid result in L9 in the example file as there isn't a matching entry in the source data. Is this just a quirk of the example, or is this likely to really happen?

rylo

it will break because there are 3 options

PAYDATE
EFFECTIVE DATE
OFFERS CLOSE

but im hoping.... im good enough to be able to modify whatever the forum throws at me.

i hate loading people up with too many scenarios

Hi

Where are you putting the macro? General module, or in a sheet module? This is meant to sit in a general module.

rylo

you've done it again! much appreciated

oh no! I thought i was good enough to convert this into an IF Statement.... any ideas why my changes don't effect the outcome at all?

For i = Cells(Rows.Count, "A").End(xlUp).Row To 7 Step -1
If Cells(i, "F") = "DIVIDEND REINVESTMENT PLANS" Then

LastRow = Sheets("Letters - Bulked").Cells(Rows.Count, 2).End(xlUp).Row

Sheets("New").Select

Range("L7").Formula = "=MID(INDEX('Letters - Bulked'!AC:AC,SUMPRODUCT(--('Letters - Bulked'!$C$2:$C$" & LastRow & "=New!C7),--(LEFT('Letters - Bulked'!$AC$2:$AC$" & LastRow & ",7)=""PAYDATE""),ROW('Letters - Bulked'!$J$2:$J$" & LastRow & "))),22,11)"
Range("L7").AutoFill Destination:=Range("L7:L" & Cells(Rows.Count, 1).End(xlUp).Row)
Range(Range("L7"), Range("L7").End(xlDown)).Value = Range(Range("L7"), Range("L7").End(xlDown)).Value

End If
Next i

Hi

Which sheet are you on when you start the run? Because after the first iteration, you will be on sheet NEW and I don't know if the IF statement wil come back true or not.

rylo

i've been starting the run on the "New" which was the changed name for the "Current"

changed everything back but now nothing to a senario i hoped would work but still nothing

Sub aaa()

Dim i As Long, LastRow As Long 'testing

For i = Cells(Rows.Count, "A").End(xlUp).Row To 7 Step -1
If Cells(i, "F") = "UK DIVIDEND REINVESTMENT PLAN" Then

  LastRow = Sheets("Letters - Bulked").Cells(Rows.Count, 2).End(xlUp).Row
  
  Sheets("Current").Select
  
  Cells(i, "F").Formula = "=MID(INDEX('Letters - Bulked'!AC:AC,SUMPRODUCT(--('Letters - Bulked'!$C$2:$C$" & LastRow & "=Current!C7),--(LEFT('Letters - Bulked'!$AC$2:$AC$" & LastRow & ",7)=""PAYDATE""),ROW('Letters - Bulked'!$J$2:$J$" & LastRow & "))),22,11)"
  Cells(i, "F").AutoFill Destination:=Range(Cells(i, "F") & Cells(i, "F").End(xlUp).Row)
  Range(Cells(i, "F"), Cells(i, "F").End(xlDown)).Value = Range(Cells(i, "F"), Cells(i, "F").End(xlDown)).Value
  
End If
Next i

End Sub

Hi

new example file time, for the structure you really want to use, and advise where / how you want this to action.

rylo

morning, new week hopefully a good week!

updated example attached with notes and final outcome

thanks rylo

update example.xlsm

Hi

Here goes

Sub aaa()
  LastRow = Sheets("Letters - Bulked").Cells(Rows.Count, 2).End(xlUp).Row
  
  Sheets("Current").Activate
  
  Range("L7").Formula = "=IF(F7=""BONUS ISSUE-TRADING"",NA(),IF(F7=""MERGER"",MID(INDEX('Letters - Bulked'!AC:AC,SUMPRODUCT(--('Letters - Bulked'!$C$2:$C$" & LastRow & "=Current!C7),--(LEFT('Letters - Bulked'!$AC$2:$AC$" & LastRow & ",14)=""EFFECTIVE DATE""),ROW('Letters - Bulked'!$J$2:$J$" & LastRow & "))),22,11),IF(F7=""TAKEOVER"",MID(INDEX('Letters - Bulked'!AC:AC,SUMPRODUCT(--('Letters - Bulked'!$C$2:$C$" & LastRow & "=Current!C7),--(LEFT('Letters - Bulked'!$AC$2:$AC$" & LastRow & ",12)=""OFFER CLOSES""),ROW('Letters - Bulked'!$J$2:$J$" & LastRow & "))),22,11),MID(INDEX('Letters - Bulked'!AC:AC,SUMPRODUCT(--('Letters - Bulked'!$C$2:$C$" & LastRow & "=Current!C7),--(LEFT('Letters - Bulked'!$AC$2:$AC$" & LastRow & ",7)=""PAYDATE""),ROW('Letters - Bulked'!$J$2:$J$" & LastRow & "))),22,11))))"
  Range("L7").AutoFill Destination:=Range("L7:L" & Cells(Rows.Count, 1).End(xlUp).Row)
  Range(Range("L7"), Range("L7").End(xlDown)).Value = Range(Range("L7"), Range("L7").End(xlDown)).Value
End Sub

rylo

so so solid

i was well off!

Thanks again rylo