Posts by dilshod_k

Important Notice


Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

    That seems strange why not have all processes in one workbook?


    It will slow the macro down because it will have to check each sheet to see if it exists in the other workbook

    By default there is only one sheet in the file BackTest.xlsm, "Sheet1", so frankly speaking module of the code checking existense of the sheet with the same name is redundant. Upon processing all the sheets are converted to files with the same names and are deleted from BackTest file. I already thougt of adding this macro to the same workbook, but it is not practical in my case as it will significantly complicate macro. The sample data I've sent to you is small, in reality it might be dosens if not hundreds of thousands rows of data, which can not be processed at once by the file BackTest.xlsm as it progressively becomes slower with incresed wolume of data to be processed, so I have to process data in approximately 2000 rows chunks (depending where ends the set of data with the same date). BackTest file downloads 30 days historical price data for each stock symbol, given the fact that there maybe hundreds of stocks in each sheet, it involves lots of data to be downloaded, analysed, copied to another sheet and saved in the form of separate file for each date.

    I'm not sure what you mean about the workbook. Do you mean that you need to new sheets to go to C:\Test\BackTest.xlsm?


    Thanks for help with date format.

    Yes, all these data was meant to be sorted by date and copied to the newly created sheets in another workbook namely BackTest.xlsm as it will be processed there by another macro.

    Thanks in advance.

    Hello Roy,


    Thank you very much for your help. Macro works perfectly well, and thanks a lot for explicit comments!

    Couple of small requests: would you be able to change code so that it creates new sheets in the book C:\Test\BackTest.xlsm, and to change name from "long date" format to short one yyyy.mm.dd? I tried to change "long date" to "short date" format, but it did not work. I can't incorporate this macro within BackTest.xlsm file for technical reasons, as I need it to be in the separate file. Thanks again. Highly appreciate your help.

    Dilshod

    Hello everyone.

    I’ve got data with multiple rows sorted in descending order based on date in the column B.

    I’ve been using macros that copies all rows with the same date to the sheet added to workbook BackTest.xslm and gives name to the sheet equal to date. For example, if I have 5 rows with date 2021.3.17, 3 rows 2021.03.10, and 12 rows with date 2021.02.16, the macros will add 3 sheets to the file BackTest.xslm with names 2021.3.17, 2021.03.10, and 2021.02.16 with rows of data copied to these sheets correspondingly. Today, for some reason this macro has stopped working properly, it creates multiple new sheets, often with dates that are not in the list, and these sheets are empty, it does not copy any data onto new sheets. I’m sure that there were no any changes made to the code, as I had several copies of the file and all of them are not working. I can’t rule out possibility that it has stopped working due to changes in Excel Options, although I could not figure out which one. I've tried to reinstall Microsoft Office with no success. As I do not exactly understand how this macro works, I can’t make amendments to it to make it work. I would be grateful for any help to restore or to modify this macro to make it work properly. Please find file with macro and sample data as an attachment to this post.

    Thanks in advance.

    Dilshod

    Well, I can still say it's not perfect. If column AY has data the macro won't work as required. This is how it should be (final :P) :

    Range("BB" & x) = Cells(x, 52).End(xlToLeft).Value

    By the way, I completely missed this statement: "Aim is to return value of the last cell of each row " in your post #1:sleeping:.


    I'm glad I've been of some help:thumbup:.

    Thanks very much rollis13.

    I've already realized that 51/52 is an empty column number from which to start countdown to the left, depending of the length of the rows. I understand that should the length of the rows increase I would have to move data from column BB to the right.

    Thanks again for your help.

    Dilshod

    Hello everyone,


    I have sub which I do not know how to modify in order to make it loop through files in subfolders.

    Code
    "C:\VBA\VolCh\"

    It normally loops through multiple files in a folder, finds all rows with text value "BUY" in the column M and copies that row to MasterSheet. Would be grateful for any help. Thanks in advance.

    Dilshod


    Code
    Option Explicit
    Sub LastCellInRow()
        Dim lr     As Long                            'last row
        Dim x      As Long                            'counter
        lr = Range("A" & Rows.Count).End(xlUp).Row
        For x = 3 To lr
            Range("BB" & x) = Cells(x, 51).End(xlToLeft).Column
        Next x
    End Sub

    This one looks like it should work right, but if you'll try to run it in the workbook attached it gives wrong values. For example, when I run it in my file AO21 value is 13.88% but in BB21 value is 41. etc.

    Sorry ...


    But just tested rollis13' s macro ... and it is working fine :thumbup:

    In my table column AY is blanc, and the line lr = Range("AY" & Rows.Count).End(xlUp).Row counts rows (if I understand right) based on a blanc column. When I tried to change AY to E (the column with contiguous data), it copies values to the column BB but wrong values, they are not the last ones in the rows and I do not know from where they are. In the previous post I've sent screenshot of the table with results in BB.

    Thanks.

    Dilshod

    Carim,

    Sorry for delay with answer. I'm on call and had to respond to call.

    I tried to run your code and it seems that it does not make any changes to the table. I tried to add .Value to the code line#6 with no success. The aim is to add VALUE OF THE LAST CELL of the each row (ideally every other row, but every row will do as well) starting from Row3 to the corresponding cell of the column BB.

    Thanks in advance.

    Dilshod

    Hello Carim,


    Thanks for your response. What I wanted is to find the value of the last cell of each row starting from Row3 down the sheet and paste it to corresponding cell in the column BB. All the rows have different length. I found how to do that for a single row, but I don't know how to do that for multiple rows.

    Thanks again.

    Dilshod

    Supposing that the other data is in the rows under AY3 and column BB and after are clear, try with these changes to your macro:

    Hi rollis13,


    Thanks for your help. Most of the rows contain data starting from columns A to AX. Contiguous data in every other column starting from column E. The code above unfortunately gives error message. Thanks again.

    Dilshod

    Hello everyone,

    I have multiple rows with data. Aim is to return value of the last cell of each row (data is not contiguous) to corresponding cell in the column BB. I found the way how to get it done with one row, but I do not know how to modify this macros for multiple rows in a range. I would be grateful for any help.

    Thanks in advance.

    Dilshod


    Code
    Sub LastCellInRow()
    
    Range("BB3").Value = Range("AY3").End(xlToLeft).Value
    
    End Sub