Visit the Forums
Register Now!

User Login


Forum Topic

excel macro programming

  • Hello, is there a way para i match yung letter ng formula kung nasaang column ka?

    For example:

    Nasa Column A ako pero ang laman ng cell sa column ko is letter B (ex. Column A cell[=B+1]), then pag na-drag ko sya sa right(Column B), mag increment sya ng +1 sa letter (Column B cell[=C+1]).

    Gusto ko sanang mangyari eh kung ano nasaang column ako eh match din yung letter nya dun sa cell ko. (Column A cell[=A+1], Column B cell[=B+1])

    Please help.

    -- edited by jeff_3429 on May 08 2016, 10:56 AM
  • jeff_3429

    Not sure if ganto gusto mo.. if you want to use it in a formula calculation, you have to combine it with the INDIRECT function.
    May better way siguro, yan lang naisip ko on top of my head.

    =SUBSTITUTE(ADDRESS(ROW(),COLUMN(),4,1),ROW(),"")
  • How to reveal texts ng isang cell?

    Parang engraved na white color sya pag naka highlight yung cell. Paano i reveal yung laman nun?

    Thanks in advance.

    -- edited by jeff_3429 on Jun 11 2016, 01:39 AM
  • How to have to texts in the result of your formula? (For example, "P"(for Pesos sign) + the result of a formula of cells so the output of it would be P xx,xxx.xx)

    Thanks in advance ^_^.
  • ^Right Click>Format Cells>Number Tab>Customs>Type = P #,##0.00
  • ^Salamar sir @dodick ^_^.
  • ^
    Parang ang dami mong projects sa Excel. hehe.
  • ^Hindi naman gaano sir @gianpaolo31, hehe. Yung binibigay kasi saming computations eh kulang kulang. Mas maganda kasi pag kumpleto at wala nang problema template. mas maganda kung lahat ng options eh may template na ko. para bato na lang ng bato sa mga leads, hehe... Buti na lang may thread na ganito dito :)
  • mga sir baka po may maktulong saken, parang special type ng filtering ang kailangan




    http://i64.tinypic.com/1177lns.jpg

    -- edited by Kircchoffs on Aug 16 2016, 01:25 PM

    -- edited by Kircchoffs on Aug 16 2016, 01:26 PM

    -- edited by Kircchoffs on Aug 16 2016, 01:28 PM
  • Di masyado kita yung image ahaha.
  • ^ sir edited po ^_^
  • Anong klaseng filter kailangan nyo sir? Anong objective?
  • @Kircchoffs
    Try this formula:

    on E:2
    =IF(C2="x",B2,IF(E1<>FALSE,IF(LEFT(B1,8)=LEFT(B2,8),B2)))

    just take note, ngstart ako sa A ung syo sa B. Do the adjustment after getting the result ung E na ifilter mo... Good luck, hope this help.

    -- edited by dodick on Aug 16 2016, 03:12 PM
  • @Sir dodick
    ok na ok po yung formula nyo sir.thanks po.

    by the way may times din po na yung sub levels di katulad ng upper level, so yung fomula na IF(LEFT(B1,8)=LEFT(B2,8),B2 di pwede.

    @Sir gianpaolo31 ganito po ang exact na gusto kong mangyari


  • @Kircchoffs
    by the way may times din po na yung sub levels di katulad ng upper level, so yung fomula na IF(LEFT(B1,8)=LEFT(B2,8),B2 di pwede.

    if that's the case it should have at least a marker or else your first problem defeat it's purpose.
  • ^ sir dodick mukhang nakuha ko na sa ganitong formula,
    =IF(C2="x",B2,IF(E1<>FALSE,IF(A2=1,FALSE,B2))), salamat sa idea
  • @Kircchoffs
    U'r most welcome at syempre Thanks din sa TS nito...
  • mga bossing patulong..

    baka meron kayo code dyan sa excel.

    balak ko mangyari e meron ako table sa excel then i-synch ko sana sa isang Sharepoint list.

    Mas ok kasi lagyan ng form yugn excel para sa pag input ng details ng list sa Sharepoint. Ang di ko lang alam pag nainput na yung mga details sa excel, pano ngayon siya ibabato sa Sharepoint.

    Di nagana yung mga codes na nakita ko sa web like the below..

    Sub Add_Item(ListName As String, SharepointUrl As String, ValueVar As String, FieldNameVar As String)

    Dim objXMLHTTP As MSXML2.XMLHTTP

    Dim strListNameOrGuid As String
    Dim strBatchXml As String
    Dim strSoapBody As String

    Set objXMLHTTP = New MSXML2.XMLHTTP

    strListNameOrGuid = ListName

    'Add New Item'
    strBatchXml = "<Batch OnError='Continue'><Method ID='3' Cmd='New'><Field Name='ID'>New</Field><Field Name=" + FieldNameVar + ">" + ValueVar + "</Field></Method></Batch>"

    objXMLHTTP.Open "POST", SharepointUrl + "_vti_bin/Lists.asmx", False
    objXMLHTTP.setRequestHeader "Content-Type", "text/xml; charset=""UTF-8"""
    objXMLHTTP.setRequestHeader "SOAPAction", "http://schemas.microsoft.com/sharepoint/soap/UpdateListItems"

    strSoapBody = "<soap:Envelope xmlns:xsi='http://www.w3.org/2001/XMLSchema-instance' " _
    & "xmlns:xsd='http://www.w3.org/2001/XMLSchema' " _
    & "xmlns:soap='http://schemas.xmlsoap.org/soap/envelope/'><soap:Body><UpdateListItems " _
    & "xmlns='http://schemas.microsoft.com/sharepoint/soap/'><listName>" & strListNameOrGuid _
    & "</listName><updates>" & strBatchXml & "</updates></UpdateListItems></soap:Body></soap:Envelope>"

    objXMLHTTP.send strSoapBody

    If objXMLHTTP.Status = 200 Then
    ' Do something with response
    End If

    Set objXMLHTTP = Nothing

    End Sub
  • hi... patulong naman please... im a developer din (SAP-ABAP) pero very limited knowledge sa VB... i need some code sana for this scenario:

    (1 column)
    1
    2
    3
    4
    5
    6
    ...

    should be converted to 3 columns:
    1 2 3
    4 5 6
    ...


    it should continue doing this until ma process nya lahat ng records dun sa single column sa taas...

    thanks in advance
  • @tatlolagi

    sir try ninyo po itong code na nagawa ko. sensya na hehe..


    Sub Convert_to_3_Columns()
    lastrow = 1
    Do Until Cells(lastrow, 1).Value = ""
    lastrow = lastrow + 1
    Loop

    rownum = 1
    colnum = 2
    For counter = 1 To (lastrow - 1)
    Cells(rownum, colnum).Value = Cells(counter, 1).Value
    colnum = colnum + 1
    If colnum = 5 Then
    colnum = 2
    rownum = rownum + 1
    End If
    Next
    End Sub


    Bale dapat ang single-column data ninyo ay nasa Column A. Then pag ni-run ninyo na yung macro, pupunta na yung data from Column B to D base sa requirements ninyo po.
  • jazzantriah Send Message View User Items on 13 Sep 16 @ 09:15 PM #

    bossing salamat ng maraming marami! gumana sya!!!

    -- edited by tatlolagi on Sep 14 2016, 08:44 AM
  • follow up question...

    another scenario ko is kailangan yung last 3 characters ko is malipat sa next column:

    from:
    column1
    TIPIDPC123
    ABCDEF56

    to:
    column1 column2
    TIPIDPC 123
    ABCDE F56


    pwede paturo ulit ng formula?

    thanks in advance again.

    -- edited by tatlolagi on Sep 14 2016, 08:47 AM
  • @tatlolagi

    sir eto na po..


    Sub Move_Last_Three_Characters()
    lastrow = 1
    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
    End Sub


    So yung data ninyo po dapat nasa Column A, pag ni-run yung code, masplit yung info sa Column B and Column C (wherein yung column C yun yung last 3 characters).

    Feeling ko meron kang 3rd request kasi sa userid mo.. "TATLOlagi" hehehe..
  • jazzantriah Send Message View User Items on 14 Sep 16 @ 06:55 PM #
    @tatlolagi
    sir eto na po..

    lufeeeetttt!!!!! salamat bossing!!!

    at dahil nasabi mong may 3rd request ako... hehehe... last question... yung syntax na LOOP, asan ung marker ng start/end ng loop? sa programming naman kasi ganito:

    LOOP
    "data processing
    ENDLOOP

    so basically, san sa code mo yung include sa loob ng loop?

    salamats ng maraming marami ulit
  • Excel guru's need po ng help.
    Gusto ko lang sana manghingi ng formula based sa excel sample sa baba. New to excel kaya pinag aaralan ko ang mga formula's unti unti. Salamat sa makakatulong.

    Bale ang focus ko lang is yung output sa "Time" kung SLA Met ay magiging green, or kung BREACH ay red.




    Salamat sa mga Guru.
  • triptolemus Send Message View User Items on 16 Sep 16 @ 09:43 AM - User is online #
    Excel guru's need po ng help.

    advanced user at best ako bro ha... pero i think kaya yan gawin via conditional formatting option... dun kasi may option kung anong magiging color ng cells mo based sa cell values.

    hope this helps.
  • @tatlolagi

    yun na pong "DO LOOP" ang ginamit ko as looping function... pede naman ibang approach ng loop pero syempre nag-work na yan e bakit pa ako magchecheck ng iba.. hehe

    bale yung code eto na...


    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


    ayan po.. happy to help..

    sana may makatulong dun sa query ko naman hehehe
  • @triptolemus

    try ninyo po ito sir...

    punta lang kayo dun sa first "Breach" na cell under SLA Status column, click ninyo po yung Conditional Formatting. I'm using 2013 Office so nasa Home tab siya then Styles group.

    Piliin ninyo yung "New Rule" then "Use a formula to determine which cells to format".. nasa dulo siya ng panibagong window na lalalabs.

    Ipasok ninyo itong formula na ito.

    =IF($E$2>$C$2,TRUE, FALSE)

    ...then click FORMAT tapos select ninyo lang yung color RED as fill. Press OK twice para marecord yung rule ninyo.

    Ulitin ninyo ulet ang paggawa ng rule para naman sa "Green" color.

    NEW RULE --> Use a formula to determine....

    then eto na yung ipasok ninyo as formula..

    =IF($E$2<$C$2,TRUE, FALSE)

    .. click FORMAT then select ninyo GREEN as fill. Press OK twice again para marecord yun rule.

    Last conditional formatting is for "On going".

    Ulitin ulet yung paggawa ng rule...

    eto naman yung formula ang ilagay.

    =ISBLANK($E$2)

    .. select FORMAT and click YELLOW for fill. Press OK twice para maseyb yung rule.


    Ok, tapos na yung mismong conditional formatting, yun naman formula para sa mismong cell.

    select ninyo lang ulet yung cell D2 (which is the first cell under column SLA Status.

    lagay ninyo itong formula na to.

    =IF(E2="","On going",IF(C2>E2,"Met","Breach"))


    Ayan.. sensya na hindi ako makapaglagay ng mga picture.. bawal sa office hehehehe.. tingin ko naman malinaw yugn pagkaka-instruct ko. Pag may tanong, edi itanong na lang ulet . hehe
  • @ jazzantriah
    maraming maraming salamat bossing sa tulong mo
  • @tatlo

    wow.... bumalik ka talaga para magpasalamat

    hehe.. welcome po..