Visit the Forums
Register Now!

User Login


Forum Topic

excel macro programming

  • @dotdll

    the problem can be solved using an array formula

    =INDEX($A$2:$C$11,MATCH(F$1&$E2,$B$2:$B$11&$C$2:$C$11,0),1)
    copy the above formula in cell F2 and enter it as an array (ctrl+shift+enter) then drag to the right (J2) then drag down.
  • Mga pafs pahingi ng tulong at medyo rusty na ako sa macro...

    Bali ganito ang conditions

    Columns A and B contains dates
    tas mag fillout siya depending on formulas I set

    tas nirecord ko na ang macro ko as per below

    Paano ko i loop ko ito hanggang makatagpo siya ng blank cell

    Bale from B1 mag fillout siya ng 3 conditional formats tas mag move siya sa sunod na cell which is B2 at i fillout niya ng conditions pero mag bago na ang reference cell

    Bale Formula no 1:="=$A$3=$B$3 >>--------->>>> eh magiging $A$4=$B$4 >>--------->>>> $A$5=$B$5
    Formula no 2:= _"=AND((A3-TODAY())<8,((A3-TODAY())>0))" >>>------->> AND((A4-TODAY())<8,((A4-TODAY())>0))
    Formula no 3:=AND((TODAY()-A3)>0,ISBLANK($C$3)) >>>--->>> =AND((TODAY()-A4)>0,ISBLANK($C$4))

    etc... etc... Hanggang makatagpo ang macro ng empty cell ay doon na siya mag stop
    TIA
    ============================



    Sub Macro1()
    '
    ' Macro1 Macro
    '
    ' Keyboard Shortcut: Ctrl+k
    '
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=$A$3=$B$3"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
    .PatternColorIndex = xlAutomatic
    .Color = 255
    .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
    "=AND((A3-TODAY())<8,((A3-TODAY())>0))"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
    .PatternColorIndex = xlAutomatic
    .Color = 49407
    .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
    "=AND((TODAY()-A3)>0,ISBLANK($C$3))"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
    .PatternColorIndex = xlAutomatic
    .ThemeColor = xlThemeColorAccent6
    .TintAndShade = -0.249946592608417
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    Range("C5").Select
    End Sub

    -- edited by emcel on Oct 17 2016, 05:46 PM
  • Do Until Cells(lastrow, 1).Value = ""
    Cells(lastrow, 2).Value = Left(Cells(lastrow, 1).Value, Len(Cells(lastrow, 1).Value) - 3)
    Cells(lastrow, 3).Value = Right(Cells(lastrow, 1).Value, 3)
    lastrow = lastrow + 1
    Loop
    Promising tong do while ni Jazzanth... kaya LANG nag eeror ako at naiistuck sa first cell.

    -- edited by emcel on Oct 17 2016, 06:42 PM
  • always trim a variable or cell before comparing to a blank "" value/string.
    try this...

    Do Until TRIM(Cells(lastrow, 1).Value) = ""
  • Ok boss tnx... subukan ko yan
  • At last nakuha ko rin... relational conditional formatting lang pala solution
  • patulong po sana ako mga masters




    <click here for link>
    ang table po ay may date/time, PC unit, no of hours, gusto ko po sana makuha yung maximum no of hours per PC per day based dun sa hourly data, tapos yung data ay ina update daily, so mas ok sana kung auto update na rin yung table

    maraming salamat!

    -- edited by seymorebutts on Nov 26 2016, 08:59 AM
  • @seymorebutts: Puwede na ata Pivot table diyan. Gawin mo na lang table range ang source.
  • @seymorebutts

    hindi ko makita yung image hehe.. blocked sa office.. pero tama naman si sir bombermac.. pede na siguro pivot table dyan