Posts by dilshod_k

    Hi Dilshod_k,

    Please use below code.

    Hi SaurabhJaina211,

    I was wandering is it possible to further modify this code and insert R1C1 formula in it.

    I tried to modify it by inserting lines:

    ActiveSheet.Cells(2, colNo) = "=(RC[-1]-RC[colNo-2])/RC[colNo-2]"
    ActiveSheet.Cells(2, colNo).Columns(colNo).Autofill

    It did not give any error message but it does not make calculations either.

    I’m trying to calculate change of the value in the column previous to blanc column in comparison to value in the column “C”.

    If I would have to write it manually it would be as it follows:

    Range("E2").FormulaR1C1 = "=(RC[-1]-RC[-2])/RC[-2]"
    Range("E2").AutoFill Destination:=Range("E2:E100")
    Range("G2").FormulaR1C1 = "=(RC[-1]-RC[-4])/RC[-4]"
    Range("G2").AutoFill Destination:=Range("G2:G100")
    Range("I2").FormulaR1C1 = "=(RC[-1]-RC[-6])/RC[-6]"
    Range("I2").AutoFill Destination:=Range("I2:I100")

    But when it comes to dozens of columns it is going to be difficult and time consuming.

    I would be grateful for any help. Thanks in advance.

    Hi Dilshod_k,

    Please use below code.

    Hi SaurabhJaina211,

    It works perfectly well. Thanks a lot. Appreciate it. :thumbup:

    Hello everyone,

    I've found code to insert multiple blanc columns between columns with data, but I don't know how to modify code so that it would assign data format (percentage) and column header (Change%) for each inserted blanc column including blanc column after the last column with data. I would be grateful for any help. Thanks in advance.


    You could test the following

    Sub SortValues()
    Dim last As Long
        last = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
        Range("A1:G" & last).Sort [A1], xlAscending, Header:=xlYes
    End Sub

    Hope this will help

    Hi Carim,

    Thanks a lot! It works and it is less bulky and looks more elegant. Appreciate it.

    Hi everyone,

    I've recorded macros to sort values in the fixed size range and failed to adjust it to flexible/dynamic range.

    I tried to replace line: .SetRange ActiveSheet.Range("A1:G21")

    with line: .SetRange ActiveSheet.Range("A2", Range("A2").End(xlDown).End(xlToRight)).Select

    It did not work and gives Run-time error "13": Type mismatch.

    I would be grateful for any help. Thanks in advance.


    Glad to hear you could fix your problem :)

    Retrieval of name without extension was not a problem. Problems started when I've modified code to download and paste data, it was not downloading them, then it was pasting them in the wrong file e.t.c. As my debugging skills are at the fetal stage of development, even though most of the problems I encounter are piece of cake for professional programmer, for me they might be pain in the neck and quite time consuming.

    Thanks a lot. I'll try to do it manually as VBA Code Cleaner does not support 64bit applications.

    You could use a UDF like this

    Thanks a lot for suggestions. I've already modified code using option suggested by Carim. It took me (with my "extensive" experience in coding) the whole day to make it work properly. It will take another few days to figure out how to incorporate your routine. Thanks very much anyway.

    Here is the code I succeeded to make it work. I failed to make status bar with percentage update though. It is flickering and does not update.

    Hello everyone,

    I want to modify code which loops through multiple files in folder.

    First thing after opening each file, I want it's name without extension pasted to MasterFile, Sheet1, cell "S5", but I do not know how to do it.

    I would be grateful for any help. Thanks in advance.

    I cannot see any obvious reasons for the code to cause a memory loss/crash.

    A variant of the main routine which may speed things up a bit is below.

    I've made no changes to the GetFiles routine other than some formatting for readability.

    Hi gijsmo,

    thanks for attempt to help. I tried to run your version of the code through 100 and 760 files and the best time after 1st run was 37 and 415 sec respectively (old version of the code takes around 320 sec to run through 760 files during 1st run after opening workbook). Apart from that for some reason status bar would not show progress (first it would show TRUE, and after 3-4 sec would show that 100% processed and that figure would stay there until the end of routine run). I found that regardless of the code, if it has been run several times consecutively ends up with progressive slowdown of MS Excel performance (at least on my computer). Thanks for your time anyway.

    If you're only copying one row, why are you autofilling 1,500 rows with formulas?

    I've added block to copy rows recently to avoid running through all files twice, so that they are copied immediately after analysis of prices done, on the fly. You are right, without formulas it takes less time to run, but that makes sense only when data already analyzed, which means I would have to run through the files twice. Basically the aim is to make two versions of code, one is to download latest prices, analyze them and copy results of analysis to master file, and another one is to copy analyzed historical prices data in the past in order to back test profitability of trading algorithm.

    Did the code crash Excel?

    I have not tried yet to run it through all the files. I became interested why there is discrepancy in slowdown figures and found that delay depends on number of times code run. The fastest processing time is when the code (does not matter which one) is run for the first time after opening workbook.

    Here is the table with figures. 760 files in the folder. Files are the same during each run.


    Sorry dangelor, I have to apologize. 705 seconds I've got yesterday trying to process 7642 files, and this time was measured manually based on readings of the Status bar indicating that 20% of files processed. Today when I placed 1520 files in the folder separately, and the time to process was measured by the build in timer, it took 812 sec to process buy your code and 873 sec to process by the old one. So you code indeed works faster, but, unfortunately it does not prevent routine slowdown. Thanks for your help again.


    Thanks very much for attempt. Unfortunately it took 812 sec to process 1520 files, with old version of the code it takes 705 seconds. My understanding that you eliminated status bar code in order to improve speed, but the old version of the code had tendency for slowing down even before adding status bar code. Thanks anyway.

    Hello everyone,

    I have routine that loops through multiple files (around 8000 files) in a folder and checks value of only one cell in the Row2, and if it meets conditions then it copies that row (around 8 cells with data) to master file. Only 0.5% of files meet these criteria, so there is not much data being transferred. The problem is that speed of macros progressively decreases during runtime. If it takes around 5 min to process first 800 files, it takes 1.4 times longer to process second 800 files, and 3.4 times longer to process 6th 800 files. If processing time would not change all the files would be processed within 50 min, instead it takes more than 1 hour to process 60% of files. Somewhere between 50% and 70% workbook crashes without finishing routine. I would be grateful for any help to make routine stable, improve speed of this routine and to fix problem with memory. The code is as it follows below. Table with delay time and images with error messages attached to the post. Thanks in advance.