m40wen
04-19-2005, 03:50 AM
Hello Gurus
I have the following code which works like a dream. However, I would like to amend it so that it onlly updates the row if column AA is blank. I think i need to do some kind of range and loop but not sure how best to approach this. I would really appreciate any help!
Sub GoBabyGoSwift()
'GoBabyGoSwift Macro
' Macro recorded 11/04/2005 by Lord Michael Owen
'Keyboard Shortcut: Ctrl+Shift+W
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)
'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
I have the following code which works like a dream. However, I would like to amend it so that it onlly updates the row if column AA is blank. I think i need to do some kind of range and loop but not sure how best to approach this. I would really appreciate any help!
Sub GoBabyGoSwift()
'GoBabyGoSwift Macro
' Macro recorded 11/04/2005 by Lord Michael Owen
'Keyboard Shortcut: Ctrl+Shift+W
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)
'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