Entries for January, 2008

January 6th, 2008

Another First Day


1217713
This time, it'll be the one for my full-time job. The first ever. Wish me luck.
Currently listening to: Jimmy Eat World
Currently reading: The Wind-Up Bird Chronicle
Currently feeling: consumed.
Posted by 1217713 at 10:27 PM | 1 !

January 10th, 2008

Why do I keep falling for women who'll never like me?


1217713

I mean, I've (finally) had it with Chinese women; or rather, I've finally realized the futility of my pathetic little attempts. And it's not like I've settled for Filipinas either; it's just that I've fallen for this girl just recently. She's definitely a Filipina (still my type though). But it seems I can't expect much from her as well.

A few days ago, referring to our coffee "date", she sent me a text message, "It's just coffee. How long would that take?" Reading this, I was sincerely crushed. I was looking forward to that date, to seeing her again. I had prepared much for that. I even had an overdue Christmas gift for her, the first I'll ever give a girl (or "I had ever given" since it's with her now). I wanted to send her a reply: "Maybe so. But what if the coffee was merely an excuse to be with you again, no matter how short that meeting would be?" Which was to say, quite brief, really, last night. But still, it was great seeing her again, being with her again, talking to her again. I'm afraid the feeling is, as always, unrequited.

Maybe I really am that much of a loser.

Maybe I really am waaaaay in over my head (yet again).

Maybe I really am doomed to spend a lifetime alone (Oh please please please let it be a short one at that.).

Maybe I really should just stop trying once and for all.

Maybe I really couldn't even handle a relationship if I had one.

Maybe Aleck was really right all along about me being unable of loving.

Maybe I really am just too stubborn to accept that fact.

Maybe I really am worth nothing after all.

Maybe I really was just fooling myself all this time.

Maybe I really ought to abandon hope completely.

Maybe so. I honestly don't know anymore.

Currently listening to: Belle And Sebastian - If She Wants Me
Currently reading: The Wind-Up Bird Chronicle
Currently feeling: depressed again.
Posted by 1217713 at 10:03 AM | 56 !

The Recluse


1217713

I wake alone
In a woman's room I hardly know
I wake alone
Pretend that I am finally home

The room is littered
With the books and notebooks
I imagine what they say
Like, "Shoo fly, don't bother me"

And I can hardly get myself out of this bed
For fear of never lying in this bed again
Oh Christ, I'm not that desperate
Oh no, oh God, I am

How'd I end up here to begin with?
I don't know
Why do I start what I can't finish?
Oh, please don't barrage me with the questions
To all those ugly answers
My ego's like my stomach
It keeps shitting what I feed it

But maybe I don't want to finish anything anymore
Maybe I can wait in bed until she comes home
And whispers, "You're in my web now
I've come to wrap you up tight until it's time to bite down"

I wake alone
In a woman's room I hardly know
I wake alone
And pretend that I am finally home

