Lookup and update between two workbooks

[m40wen]

Hello All

Excuse my (in)ability as I am a real novice, but I guess we all have to start somewhere! Basically I have two worksheets, one that I work on all day another that I get sent to update a certain section. What I would know how to do is lookup references in my main workbook (which I will call workbookm) from my sent one (workbooks), and update 5 cells in workbookm dependent if it satisfies a condition (a stautus on the sent workbook). I therefore am assuming it will be some kind of lookup - IF - update command.

However it gets a little more complicated as the sent workbook are not all for my attention, so every reference has to be looked up in the main spreadsheet, but if it cant be found then to move on to the next reference and so on, though the main workbook should only be updated if the status returned on the sent worksheet is 'prematched' and to ignore others. Further to this, the refernce has an additional amount of numbers attached to it so I must only look at the first ten digits to look up in my main worksheet.

Sorry I know this is abit difficult to follow, but it seems easier to show rather than explain. I have attached an example workbook in the hope that this helps. I know that this is possible, but as I am trying to sprint before I can even stand, I dont know where to start!

[acw]

Hi

I'm guessing that you are matching the trade_reference from the main sheet (column C) to the client_ref in the sent sheet (column B) and column P in the sent sheet has to be "prematched", but where do the details come from that populate columns Y to AC on the main sheet???

Tony

[m40wen]

Hi Tony, thanks for the reply

The columns Y to AC are standard if the reference is prematched with AB being my user id (is that the environ command?) and todays date as AC. Essentially what I am trying to do is write a macro that will lookup every reference for me (there are 2000 rows, so you can see why I am keen to try and automate the process!), and the ones that have 'prematched' on the row on the sent sheet, I can then update 5 cells on that row with standard text (as shown in the examples MA - SFT - Trade matched - login - todays date). IF the reference can not be found, or that the condition is not met on the sent sheet (ie it doesnt have 'prematched') then the macro wil simply not update but ignore and look up the next reference on the next row until it gets to the bottom of the sheet.

Ok its only the 'Main Sheet' I am amending. On the example, sheet 3 'Main sheet amended' is what happens after I have updated using the sent sheet data. I have tried to keep it simple by saying that there are four items on the main sheet, and five on the sent sheet (though in reality, i Have approx 8000 rows on my main sheet, and about 2000 rows on the one sent to me, though out of this 2000 only a few hundred are for me).

In the example I have posted you can see that I can only update the top 2 rows which is highlighted in red on 'Main Sheet Amended' as the trade reference from the 'sent sheet' can be found (the reference is called client ref on the sent sheet, and trade ref on the main sheet but is the same thing), and columns y to ac are thus updated. Row 4 cannot be updated because although the reference can be found, the criteria has not been met (it is showing pending on Sent sheet, cell P4). Row 5 cannot be updated/amended because the refernce cannot be found in the main sheet.

Any help would be greatly appreciated as I am getting abit fed up of working 12 hours a day!

[acw]

HI

Can you try this and see if it will get you going.


Sub aaa()
For Each ce In Sheets("main sheet").Range("C2:C" & Sheets("main sheet").Range("C65536").End(xlUp).Row)
Set holder = Sheets("sent sheet").Range("B:B").Find(what:=ce.Value)
If Not holder Is Nothing Then
If holder.Offset(0, 14).Value = "prematched" Then
ce.Offset(0, 22) = "MA"
ce.Offset(0, 23) = "SFT"
ce.Offset(0, 24) = "Trade matched (06/01)="
ce.Offset(0, 25) = "owenmi"
ce.Offset(0, 26) = Now()
End If
End If
Next ce
End Sub


Tony

[m40wen]

Tony

I can't express how grateful I am! Thanks alot. If anyone can direct me to a website/explain what the code means in laymans terms that would also be great as I am very interestested in knowing how things work. Alternatively a book which is easy to read might be of help!


Out of interest, how would i write the code if it was two workbooks rather than two sheets in one work book (with for arguments sake the books called main and sent, and the sheets mainsheet and sentsheet retrospectively - both Excel files open on my window)?

You have just made one person very happy! Thanks

Mike

[m40wen]

I have just tried to amend the code for another report I get (which does the same thing, just another market) but when I play the code, nothing gets updated:-



