Sleeper: Using part cell to lookup refs

[m40wen]

Hello all

I think Im starting to get an addiction to this website! Ok, previously I was able to use some visual basic code to 'lookup' cells. However, for one client this refernce can be moved to a different cell and has the standard text 'New reference (todays date) ref12345 = new ref.' I therefore wish to lookup the new reference in this example 'Ref12345' - which is dynamic. Would i use wildcards for this? Furthermore, i also need to update a cell with this new reference number if it meets a set criteria. Any ideas?

Thanks to you all in advance


Mike

[Zack Barresse]

Hi there Mike,

Well first I'm glad you're getting an addiction to the site. :yes I am thoroughly addicted as well!! :D

Okay. It's a little unclear as to what it is you are talking about. Are you referring to some code or another thread? If so, please provide either the code or the link. I'm still trying to get a grip on the logic and the flow of your information. If you could provide that it would be very helpful. :)

[m40wen]

Hi Zack!
This is the miss mash of my brain and some help form the fellas on here. As i say this code works well, but for one client i want it to look up the reference in column AA as well. Code stands at


If MsgBox("Do you want to proceed with Swift Update? ", vbYesNo) = vbNo Then Exit Sub
If MsgBox("Make sure Swift report is open, has been saved as Machtrades " & _
"for file name and that sheet name is qSel_MACHStatus. Has this been done?", vbYesNo) = vbNo Then Exit Sub
If MsgBox("Please wait while I update the records ", vbYesNo) = vbNo Then Exit Sub
Dim todayDate As String
todayDate = Format(Date, "dd/mm")
'make sure that you are in the workbook main
Workbooks("PersonalOpenTrades.xls").Activate
'look at each entry in column C
For Each ce In Sheets("qu_Create_All_PrimeBrokerage").Range("C2:C" & _
Sheets("qu_Create_All_PrimeBrokerage").Range("C65536").End(xlUp).Row)
If (Sheets("qu_Create_All_PrimeBrokerage").Cells(ce.Row, "F") >= Date + 1) And _
((Sheets("qu_Create_All_PrimeBrokerage").Cells(ce.Row, "Y") = "UC") Or _
(Sheets("qu_Create_All_PrimeBrokerage").Cells(ce.Row, "Y") = "UM")) Then
'determine if the trade reference in main.xls is found in [sent.xls]sent sheet
Set holder = Workbooks("MACHtrades.xls").Sheets("qSel_MACHStatus").Range("A:A").Find(what:=ce.Value)
'Something is found
If Not holder Is Nothing Then
'check to see if the value in column O is prematched
If (holder.Offset(0, 1).Value = "MACH") 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 & ")=Via Swift Direct"
ce.Offset(0, 25) = "Autoupdate"
ce.Offset(0, 26) = Date
ce.Offset(0, 26).Select
Selection.HorizontalAlignment = xlLeft
End If
If (holder.Offset(0, 1).Value = "FUT") Or (holder.Offset(0, 1).Value = "FUT/MAT") 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 & ")=FUT Via Swift Direct"
ce.Offset(0, 25) = "Autoupdate"
ce.Offset(0, 26) = Date
ce.Offset(0, 26).Select
Selection.HorizontalAlignment = xlLeft
End If
If (holder.Offset(0, 1).Value = "COUNTERPARTY INSUFFICIENT SECURITIES") Then
'output the fixed values to columns Y:AC
ce.Offset(0, 22) = "MA"
ce.Offset(0, 23) = "SFT"
ce.Offset(0, 24) = "Counterparty Insufficient (" & todayDate & ")= Securities"
ce.Offset(0, 25) = "Autoupdate"
ce.Offset(0, 26) = Date
ce.Offset(0, 26).Select
Selection.HorizontalAlignment = xlLeft
End If
If (holder.Offset(0, 1).Value = "COUNTERPARTY INSUFFICIENT MONEY") Then
'output the fixed values to columns Y:AC
ce.Offset(0, 22) = "MA"
ce.Offset(0, 23) = "SFT"
ce.Offset(0, 24) = "Counterparty Insufficient (" & todayDate & ")= Money"
ce.Offset(0, 25) = "Autoupdate"
ce.Offset(0, 26) = Date
ce.Offset(0, 26).Select
Selection.HorizontalAlignment = xlLeft
End If
End If
End If
Next ce
End Sub

