Posting 1

Posted on 2014-07-17
Last Modified: 2014-07-18
Hi Experts,

Need your help with piece of code ...please help me out on this.
In attached EE Demo.xlsm WB there is three sheet - Data - Recorder - LogFile.
I need to code perform following procedures in sheet Recorder when in sheet Data - Column L <> "" for each row.
In Sheet Recorder
Column A - Date of Occurrence
Column B - Time of occurrence
Column C - Sheet Data - Column E - Copy & Past Special Value To - Sheet Recorder - Column  C with Format 0.00(Two Decimal Numeric)
Column E - Sheet Data  - Column J - Copy & Past Special Value To - Sheet Recorder - Column E With Format
%(percentage With No Decimals)
Column F - Sheet Data  - Column K - Copy & Past Special Value To - Sheet Recorder - Column F With Format
%(percentage With No Decimals)
Column G - Sheet Data  - Column AA - Copy & Past Special Value To - Sheet Recorder - Column G With Format 0.00(Two Decimal Numeric)
Column I - Sheet Data  - Column A - Copy & Past Special Value To - Sheet Recorder - Column I
Column J - Sheet Data  - Column B - Copy & Past Special Value To - Sheet Recorder - Column J
Column K - Sheet Data  - Column C - Copy & Past Special Value To - Sheet Recorder - Column K
Column L - Sheet Data  - Column D - Copy & Past Special Value To - Sheet Recorder - Column L

This is the same as my previous Question which is solved by MR. Glenn Ray

See Attached

Question by:itjockey
    LVL 76

    Expert Comment

    If this is the same  as a previous and solved question, why not apply the same solution?
    LVL 8

    Author Comment

    It is not like that ......same I.e. Posting from one sheet to other but way of doing it is different.

    LVL 76

    Expert Comment

    So what is the exact difficulty that you are experiencing?
    LVL 8

    Author Comment

    Here We Go....
    Option Explicit
    Sub Copy_Trigger_Data()
        Dim rng As Range
        Dim c As Object
        Dim intLR, r As Integer
        Dim dblLTP As Double
        Dim dblCR1 As Double
        Dim dblCR2 As Double
        Dim dblCircuit As Double
        Dim dtStamp As Date
        Dim tiStamp As Date
        Dim strTicker, strName, strCode, strSegment As String
        Application.ScreenUpdating = False
        'Start on main worksheet and select all cells in column L
        intLR = Range("B" & Cells.Rows.Count).End(xlUp).Row 'assuming that data will always exist here
        Range("L2:L" & intLR).Select
        Set rng = Selection
        For Each c In rng
            If c.Value <> "" Then 'trigger
                'assign key values
                dtStamp = Now()
                tiStamp = Now()
                dblCR1 = c.Offset(0, -2).Value
                dblCR2 = c.Offset(0, -1).Value
                dblCircuit = c.Offset(0, 15).Value
                strTicker = c.Offset(0, -11).Value
                strName = c.Offset(0, -10).Value
                strCode = c.Offset(0, -9).Value
                strSegment = c.Offset(0, -8).Value
                dblLTP = c.Offset(0, -7).Value
                Range("A" & Cells.Rows.Count).End(xlUp).Offset(1, 0).Select
                'insert values
                ActiveCell.Value = Format(dtStamp, "d/mmm/yy")
                ActiveCell.Offset(0, 1).Value = Format(tiStamp, "hh:mm")
                ActiveCell.Offset(0, 2).Value = Format(dblLTP, "0.00")
                ActiveCell.Offset(0, 4).Value = Format(dblCR1, "Percent")
                ActiveCell.Offset(0, 5).Value = Format(dblCR2, "Percent")
                ActiveCell.Offset(0, 6).Value = Format(dblCircuit, "0.00")
                ActiveCell.Offset(0, 8).Value = strTicker
                ActiveCell.Offset(0, 9).Value = strName
                ActiveCell.Offset(0, 10).Value = strCode
                ActiveCell.Offset(0, 11).Value = strSegment
            End If
        Next c
        Application.ScreenUpdating = True

    Open in new window

    Problem with Formatting's
    There is 2 Columns - Date & Time (OK)
    There is 4 Columns For Numeric values - out of 2 are numeric which I want in 0.00 format instead of #,#00.00
    & 2 are Percentage format which I want in percentage without decimal instead of 0.00%

    LVL 76

    Accepted Solution

    You can format a column  as percentage with no decimal places, so this will format column D
     Columns("D:D").NumberFormat = "0%"

    Open in new window

    You have mentioned the format string for numeric without the thousands separator in your last comment, but here it is in a code statement that will format columns E and F
     Columns("E:F").NumberFormat = "0.00"

    Open in new window

    LVL 8

    Author Closing Comment

    1) May I ask follow up question...?(Surly new question)
    2) Code looks perfect?


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
    Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
    The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now