Posting 1

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

Naresh PatelTraderAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

If this is the same  as a previous and solved question, why not apply the same solution?
Naresh PatelTraderAuthor Commented:
It is not like that ......same I.e. Posting from one sheet to other but way of doing it is different.

So what is the exact difficulty that you are experiencing?
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Naresh PatelTraderAuthor Commented:
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%

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


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Naresh PatelTraderAuthor Commented:
1) May I ask follow up question...?(Surly new question)
2) Code looks perfect?

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.