Sub GoBabyGoItalian()
For Each ce In Sheets("main sheet").Range("C2:C" & Sheets("mainsheet").Range("C65536").End(xlUp).Row)
Set holder = Sheets("PendingItalian").Range("B:B").Find(what:=ce.Value)
If Not holder Is Nothing Then
If holder.Offset(0, 14).Value = "OK_PRE-MATCHED" Then
ce.Offset(0, 22) = "MA"
ce.Offset(0, 23) = "SFT"
ce.Offset(0, 24) = "Trade matched at agent (30/03)="
ce.Offset(0, 25) = "owenmi"
ce.Offset(0, 26) = "30/03/2005"
End If
End If
Next ce
End Sub

The only difference in this 'sentitalian' report is that instead of 'prematched' i get sent 'OK_PREMATCHED', and that instead of this information (status) being in column P (as in the sent sheet example above), its in column D. Furthermore, the 'sentitalian' has an additional 4 random digits for the reference number i.e refernce abcd1234 in my main sheet is ref abcd1234/xyz in the one I get sent.

Any ideas chaps? I get no errors when running. Sorry if this isnt clear, but its coming towards the end of my day and im quite tired!

[acw]

Mike

The code below should work for the 2 workbooks. It does assume that both are open. I've put some comments into the code so you can see what it is doing.



Sub aaa()
'make sure that you are in the workbook main
Workbooks("main.xls").Activate
'look at each entry in column C
For Each ce In Sheets("main sheet").Range("C2:C" & Sheets("main sheet").Range("C65536").End(xlUp).Row)
'determine if the trade reference in main.xls is found in [sent.xls]sent sheet
Set holder = Workbooks("sent.xls").Sheets("sent sheet").Range("B:B").Find(what:=ce.Value)
'Something is found
If Not holder Is Nothing Then
'check to see if the value in column P is prematched
If holder.Offset(0, 14).Value = "prematched" Then
'output the fixed values to columns Y:AC
ce.Offset(0, 22) = "MA"
ce.Offset(0, 23) = "SFT"
ce.Offset(0, 24) = "Trade matched (06/01)="
ce.Offset(0, 25) = "owenmi"
ce.Offset(0, 26) = Now()
End If
End If
Next ce
End Sub


For you second request change the line


If holder.Offset(0, 14).Value = "OK_PRE-MATCHED" Then

to


If holder.Offset(0, 2).Value = "OK_PRE-MATCHED" Then


The offset determines the number of columns to the right that the item is located. As the original was in Column P, it was 14 columns to the right of column B. As it is now in column D, it is only 2 columns to the right.


HTH

Tony

[m40wen]

Tony

I am so happy I am thinking of naming my kids after you! In all seriousness thanks alot, this helps me out no end. My code finally stands at


Sub GoBabyGoFrench()
' GoBabyGoFrench Macro
' Macro recorded 31/03/2005 by Lord Michael Owen
' Keyboard Shortcut: Ctrl+shift+f
If MsgBox("Do you want to procced with French Update? ", vbYesNo) = vbNo Then Exit Sub
If MsgBox("Make sure report sent by agent is open, and has been saved as FrenchReport for both file name AND sheet name. _
Is this done?", vbYesNo) = vbNo Then Exit Sub
Dim todayDate As String
todayDate = Format(Date, "dd/mm")
'make sure that you are in the workbook main
Workbooks("Mainbook.xls").Activate
'look at each entry in column C
For Each Ce In Sheets("mainsheet").Range("C2:C" & Sheets("Mainsheet").Range("C65536").End(xlUp).Row)
'determine if the trade reference in main.xls is found in [sent.xls]sent sheet
Set holder = Workbooks("frenchreport.xls").Sheets("frenchreport").Range("B:B").Find(what:=Ce.Value)
'Something is found
If Not holder Is Nothing Then
'check to see if the value in column P is prematched
If holder.Offset(0, 14).Value = "prematched" Then
'output the fixed values to columns Y:AC
Ce.Offset(0, 22) = "MA"
Ce.Offset(0, 23) = "SFT"
Ce.Offset(0, 24) = "Trade matched at agent (" & todayDate & ")="
Ce.Offset(0, 25) = "Autoupdate"
Ce.Offset(0, 26) = Date
Ce.Offset(0, 26).Select
Selection.HorizontalAlignment = xlLeft
End If
End If
Next Ce
End Sub


Not bad for somebody who yesterday didnt know what he was doing!

Mike