Announcement

Collapse
No announcement yet.

Copy calculated data ranges in Sheet2 O:O to Sheet1 offset column block areas in C:C

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Copy calculated data ranges in Sheet2 O:O to Sheet1 offset column block areas in C:C



    I apologize for being such a Noob but that's what I am and I need to do some advanced things with VBA and Excel. I look to this community as the helping experts and appreciate any help I get. Thanks for reading my post.


    This seems like it would be an easy thing but I am struggling to get the desired results. I have searched the internet for days to solve this without finding the solution. I really need some help with this. But I have made some progress.


    In Sheet2, I have a simple data stream of values coming in from a connection to a continually updating value table. Once the values have come they do not normally change.

    In Sheet2 column "O:O" contains results from math performed on the incoming data table. This is simulated in the linked sheet. The basic result is a number with "0.000" format with the exception of two occasional text values. "Stop" and "Station". These text strings can be anywhere in O:O and are always the same text.

    Each time a new value shows up in the table, the resulting values from the first calculation row in Column "O:O" down to and including the first text value need to be copied to a block range in Sheet1 starting at cell C21, named "Run_1_Start".

    Once that has been done the next and subsequent calculated numeric values need to be copied to a new block range starting at cell C35, Named "Run_2_Start", until the second instance of text value. The process repeats with the each data sample copied to Next block range starting Cell.
    Occasionally a block of data will spill over into the next so a function of looking at the next Sheet1 start cell in line to determine if it contains data then go to the next start cell would be beneficial. That can be handled later after the main goal is accomplished.

    I can get and post the cell address of each text value in Sheet2 P:P adjacent to its O:O cell with the first code. Might be able to help define a range.
    With the second code I can reliably paste the data without duplicates where it needs to go. I have manually assigned worksheet named ranges to the calculated data in Sheet2 which needs to be done automatically in VBA if it is to be used.

    I have also manually assigned named cells at the start of each Sheet1 block to be pasted to which can remain static. When using a Named Range in Sheet1 i.e "Block1", caused the anomaly of if the copied data from Sheet2 was 7 cells, or half of the Sheet1 named range of 14 cells it would paste the same data twice, filling up the named range in Sheet1 with duplicates.

    If I could use VBA to name the range in Sheet2, "Run_1" from the start, O2, to each new data result each time it comes in and is calculated to a value in O:O down to the occurrence of a Text value, Pasting the results to a named cell in Sheet1 "Run_1_Start" cell. Have the first Named range in Sheet2 remain from O2, to including the first text string, then start naming the next numeric value/s in Sheet2 O:O "Run_2" pasting that to down to and including the next occurrence of a Text value to Sheet1 "Run_2_Start" cell. This will eventually span a total of around 20 data blocks in Sheet1 so needs to be scalable

    The addresses in Sheet2 P:P and all pasted data in Sheet1 C21:C can be erased to watch the macros I have work in the linked sheet bellow. Any Help would be greatly appreciated.

    Thanks In Advance.

    DataBlockCopyPasteExample.xlsm

    https://drive.google.com/file/d/1ndF...ew?usp=sharing


    Get and place in P:P the Cell Address of Stop or Station in O:O

    Code:
    Sub GetCellAddy()
        'Find all instances of "Stop" and "Station" in Sheet2 Column O:O then paste the cell name in adjacent cell in ColumnP:P
        'Stop Updating, Alerts, Events and Calcs for speed and fewer VBA Crashes
        Application.ScreenUpdating = False
        Application.DisplayAlerts = False
        Application.EnableEvents = False
        Application.Calculation = xlCalculationManual
        Worksheets(2).Select
    row_count = 220  ' the number of rows in your range
    For r = 1 To row_count
        If Cells(r, 15) = "Stop" Then
            Cells(r, 16) = Cells(r, 15).Address
        End If
    Next r
    For r = 1 To row_count
        If Cells(r, 15) = "Station" Then
            Cells(r, 16) = Cells(r, 15).Address
        End If
    Next r
        'Restart Updating, Alerts, Events and Calcs after code completes
        Application.ScreenUpdating = True
        Application.DisplayAlerts = True
        Application.EnableEvents = True
        Application.Calculation = xlCalculationAutomatic
    End Sub
    Copy and Paste data appropriately but its dependent on manually named ranges and cells

    Code:
    Sub PasteNamedRange()
    'This could work if I can write code to Name the range fron start to first "Stop" then fron the cell below first "Stop" to next "Stop" etc.
    
    'Stop Updating, Alerts, Events and Calcs for speed and fewer VBA Crashes.
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual
    
          ' Run the Error handler "ErrHandler" when an error occurs.
          On Error GoTo Errhandler
    
    Worksheets(2).Range("Run_1").Copy
    Worksheets(1).Range("Run_1_Start").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False 'Problem: when a range that is half of the Named Block size I paste to i.e.. Block1 on Sheet1, it pastes it twice.
    Application.CutCopyMode = False
    Worksheets(2).Range("Run_2").Copy
    Worksheets(1).Range("Run_2_Start").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        Worksheets(2).Range("Run_3").Copy
    Worksheets(1).Range("Run_3_Start").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Worksheets(2).Range("Run_4").Copy
    Worksheets(1).Range("Run_4_Start").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Worksheets(2).Range("Run_5").Copy
    Worksheets(1).Range("Run_5_Start").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Worksheets(2).Range("Run_6").Copy
    Worksheets(1).Range("Run_6_Start").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
       :=False, Transpose:=False
        
    'Put more copy lines here if I learn code to name ranges in Sheet2 based on the above search that works well
    
        'Restart Updating, Alerts, Events and Calcs after code completes.
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    Application.EnableEvents = True
    Application.Calculation = xlCalculationAutomatic
    
    Worksheets(2).Select
    Range("A1").Select
    Application.CutCopyMode = False
    Worksheets(1).Select
    Range("A1").Select
    Application.CutCopyMode = False
    
    Errhandler:
        Worksheets(2).Select
        Range("A1").Select
        Application.CutCopyMode = False
        Worksheets(1).Select
        Range("A1").Select
        Application.CutCopyMode = False
            'Restart Updating, Alerts, Events and Calcs after code Error.
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    Application.EnableEvents = True
    Application.Calculation = xlCalculationAutomatic
            Exit Sub
            
                    'Restart Updating, Alerts, Events and Calcs after code Error.
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    Application.EnableEvents = True
    Application.Calculation = xlCalculationAutomatic
    
    End Sub
    Attached Files
    Last edited by errolw98; 1 week ago.

  • #2
    Hello,

    First of all ... Congratulations for all your efforts ... !!!

    There are several tiny comments to be made ...

    Let's start by an example to simplify your code to list your Addresses ...

    Code:
    ' Determine the number of rows in your Column O
        row_count = Sheet2.Cells(Application.Rows.Count, "O").End(xlUp).Row
        For r = 1 To row_count
            If Sheet2.Cells(r, 15) = "Stop" Or Sheet2.Cells(r, 15) = "Station" Then
                Sheet2.Cells(r, 16) = Sheet2.Cells(r, 15).Address
            End If
        Next r
    Hope this will help
    If you feel like saying "Thank You" for the help received ...You can click on the "Like" icon ...just underneath ... ... in the bottom right corner ...

    Comment


    • #3
      Carim,
      Thanks for that. This is a much more compact way to accomplish the task of identifying the cell address of the text entries. Great piece of work combining the search parameters. I know my application = False and application = True statements cloud up the code a bit.
      The bigger problem for me is selecting the text cell and all the numeric cells above, repeating this with the second text entry excluding the first text entry and putting them both in the correct block on Sheet1.
      I found these two bits of code that on my example workbook can copy and move the first block including the text within the same Sheet2 but I haven't yet discovered how to change the destination to Sheet1. I can easily play with the offset or range but nothing seems to work to change destination sheet. Everything I try gives an error so far. Maybe I will be able to combine disciplines. these both do the same thing in different ways but neither loop or search for all text values.

      But seriously, Thank you for even looking at the problem I'm facing. You are the first response in 4 days of searching.

      Code:
      'Can't yet figure how to paste the results to a diifferent sheet
          Dim sht1 As Worksheet, r As Range
          Set sht1 = Sheets(1)
          Dim sht2 As Worksheet
          Set sht2 = Sheets(2)
      
          With sht2
              Set r = .Range(.Range("O2"), .Columns("O:O").Find("Stop", LookIn:=xlValues))
          End With
          r.Offset(0, 1) = r.Value
      Code:
      'Can't yet figure how to paste the results to a diifferent sheet
      Dim sht1 As Worksheet
      Dim sht2 As Worksheet, r As Range
      
      Set sht1 = Sheets(1)
      Set sht2 = Sheets(2)
      Set r = sht2.Columns("O:O").Find("Stop", LookIn:=xlValues)
      
      If Not r Is Nothing Then
          'should add sheet references here too
          With Range("O1").Resize(r.Row)
              Range("P1").Resize(r.Row).Value = .Value
      
          End With
      End If

      Comment


      • #4
        Hello again,

        In order to make your life easier ... and avoid the manual process of creating all your Named ranges ...

        Attached is your test File Version 2 ...

        Hope this will help
        Attached Files
        If you feel like saying "Thank You" for the help received ...You can click on the "Like" icon ...just underneath ... ... in the bottom right corner ...

        Comment


        • #5
          Carim,
          I am not sure what kind of wizardry this is but it's brilliant. You indeed have made my life easier. I was close to shelving this for a while and redesign a new workbook that would run linear. This option with different blocks is far better. Works like a charm with all data that has a text value at the end of each block. I added more data to O:O and extended the Q:Q formula and had exactly the function I was hoping for. You fully ROCK and saved this project format for me.
          This is so close to 100% for me now. I have code to erase the max known range names in Sheet2 based on number of blocks available in Sheet1 giving me the ability to make this a template for a new event. I can add an error handler to that code so if a "Run_X" range hasn't been created that macro won't hang.
          There are only two things left to sort out.

          1. Need the ability to dynamically paste each following numeric value from Sheet2 into the appropriate "Run_X_Start" cell on Sheet1 and below as they happen or at the click of a Button. (i.e.) If "Run_3" already exists and new Sheet2 O:O data is filling the soon to be "Run_4", These values need to be able to begin filling from "Run_4_Start" down until the next text value at which time they can be pasted over with the "Run_4" range data as it will be the same.
          2. Need the ability to check if any "Run_X" block data has overflowed and pasted data to next "Run_Y_Start" cell, and if so paste the "Run_Y" range to the next "Run_Z_Start" cell. This is a rarer occurrence but can happen at times. Last priority for me. One way to handle this would be to expand the block size on Sheet1 so it is less likely.

          You are my new VBA hero.

          Comment


          • #6
            Very glad to hear you are heading in the right direction ... !!!

            As soon as I have a moment ... I will take a look at your last two items ...
            If you feel like saying "Thank You" for the help received ...You can click on the "Like" icon ...just underneath ... ... in the bottom right corner ...

            Comment


            • #7
              Hello,

              Attached is your test file Version 3 ...

              Hopefully, this will be in line with your objectives ...
              Attached Files
              If you feel like saying "Thank You" for the help received ...You can click on the "Like" icon ...just underneath ... ... in the bottom right corner ...

              Comment


              • #8
                Carim,
                You are indeed an OzMVP. I did not do my job well and didn't properly list the objectives on Sheet1 as I did in the OP or Post #5. I will try to clear that up as well.
                I think I either broke the V3 or it doesn't work as hoped. I tried eliminating the 3rd Stop in O25, efectively making Run_3 = 17 cells to O28 to test the overrun data function and then hit the Process CMD Button. The "Run_3" data pasted into "Run_4_Start" cell as expected with one oddity. The data skipped the 14th cell in Run 3 block and the 14th - 17th pasted into the Run_4_Start block to get all 17 cells. This is not a big deal so that part is functional.

                The bad news is that the "Run_4" data did not paste into "Run_5_Start" cell as desired, or anywhere else I can determine. Also after extending the Q formula and adding some new data to O:O, there was no copy of that to any location either.

                I am playing with the number and scope of the Add Named Ranges feature. I found Multiple "Run_X" ranges in both Worksheet and Workbook scope. Maybe that is causing the trouble. I think these should be worksheet scoped and I think on my change of data that existed, all the previous named ranges should be deleted before adding new ones. I'll start by deleting all unnecessary, unused named cells and named ranges and macros. Then I'll add a call to a macro to delete all named ranges in Sheet2 at the beginning of the Add_NameRanges macro to see if that helps. I have a copy of V3 untouched to refer back to as well. I am playing with the potential of adding a Dynamic Named Range in VBA under the last named range in O:O as well. I can do this manually and that brings in the any new data but I'll study on creating it in VBA after the last Run_X range is added.Maybe an addition to your Q formula or a search for lastrow in Column Q and maybe an Offset to Column O and 1 row down to define Firstrow of the dynamic range.
                I'm having trouble deciphering the CMD Button too. Noob thing. I'm used to Form Controls and Objects to attach macros to but not ActiveX Controls so much.
                Thanks again for your help, I'm trying to learn some stuff. I'll keep you posted on any updates and new versions.

                Comment


                • #9
                  Carim,
                  Still working on the execution of this project. I have a way to create a Named Range for new data that works. Problem is the starting point is manual so I'm looking for code that would find the end of the last range or last q row value and start the named range row at the first numeric value after the last range. Maybe even a formula as you did to create the named ranges in order. Anyway I have tried several things with no joy thus far. It is so close that I can almost see it working.
                  Thanks Again for getting me this close. V4 Attached
                  Attached Files

                  Comment


                  • #10
                    Hello,

                    Sorry but you are confusing me ...

                    1. Could you clarify ' the starting point ' ... which one ... where ... ?

                    2. What is the final result expected ... ?

                    3. Do you actually need all these Named ranges ... ? if yes, what for ...?
                    If you feel like saying "Thank You" for the help received ...You can click on the "Like" icon ...just underneath ... ... in the bottom right corner ...

                    Comment


                    • #11
                      Hello again,

                      In the attached Version 5 ... you do need to worry about the Destination Named ranges ...

                      Hope this will help
                      Attached Files
                      If you feel like saying "Thank You" for the help received ...You can click on the "Like" icon ...just underneath ... ... in the bottom right corner ...

                      Comment


                      • #12
                        Carim,
                        Sorry to confuse you. I think it is clear that I am not a VBA or Excel expert by any stretch of the imagination. I hope to clear this up for you by answering your questions in Post #10 first.
                        1. In this Scheme the "starting point" or cell in O:O for the "New_Data" Dynamic Range has to be able to find the end of the last Sheet2 named range, or start at O2 in the case of the start of the data stream session when there would be no named ranges yet, and in "Sub createNew_Data" change the "myFirstRow = X" line from my manual static input row of 38 now, to the cell after the last "Stop" or "Station" text occurred, wherever that may be in the process of Column O:O being filled with data results.
                        2. The final result being after the creation of any Sheet2 "Run_X" range would be the start point of the Dynamic Named Range of "New_Data" so it can be copied to the next open Sheet1 Run_X_Start cell, whichever that may be in Sheet1 C row. Then when Sheet2- "Run_Y" is created the "myFirstRow = X" value automatically starts after the last O:O text value. Also the "Sub GetNewData" has to have a dynamic ability to select the next open Sheet1, "Run_X_Start" cell as opposed to being directed to "Run_7_Start" manually as it is now.

                        3. Honestly I don't know if I need any named ranges or cells in Sheet1 or Sheet2. It is just the only way I could see to do it. As you know with VBA in Excel, there are many ways to make something work. The only thing I am certain of is the project goals.

                        Situation and end goal;
                        Lets say the Sheet2 column O:O could go down 200 rows, more or less is possible, filling each next row cell at the rate of one every 90.xxx seconds with a numeric value or the randomly placed text values as they occur in real time. Each time new results fill the next cell down O:O, it needs to be able to be placed in order in the correct "Run" block on Sheet1 rather than being all in one line as it comes from the stream. The run blocks on Sheet1 are currently 14 rows but this could be modified if needed. How, in VBA or Excel formulas or any combination of those, that end goal is accomplished makes no difference. I'm sure there is a better way that is cleaner and faster, I just don't know what it is.
                        I hope that clears up where I may have been confusing you. Enjoy your day.
                        Thanks for all your help so far on this.

                        Comment


                        • #13

                          Thanks for all your explanations ...

                          Once you have tested the Version 5 ... posted in message # 11 ...

                          Let me have your comments ...
                          If you feel like saying "Thank You" for the help received ...You can click on the "Like" icon ...just underneath ... ... in the bottom right corner ...

                          Comment


                          • #14
                            Carim,
                            Sorry for the delay. Sadly V5 is fraught with some failures for me.
                            1. If you just change one of the Stops to a value making the run longer than 14 rows, while that range seems to be created properly it is skipped in the copy paste routine.
                            2. If you just start over with O:O data and make the 2nd run longer than 14 but less than 28 rows then hitting the Process button at each stop, the range is created but the long Run_2 doesn't paste.to blocks 2 and 3 as desired and is skipped.
                            3. regarding New_Data, as it should any New_Data still pastes to Run_7_Start because that's where it is pointed and the myFirstRow value had to be changed manually to match, again as expected.
                            I have been drawn off this for a fair while today. I did have the opportunity to try a different strategy altogether.this evening that works great with one big drawback. it worked like magic in my test workbook but failed in my Real World workbook. You will appreciate how lightweight and simple the really good the macro works. There are no named ranges or cells and it is super fast. it has one big problem to overcome that I have tried many things on to solve. It is documented on both Sheet1 and Sheet2. I have attached it for you as the code may help you out in the future. It is in failure mode right now but if you remove Sheet2, Cell O48 formula it is pretty amazing.
                            Thanks again. I owe you a beer, coffee, drink or ????
                            Attached Files

                            Comment


                            • #15


                              Hello,

                              Attached is your latest Version 6 ... which includes all the corrections Version 5 deserves ...

                              Hope this will help



                              P.S. Sorry have not analysed your latest version ( which you also numbered V6 ..)
                              For consistency purposes, really think we should remain coherent ...
                              Attached Files
                              If you feel like saying "Thank You" for the help received ...You can click on the "Like" icon ...just underneath ... ... in the bottom right corner ...

                              Comment

                              Working...
                              X