(You're in my web now)
Home
Home
(You're in my web now)
Home
Home
(You're in my web now)
Home
Home
(You're in my web now)

Currently listening to: Go figure.
Currently reading: The Wind-Up Bird Chronicle
Currently feeling: depressed.
Posted by 1217713 at 02:08 PM | 2 !

January 13th, 2008

The current count is...


1217713

3726 tested and 85 pending.

 And I'm only at letter N.

Currently listening to: Metallica - Loverman
Currently reading: The Wind-Up Bird Chronicle
Currently feeling: depressed.
Posted by 1217713 at 07:25 PM | 1 !

January 17th, 2008

So...


1217713

My second week at work is almost done. I've learned a lot of new things and I wish I had known about these sooner.

 Oh well.

Currently listening to: Fugazi - Bed For The Scraping
Currently reading: The Wind-Up Bird Chronicle
Currently feeling: depressed still.
Posted by 1217713 at 05:40 PM | ?

January 20th, 2008

I've asked it before.


1217713

And I'll ask it yet again.

 

Why do I even bother?

Currently listening to: Rise Against
Currently reading: The Wind-Up Bird Chronicle
Currently feeling: depressed. Forever.
Posted by 1217713 at 06:04 PM | 2 !

January 24th, 2008

Since...


1217713

I'm spending time with these, I might as well post them. Or not.

Oh, what the hell. Here goes. Haha.

 

'Procedure
  '1.Open Disposal file of a cluster.
  '2.Overwrite the filenames for wksDisp.
  '3. Run ForDisposal.
  '4.Copy Headings
 
Sub ForDisposal()
  Dim LastRow0 As Long
  Set wksDisp = Workbooks("Testing123_Disposal.xls").Worksheets(1)
  wksDisp.Activate
  CheckStatDisp
  DeleteRowsDisp
  Get_LastRow wksDisp, 9, 6, LastRow0
  For l = 9 To LastRow0
    If Range("H" & l) = "" Then
      Range("H" & l).Interior.ColorIndex = 3
    End If
  Next l
End Sub
Sub CheckStatDisp()
  Dim LastRow As Long
  Get_LastRow wksDisp, 9, 9, LastRow
  For i = 9 To LastRow
  'For Allocated Date and Status matching
    If ActiveSheet.Range("I" & i) = "Requested" And Range("P" & i) = "" Then
      Range("I" & i).Interior.ColorIndex = 3
      Range("P" & i).Interior.ColorIndex = 3
    End If
  'For Execution Start Date and Status matching
    If Range("I" & i) = "Execution" And Range("L" & i) = "" Then
      Range("I" & i).Interior.ColorIndex = 3
      Range("L" & i).Interior.ColorIndex = 3
    End If
  'For Completion Date and Status matching
    If Range("I" & i) = "Completed" And Range("K" & i) = "" Then
      Range("I" & i).Interior.ColorIndex = 3
      Range("K" & i).Interior.ColorIndex = 3
    End If
  'For C/DC Date and Status matching
    If Range("I" & i) = "Commissioned/Decommissioned" And Range("J" & i) = "" Then
      Range("I" & i).Interior.ColorIndex = 3
      Range("J" & i).Interior.ColorIndex = 3
    End If
  'For IP and IP Approval Date matching
    If Range("M" & i) = "N" And Not Range("N" & i) = "" Then
      Range("M" & i).Interior.ColorIndex = 3
      Range("N" & i).Interior.ColorIndex = 3
    End If
  'For Allocation and Allocated Date matching
    If Range("O" & i) = "N" And Not Range("P" & i) = "" Then
      Range("O" & i).Interior.ColorIndex = 3
      Range("P" & i).Interior.ColorIndex = 3
    End If
  'For Project Status and IP and Allocation matching
    If Range("M" & i) = "Y" Then
      If Range("O" & i) = "Y" And (Range("I" & i) = "Identified" Or Range("I" & i) = "Requested") Then
        Range("I" & i).Interior.ColorIndex = 3
      End If
'      If Range("J" & i) = "" Then               '
'        Range("J" & i).Interior.ColorIndex = 3  'For checking C/DC Date against IP
'        Range("K" & i).Interior.ColorIndex = 3  'For checking Completion Date against IP
'        Range("L" & i).Interior.ColorIndex = 3  'For checking Exec Start Date against IP
'      End If                                    '
    End If
    If Range("O" & i) = "N" And (Not Range("I" & i) = "Identified" Or Not Range("I" & i) = "Requested") Then
      Range("I" & i).Interior.ColorIndex = 3
    End If
  'For PM checking against Allocation
    If Range("O" & i) = "Y" And Range("AA" & i) = "" Then
      Range("AA" & i).Interior.ColorIndex = 3
    End If
    If Not Range("N" & i) = "" And Not Range("P" & i) = "" And Range("N" & i) < Range("P" & i) Then
      Range("N" & i).Interior.ColorIndex = 3
    End If
    If Not Range("P" & i) = "" And Not Range("Q" & i) = "" And Range("P" & i) < Range("Q" & i) Then
      Range("P" & i).Interior.ColorIndex = 3
    End If
  Next i
End Sub
Sub DeleteRowsDisp()
  'Set wksDisp = Workbooks("East_Datagaps_0123_Disposaltrial.xls").Worksheets(1)
  UnmergeDisp
  Dim LastRow As Long
  Get_LastRow wksDisp, 9, 3, LastRow
  For j = 9 To LastRow
    If Not Range("J" & j) = "" Then
      If Range("C" & j) = "2006" And Range("J" & j) < #1/1/2007# Then
        Rows(j).Select
        Range("C" & j).Activate
        'Selection.Interior.ColorIndex = 5
        Selection.Delete Shift:=xlUp
        LastRow = LastRow - 1
        j = j - 1
      End If
    End If
  Next j
End Sub
Sub UnmergeDisp()
  Range("A9:B65536").Select
  With Selection
    .HorizontalAlignment = xlLeft
    .VerticalAlignment = xlCenter
    .MergeCells = False
  End With
  'Set wksDisp = Workbooks("CSE_0122_Disposaltrialrun.xls").Worksheets(1)
  Dim LastRow1 As Long
  Dim MarketTemp As String
  Get_LastRow wksDisp, 9, 3, LastRow1
  For k = 9 To LastRow1
    If Not Range("B" & k) = "" Then
      MarketTemp = Range("B" & k)
    End If
    If Range("B" & k) = "" Then
      Range("B" & k) = MarketTemp
    End If
  Next k
End Sub

And here's the worse version of the program above. This one's for another file though.

 'Procedure
  '1.Open Acquisition file of a cluster.
  '2.Open Additional file using Datagaps_new on Cognos
  '3.Overwrite the filenames for wksAcq and the ones in the Populate Sub
  '4. Run ForAcquisition
  'Note: At the moment (until some of the formula for the VLOOKUP part is fixed),
  'run the CheckNewFields Sub separately. That is, after you MANUALLY delete
  'the erroneous data returned by VLOOKUP.
  '5.Copy Headings

Sub ForAcquisition()
  Application.ScreenUpdating = False
  Dim LastRow0 As Long
  Set wksAcq = Workbooks("Testing123_Acquisition.xls").Worksheets(1)
  wksAcq.Activate
  InsertCols
  CheckStatAcq
  Populate
  'Insert macro for checking values of inserted columns
  DeleteRowsAcq
  Get_LastRow wksAcq, 9, 6, LastRow0
  For l = 9 To LastRow0
    If Range("H" & l) = "" Then
      Range("H" & l).Interior.ColorIndex = 3
    End If
    For q = 28 To 32
      If Range("Z" & l) = "Growth" And Cells(l, q) = "" Then
        Cells(l, q).Interior.ColorIndex = 3
      End If
      If q = 30 Then
        If Cells(l, q) > 50 Then
          Cells(l, q).Interior.ColorIndex = 3
        End If
      End If
    Next q
  Next l
  'CheckNewFields
  Application.ScreenUpdating = True
End Sub
Sub CheckStatAcq()
  Dim LastRow As Long
  Get_LastRow wksAcq, 9, 9, LastRow
  For i = 9 To LastRow
  'For Allocated Date and Status matching
    If ActiveSheet.Range("I" & i) = "Requested" And Range("T" & i) = "" Then
      Range("I" & i).Interior.ColorIndex = 3
      Range("T" & i).Interior.ColorIndex = 3
    End If
  'For Execution Start Date and Status matching
    If Range("I" & i) = "Execution" And Range("M" & i) = "" Then
      Range("I" & i).Interior.ColorIndex = 3
      Range("M" & i).Interior.ColorIndex = 3
    End If
  'For Completion Date and Status matching
    If Range("I" & i) = "Completed" And Range("L" & i) = "" Then
      Range("I" & i).Interior.ColorIndex = 3
      Range("L" & i).Interior.ColorIndex = 3
    End If
  'For C/DC Date and Status matching
    If Range("I" & i) = "Commissioned/Decommissioned" And Range("J" & i) = "" Then
      Range("I" & i).Interior.ColorIndex = 3
      Range("J" & i).Interior.ColorIndex = 3
    End If
  'For IP and IP Approval Date matching
    If Range("Q" & i) = "N" And Not Range("R" & i) = "" Then
      Range("Q" & i).Interior.ColorIndex = 3
      Range("R" & i).Interior.ColorIndex = 3
    End If
  'For Allocation and Allocated Date matching
    If Range("S" & i) = "N" And Not Range("T" & i) = "" Then
      Range("S" & i).Interior.ColorIndex = 3
      Range("T" & i).Interior.ColorIndex = 3
    End If
  'For Project Status and IP and Allocation matching
    If Range("Q" & i) = "Y" Then
      If Range("S" & i) = "Y" And (Range("I" & i) = "Identified" Or Range("I" & i) = "Requested") Then
        Range("I" & i).Interior.ColorIndex = 3
      End If
'      If Range("J" & i) = "" Then               '
'        Range("J" & i).Interior.ColorIndex = 3  'For checking C/DC Date against IP
'        Range("L" & i).Interior.ColorIndex = 3  'For checking Completion Date against IP
'        Range("M" & i).Interior.ColorIndex = 3  'For checking Exec Start Date against IP
'      End If                                    '
    End If
    If Range("S" & i) = "N" And (Not Range("I" & i) = "Identified" Or Not Range("I" & i) = "Requested") Then
      Range("I" & i).Interior.ColorIndex = 3
    End If
  'For PM checking against Allocation
    If Range("S" & i) = "Y" And Range("AG" & i) = "" Then
      Range("AG" & i).Interior.ColorIndex = 3
    End If
  Next i
End Sub
Sub DeleteRowsAcq()
  'Set wksAcq = Workbooks("NW_Europe_0123_Acquisition.xls").Worksheets(1)
  'wksAcq.Activate
  UnmergeAcq
  Dim LastRow As Long, LastRowq As Long
  Get_LastRow wksAcq, 9, 3, LastRow
  For j = 9 To LastRow
    If Not Range("J" & j) = "" Then
      If Range("C" & j) = "2006" And Range("J" & j) < #1/1/2007# Then
        Rows(j).Select
        Range("C" & j).Activate
        'Selection.Interior.ColorIndex = 5
        Selection.Delete Shift:=xlUp
        LastRow = LastRow - 1
        j = j - 1
      End If
    End If
  Next j
  Get_LastRow wksAcq, 9, 3, LastRowq
  For g = 9 To LastRowq
    If Not Range("K" & g) = "" Then
      If Range("C" & g) = "2006" And Range("K" & g) < #1/1/2007# Then
        Rows(g).Select
        Range("C" & g).Activate
        'Selection.Interior.ColorIndex = 7
        Selection.Delete Shift:=xlUp
        LastRowq = LastRowq - 1
        g = g - 1
      End If
    End If
  Next g
End Sub
Sub InsertCols()
    Columns(11).Select
    Selection.Insert Shift:=xlToRight
    Range("K9").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Interior.ColorIndex = xlNone
    Columns(11).Select
    Selection.ColumnWidth = 17.71
    Columns(11).Select
    Selection.Copy
    For A = 14 To 16
      Columns(A).Select
      Selection.Insert Shift:=xlToRight
    Next A
    Columns(31).Select
    Selection.Insert Shift:=xlToRight
    Columns(22).Select
    Selection.Insert Shift:=xlToRight
    Application.CutCopyMode = False
    Range("K8").Select
    ActiveCell.FormulaR1C1 = "Actual Trading Date"
    Range("N8").Select
    ActiveCell.FormulaR1C1 = "Building Permit Date"
    Range("O8").Select
    ActiveCell.FormulaR1C1 = "Zoning/Planning Approval"
    Range("P8").Select
    ActiveCell.FormulaR1C1 = "Land Secured Date"
    Range("V8").Select
    ActiveCell.FormulaR1C1 = "Projected Trading Date"
    Range("AF8").Select
    ActiveCell.FormulaR1C1 = "RTEP/IRR(%)"
    Range("AF9").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.NumberFormat = "0.00"
End Sub
Sub Populate()
  'Set wksAcq = Workbooks("Testing123_Acquisition.xls").Worksheets(1)
  Dim LastRowAcq As Long
  Get_LastRow wksAcq, 9, 6, LastRowAcq
  For d = 9 To LastRowAcq
    On Error Resume Next
    wksAcq.Range("K" & d).Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-5],[Testing123_Add.xls]Page1!R3C4:R65536C28,22,0)"
    Range("N" & d).Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-8],[Testing123_Add.xls]Page1!R3C4:R65536C28,21,0)"
    Range("O" & d).Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-9],[Testing123_Add.xls]Page1!R3C4:R65536C28,20,0)"
    Range("P" & d).Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-10],[Testing123_Add.xls]Page1!R3C4:R65536C28,19,0)"
    Range("V" & d).Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-16],[Testing123_Add.xls]Page1!R3C4:R65536C28,24,0)"
    Range("AF" & d).Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-26],[Testing123_Add.xls]Page1!R3C4:R65536C28,11,0)"
    If Range("K" & d) < #1/1/1900# Then
      wksAcq.Range("K" & d) = ""
    End If
    If Range("N" & d) < #1/1/1900# Then
      wksAcq.Range("N" & d) = ""
    End If
    If Range("O" & d) < #1/1/1900# Then
      wksAcq.Range("O" & d) = ""
    End If
    If Range("P" & d) < #1/1/1900# Then
      wksAcq.Range("P" & d) = ""
    End If
    If Range("V" & d) < #1/1/1900# Then
      wksAcq.Range("V" & d) = ""
    End If
    If Range("AF" & d) = 0 Then
      wksAcq.Range("AF" & d) = ""
    End If
  Next d
