Posts by chirayuw

Sub Scenario_Future()


'Define Variables
Dim FDT As Date 'Future Date
Dim FRow As Integer 'Find row in which our future date is in colum C


'Set Variables
FDT = Range("A7") + 89 'If we count A7 as day 1 - otherwise change to 90
FRow = Range("C:C").Find(what:=FDT, LookIn:=xlValues, lookat:=xlWhole).Row


'Add row after date
Range("C" & FRow + 1).EntireRow.Insert shift:=xlDown


End Sub


Sub Scenario_Past_And_Future()


'Define Variables
Dim FDT As Date 'Future Date
Dim PDT As Date 'Past Date
Dim FRow As Integer 'Find row in which our future date is in colum C
Dim PRow As Integer 'Find row in which our past date is in colum C


'Set Variables
FDT = Range("A7") + 89 'If we count A7 as day 1 - otherwise change to 90
PDT = Range("A7") - 89 'If we count A7 as day 1 - otherwise change to 90
FRow = Range("C:C").Find(what:=FDT, LookIn:=xlValues, lookat:=xlWhole).Row
PRow = Range("C:C").Find(what:=PDT, LookIn:=xlValues, lookat:=xlWhole).Row


'Add row after date
Range("C" & FRow + 1).EntireRow.Insert shift:=xlDown
Range("C" & PRow + 1).EntireRow.Insert shift:=xlDown


End Sub

Sub VAL_PASTE()


Dim Bval As Double
Dim CVal As Long
Dim DT As Date


With Sheets("Sheet1")
    DT = .Range("G1")
    Bval = .Range("F3")
    CVal = .Range("H5")
End With


Sheets("Sheet2").Select
Range("A:A").Find(what:=DT, LookIn:=xlValues, lookat:=xlWhole).Activate
Range("B" & ActiveCell.Row) = Bval
Range("B" & ActiveCell.Row).NumberFormat = "0%"
Range("C" & ActiveCell.Row) = CVal
Sheets("Sheet1").Select


End Sub

Sub MyTarget()


Dim LstRow As Integer
Dim Target As Long
Dim Val As Long
Dim GRP As String


LstRow = Cells(Rows.Count, "A").End(xlUp).Row


Range("C2").Select
Do While ActiveCell.Row <= LstRow
   
    If Not IsEmpty(ActiveCell) Then Target = ActiveCell
    GRP = Range("A" & ActiveCell.Row)
   
    If GRP = "" And ActiveCell.Row <> LstRow Then
        ActiveCell.Offset(1, 0).Select
    Else
   
        Val = Range("B" & ActiveCell.Row)
        ActiveCell.Offset(1, 0).Select
       
        Do While GRP = Range("A" & ActiveCell.Row)
            If GRP = Range("A" & ActiveCell.Row) And Val <= Target Then
                Val = Val + Range("B" & ActiveCell.Row)
                ActiveCell.Offset(1, 0).Select
            Else
                ActiveCell.EntireRow.Delete
            End If
        Loop
       
        LstRow = Cells(Rows.Count, "A").End(xlUp).Row
   
    End If
   
Loop


End Sub

Sub lookup()


Dim lLastRow2 As Long


With Sheets("CLEANUP")
lLastRow2 = .Cells(.Rows.Count, "A").End(xlUp).Row
With .Range("B2:B" & lLastRow2)
.Formula = "=IFERROR(VLOOKUP(A2,ACTION!A:E,5,0),""N/A"")"
.Value = .Value
End With
End With


Calculate


On Error Resume Next
ActiveSheet.AutoFilterMode = False


ActiveSheet.Range("$A$1:$B$" & lLastRow2).AutoFilter Field:=2, Criteria1:="<>N/A"


End Sub