Page 1 of 4 123 ... LastLast
Results 1 to 15 of 55
  1. #1
    3 Star Lounger
    Join Date
    Nov 2011
    Location
    Australia
    Posts
    221
    Thanks
    80
    Thanked 3 Times in 2 Posts

    Populate next Column to Right if blank

    This is ongoing from previous thread
    http://windowssecrets.com/forums/sho...mula-with-TEXT

    I have provided a example workbook showing what the end process has to do with simulation.

    Firstly, Module2 can be disregarded, it simply shows basically what's going on, sort of like a web page's real time data process_ simulator. But it illustrates what does happen, 2- 3 updates within the same minute, or 5M TOGO.

    The problem is in Module1
    Sub LOGGING
    code is very wrong, it requires a complete overhaul.

    The problem's scenario is, there may be 2-3 updates within the same minute, the numericals A and B in Sheet 2 constantly change.
    When there is a change, then
    Sub TimerCalc2 activates. ( that section working OK. )

    I need some sort of coding that populates the values in from Sheet2 Column B3:C28 to the next available 2 columns on the right IF those 2 columns on the right are blank.

    The example show values up to row 11.
    That's Ok, it can be anywhere between Row 3 and 28

    Thanks
    Attached Files Attached Files

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    I am confused about what you want exactly and what is "wrong" with the code. Does it give errors, is the logic wrong, where is it wrong, etc...

    Could you provide a sample workbook of what you have at the start (before the "LOGGING" code is run) and what you want the results to be (using that example data) and walk us through (in words) what the code should be doing step by step.

    I also don't understand what your "simulation" is supposed to be representing. Could you elaborate?

    Steve

  3. #3
    3 Star Lounger
    Join Date
    Nov 2011
    Location
    Australia
    Posts
    221
    Thanks
    80
    Thanked 3 Times in 2 Posts
    Ok

    I knew this problem of what I'm trying to ask will confuse, that is why I made the simulator.

    The simulation is what will happen, each time there is an update or change in the A & B numbers from one chosen event. Let's say we are tracking price changes or trends per minute via web query that is Looping consistently.

    There can be 3 changes or 3 variations within 1 minute.

    For every change or variation
    Sub TimerCalc2() is activated,
    so to place those A and B numbers in 2 columns and a graph will progress. ( graphing is under construction)

    So at 20M is the first log, the web query Loops again and 15 seconds later the web query's data indicates another change, yet again it may not, depending on what the trends are in real time.

    Therefore
    the first set of 20M numbers will be wiped out because they go into the same column
    I need the new numbers A and B to go to the next column to the right.

    This coding I wrote is wrong as in method.
    It is static, 20M will always be in Range"D3:E27" and so forth.
    There are no errors in the code, it's just the wrong method to apply to this situation.
    This method is wrong, but I don't know how to code a
    Code:
    If is not blank, then next 2 columns to the right
    .
    for the new set of A and B data.

    This method causes more problems if I was to add more or less #M TOGO
    Code:
    Sub LOGGING()
    Sheets("Sheet1").Select 'OPTIONAL
    Sheets("Sheet1").Range("B3").Select
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    'not the correct method is here.
    If Sheets("Sheet1").Range("B3").Value = "20M TOGO" Then
       Sheets("Sheet1").Range("D3:E27").Value = Sheets("Sheet1").Range("B3:C27").Value
      End If
    '''''''''''''''''''''''''''''''''''''''''''''''''' ''''''

    If you look further down the simulators coding at;

    '
    Sheets("Sheet2").Range("C1").Value = "5M TOGO"
    TimerCalc2
    '
    Application.Wait (Now + TimeValue("0:00:02")) 'FOR TESTING
    '
    Sheets("Sheet2").Range("C1").Value = "5M TOGO"
    TimerCalc2
    Notice I made it to Run "5M TOGO" twice. This is not a error.

    It's to show precisely the very point of error.
    In real time trend updates,
    2 sets of different A and B numbers will go into the same column twice, wiping out the first set.

    The purpose of placing the A and B numbers in columns next to each other progressively is to eventually view a graph showing the rise or fall or trends in percent from -20 minutes before the start of an event to -0 minutes.

    Hope this helps

    Thanks

  4. #4
    3 Star Lounger
    Join Date
    Nov 2011
    Location
    Australia
    Posts
    221
    Thanks
    80
    Thanked 3 Times in 2 Posts
    On Sheet2 Range C1, change to
    0M TOGO
    then activate the simulator, this will show more of the process.

  5. #5
    3 Star Lounger
    Join Date
    Nov 2011
    Location
    Australia
    Posts
    221
    Thanks
    80
    Thanked 3 Times in 2 Posts
    Fixed for now,

    I used a "marker" with Find so it goes to the next required Columns on Right.

    A bit clunky, but not sure how else to code it.
    Attached Files Attached Files

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Still not exactly sure what you want. Here is a complete guess, try it on a copy and see if this is what you are after. If not try to elaborate on the problem. Each time this runs it copys the timer column to A, the "A" list to the next avail column and the "B" column to the column after that. It labels the "A" and "B" that are added and puts in the value from C1.

    Code:
    Option Explicit
    Sub NewLogging()
      Dim wSource As Worksheet
      Dim wDest As Worksheet
      Dim iColTimer As Integer
      Dim iColSourceA As Integer
      Dim iColSourceB As Integer
      Dim rCell As Range
      Dim lStartRow As Long
      Dim lLastRow As Long
      Dim iColDestA As Integer
      
      'Set as desired
      Set wSource = Worksheets("Sheet2")
      Set wDest = Worksheets("Sheet1")
      lStartRow = 4
      iColTimer = 2 'Column B
      iColSourceA = 6 'Column F
      iColSourceB = 11 'Column K
      Set rCell = wSource.Range("C1")
      
      iColDestA = wDest.Cells(lStartRow, wDest.Columns.Count).End(xlToLeft).Column + 1
    
      With wDest
        'add labels
        .Cells(lStartRow - 2, iColDestA) = "A"
        .Cells(lStartRow - 2, iColDestA + 1) = "B"
        .Cells(lStartRow - 1, iColDestA) = rCell.Value
      End With
      With wSource
        lLastRow = .Cells(.Rows.Count, iColSourceA).End(xlUp).Row
        'Copy Timer Info
        .Range(.Cells(lStartRow, iColTimer), .Cells(lLastRow, iColTimer)).Copy _
          wDest.Cells(lStartRow, 1)
        'Copy A
        .Range(.Cells(lStartRow, iColSourceA), .Cells(lLastRow, iColSourceA)).Copy _
          wDest.Cells(lStartRow, iColDestA)
        'copy B
        .Range(.Cells(lStartRow, iColSourceB), .Cells(lLastRow, iColSourceB)).Copy _
          wDest.Cells(lStartRow, iColDestA + 1)
      End With
    End Sub
    Steve

  7. #7
    3 Star Lounger
    Join Date
    Nov 2011
    Location
    Australia
    Posts
    221
    Thanks
    80
    Thanked 3 Times in 2 Posts
    Thanks Steve,
    your code works perfectly.

    I implemented it in the simulator section, works fine.
    A bit new for me to understand that type of coding, but I'm sure it makes sense.

    XP

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    If you need an explanation of any the sections let me know (I recommend stepping through the code to see what it is doing)

    Steve

  9. #9
    3 Star Lounger
    Join Date
    Nov 2011
    Location
    Australia
    Posts
    221
    Thanks
    80
    Thanked 3 Times in 2 Posts
    It's not the actual coding, it becomes obvious after the fact!
    It's the "mechanics" behind the coding.
    For example, in my younger years, car had distributors and points, with a cam that opened and closed the points to make the spark. One can "see" it.
    Today it's all non movable parts enclosed in black boxes working the same in principle, via another method.

    When a macro is recorded to do the basics then tweak it, it's the same in principle.
    The Dim's and i's and all that type to me is like that "black box"..

    I m aware these codings are for some sort of memory pre-stuff
    Option Explicit
    Code:
    Sub NewLogging()
      Dim wSource As Worksheet
      Dim wDest As Worksheet
      Dim iColTimer As Integer
      Dim iColSourceA As Integer
      Dim iColSourceB As Integer
      Dim rCell As Range
      Dim lStartRow As Long
      Dim lLastRow As Long
      Dim iColDestA As Integer

    Tutorials and "books for dummies" sort of explain some, but the illustrations assume everyone works in finance,banks and has a degree in accounting.
    If There were graphical line by line of what's happening behind the actual coding, "behind the scenes" so to speak, and in simple layman's, probably written by a layperson who once drove something as bold as a tractor.
    That's what my coding is like, a home made tractor !

  10. #10
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    The DIMS are only for Defining the variables that are being used (DIM is short for DIMensions). Defining the variables is not required, but it aids in many ways: it makes the code more readable and can help ensure that you don't have any typos. I use the practice of having the first letter be based on the variable type and the name is descriptive (to aid in readability of the code).

    The "graphical" nature (flowcharting) is still done, but it is not line-by-line. The code I do is small enough that I don't flowchart it explicitly, but more envision the mechanics in my mind.

    I have put some comments in the code to help understand, and as I mentioned, I will answer any specific questions. In general terms the code
    Dimensions variables
    Defines particular variables (sheets, cols, rows that will be used)
    Calcs the next col on the dest sheet
    On the dest sheet, puts A & B label and the value from C1
    Then determines the last used row in Source "A"
    Then copies the timer data to the first column
    A to the next avail column
    B to next avail column

    Steve

  11. #11
    3 Star Lounger
    Join Date
    Nov 2011
    Location
    Australia
    Posts
    221
    Thanks
    80
    Thanked 3 Times in 2 Posts
    Steve,
    If I was to change anything, it be to add to A and B data.
    Firstly,
    The calculation are 2 types, and then secondly there is a graph, all of this during the same progression.

    The first calculation/s is called "Overall Variance". It means from the first A and B data column,
    ( Columns B an C) to the latest #M TOGO data.
    It's a percent of how much the Value has Decreased. So if the first was A 5.00 and the latest input became 2.50, then it will show -50% in red next to it.

    So if at 20M TOGO was showing 5.00 and at the end of the progression to 1M TOGO it became 2.50,
    the formula would be between the first and the last. Hence, progressively "Overall Variance".
    There is no need to graph that one.

    The other progressive formula is at every update, same formula as the "Overall Variance", but it's for each new update.
    So if it started at 20M then 2nd update at 17M and then15M, the Variance would be between the 17M and 15M.
    The next update if at 13M, then the Variance will be between 15M and 13M, and so on.
    It's this progression that gets graphed.
    -----,

    The graph's structure, the 3 boldest graph lines that go UP, are the one's where the Values have Descended the most in percent.
    So 5.50 ~ 2.50 is -50%, then the line would be going UP on the graph
    If the next "lowest" is -45%, that red line on the graph would be going up to, same with the 3rd.
    All others going up or down are blue lines.
    Background is Black.

    2 more questions
    1.It may take me a few days,
    should I get this DOS program fired up on another PC I have with DOSbox installed, manually type columns of numbers same as, do a screen capture to post here to show what the graph looks like ?
    The graph section of that DOS program, the graph's graphics don't show up on XP platform.

    2. Once the graphing is done, it's basically the end of the project,
    so I think I ought remain on this same thread?
    Because I'd be using the same demo-workbook.

  12. #12
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    With all you have written, I am not exactly sure what you are asking (I don't see any questions whatsoever before you mention "2 more questions"). If you are asking for help in this beginning part of your post, I think you need to reword and elaborate...

    My take from it, is that you want some help with the charting. I see no problem with remaining on this thread. I would suggest that you post an example completed file (after you macro has some number of datapoints) since the example has all the same data for all the periods (so we can see changes in the charting).

    You can post an example chart that is a "picture" (created in powerpoint of other graphic program) to give us a sense of what it should look like and what the axes are (based on the example data). If the dataset is not directly connected you may want to elaborate on what the X and Y data is explicitly so we understand.

    Steve

  13. #13
    3 Star Lounger
    Join Date
    Nov 2011
    Location
    Australia
    Posts
    221
    Thanks
    80
    Thanked 3 Times in 2 Posts
    I made some print-screen using the other PC with DOSBox.

    Each screen shot is progressive to show a blank view of the program's columns,
    the first point of numerical data entry, ( manually typed in )

    Each PAINT screen shot has a comment to explain what is going on. It shows the calculations, but I am not sure what the formula is, I know it's Percents.

    I think the graphics "speak" better than my explanations !

    Hope the graphics are clear enough.

    Thanks
    Attached Files Attached Files

  14. #14
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    I don't follow what you are posting or what exactly you are asking for help with.

    Please attach a file with sample excel data of what you start with and what you want with at the end if you want help with formulas or data manipulation. [The data does not have to be real, but it should be representative of what you will get]

    If you want help creating a chart, please attach a sheet with the data you have that you want to chart and some representation of what the chart for that data should look like. The chart can be a screen shot, but you may have to explain what the X and Y values are in the data (especially if the chart data is not tabulated directly into the X/Y datasets

    Steve

  15. #15
    3 Star Lounger
    Join Date
    Nov 2011
    Location
    Australia
    Posts
    221
    Thanks
    80
    Thanked 3 Times in 2 Posts
    Please attach a file with sample excel data of what you start with and what you want with at the end if you want help with formulas or data manipulation. [The data does not have to be real, but it should be representative of what you will get]
    Thanks,
    I need to get the Formulas sorted first.

    In cell's value, N3 and O3 under the heading "DOS CALC" is (79.47) AND 35.00
    This is what is showing in the DOS program/calculator's calculations.

    In Cells N4 and O4 I have used Formula as best as I know.
    But am not getting the exact same Decimal points as the DOS calculator.

    Once the formulas are sorted I'll compare/improvise/test with the DOS calculator.

    XP
    Attached Files Attached Files

Page 1 of 4 123 ... LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •