>

User Login

TipidPC.com is the largest online IT Community in the Philippines. Have something to sell or share? Sign up for an account now. It's absolutely free!

Forum Topic

excel macro programming

  • thanks Sir Ram, noted yan :)
  • johnToT on September 30, 2014 11:51 PM

    Macro recording is cool. Here is a sample. It uses the font property such as name, size and others - truly an object oriented approach.
    Sub ChangeFontType()
    '
    ' ChangeFontType Macro
    '

    '
    Range("A1").Select
    With Selection.Font
    .Name = "Candara"
    .Size = 10
    .Strikethrough = False
    .Superscript = False
    .Subscript = False
    .OutlineFont = False
    .Shadow = False
    .Underline = xlUnderlineStyleNone
    .ThemeColor = xlThemeColorLight1
    .TintAndShade = 0
    .ThemeFont = xlThemeFontNone
    End With
    End Sub
  • indeed, dyan lang din ako kumukuha ng idea sa paggawa ng simple macro.

    HTML body in may email macro proble is i dont know the code that will call the value in cell, instead I used below code problem is not sure how can i incorporate the codes in changing the font size, color etc. by default its times new roman '12. No issue naman as long as gumagana xa :) nice to have lang ung makapagpalit ng font type size color.

    StrBody = "Hi " & cell.Offset(0, 5).Value & "<br><br>" & _
    Sheets("Email").Range("L4").Value & "<br><br>" & _
    Sheets("Email").Range("L6").Value & "<br><br>" & _
    Sheets("Email").Range("L8").Value & "<br><br><br>" & _
    Sheets("Email").Range("L11").Value & "<br>" & _
    Sheets("Email").Range("L12").Value & "<br><br><br><br><br>" & _
    Sheets("Email").Range("L16").Value & "<br><br><br><br>" & _
    Sheets("Email").Range("L27").Value & "<br>" & _
    Sheets("Email").Range("L28").Value & "<br>"
  • johnToT Send Message View User Items on October 01, 2014 09:48 PM

    I think you like to see on your email body a formatted message using excel macro, and not formatted value in a cell.
    All right here is an example, I use ms outlook 2007.
    ' Compose a message
    Strbody = "Hi " & Range("A1").Offset(0, 3).Value & "<br>" & _
    Sheets("sheet1").Range("E1").Value & "<br><br>"

    ' Format font
    StrbodyFormated = "<font face='calibri' size='3.0' color='green'>" & Strbody & "</font>"

    With OutMail
    .to = Range("A1").Value ' Cell A1 as reference cell used for offsets
    .Subject = Range("A1").Offset(0, 2).Value
    .cc = Range("A1").Offset(0, 1).Value
    .HTMLBody = StrbodyFormated
    .Display
    End With

    On your excel sheet at sheet1, row 1, from column A to E
    sendto-tpc@yahoo.com | sendcopy-me@gmail.com | My subject line | Here is my message!! | I am at E1

    Sample output message in green color.
    Hi Here is my message!!
    I am at E1

    Note: I am recalling my little knowledge in html and forced to setup my gmail account in ms outlook 2007 :))

    -- edited by ram2010 on Oct 02 2014, 07:01 AM
  • @ Ram

    Yes it should be a formatted message using excel macro. even if you format the text in cell such as color size etc it is useless since excel macro send it by default (im using ms outlook 2010) since i don't know how can i format the text via vb code - i also tried macro recording to get the code but still no success :'(

    surprisingly my background pala kayo sa html hehehe galing!

    Thanks again sir Ram, ill try it then :) so sana mapagana ko hehe.
  • Good day sir pa help po sa excel im trying to look for this formula i can't actually find it on google.

    I need a formula that works like a search toolbar, where in i would type a certain text on a particular cell and all entries with that "certain text" will be filtered/ will be displayed.

    Thanks sirs!
  • mikedc1204 on October 14, 2014 08:18 AM

    1. Open your workbook
    2. Press control+F
    3. Click options
    4. In the "within: textbox, select workbook. This is sheet by default.
    5. Under find what: type any text you want to search
    6. Press find all

    Excel will give you every cell in every sheets that it finds the text that you want to search, and will be
    presented to you in an extension window, where you will just have to click the cell links and that cell will be
    selected by excel for your further inspection.

    Of course I can make a macro with this. But I think this excel tool is already sufficient.
    If you want to process the text that you find for another tasks, then probably a macro is better.
  • Sub merry_christmas()

    MsgBox ("Merry christmas to everyone!!")

    End Sub
  • Happy holiday's
  • Hi sir ram..


    ako po ung dating ginawan nyo po ng template about sa payroll


    sir help sana po kung pano apply ung ginawa nyo dati

    gusto ko po sana magyari eh auto compute n sa pag ibig/sss/Tax/ pero di nila nakikita ung computation
    then pano po kaya ung payslip po na iprint namin.. ganyan kasi ginagawa namin eh connect po. pano pb ung mas maganda at best way na mabilis. sample sa isang short bond paper 4 payslip na pede dun na iprint namin.

    sana meron na rin xia data base dito sa isang excel sir pano pb un.. parang NEW..

    kasi ginagawa namn ang dami excel copy lagay kami ng date kung kaylan.. pede kaya isang excel lang kita na lahat ng data mapalumang payroll bsata masearch..


    help po sir..


    eto p ung excel file namn


    https://www.dropbox.com/s/02uih0l1rrtcu2o/Payroll.xlsx?dl=0
  • fmstatic14 Send Message View User Items on January 26, 2015 11:10 AM
    Hi sir ram..


    ako po ung dating ginawan nyo po ng template about sa payroll


    sir help sana po kung pano apply ung ginawa nyo dati

    gusto ko po sana magyari eh auto compute n sa pag ibig/sss/Tax/ pero di nila nakikita ung computation

    Doon sa uploaded file mo, may auto computation na sa promoter sheet. Alin paba ang e auto compute?
    Anong ibig mong sabihin sa "pero di nila nakikita ung computation"?

    then pano po kaya ung payslip po na iprint namin.. ganyan kasi ginagawa namin eh connect po. pano pb ung mas maganda at best way na mabilis. sample sa isang short bond paper 4 payslip na pede dun na iprint namin.

    Mayron ka nang payslip template doon sa pay stub sheet, at may database ka narin sa promoter sheet, pwede gawin dyan is se-select ka nalang ng 4 employees then gawan yan ng vba para mag create ng 4 na pay slip or stub then print. Pwede rin select ka ng gusto mong number of employees at e print yong mga pay slips sa mga selected employees.

    sana meron na rin xia data base dito sa isang excel sir pano pb un.. parang NEW..

    kasi ginagawa namn ang dami excel copy lagay kami ng date kung kaylan.. pede kaya isang excel lang kita na lahat ng data mapalumang payroll bsata masearch..

    Pwede isang excel sheet na lang yan, elilipat na lang pag napupuno na. Pero kailangan may baguhin sa formatting doon sa promoter sheet.
    Example yong date na JANUARY 10-15, 2015 sa itaas ay isasama sa table, meaning, may sariling column siya, ito dapat ang tamang pag gamit ng excel sheets.

    -- edited by ram2010 on Jan 27 2015, 05:35 PM
  • up for you master ram2010
  • johnToT Send Message View User Items on January 27, 2015 06:27 PM - USER IS ONLINE
    up for you master ram2010

    Thanks up din sa inyo.

    Hirap mag hanap trabaho dito sa pinas, hopefully this year makahanap na.
  • Sir Ram sa programming ba?

    not sure if my open ba dun sa dati kong company but i can check, name nung company is Maersk btw.
  • Not programming but I am also looking into that posibilities, kaya search ng search lang muna.
  • deleted

    -- edited by ram2010 on Jan 27 2015, 07:23 PM
  • oki Sir Ram hopefully makakuha na kayo ng work :)

    aja!
  • sir ram pede pb mgpatulong na syo sir.. pede pb ba gawan m na ako ng sample sir.?

    please..

    gusto k sna sir ram eh alam m ung gnwa mo sa akn last tym na excel payroll na nasa vba lahat ng code ganon sna po mgyari pati ung mga loan nla mkikita at auto bawas narn..

    sir ram pede pb ako gawan ng sample lang ako na p mg tutuloy study q n lng po. thank s
  • sir ram help po huhu
  • kung pno p di ko magets eh eheh nalito ako sa ginagawa q sa sinavi nyo sir ram
  • Hindi ito madali, payrol ito. Maraming detalye ang kailangan.
    Kung gusto mong mag praktis ng vba, wag itong payrol system.
  • ah gnon sir ram.. ngtry kasi ako ung sinasabi mghigligt k lng ng print m ehe d q malipat..ung detalye.. send q sir ram

    sir ram check nyo nga po.. plan ko pag highlight q s iprint kahit 4 payslip sa isang bondpaper.. eto upload q

    https://www.dropbox.com/s/wzqn05qho7ixcx9/PROMOTERS%20PAYROLL%202015%20%281%29.xlsm?dl=0
  • mga bossing, pa help naman po. pano po gagawin ko sa insert function? ung sa baba na result po ang gusto ko makuha. thanks a lot! (y)

    External Image
  • z3phyr2012 Send Message View User Items on March 30, 2015 10:56 AM - USER IS ONLINE
    mga bossing, pa help naman po. pano po gagawin ko sa insert function? ung sa baba na result po ang gusto ko makuha. thanks a lot! (y)

    Mahirap intindihin ito. Bakit kailangan pa dito ang function? Di ba pwede copy paste na lang and then edit yong Tag032 to Tag032LW?
    Can you explain more?

    -- edited by ram2010 on Mar 30 2015, 12:40 PM
  • ^ marami po kasi syang Tag. nasa TAg170 na. Ung parang formula na pag inenter ko, automatic lalabas ung first letter ng TYPE saka STATUS.

    thanks!
  • Try the formula shown after fx at the top. It uses concatenation & and LEFT() functions. Enter that formula in cell A5.
    Be careful when you copy that formula, you have to use absolute or perhaps relative references, depends on where you copy it.

    External Image


    -- edited by ram2010 on Mar 30 2015, 01:00 PM
  • Here is another option to make the copying more easier.
    Enter the formula once and then copy it downward. No worries for cell references.

    External Image

Who's Online

915 active users within the last minute, 494 members, 421 guests.
Our newest member is zion2014
Click here to see online members.

Browse Items

More »

Search TipidPC


New Want to Buys

Active Items for Sale

Active Want to Buys