End Sub
Sub UnmergeAcq()
  Range("A9:B65536").Select
  With Selection
    .HorizontalAlignment = xlLeft
    .VerticalAlignment = xlCenter
    .MergeCells = False
  End With
  Dim LastRow1 As Long
  Dim MarketTemp As String
  Get_LastRow wksAcq, 9, 3, LastRow1
  For k = 9 To LastRow1
    If Not Range("B" & k) = "" Then
      MarketTemp = Range("B" & k)
    End If
    If Range("B" & k) = "" Then
      Range("B" & k) = MarketTemp
    End If
  Next k
End Sub
Sub CheckNewFields()
  Dim LastRowp As Long
  Set wksAcq = Workbooks("Testing123_Acquisition.xls").Worksheets(1)
  Get_LastRow wksAcq, 9, 6, LastRowp
  For q = 9 To LastRowp
    If (Range("I" & q) = "Commissioned/Decommissioned" Or Range("I" & q) = "Project Invoicing Complete") And Range("K" & q) = "" Then
      Range("K" & q).Interior.ColorIndex = 3
    End If
    If Not Range("K" & q) = "" And Not Range("J" & q) = "" And Range("K" & q) < Range("J" & q) Then
      Range("K" & q).Interior.ColorIndex = 3
    End If
    If Range("I" & q) = "Execution" Or Range("I" & q) = "Completed" Or Range("I" & q) = "Commissioned/Decommissioned" Or Range("I" & q) = "Project Invoicing Complete" Then
      If Range("N" & q) = "" Then
        Range("N" & q).Interior.ColorIndex = 3
      End If
      If Range("O" & q) = "" Then
        Range("O" & q).Interior.ColorIndex = 3
      End If
      If Range("P" & q) = "" Then
        Range("P" & q).Interior.ColorIndex = 3
      End If
    End If
    If Not Range("N" & q) = "" And Not Range("K" & q) = "" And Range("N" & q) > Range("K" & q) Then
      Range("N" & q).Interior.ColorIndex = 3
    End If
    If Not Range("O" & q) = "" And Not Range("K" & q) = "" And Range("O" & q) > Range("K" & q) Then
      Range("O" & q).Interior.ColorIndex = 3
    End If
    If Not Range("P" & q) = "" And Not Range("R" & q) = "" And Range("P" & q) < Range("R" & q) Then
      Range("P" & q).Interior.ColorIndex = 3
    End If
    If Not Range("R" & q) = "" And Not Range("T" & q) = "" And Range("R" & q) < Range("T" & q) Then
      Range("R" & q).Interior.ColorIndex = 3
    End If
    If Not Range("T" & q) = "" And Not Range("U" & q) = "" And Range("T" & q) < Range("U" & q) Then
      Range("T" & q).Interior.ColorIndex = 3
    End If
    If Range("V" & q) = "" Then
      Range("V" & q).Interior.ColorIndex = 3
    End If
  Next q
End Sub

Looks enticing eh?

Currently listening to: Fugazi!!!
Currently reading: nothing.
Currently feeling: I don't know what.
Posted by 1217713 at 06:10 PM | 8 !

January 27th, 2008

Another of those entries.


1217713

At the moment, the book I'm reading is Thomas Harris' Hannibal. I got it for a little over a hundred at a second-hand bookshop. But that's not the point of this entry. The reason I'm typing these words here is due to a recent realization about fictionists, their work, and their characters. It hit me while I was in the shower, which is not to say where I get most of my ideas, mind you. It just so happened I was taking a bath at that time when I realized something about writing.

Anyway, we all know that the characters' fate in stories depend upon their respective author's ideas, right? The same goes for their skills, knowledge, way of life, beliefs, et cetera. Now, Hannibal's protagonist/anti-hero, (?) Hannibal Lecter, is of course, a genius. That's a given. We know this for certain because that was how he was portrayed. Now, having a character of this sort in a story and convincing the readers that that character is indeed a genius is no easy feat. Why, you may ask. Well, I figured other character personalities can be easily copied from real life. Say, if you need a teacher in the story, you have lots of those in school. Or perhaps if you want a driver, there's a whole variety of them out on the streets. And so on. And you can even splice certain aspects you see in people and join them to come up with a character with a whole new personality than their "parents". What I'm saying here is that if you need a basis for a particular character you may want to create as a fictionist, you have a seemingly inexhaustible supply all around you. Practically within reach, even. However, that's not the case if you have a Hannibal Lecter in the picture. The author has to at least be as smart as his or her character to be able to pull that trick off. He has to dig deep within to flesh out the genius.

Currently listening to: Rise Up! Rise Up!
Currently reading: Hannibal
Currently feeling: okay. Relatively.
Posted by 1217713 at 10:29 PM | ?

Cross-Eyed Cat.


1217713

Seryoso. Duling nga. Haha.

