Updating blank only cells

[m40wen]

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

[TonyJollans]

Lord Michael,

I assume you mean that you want to update rows where column AA in that row is blank (as opposed to suppressing all updates unless the whole of column AA is blank). All you should need is an extra If inside your loop ..

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, "AA") = "" Then
        '
        ' The rest of your code
        '
    End If
Next Ce

[m40wen]

Thanks Tony, that helps alot!

However, if I try to use the following line it doesnt work (I am trying to be cleverer than I am!)

If (Sheets("qu_Create_All_PrimeBrokerage").Cells(ce.Row, "F") >= Date + 1) And _
(Sheets("qu_Create_All_PrimeBrokerage").Cells(ce.Row, "Y" = "UM")) Then

But it comes up with an error. Is this not possible?

Many thanks in advance for your help

[TonyJollans]

I think it's just a misplaced parenthesis ..

....Cells(Ce.Row,"Y" = "UM"))

should be

....Cells(Ce.Row,"Y") = "UM")

[m40wen]

Indeed it is Tony, how schoolboy of me.

Thanks alot!