Posts by Akhas

Just ignore the last message.

Its working perfectly well.

You are a great help and Genius.

Hi KjBox,

It is working perfectly but not showing the progress status message.

Quote from KjBox

I am attaching 2 files

The first ("FileConverter") will run through all your files in the folder and do the following:

1. Remove the button

2. Remove the Data Validation

3. Save the file as .xlsx

4. Completely delete the original .xlsm file

This will just be a one-off run but should save you a ton of time rather than making those 4 changes on 400 files manually!

The second is the Controller file I mentioned. Complete the required criteria in cells D5 to D9 (none can be left empty!) then click the button.

The code will loop through all the files in a folder and update them according to the entered criteria. It will take a bit of time to update all 400 files, so you can go for a coffee break and leave it churning away! There will be a message when the code completes telling you how many files out of the total file count have been updated.

I have tested various different criteria and all works except for "Dividends" & "Splits", but I notice that fails on the Sample file you sent.

I have left your existing code much as it was, just change the indentation, and modified the variables where necessary. I also added a bit to the error message box so that it shows the file name where the error occurred.

I suggest you copy 6 or so files to a new folder and run tests on those before running the code on all 400 files.

Display More

Hi KjBox,

The historicaldatacontroller.xlsm file is working correctly now.

Can you make a small change that it updates first 25 file then wait for 5 min then again updates the next 25 files and waits for 5 min and continues like this till all the files are updated.

Rest everything remains same.

Actually yahoo is not allowing to update more than 25 files at a go.

Quote from KjBox

I am attaching 2 files

The first ("FileConverter") will run through all your files in the folder and do the following:

1. Remove the button

2. Remove the Data Validation

3. Save the file as .xlsx

4. Completely delete the original .xlsm file

This will just be a one-off run but should save you a ton of time rather than making those 4 changes on 400 files manually!

The second is the Controller file I mentioned. Complete the required criteria in cells D5 to D9 (none can be left empty!) then click the button.

The code will loop through all the files in a folder and update them according to the entered criteria. It will take a bit of time to update all 400 files, so you can go for a coffee break and leave it churning away! There will be a message when the code completes telling you how many files out of the total file count have been updated.

I have tested various different criteria and all works except for "Dividends" & "Splits", but I notice that fails on the Sample file you sent.

I have left your existing code much as it was, just change the indentation, and modified the variables where necessary. I also added a bit to the error message box so that it shows the file name where the error occurred.

I suggest you copy 6 or so files to a new folder and run tests on those before running the code on all 400 files.

Display More

Hi KjBox,

Sorry for disturbing you again,

The files " Historical data controller" was working fine till 30April 2022, but I think Yahoo has made some changes and now the error shows "Emulate URL: unexpexted error"

Please correct it.

Great its working fine.

Thanks for your great Help

Hi,

I need to delete few sheets from multiple workbooks in given folder.

The sheets name are same in all the workbooks.

Please help

Quote from gijsmo

Can you please clarify.

When you say "5 cell data's" does this relate to the number of cells being scraped from each PreTerm sheet or do you mean the issue occurs after 5 workbooks?

If it relates to cells, the next step would be to try and work out if there is a particular cell on a particular sheet that may be causing the issue.

It is very hard to diagnose without actual data/workbooks but obviously you cannot upload these if they contain sensitive data.

Display More

5 Cell data relates to number of cells being Scraped

No particular cell or sheet causing the issue. Because when I reduce to 5 cells only it works fine.

It crashes sometimes immediately, sometimes after few minutes.

Quote from gijsmo

Without 200 or so test files, it's difficult to diagnose the issue.

Have you tried breaking the files up into groups of, say, 20-50 files and test on those ie, not all the files at once but eventually run through all files.

This might help determine if it's the number of files being processed or if there is one or more files that may be causing the issue.

Hi Gijsmo,

I have found the problem. Its number of cell data to scrape.

If I scrape only 5 cell data's then it works fine.

But If I need to scrape 6-10 Cell data then it collapses.

Any solution for this?

Quote from Mumps

Try:

Code

Sub FormatCells()
    Application.ScreenUpdating = False
    Dim wkbSource As Workbook, LastRow As Long
    Const strPath As String = "C:\Test\" 'change folder path to suit your needs
    ChDir strPath
    strExtension = Dir(strPath & "*.xlsx")
    Do While strExtension <> ""
        Set wkbSource = Workbooks.Open(strPath & strExtension)
        With Sheets(1)
            LastRow = .Range("A" & .Rows.Count).End(xlUp).Row
            .Range("F2:F" & LastRow).Select
            With Selection
                .Interior.ColorIndex = xlNone
                .FormatConditions.Add Type:=xlExpression, Formula1:="=F2=""SELL"""
                .FormatConditions(.FormatConditions.Count).SetFirstPriority
            End With
            With Selection.FormatConditions(1).Font
                .ThemeColor = xlThemeColorDark1
                .TintAndShade = 0
            End With
            With Selection.FormatConditions(1).Interior
                .PatternColorIndex = xlAutomatic
                .Color = 255
                .TintAndShade = 0
            End With
            With Selection
                .FormatConditions(1).StopIfTrue = False
                .FormatConditions.Add Type:=xlExpression, Formula1:="=F2=""BUY"""
                .FormatConditions(.FormatConditions.Count).SetFirstPriority
            End With
            With Selection.FormatConditions(1).Font
                .Color = -16776961
                .TintAndShade = 0
            End With
            With Selection.FormatConditions(1).Interior
                .PatternColorIndex = xlAutomatic
                .Color = 5287936
                .TintAndShade = 0
            End With
            Selection.FormatConditions(1).StopIfTrue = False
            wkbSource.Close True
        End With
        strExtension = Dir
    Loop
    Application.ScreenUpdating = True