Untitled

Currently listening to: The Dresden Dolls
Currently reading: Hannibal
Currently feeling: dunno.
Posted by 1217713 at 10:34 PM | ?

January 28th, 2008

Version 2


1217713

After a few trial runs and some debugging, I managed to fix the programs' bugs (or so I hope). Woohoo.

 

Part 1:

'Procedure:
  '1.Open Disposal file of a cluster.
  '2.Overwrite the filenames for wksDisp.
  '3.Run ForDisposal.
 
Sub ForDisposal()
  'Application.ScreenUpdating = False
  Set wksDisp = Workbooks("Testing123_Disposalorig.xls").Worksheets(1)
  wksDisp.Activate
  CheckStatDisp
  DeleteRowsDisp
''Add Heading''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
  Workbooks("Create_OU_csv_cleanse.xls").Worksheets("HeadingSource").Activate '
  Range("A7:Z7").Select                                                       '
  Selection.Copy                                                              '
  wksDisp.Activate                                                            '
  Range("B7").Select                                                          '
  ActiveSheet.Paste                                                           '
  Application.CutCopyMode = False                                             '
  Application.ScreenUpdating = True                                           '
End Sub
Sub CheckStatDisp()
  Dim LastRow As Long
  Get_LastRow wksDisp, 9, 9, LastRow
  For i = 9 To LastRow
''''For Allocated Date and Status matching''''''''''''''''''''''''''''''''''''
    If ActiveSheet.Range("I" & i) = "Requested" And Range("P" & i) = "" Then '
      Range("I" & i).Interior.ColorIndex = 3                                 '
      Range("P" & i).Interior.ColorIndex = 3                                 '
    End If                                                                   '
''''For Execution Start Date and Status matching''''''''''''''''''''''''''''''
    If Range("I" & i) = "Execution" And Range("L" & i) = "" Then             '
      Range("I" & i).Interior.ColorIndex = 3                                 '
      Range("L" & i).Interior.ColorIndex = 3                                 '
    End If                                                                   '
''''For Completion Date and Status matching'''''''''''''''''''''''''''''''''''
    If Range("I" & i) = "Completed" And Range("K" & i) = "" Then             '
      Range("I" & i).Interior.ColorIndex = 3                                 '
      Range("K" & i).Interior.ColorIndex = 3                                 '
    End If                                                                   '
''''For C/DC Date and Status matching'''''''''''''''''''''''''''''''''''''''''''''''
    If Range("I" & i) = "Commissioned/Decommissioned" And Range("J" & i) = "" Then '
      Range("I" & i).Interior.ColorIndex = 3                                       '
      Range("J" & i).Interior.ColorIndex = 3                                       '
    End If                                                                         '
''''For IP and IP Approval Date matching''''''''''''''''''''''''''''''''''''''''''''
    If (Range("M" & i) = "N" And Not Range("N" & i) = "") _
          Or (Range("M" & i) = "Y" And Range("N" & i) = "") Then  '
      Range("M" & i).Interior.ColorIndex = 3                      '
      Range("N" & i).Interior.ColorIndex = 3                      '
    End If                                                        '
''''For Allocation and Allocated Date matching'''''''''''''''''''''
    If Range("O" & i) = "N" And Not Range("P" & i) = "" _
          Or (Range("O" & i) = "Y" And Range("P" & i) = "") Then  '
      Range("O" & i).Interior.ColorIndex = 3                      '
      Range("P" & i).Interior.ColorIndex = 3                      '
    End If                                                        '
''''For Project Status and IP and Allocation matching''''''''''''''''''
    If Range("M" & i) = "Y" Then                                      '
      If Range("O" & i) = "Y" And (Range("I" & i) = "Identified" _
            Or Range("I" & i) = "Requested") Then                     '
        Range("I" & i).Interior.ColorIndex = 3                        '
      End If                                                          '
''''''For checking C/DC Date against IP''''''''''                     '
      If Range("J" & i) = "" Then               '                     '
        Range("J" & i).Interior.ColorIndex = 3  '                     '
      End If                                    '                     '
''''''For checking Completion Date against IP''''                     '
      If Range("K" & i) = "" Then               '                     '
        Range("K" & i).Interior.ColorIndex = 3  '                     '
      End If                                    '                     '
''''''For checking Exec Start Date against IP   '                     '
      If Range("L" & i) = "" Then               '                     '
        Range("L" & i).Interior.ColorIndex = 3  '                     '
      End If                                    '                     '
    End If                                      '                     '
    If Range("O" & i) = "N" Then                                      '
      If Range("I" & i) = "Identified" Or Range("I" & i) = "Requested" Then                     '
        Range("I" & i).Interior.ColorIndex = xlNone                   '
      Else                                                            '
        Range("I" & i).Interior.ColorIndex = 3                        '
      End If                                                          '
    End If                                                            '
''''For PM checking against Allocation'''''''''''''''''''''''''''''''''
    If Range("O" & i) = "Y" And Range("AA" & i) = "" Then             '
      Range("AA" & i).Interior.ColorIndex = 3                         '
    End If                                                            '
''''For checking if IP Date is earlier than Allocation Date''''''''''''
    If Not Range("N" & i) = "" And Not Range("P" & i) = "" _
            And Range("N" & i) < Range("P" & i) Then                  '
      Range("N" & i).Interior.ColorIndex = 3                          '
    End If                                                            '
''''For checking if Allocation Date is earlier than Proj ID Date'''''''
    If Not Range("P" & i) = "" And Not Range("Q" & i) = "" _
            And Range("P" & i) < Range("Q" & i) Then                  '
      Range("P" & i).Interior.ColorIndex = 3                          '
    End If                                                            '
''''To check if Site Name is not empty'''''''''''''''''''''''''''''''''
    If Range("H" & i) = "" Then                                       '
      Range("H" & i).Interior.ColorIndex = 3                          '
    End If                                                            '
''''To check if dates are chonological'''''''''''''''''''''''''''''''''
    If Not Range("N" & i) = "" And Not Range("P" & i) = "" And Range("N" & i) < Range("P" & i) Then
      Range("N" & i).Interior.ColorIndex = 3
    End If
    If Not Range("P" & i) = "" And Not Range("Q" & i) = "" And Range("P" & i) < Range("Q" & i) Then
      Range("P" & i).Interior.ColorIndex = 3
    End If
''''To check if columns W to Z are consistent with CAPEX Category'''''
    For q = 23 To 26                                                 '
      If Range("U" & i) = "Growth" And Cells(i, q) = "" Then         '
        Cells(i, q).Interior.ColorIndex = 3                          '
      End If                                                         '
    Next q
  Next i
End Sub
Sub DeleteRowsDisp()
  'Set wksDisp = Workbooks("East_Datagaps_0123_Disposaltrial.xls").Worksheets(1)
  UnmergeDisp
  Dim LastRow As Long
  Get_LastRow wksDisp, 9, 3, LastRow
