Posts by dilshod_k

It works great! Thanks a lot, appreciate your help.

Quote from royUK

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.

Quote from royUK

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

Quote from rollis13

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 ) :

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.

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

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.

"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

Sub DatesWithBuyOrder()
'PURPOSE: Determine how many seconds it took for code to completely run
'SOURCE: www.TheSpreadsheetGuru.com/the-code-vault

Dim StartTime As Double
Dim SecondsElapsed As Double

'Remember time when macro starts
  StartTime = Timer

    Dim myDir As String, fn As String, cn As Object, rs As Object
    myDir = "C:\VBA\VolCh\To be Processed 1\"
        If myDir = "" Then Exit Sub
    With Sheets("mastersheet")
        .Cells.ClearContents
        .[a1:n1] = Array("Symbol", "Date", "Open", "High", "Low", "Close", _
        "Adj. Close", "Volume", "V/SMA10", "Vol/Change%", "SMA10", "SMA30", "Buy/Sell", "Close/Change%")
    End With
    Set cn = CreateObject("ADODB.Connection")
    Set rs = CreateObject("ADODB.Recordset")
    fn = Dir(myDir & "\*.csv")
    Do While fn <> ""
        With cn
            .Provider = "Microsoft.Ace.OLEDB.12.0"
            .Properties("Extended Properties") = "Text;HDR=Yes;FMT=Delimited;"
            .Open myDir
        End With
        rs.Open "Select * From [" & fn & "]  Where UCase(`BUY/SELL VolChange`) = 'BUY'", cn, 3, 1, 1
        Sheets("mastersheet").Range("a" & Rows.Count).End(xlUp)(2).CopyFromRecordset rs
        rs.Close: cn.Close
        fn = Dir
    Loop
    Set cn = Nothing: Set rs = Nothing
    'Determine how many seconds code took to run
  SecondsElapsed = Round(Timer - StartTime, 2)

'Notify user in seconds
'  MsgBox "This code 1 ran successfully in " & SecondsElapsed & " seconds", vbInformation
  
  ThisWorkbook.Worksheets("Sheet1").Range("X3").Value = "code 1 done in " & SecondsElapsed & " seconds"
  ActiveSheet.Columns.AutoFit
  ActiveWorkbook.Save
'  Windows("PriceAn.xlsm").Close
 
Call TestPlayWavFile

    
End Sub

Rollis13 and Carim,

Thank you very much for your help! I would certainly not be able to solve it on my own.

Highly appreciate it guys!

Dilshod

Solved. I've changed line 7 to Range("BB" & x) = Cells(x, 51).End(xlToLeft).Value

Quote from rollis13

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.

Quote from rollis13

Now that you showed the layout of your data this line of code should be: lr = Range("A" & Rows.Count).End(xlUp).Row or something like that.

I already tried. It gives value 54 in each cell of the column BB.

Quote from Carim

Obviously ... you are facing a problem of how to adapt to your specific file ...

Why don't you attach a sample file (without sensitive data ....) BUT with the actual structure ...

There is no classified data. Please fin an attachment.

Quote from Carim

Sorry ...

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

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

Quote from Carim

Just read your comment to rollis13 ...

Have a go with following

Hope this will help

Display More

I've changed AX to E in line for of your code, it now copies values to the column BB but wrong values, and I can't figure out how to make it work properly.

Quote from rollis13

Sorry, my mistake, there is a typo; should be: lr = Range("AY" & Rows.Count).End(xlUp).Row

Hi rollis13,

Thanks, now it does not give any error, but it does not copy values to the column BB as well.

Dilshod

Quote from Carim

Just read your comment to rollis13 ...

Have a go with following

Hope this will help

Display More

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

Quote from Carim

Hello,

You could test following UDF to get the Last Column Number for any given row ...

Hope this will help

Display More

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

Quote from rollis13

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

Sub LastCellInRow()

Range("BB3").Value = Range("AY3").End(xlToLeft).Value

End Sub

Quote from royUK

The code should be saving as the worksheet's name

Hello royUK,

The code is working perfectly well. I would not be able to solve it on my own. Advantage is that I do not have to rename sheets any more to save files. Thanks for explicit comments in the code. Highly appreciate it.

Dilshod