End Sub

Display More

Perfect.

Job Done.

You are Great Mumps

Quote from Mumps

Place this macro in a separate workbook. The macro assumes that the sheet to be formatted will always be the first sheet in each workbook. Change the folder path (in red) to suit your needs.

Code

Sub FormatCells()
    Application.ScreenUpdating = False
    Dim wkbSource As Workbook, LastRow As Long
    Const strPath As String = "C:\Test\"
    ChDir strPath
    strExtension = Dir(strPath & "*.xlsx")
    Do While strExtension <> ""
        Set wkbSource = Workbooks.Open(strPath & strExtension)
        With Sheets(1)
            LastRow = .Range("A" & .Rows.Count).End(xlUp).Row
            .Range("F2:F" & LastRow).Select
            Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=F2=""SELL"""
            Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
            With Selection.FormatConditions(1).Font
                .ThemeColor = xlThemeColorDark1
                .TintAndShade = 0
            End With
            With Selection.FormatConditions(1).Interior
                .PatternColorIndex = xlAutomatic
                .Color = 255
                .TintAndShade = 0
            End With
            Selection.FormatConditions(1).StopIfTrue = False
            Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=F2=""BUY"""
            Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
            With Selection.FormatConditions(1).Font
                .Color = -16776961
                .TintAndShade = 0
            End With
            With Selection.FormatConditions(1).Interior
                .PatternColorIndex = xlAutomatic
                .Color = 5287936
                .TintAndShade = 0
            End With
            Selection.FormatConditions(1).StopIfTrue = False
            wkbSource.Close True
        End With
        strExtension = Dir
    Loop
    Application.ScreenUpdating = True
End Sub

Display More

Thanks Mumps,

Its working perfectly.

But only one thing. It is not able to remove previous formatting of cells filled in Green color.

Quote from Mumps

Manually remove the green colour in column F if it exists. Then select all the cells with data in column F and create two conditional formatting rules using the following two formulae:

=F2="BUY" ---- select Green for fill format and Red in font colour format

=F2="SELL" ---- select Red for fill format and White in font colour format

Thanks Mumps for your advise, but as I mentioned there are multiple files in the given folder and all the files require the same formatting.

I need a VBA code to format all the files at one go.

There are multiple files as sample file attached in a given folder.

In the following attached file ,Column F, following Conditional formatting required;

• Green Color filled to be removed,
• Cell with value BUY to be filled in Green and font color Red
• Cell with value SELL to be filled in Red and font color White
• The blank cell should have no color

Please

Quote from royUK

This should work

Code

Sub FindmatchesAll()
    
    Dim rCl As Range, rFnd As Range, rSrc As Range
    Dim FirstFnd As String
    
    Set rSrc = Workbooks("Nifty All.xlsx").Sheets(1).Range("A1").CurrentRegion
    
    For Each rCl In ThisWorkbook.Sheets("Sheet1").Range("A1").CurrentRegion.Columns(1).Cells
        With rSrc.Cells
            Set rFnd = rSrc.Find(rCl.Value)
            If Not rFnd Is Nothing Then
                FirstFnd = rFnd.Address
                Do
                    rFnd.Interior.Color = vbGreen
                    Set rFnd = .FindNext(rFnd)
                Loop While Not rFnd Is Nothing And rFnd.Address <> FirstFnd
            End If
        End With
    Next rCl
End Sub

Display More

It worked Perfectly as your magic works always.

Thanks a Lot.

Quote from royUK

Attach your workbook

Quote from Akhas

Please find attached

Where you able to work on it

Quote from royUK

Attach your workbook

Please find attached

Quote from royUK

Check this

Code

Sub Findmatches()
    Dim rCl As Range, rFnd As Range, rSrc As Range
    
    Dim FirstFnd As String
    Set rSrc = Workbooks("Nifty All.xlsx").Sheets(1).Range("A1").CurrentRegion
    For Each rCl In ThisWorkbook.Sheets("Sheet1").Range("A1").CurrentRegion.Columns(1).Cells
        Set rFnd = rSrc.Find(rCl.Value)
        If Not rFnd Is Nothing Then
            FirstFnd = rFnd.Address
            Do
                rFnd.Interior.Color = vbGreen
                Set rCl = rSrc.FindNext(rCl)
            Loop While Not rCl Is Nothing
        End If
    Next rCl
End Sub

Display More

• Hi Roy,

  Thanks for your response.

  It's not working.

  Just highlighting one cell and hangs up.

Hi Roy,

Thanks for your response.

It's not working.

Just highlighting one cell and hangs up.

Quote from royUK

Open both WorkBooks.

Click the button in Book1

Hi Roy,

This VBA highlights only one cell of particular name.

Actually there maybe more than 1 cells matching, so can it highlight all the matching cell of that particular name.

I have already created new thread for this.

Inserting Formula in multiple files - Excel VBA / Macros - OzGrid Free Excel/VBA Help Forum

Hi,

I need to insert two formula's, in Sheet GOSTOP

Following formula at cell C2 and then copied it down to cell C102.

=IF(AND(C3<0,C2>0),"GO"," ")

so that formula at C102 shows =IF(AND(C103<0,C102>0),"GO"," ")

And following formula at E2 and then copied it down to cell E102.

=IF(AND(E3>0,E2<0),"STOP"," ")

so that formula at E102 shows =IF(AND(E103>0,E102<0),"STOP"," ")

I need this to be done across multiple files in a given folder.

Any Help

Please