''To delete Year 2006 projects that has C/DC Date earlier than 2007''''
  For j = 9 To LastRow                                                '
    If Not Range("J" & j) = "" Then                                   '
      If Range("C" & j) = "2006" And Range("J" & j) < #1/1/2007# Then '
        Rows(j).Select                                                '
        Range("C" & j).Activate                                       '
        'Selection.Interior.ColorIndex = 5                            '
        Selection.Delete Shift:=xlUp                                  '
        LastRow = LastRow - 1                                         '
        j = j - 1                                                     '
      End If                                                          '
    End If                                                            '
  Next j                                                              '
End Sub
Sub UnmergeDisp()
''Unmerge columns A and B''''''''''''''''''''
  Range("A9:B65536").Select                 '
  With Selection                            '
    .HorizontalAlignment = xlLeft           '
    .VerticalAlignment = xlCenter           '
    .MergeCells = False                     '
  End With                                  '
''Copy Market Name to their respective cells'
  Dim LastRow1 As Long                      '
  Dim MarketTemp As String                  '
  Get_LastRow wksDisp, 9, 3, LastRow1       '
  For k = 9 To LastRow1                     '
    If Not Range("B" & k) = "" Then         '
      MarketTemp = Range("B" & k)           '
    End If                                  '
    If Range("B" & k) = "" Then             '
      Range("B" & k) = MarketTemp           '
    End If                                  '
  Next k                                    '
End Sub

 Part 2:

'Procedure:
  '1.Open Acquisition file of a cluster.
  '2.Open Additional file acquired via Datagaps_new Tool on Cognos.
  '3.Overwrite the filenames for wksAcq.
  '4.Run ForAcquisition.

Sub ForAcquisition()
  Application.ScreenUpdating = False
  Dim LastRow0 As Long
  Set wksAcq = Workbooks("Testing123_Acquisitionorig.xls").Worksheets(1)
  wksAcq.Activate
  InsertCols
  CheckStatAcq
  Populate
  DeleteRowsAcq
  Get_LastRow wksAcq, 9, 6, LastRow0
  For l = 9 To LastRow0
''''To check if Site Name is not empty'''''''''''''''''''''''''''''''
    If Range("H" & l) = "" Then                                     '
      Range("H" & l).Interior.ColorIndex = 3                        '
    End If                                                          '
''''To check if columns AB to AF are consistent with CAPEX Category''
    For q = 28 To 32                                                '
      If Range("Z" & l) = "Growth" And Cells(l, q) = "" Then        '
        Cells(l, q).Interior.ColorIndex = 3                         '
      End If                                                        '
    Next q                                                          '
  Next l                                                            '
  CheckNewFields
  Application.ScreenUpdating = True
End Sub
Sub CheckStatAcq()
  Dim LastRow As Long
  Get_LastRow wksAcq, 9, 9, LastRow
  For i = 9 To LastRow
''''For Allocated Date and Status matching''''''''''''''''''''''''''''''''''''
    If ActiveSheet.Range("I" & i) = "Requested" And Range("T" & i) = "" Then '
      Range("I" & i).Interior.ColorIndex = 3                                 '
      Range("T" & i).Interior.ColorIndex = 3                                 '
    End If                                                                   '
''''For Execution Start Date and Status matching''''''''''''''''''''''''''''''
    If Range("I" & i) = "Execution" And Range("M" & i) = "" Then             '
      Range("I" & i).Interior.ColorIndex = 3                                 '
      Range("M" & i).Interior.ColorIndex = 3                                 '
    End If                                                                   '
''''For Completion Date and Status matching'''''''''''''''''''''''''''''''''''
    If Range("I" & i) = "Completed" And Range("L" & i) = "" Then             '
      Range("I" & i).Interior.ColorIndex = 3                                 '
      Range("L" & i).Interior.ColorIndex = 3                                 '
    End If                                                                   '
''''For C/DC Date and Status matching'''''''''''''''''''''''''''''''''''''''''''''''
    If Range("I" & i) = "Commissioned/Decommissioned" And Range("J" & i) = "" Then '
      Range("I" & i).Interior.ColorIndex = 3                                       '
      Range("J" & i).Interior.ColorIndex = 3                                       '
    End If                                                                         '
''''For IP and IP Approval Date matching''''''''''''''''''''''''''''''''''''''''''''
    If (Range("Q" & i) = "N" And Not Range("R" & i) = "") _
            Or (Range("Q" & i) = "Y" And Range("R" & i) = "") Then                 '
      Range("Q" & i).Interior.ColorIndex = 3                                       '
      Range("R" & i).Interior.ColorIndex = 3                                       '
    End If                                                                         '
''''For Allocation and Allocated Date matching''''''''''''''''''''''''''''''''''''''
    If Range("S" & i) = "N" And Not Range("T" & i) = "" _
            Or (Range("S" & i) = "Y" And Range("T" & i) = "") Then                 '
      Range("S" & i).Interior.ColorIndex = 3                                       '
      Range("T" & i).Interior.ColorIndex = 3                                       '
    End If                                                                         '
''''For Project Status and IP and Allocation matching'''''''''''''''''''''''''''''''
    If Range("Q" & i) = "Y" Then                                      '
      If Range("S" & i) = "Y" And (Range("I" & i) = "Identified" _
              Or Range("I" & i) = "Requested") Then                   '
        Range("I" & i).Interior.ColorIndex = 3                        '
      End If                                                          '
''''''For checking C/DC Date against IP''''''''''                     '
      If Range("J" & i) = "" Then               '                     '
        Range("J" & i).Interior.ColorIndex = 3  '                     '
      End If                                    '                     '
''''''For checking Completion Date against IP''''                     '
      If Range("L" & i) = "" Then               '                     '
        Range("L" & i).Interior.ColorIndex = 3  '                     '
      End If                                    '                     '
''''''For checking Exec Start Date against IP   '                     '
      If Range("M" & i) = "" Then               '                     '
        Range("M" & i).Interior.ColorIndex = 3  '                     '
      End If                                    '                     '
    End If                                                            '
    If Range("S" & i) = "N" Then
      If Range("I" & i) = "Identified" Or Range("I" & i) = "Requested" Then
        Range("I" & i).Interior.ColorIndex = xlNone                   '
      Else                                                            '
        Range("I" & i).Interior.ColorIndex = 3                        '
      End If                                                          '
    End If                                                            '
''''For PM checking against Allocation'''''''''''''''''''''''''''''''''
    If Range("S" & i) = "Y" And Range("AG" & i) = "" Then             '
      Range("AG" & i).Interior.ColorIndex = 3                         '
    End If                                                            '
  Next i
End Sub
Sub DeleteRowsAcq()
  'Set wksAcq = Workbooks("Testing123_Acquisition.xls").Worksheets(1)
  'wksAcq.Activate
  UnmergeAcq
  Dim LastRow As Long, LastRowq As Long
  Get_LastRow wksAcq, 9, 3, LastRow
''To delete Year 2006 projects that has C/DC Date earlier than 2007''''
  For j = 9 To LastRow                                                '
    If Not Range("J" & j) = "" Then                                   '
      If Range("C" & j) = "2006" And Range("J" & j) < #1/1/2007# Then '
        Rows(j).Select                                                '
        Range("C" & j).Activate                                       '
        'Selection.Interior.ColorIndex = 5                            '
        Selection.Delete Shift:=xlUp                                  '
        LastRow = LastRow - 1                                         '
        j = j - 1                                                     '
      End If                                                          '
    End If                                                            '
  Next j                                                              '
  Get_LastRow wksAcq, 9, 3, LastRowq                                  '
''To delete Year 2006 projects that has ATD Date earlier than 2007'''''
  For g = 9 To LastRowq                                               '
    If Not Range("K" & g) = "" Then                                   '
      If Range("C" & g) = "2006" And Range("K" & g) < #1/1/2007# Then '
        Rows(g).Select                                                '
        Range("C" & g).Activate                                       '
        'Selection.Interior.ColorIndex = 7                            '
        Selection.Delete Shift:=xlUp                                  '
        LastRowq = LastRowq - 1                                       '
        g = g - 1                                                     '
      End If                                                          '
    End If                                                            '
  Next g                                                              '
