Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 167
  • Last Modified:

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 Patel
Naresh Patel
  • 3
  • 3
1 Solution
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?
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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

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


Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now