Basically I know I have to add an IF command as well to say if column a says joe bloggs then look in column aa. what I dont know is how too get the macro to ignore some text as I have already stated


For Each ce In Sheets("qu_Create_All_PrimeBrokerage").Range("C2:C" & _
Sheets("qu_Create_All_PrimeBrokerage").Range("C65536").End(xlUp).Row)
If (Sheets("qu_Create_All_PrimeBrokerage").Cells(ce.Row, "A") = Joe Bloggs) then.....

I want it to look in column aa, copy the reference in this cell from the text and continue as before. I then need to copy this 'look up' reference and paste it into my sheet if it satisfies the above conditions as before i.e cellAA shows 'New trade reference (date) = REF12345,' excel looks up REF12345 and will change the cell to becomes trade matched at agent (date)= REF12345 if it satisfies conditions. If it helps, the ref to lookup will always start REF#

Hope this makes sense to you all, and any help would be appreciated!

[Zack Barresse]

Okay, a little revising. First of all, these two lines ...


If MsgBox("Make sure Swift report is open, has been saved as Machtrades for file name and that sheet name is qSel_MACHStatus. Has this been done?", vbYesNo) = vbNo Then Exit Sub
If MsgBox("Please wait while I update the records ", vbYesNo) = vbNo Then Exit Sub

.. they seem fairly useless. I would take care of this all in code. One message box, yes or no. Don't confuse your users or over use the tools we have (you'll annoy the crap out of the end users - not to mention you when testing). I have not coded workaround solutions, but can if you so desire; so these have been left in the code below.

This is a little revision and should do the (basically) same thing.

..


Option Explicit

Sub MacroHere()
Dim todayDate As String, ws As Worksheet, wb As Workbook
Dim ce As Range, lastRow As Long
Dim lookRng1 As Range, lookRng2 As Range, mushRng As Range
If MsgBox("Do you want to proceed with Swift Update? ", vbYesNo) = vbNo Then Exit Sub
If MsgBox("Make sure Swift report is open, has been saved as Machtrades for file name and that sheet name is qSel_MACHStatus. Has this been done?", vbYesNo) = vbNo Then Exit Sub
If MsgBox("Please wait while I update the records ", vbYesNo) = vbNo Then Exit Sub
todayDate = Format(Date, "dd/mm")
Set wb = Workbooks("PersonalOpenTrades.xls")
Set ws = wb.Sheets("qu_Create_All_PrimeBrokerage")
Set lookRng1 = Workbooks("MACHtrades.xls").Sheets("qSel_MACHStatus").Range("A:A")
Set lookRng2 = Workbooks("MACHtrades.xls").Sheets("qSel_MACHStatus").Range("AA:AA")
Set mushRng = Union(lookRng1, lookRng2)
lastRow = ws.Range("C65536").End(xlUp).Row
wb.Activate
For Each ce In ws.Range("C2:C" & lastRow)
If ce.Offset(, 3) >= Date + 1 And ce.Offset(, 22) = "UC" Or ce.Offset(, 22) = "UM" Then
Set holder = mushRng.Find(what:=ce.Value)
If Not holder Is Nothing Then
ce.Offset(0, 22) = "MA"
ce.Offset(0, 23) = "SFT"
ce.Offset(0, 25) = "Autoupdate"
ce.Offset(0, 26) = Date
ce.Offset(0, 26).Select
Select Case holder.Offset(0, 1).Value
Case "MACH"
ce.Offset(0, 24) = "Trade matched at agent (" & todayDate & ")=Via Swift Direct"
Case "FUT", "FUT/MAT"
ce.Offset(0, 24) = "Trade matched at agent (" & todayDate & ")=FUT Via Swift Direct"
Case "COUNTERPARTY INSUFFICIENT SECURITIES"
ce.Offset(0, 24) = "Counterparty Insufficient (" & todayDate & ")= Securities"
Case "COUNTERPARTY INSUFFICIENT MONEY"
ce.Offset(0, 24) = "Counterparty Insufficient (" & todayDate & ")= Money"
End Select
End If
End If
Next ce
End Sub

I don't have a suitable workbook to test this out on, so be sure and SAVE YOUR WORK FIRST! I have screwed up too many things because of not testing them. Or you can upload a sample workbook.

[m40wen]