End Sub
Sub InsertCols()
  Columns(11).Select
  Selection.Insert Shift:=xlToRight
  Range("K9").Select
  Range(Selection, Selection.End(xlDown)).Select
  Selection.Interior.ColorIndex = xlNone
  Columns(11).Select
  Selection.ColumnWidth = 17.71
  Columns(11).Select
  Selection.Copy
  For A = 14 To 16
    Columns(A).Select
    Selection.Insert Shift:=xlToRight
  Next A
  Columns(31).Select
  Selection.Insert Shift:=xlToRight
  Columns(22).Select
  Selection.Insert Shift:=xlToRight
  Application.CutCopyMode = False
  Range("K8").Select
  ActiveCell.FormulaR1C1 = "Actual Trading Date"
  Range("N8").Select
  ActiveCell.FormulaR1C1 = "Building Permit Date"
  Range("O8").Select
  ActiveCell.FormulaR1C1 = "Zoning/Planning Approval"
  Range("P8").Select
  ActiveCell.FormulaR1C1 = "Land Secured Date"
  Range("V8").Select
  ActiveCell.FormulaR1C1 = "Projected Trading Date"
  Range("AF8").Select
  ActiveCell.FormulaR1C1 = "RTEP/IRR(%)"
End Sub
Sub Populate()
  'Set wksAcq = Workbooks("Testing123_Acquisition.xls").Worksheets(1)
  Dim LastRowAcq As Long
  Dim wksTesting As New Worksheet
  Set wksTesting = Workbooks("Testing123_Add.xls").ActiveSheet
  Get_LastRow wksAcq, 9, 6, LastRowAcq
  For d = 9 To LastRowAcq
    On Error Resume Next
    wksAcq.Range("K" & d).Select
    Selection.NumberFormat = "d mmm, yyyy;@"
    wksAcq.Range("K" & d) = Application.VLookup(wksAcq.Range("F" & d), wksTesting.Range("D3:AE65536"), 22, 0)
    Range("N" & d).Select
    Selection.NumberFormat = "d mmm, yyyy;@"
    wksAcq.Range("N" & d) = Application.VLookup(wksAcq.Range("F" & d), wksTesting.Range("D3:AE65536"), 21, 0)
    Range("O" & d).Select
    Selection.NumberFormat = "d mmm, yyyy;@"
    wksAcq.Range("O" & d) = Application.VLookup(wksAcq.Range("F" & d), wksTesting.Range("D3:AE65536"), 20, 0)
    Range("P" & d).Select
    Selection.NumberFormat = "d mmm, yyyy;@"
    wksAcq.Range("P" & d) = Application.VLookup(wksAcq.Range("F" & d), wksTesting.Range("D3:AE65536"), 19, 0)
    Range("V" & d).Select
    Selection.NumberFormat = "d mmm, yyyy;@"
    wksAcq.Range("V" & d) = Application.VLookup(wksAcq.Range("F" & d), wksTesting.Range("D3:AE65536"), 24, 0)
    Range("AF" & d).Select
    Selection.NumberFormat = "0.00"
    wksAcq.Range("AF" & d) = Application.VLookup(wksAcq.Range("F" & d), wksTesting.Range("D3:AE65536"), 11, 0)
    If Range("K" & d) < #1/1/1900# Then
      wksAcq.Range("K" & d) = ""
    End If
    If Range("N" & d) < #1/1/1900# Then
      wksAcq.Range("N" & d) = ""
    End If
    If Range("O" & d) < #1/1/1900# Then
      wksAcq.Range("O" & d) = ""
    End If
    If Range("P" & d) < #1/1/1900# Then
      wksAcq.Range("P" & d) = ""
    End If
    If Range("V" & d) < #1/1/1900# Then
      wksAcq.Range("V" & d) = ""
    End If
    If Range("AF" & d) = 0 Then
      wksAcq.Range("AF" & d) = ""
    End If
  Next d
''Add Heading''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    Workbooks("Create_OU_csv_cleanse.xls").Worksheets("HeadingSource").Activate '
    Range("A5:AF5").Select                                                      '
    Selection.Copy                                                              '
    wksAcq.Activate                                                 

Currently listening to: Phantom Planet
Currently reading: Hannibal
Currently feeling: sleepy.
Posted by 1217713 at 02:43 PM | ?

January 31st, 2008

Final Version


1217713

Hopefully. But definitely, this is more user friendly!

Public wksDisp As Worksheet
Public wksAcq As Worksheet
Public wksAdd As Worksheet

Public wkbAcq As Workbook
Public wkbDisp As Workbook
Public wkbAdd As Workbook

Public datetoday As Date
Public yeartoday As Long
Public monthtoday As Long
Public delearlier As Date
Public datestartmonth As Date

'Procedure:
  '1.Run ForDisposal
  '2.Select file obtained via Datagap2 on Cognos.
 
Sub ForDisposal()
  Application.ScreenUpdating = False
 
  Dim strTempName As String
 
  strTempName = "Page1"

  fileToOpen = Application.GetOpenFilename("Excel Files (*.xls), *.xls")

  If fileToOpen <> False Then
    Workbooks.Open fileToOpen
    Set wkbDisp = ActiveWorkbook
    Set wksDisp = wkbDisp.Worksheets(strTempName)
  Else
    Exit Sub
  End If
  'Set wksDisp = Workbooks("PH_NPSGAPDODISP_020108.xls").Worksheets(1)
  wksDisp.Activate
  CheckStatDisp
  DeleteRowsDisp
''Add Heading''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
  Workbooks("Get_NPS_Datagaps(1.2).xls").Worksheets("HeadingSource").Activate '
  Range("A7:Z7").Select                                                       '
  Selection.Copy                                                              '
  wksDisp.Activate                                                            '
  Range("B7").Select                                                          '
  ActiveSheet.Paste                                                           '
  Application.CutCopyMode = False                                             '
''Set date fields to desired date format'''''''''''''''''''''''''''''''''''''''
  Range("J9:L65536, N9:N65536, P9:R65536").Select                             '
  Selection.NumberFormat = "d mmm, yyyy;@"                                    '
  Application.ScreenUpdating = True                                           '
End Sub
Sub CheckStatDisp()
  Dim LastRow As Long
  Get_LastRow wksDisp, 9, 9, LastRow
  For i = 9 To LastRow
''''For Allocated Date and Status matching''''''''''''''''''''''''''''''''''''
    If ActiveSheet.Range("I" & i) = "Requested" And Range("P" & i) = "" Then '
      Range("I" & i).Interior.ColorIndex = 3                                 '
      Range("P" & i).Interior.ColorIndex = 3                                 '
    End If                                                                   '
''''For Execution Start Date and Status matching''''''''''''''''''''''''''''''
    If Range("I" & i) = "Execution" And Range("L" & i) = "" Then             '
      Range("I" & i).Interior.ColorIndex = 3                                 '
      Range("L" & i).Interior.ColorIndex = 3                                 '
    End If                                                                   '
''''For Completion Date and Status matching'''''''''''''''''''''''''''''''''''
    If Range("I" & i) = "Completed" And Range("K" & i) = "" Then             '
      Range("I" & i).Interior.ColorIndex = 3                                 '
      Range("K" & i).Interior.ColorIndex = 3                                 '
    End If                                                                   '
''''For C/DC Date and Status matching'''''''''''''''''''''''''''''''''''''''''''''''
    If Range("I" & i) = "Commissioned/Decommissioned" And Range("J" & i) = "" Then '
      Range("I" & i).Interior.ColorIndex = 3                                       '
      Range("J" & i).Interior.ColorIndex = 3                                       '
    End If                                                                         '
''''For IP and IP Approval Date matching''''''''''''''''''''''''''''''''''''''''''''
    If (Range("M" & i) = "N" And Not Range("N" & i) = "") _
          Or (Range("M" & i) = "Y" And Range("N" & i) = "") Then  '
      Range("M" & i).Interior.ColorIndex = 3                      '
      Range("N" & i).Interior.ColorIndex = 3                      '
    End If                                                        '
