Forum Topic

excel macro programming

  • olayskie on 06 Oct 16 @ 12:02 AM #

    @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.

  • emcel on 17 Oct 16 @ 05:45 PM #

    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

  • emcel on 17 Oct 16 @ 06:00 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

  • dodick on 17 Oct 16 @ 06:48 PM #

    always trim a variable or cell before comparing to a blank "" value/string.
    try this...

    Do Until TRIM(Cells(lastrow, 1).Value) = ""

  • emcel on 17 Oct 16 @ 07:03 PM #

    Ok boss tnx... subukan ko yan

  • emcel on 18 Oct 16 @ 06:06 PM #

    At last nakuha ko rin... relational conditional formatting lang pala solution

  • seymorebutts on 26 Nov 16 @ 08:54 AM #

    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

  • bombermac on 28 Nov 16 @ 09:15 AM #

    @seymorebutts: Puwede na ata Pivot table diyan. Gawin mo na lang table range ang source.

  • jazzantriah on 28 Nov 16 @ 09:16 PM #

    @seymorebutts

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

  • olayskie on 05 Dec 16 @ 10:20 PM #

    @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))

  • Dyaems on 09 Dec 16 @ 01:17 PM #

    @ 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

  • emcel on 10 Dec 16 @ 01:54 PM #

    @ 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.

  • dodick on 11 Dec 16 @ 12:02 AM #

    @ 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

  • Dyaems on 19 Dec 16 @ 12:53 PM #

    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

  • emcel on 19 Dec 16 @ 03:02 PM #

    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

  • Dyaems on 19 Dec 16 @ 03:42 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

  • emcel on 19 Dec 16 @ 04:29 PM #

    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

  • Kircchoffs on 27 Jan 17 @ 07:18 PM #

    good evening mga sir.

    baka may makatulong po ulit sa problem ko.

    bale working activities monitoring sya ng different persons with diffrent task




    -- edited by Kircchoffs on Jan 27 2017, 07:22 PM

  • emcel on 29 Jan 17 @ 02:04 PM #

    Yung scroll bar... di ko pa nasubukan yan pero yang duration eh makukuha mo sa formula's (difference) at yung graph na may kulay eh sa conditional formating naman yan... subukan ko ipost ang conditional formula nyan mamaya.

  • Kircchoffs on 07 Feb 17 @ 01:39 PM #

    mga sir need your help again kung pwedeng formula para ma extract ko yung number na my *

    ex sa 82XB BEST BAT.295*275*162
    gusto lang ma y extract 295*275*162, kapag na extract ko pwede na syang kahit text to column

    82XB BEST BAT.295*275*162
    1XX BATTERY 520*280*124
    060(S) 595*330*345
    173R CENT 355*554*194
    1XX BATTERY 250*200*100
    1X3 744*584*174
    0X3 265*200*110
    265*150*80 O/P EMI
    1XXRT 497*319*225
    PX) 1122F IBM 962*595*322
    15X 322*133*283
    FIXOUT 71X9 OMR 105*105*35
    NV800W/220V/48V ARGUS375*140*100
    1XXRT HP 534*334*116
    112 962*595*322
    1073T PW 595*423*445
    9001 600*400*140
    1XX BATTERY 319*217*107
    0X3 CHL.625*525*250
    5A 575*375*548
    OUTSIDE)7129 OMR 571*471*329
    N612*417*438 (OUTER)

  • dodick on 07 Feb 17 @ 03:30 PM #

    assuming the format is constant: ###*###*###
    you can apply the below:
    =MID(A1,FIND("*",A1)-3,11)
    -3 = position start of extraction
    11= total chars count of, to be extracted

  • totnaker on 07 Feb 17 @ 03:52 PM #

    @all ano magandang excel books? :)

    thanks!

  • dodick on 07 Feb 17 @ 04:05 PM #

    ^depende ito sa level ng knowledge mo sa excel. either books or thru online, my advise is to learn thru interactive Help feature/function and apply it direct. meron naman itong search function pra specific... same applies to all software/application.

  • totnaker on 07 Feb 17 @ 04:47 PM #

    ^depende ito sa level ng knowledge mo sa excel. either books or thru online, my advise is to learn thru interactive Help feature/function and apply it direct. meron naman itong search function pra specific... same applies to all software/application.


    @dodick

    salamat boss

  • Kircchoffs on 07 Feb 17 @ 05:35 PM #

    assuming the format is constant: ###*###*###
    you can apply the below:
    =MID(A1,FIND("*",A1)-3,11)
    -3 = position start of extraction
    11= total chars count of, to be extracted


    @Sir dodick, working po sya. thanks! ^_^

  • Kircchoffs on 10 Feb 17 @ 09:42 AM #

    mga sir another questio po

    pano ko po ma extract yung content

    ex. lahat ng nagsisimula sa 110, yung kailangan ko lang ay 70030 at U0001
    110-70030-00
    110-U0001-00


    092-29220-00
    106-U0000-00
    106-U0001-00
    106-U0005-00
    108-21515-00
    110-70030-00
    110-U0001-00
    112-00401-00
    112-00433-00
    122-21010-00
    122-52106-00
    122-87020-01
    122-U0001-00
    518-10020-02
    520-05980-00
    610-14887-03
    610-52000-01
    610-65464-00
    610-U0022-00
    610-U0036-00
    610-U0039-01
    610-U0060-00
    610-U0072-00
    612-01322-03

  • basilacuzar on 10 Feb 17 @ 07:23 PM #

    @Kircchoffs

    I'll give your query a shot. Here you go -
    =IFERROR(IF(SEARCH("110*",A1,1)>0,MID(A1,FIND("-",A1,1)+1,5),"-"),"-")

    granting na constant na 5-digits lang yung lagi mong i-extract. If not, please advise para marevise natin yung formula to be dynamic. :)

  • olayskie on 11 Feb 17 @ 08:15 AM #

    @Kircchoffs

    The formula works kung yung prefix mo ay exactly 3 characters lang palagi at yung value na i-extract mo ay exactly 3 chars long lang.

    =IF(LEFT(A7,3)="110",MID(A7,5,5),"")

  • Kircchoffs on 11 Feb 17 @ 08:35 AM #

    I'll give your query a shot. Here you go -
    =IFERROR(IF(SEARCH("110*",A1,1)>0,MID(A1,FIND("-",A1,1)+1,5),"-"),"-")

    granting na constant na 5-digits lang yung lagi mong i-extract. If not, please advise para marevise natin yung formula to be dynamic. :)


    @basilacuzar

    thanks a lot sir, working po sya kaso need ko lang po ma filter starting with 110, about naman po sa extracted na 5 digit pwede rin pong di naka fix pa share na rin ng dynamic formula ^_^