Thanks for that Zack - what you have done looks quite neat! However, it doest appear to work im afraid! Ive reedited my above post to make things abit clearer (I hope!)

Cheers all

[Zack Barresse]

Can you upload a sample workbook?

[m40wen]

Yes of course

You can ignore the first few lines, its the last row that I am now trying to do

[Zack Barresse]

Okay, I'll take a look at this when I have time, a bunch of work just popped up. If I don't respond by the end of this weekend, shoot me an email (unless somebody else doesn't get to ya first ;) ).

[Zack Barresse]

Okay, the workbook example is kind of confusing me now. The sheet names and that are not the same as the code you posted, nothing seems to be matching up. I can't even find any standard Modules in your uploaded example. Am I missing something? :dunno

Maybe you can tell me what it is that I'm looking at with this workbook? Take me step by step what it is that you want done with it. Be as detailed as possible. :)

[m40wen]

Hey Zack, somebody who is only half knowledgable is designed to make others crazy!

Sorry, the confusion was because I was trying to keep things simple as my 'eaxmple' had a different name from real life (security reasons mainly). I wouldnt worry too much about the actual names of the workbooks or sheets. I have reattached another eaxample that makes thinkgs abit easier - in real life it would be two workbooks that i would be using rather than two worksheets, and I have tried to show a before and after example. The key is that in the example i have written, because the client is Forest Gump, it must ALSO look up in column AA for another reference

Ok it may help if you read my other thread 'Lookup and update between two workbooks.' That code works brilliantly. However, for one of my clients (Forest Gump) SOME of the references gets changed and moved into 'personalopentrades', column AA. The reference then contains "New reference (todaysdate) = REF#####" I therefore have to then lookup REF#####, rather than the orginal reference located in column B on personalopentrades. If REF##### meets the criteria, then I have to overwrite column AA with "trade matched at agent= REF#####' - its important that the REF##### remains in there after it is rewritten.

Hope that makes things simpler to understand! Oh the other thing is in the posted example, on the sent report that I get occassionally i would like to copy what is written in column 'Q' and put as part of column AA. For example, in the example Ref ABC123EFI on report has a stautus of pending, but in Column 'Q' it tells me that the 'counterparty has no instruction'. So I would write


If (holder.Offset(0, 14).Value = "Pending") then
ce.Offset(0, 22) = "UM"
ce.Offset(0, 23) = "SFT"
ce.Offset(0, 24) = "Trade not matched because ":dunno whatever is written in column q on that row thats being looked up from the sent report:dunno (" & todayDate & ")="
ce.Offset(0, 25) = "Autoupdate"
ce.Offset(0, 26) = Date
ce.Offset(0, 26).Select
Selection.HorizontalAlignment = xlLeft

I hope its just a matter of adding a sentence between the smilies, as I would prefer to get my head round this structure - my visual basic knowledge isnt the best but Im keen to learn!

Apologies for the confusing nature, if you need any more help then I would be more than willing to oblige!

[m40wen]

Cant believe its monday already chaps!

Have answered one of my own questions in how to paste a cell value


If (holder.Offset(0, 13).Value = "DD") Or (holder.Offset(0, 13).Value = "DT") Then
'output the fixed values to columns Y:AC
ce.Offset(0, 22) = "UM"
ce.Offset(0, 23) = "SFT"
ce.Offset(0, 24) = "Mismatch - date discrepancy" & holder.Offset(0, 15).Value & (" & todaydate & ") = ""
ce.Offset(0, 25) = "Autoupdate"
ce.Offset(0, 26) = Date
ce.Offset(0, 26).Select
Selection.HorizontalAlignment = xlLeft
End If

But if any of you can answer my other question of using part of a cell text to 'lookup' as a reference, and replace if it satisfies the conditions, I would still be very grateful! Hey even Anakin Skywalker had Jedis to learn from!!!

[m40wen]

I think I could use the following code to get my new reference look up, but dont know how it should sit in with the rest. Does anyone have any ideas? I want the macro to run as it does now then for the client 'Joe Bloggs' to be checked in the additional column.


Function findIPBref(newref) As String
Dim x As String
Dim y As Integer
x = Worksheets("qu_Create_All_PrimeBrokerage").range(cellref).Text
y = InStr(x, "IPB")
findIPBref = (Mid(x, y, 10))
End Function