''''For Allocation and Allocated Date matching'''''''''''''''''''''
    If Range("O" & i) = "N" And Not Range("P" & i) = "" _
          Or (Range("O" & i) = "Y" And Range("P" & i) = "") Then  '
      Range("O" & i).Interior.ColorIndex = 3                      '
      Range("P" & i).Interior.ColorIndex = 3                      '
    End If                                                        '
''''For Project Status and IP and Allocation matching''''''''''''''''''
    If Range("M" & i) = "Y" Then                                      '
      If Range("O" & i) = "Y" And (Range("I" & i) = "Identified" _
            Or Range("I" & i) = "Requested") Then                     '
        Range("I" & i).Interior.ColorIndex = 3                        '
      End If                                                          '
''''''For checking C/DC Date against IP''''''''''                     '
      If Range("J" & i) = "" Then               '                     '
        Range("J" & i).Interior.ColorIndex = 3  '                     '
      End If                                    '                     '
''''''For checking Completion Date against IP''''                     '
      If Range("K" & i) = "" Then               '                     '
        Range("K" & i).Interior.ColorIndex = 3  '                     '
      End If                                    '                     '
''''''For checking Exec Start Date against IP   '                     '
      If Range("L" & i) = "" Then               '                     '
        Range("L" & i).Interior.ColorIndex = 3  '                     '
      End If                                    '                     '
    End If                                      '                     '
    If Range("O" & i) = "N" Then                                      '
      If Range("I" & i) = "Identified" Or Range("I" & i) = "Requested" Then                     '
        Range("I" & i).Interior.ColorIndex = xlNone                   '
      Else                                                            '
        Range("I" & i).Interior.ColorIndex = 3                        '
      End If                                                          '
    End If                                                            '
''''For PM checking against Allocation'''''''''''''''''''''''''''''''''
    If Range("O" & i) = "Y" And Range("AA" & i) = "" Then             '
      Range("AA" & i).Interior.ColorIndex = 3                         '
    ElseIf Range("O" & i) = "N" And Range("AA" & i) = "" Then         '
      Range("AA" & i).Interior.ColorIndex = xlNone                    '
    End If                                                            '
''''For checking if IP Date is earlier than Allocation Date''''''''''''
    If Not Range("N" & i) = "" And Not Range("P" & i) = "" _
            And Range("N" & i) < Range("P" & i) Then                  '
      Range("N" & i).Interior.ColorIndex = 3                          '
    End If                                                            '
''''For checking if Allocation Date is earlier than Proj ID Date'''''''
    If Not Range("P" & i) = "" And Not Range("Q" & i) = "" _
            And Range("P" & i) < Range("Q" & i) Then                  '
      Range("P" & i).Interior.ColorIndex = 3                          '
    End If                                                            '
''''To check if Site Name is not empty'''''''''''''''''''''''''''''''''
    If Range("H" & i) = "" Then                                       '
      Range("H" & i).Interior.ColorIndex = 3                          '
    End If                                                            '
''''To check if dates are chonological'''''''''''''''''''''''''''''''''
    If Not Range("N" & i) = "" And Not Range("P" & i) = "" And Range("N" & i) < Range("P" & i) Then
      Range("N" & i).Interior.ColorIndex = 3
    End If
    If Not Range("P" & i) = "" And Not Range("Q" & i) = "" And Range("P" & i) < Range("Q" & i) Then
      Range("P" & i).Interior.ColorIndex = 3
    End If
''''To check if columns W to Z are consistent with CAPEX Category'''''
    For q = 23 To 26                                                 '
      If Range("U" & i) = "Growth" And Cells(i, q) = "" Then         '
        Cells(i, q).Interior.ColorIndex = 3                          '
      End If                                                         '
      If q = 25 And Cells(i, q) = "" Then                            '
        Cells(i, q).Interior.ColorIndex = 3                          '
      End If                                                         '
    Next q                                                           '
''''To check if Site ID shouldn't be colored red'''''''''''''''''''''''''''
    If (Range("I" & i) = "Identified" Or Range("I" & i) _
            = "Requested" Or Range("I" & i) = "Execution Pre-Work") _
            And Not Range("D" & i) = "" Then                              '
      Range("D" & i).Interior.ColorIndex = xlNone                         '
    End If                                                                '
''''To check if dates are chronological''''''''''''''''''''''''''''''''''''
    If Not Range("L" & i) = "" And Not Range("K" & i) = "" And _
        Range("L" & i) > Range("K" & i) Then                              '
      Range("L" & i).Interior.ColorIndex = 3                              '
      Range("K" & i).Interior.ColorIndex = 3                              '
    End If                                                                '
    If Not Range("K" & i) = "" And Not Range("J" & i) = "" And _
        Range("K" & i) > Range("J" & i) Then                              '
      Range("K" & i).Interior.ColorIndex = 3                              '
      Range("J" & i).Interior.ColorIndex = 3                              '
    End If                                                                '
    If Not Range("L" & i) = "" And Not Range("J" & i) = "" And _
        Range("L" & i) > Range("J" & i) Then                              '
      Range("L" & i).Interior.ColorIndex = 3                              '
      Range("J" & i).Interior.ColorIndex = 3                              '
    End If                                                                '
  Next i
End Sub
Sub DeleteRowsDisp()
  'Set wksDisp = Workbooks("East_Datagaps_0123_Disposaltrial.xls").Worksheets(1)
  UnmergeDisp
  Dim LastRow As Long
  Get_LastRow wksDisp, 9, 3, LastRow
  GetWindow
''To delete Year 2006 projects that has C/DC Date earlier than 2007''''REVISE THIS!!!
  For j = 9 To LastRow                                                '
    If Not Range("J" & j) = "" Then                                   '
      If Range("C" & j) < (yeartoday - 1) And Range("J" & j) < delearlier Then
        Rows(j).Select                                                '
        Range("C" & j).Activate                                       '
        'Selection.Interior.ColorIndex = 5                            '
        Selection.Delete Shift:=xlUp                                  '
        LastRow = LastRow - 1                                         '
        j = j - 1                                                     '
      End If                                                          '
    End If                                                            '
  Next j                                                              '
