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
  • @seymorebutts
    formula under pc1 , then drag to the right

    =sum(IF((F$1=$B$2:$B$11)*(TEXT($E2,"mm:dd:yyyy")=TEXT($A$2:$A$11,"mm:dd:yyyy")),$C$2:$C$11))
  • @ masters

    pwede ba mag tanong dito na hinde macro related? wala lumalabas na hinahanap ko sa google eh.

    gusto ko yung specific cell pag nag input ako ng number, mag insert siya automatically ng 1 decimal place. kunyari pag nag type ako 12345 sa cell A1, lalabas siya ng 1234.5 instead.

    nakita ko lang sa google yung sa advanced options mag insert ng decimal point, pero nag aapply ata siya sa buong spreadsheet. gusto ko lang sa specific cells lang siya nag aapply.

    possible ba yun? salamat
  • @ masters

    pwede ba mag tanong dito na hinde macro related? wala lumalabas na hinahanap ko sa google eh.

    gusto ko yung specific cell pag nag input ako ng number, mag insert siya automatically ng 1 decimal place. kunyari pag nag type ako 12345 sa cell A1, lalabas siya ng 1234.5 instead.

    nakita ko lang sa google yung sa advanced options mag insert ng decimal point, pero nag aapply ata siya sa buong spreadsheet. gusto ko lang sa specific cells lang siya nag aapply.

    possible ba yun? salamat
    format cell... choose number.... then reduce decimal spaces to 1.
  • @ masters
    pano po gagawing visible prin ang highlighted cell(s) kapag out of focus na ung worksheet? switched focus on another application. Thanks po.
  • Post deleted #11827034
  • emcel

    thanks boss na try ko na yan bago ko ipost yung tanong ko hehe, x.0 lagi lumalabas sa dulo. anyways thanks ule mamano mano ko na lang to haha
  • thanks boss na try ko na yan bago ko ipost yung tanong ko hehe, x.0 lagi lumalabas sa dulo. anyways thanks ule mamano mano ko na lang to haha
    ah... you mean... kapag 15 i-type mo... magiging 1.5? then click here

    <click here for link>

    nasa options yan

    1. Follow my first instructions
    2. highlight all cells you need the operation
    3. Click file
    4. Click options
    5. Click advanced
    5. Click auto decimal and change 2 to 1

    go try...

    -- edited by emcel on Dec 19 2016, 03:03 PM
  • emcel

    oo boss, pero natry ko na din yan pero gusto ko sa specific cell lang haha. dapat ata sinulat ko muna mga yan bago ako nag post xD

    kasi nung ginawa ko yan yung sa advanced options para sa buong spreadsheet eh. ok lang kung wala mano mano na lang, hehe
  • oo boss, pero natry ko na din yan pero gusto ko sa specific cell lang haha. dapat ata sinulat ko muna mga yan bago ako nag post xD
    yung specific cell na lang i highlight mo hehehe