''To delete projects whose status are "On Hold" or "Cancelled"''''''''''
  For a = 9 To LastRoww                                                '
    If Range("I" & a) = "Cancelled" Or Range("I" & a) = "On Hold" Then '
      Rows(a).Select                                                   '
      Range("C" & a).Activate                                          '
      'Selection.Interior.ColorIndex = 7                               '
      Selection.Delete Shift:=xlUp                                     '
      LastRoww = LastRoww - 1                                          '
      a = a - 1                                                        '
    End If                                                             '
  Next a
End Sub
Sub UnmergeDisp()
''Unmerge columns A and B''''''''''''''''''''
  Range("A9:B65536").Select                 '
  With Selection                            '
    .HorizontalAlignment = xlLeft           '
    .VerticalAlignment = xlCenter           '
    .MergeCells = False                     '
  End With                                  '
''Copy Market Name to their respective cells'
  Dim LastRow1 As Long                      '
  Dim MarketTemp As String                  '
  Get_LastRow wksDisp, 9, 3, LastRow1       '
  For K = 9 To LastRow1                     '
    If Not Range("B" & K) = "" Then         '
      MarketTemp = Range("B" & K)           '
    End If                                  '
    If Range("B" & K) = "" Then             '
      Range("B" & K) = MarketTemp           '
    End If                                  '
  Next K                                    '
End Sub

'Procedure:
  '1.Run ForAcquisition.
  '2 Select file obtained via DataGap2 on Cognos.
  '3 Select file obtained via Datagapnew on Cognos.

Sub ForAcquisition()
  Application.ScreenUpdating = False
  Dim LastRow0 As Long
  Dim strTempName As String
 
  strTempName = "Page1"

  fileToOpen = Application.GetOpenFilename("Excel Files (*.xls), *.xls")

  If fileToOpen <> False Then
    Workbooks.Open fileToOpen
    Set wkbAcq = ActiveWorkbook
    Set wksAcq = wkbAcq.Worksheets(strTempName)
    fileToOpen2 = Application.GetOpenFilename("Excel Files (*.xls), *.xls")
    If fileToOpen2 <> False Then
      Workbooks.Open fileToOpen2
      Set wkbAdd = ActiveWorkbook
      Set wksAdd = wkbAdd.Worksheets(strTempName)
    Else
      Exit Sub
    End If
  Else
    Exit Sub
  End If

  'Set wksAcq = Workbooks("PH_NPSGAPCO_020108.xls").Worksheets(1)
  wksAcq.Activate
  InsertCols
  CheckStatAcq
  Populate
  DeleteRowsAcq
  Get_LastRow wksAcq, 9, 6, LastRow0
  For l = 9 To LastRow0
''''To check if Site ID shouldn't be colored red'''''''''''''''''''''
    If (Range("I" & l) = "Identified" Or Range("I" & l) _
            = "Requested" Or Range("I" & l) = "Execution Pre-Work") _
            And Not Range("D" & l) = "" Then                        '
      Range("D" & l).Interior.ColorIndex = xlNone                   '
    End If                                                          '
''''To check if Site Name is not empty'''''''''''''''''''''''''''''''
    If Range("H" & l) = "" Then                                     '
      Range("H" & l).Interior.ColorIndex = 3                        '
    End If                                                          '
''''To check if columns AB to AF are consistent with CAPEX Category''
    For q = 28 To 32                                                '
      If Range("Z" & l) = "Growth" And Cells(l, q) = "" Then        '
        Cells(l, q).Interior.ColorIndex = 3                         '
      End If                                                        '
      If q = 30 And Cells(l, q) = "" Then                           '
        Cells(l, q).Interior.ColorIndex = 3                         '
      End If                                                        '
    Next q                                                          '
  Next l                                                            '
  CheckNewFields
''Set date fields to desired date format'''''''''''''''''''''''''''''
  Range("J9:P65536, R9:R65536, T9:W65536").Select                   '
  Selection.NumberFormat = "d mmm, yyyy;@"                          '
  Application.ScreenUpdating = True
End Sub
Sub CheckStatAcq()
  Dim LastRow As Long
  Get_LastRow wksAcq, 9, 9, LastRow
  For i = 9 To LastRow
''''For Allocated Date and Status matching''''''''''''''''''''''''''''''''''''
    If ActiveSheet.Range("I" & i) = "Requested" And Range("T" & i) = "" Then '
      Range("I" & i).Interior.ColorIndex = 3                                 '
      Range("T" & i).Interior.ColorIndex = 3                                 '
    End If                                                                   '
''''For Execution Start Date and Status matching''''''''''''''''''''''''''''''
    If Range("I" & i) = "Execution" And Range("M" & i) = "" Then             '
      Range("I" & i).Interior.ColorIndex = 3                                 '
      Range("M" & i).Interior.ColorIndex = 3                                 '
    End If                                                                   '
''''For Completion Date and Status matching'''''''''''''''''''''''''''''''''''
    If Range("I" & i) = "Completed" And Range("L" & i) = "" Then             '
      Range("I" & i).Interior.ColorIndex = 3                                 '
      Range("L" & i).Interior.ColorIndex = 3                                 '
    End If                                                                   '
''''For C/DC Date and Status matching'''''''''''''''''''''''''''''''''''''''''''''''
    If Range("I" & i) = "Commissioned/Decommissioned" And Range("J" & i) = "" Then '
      Range("I" & i).Interior.ColorIndex = 3                                       '
      Range("J" & i).Interior.ColorIndex = 3                                       '
    End If                                                                         '
''''For IP and IP Approval Date matching''''''''''''''''''''''''''''''''''''''''''''
    If (Range("Q" & i) = "N" And Not Range("R" & i) = "") _
            Or (Range("Q" & i) = "Y" And Range("R" & i) = "") Then                 '
      Range("Q" & i).Interior.ColorIndex = 3                                       '
      Range("R" & i).Interior.ColorIndex = 3                                       '
    End If                                                                         '
''''For Allocation and Allocated Date matching''''''''''''''''''''''''''''''''''''''
    If Range("S" & i) = "N" And Not Range("T" & i) = "" _
            Or (Range("S" & i) = "Y" And Range("T" & i) = "") Then                 '
      Range("S" & i).Interior.ColorIndex = 3                                       '
      Range("T" & i).Interior.ColorIndex = 3                                       '
    End If                                                                         '
''''For Project Status and IP and Allocation matching'''''''''''''''''''''''''''''''
    If Range("Q" & i) = "Y" Then                                      '
      If Range("S" & i) = "Y" And (Range("I" & i) = "Identified" _
              Or Range("I" & i) = "Requested") Then                   '
        Range("I" & i).Interior.ColorIndex = 3                        '
      End If                                                          '
''''''For checking C/DC Date against IP''''''''''                     '
      If Range("J" & i) = "" Then               '                     '
        Range("J" & i).Interior.ColorIndex = 3  '                     '
      End If                                    '                     '
''''''For checking Completion Date against IP''''                     '
      If Range("L" & i) = "" Then               '                     '
        Range("L" & i).Interior.ColorIndex = 3  '                     '
      End If                                    '                     '
''''''For checking Exec Start Date against IP   '                     '
      If Range("M" & i) = "" Then               '                     '
        Range("M" & i).Interior.ColorIndex = 3  '                     '
      End If                                    '                     '
    End If                                                            '
    If Range("S" & i) = "N" Then
      If Range("I" & i) = "Identified" Or Range("I" & i) = "Requested" Then
        Range("I" & i).Interior.ColorIndex = xlNone                   '
      Else                                                            '
        Range("I" & i).Interior.ColorIndex = 3                        '
      End If                                                          '
    End If                                                            '
''''For PM checking against Allocation'''''''''''''''''''''''''''''''''
    If Range("S" & i) = "Y" And Range("AG" & i) = "" Then             '
      Range("AG" & i).Interior.ColorIndex = 3                         '
    ElseIf Range("S" & i) = "N&qu

Currently listening to: Fell, Destroyed
Currently reading: Hannibal
Currently feeling: wala.
Posted by 1217713 at 04:38 PM | ?
« 2007/12 | 2008/02 »