Posts by mar050703

    HI Carim


    Thankyou so very much for your patience and helpful guidance.


    It worked, but by changing the line

    Code
    1. Set wb = Workbooks.Open(Filename:=cmMyPath & file)


    to

    Code
    1. Set wb = Workbooks.Open(Filename:=file)

    ,


    as the first line now had the path twice (I guess because of fsopath and cmMyPath)?


    Thanks again for aiding my learning.

    Carim


    Thank you for your comment, I changed

    Code
    1. Dim cmMyFile as String


    to

    Code
    1. Dim cmyMyFile as variant


    that did not change the outcome, so maybe I misunderstood.


    Look forward to the explanation that will solve this for me.

    Thanks Carim


    I tested this, as you suggested in a module of its own, and first instead of opening workbook etc, I called a msgbox to give the file name. This worked, however...


    When I implemented the line


    Code
    1. Set wb = Workbooks.Open(Filename:=cmMyPath & cmMyFile)


    again it opens the 1st file (Cash Management FEBRUARY 2019 TEST) on each next file.


    Almost there, if you could suggest the issue I really would be grateful.


    Thanks

    Hi Carim, Yes I have done that. 1st thing I checked. However, I can now confirm by referencing fso like this:

    Code
    1. Dim fso As Object
    2. Dim fsopath As Scripting.Folder
    3. Set fso = New FileSystemObject
    4. Set fsopath = fso.GetFolder(cmMyPath)


    I now do not get any errors, but it keeps opening up the same file, it is not scrolling through to the next.


    so

    Code
    1. cmMyFile = Dir(cmMyPath & MyExt)


    appears to be always the 1st file in the sub folder Maureen. (for testing purposes there are 4 files, and only 2 are like "Cash Management"


    Do I need to reference fso.getfiles or something?


    Thanks

    Hi Carim Thanks for your comments, and no I don't mind, we are all learning, and as I am self taught in VB, even more so.


    Your 3rd point Cell I3 is looking at the folder location - it reads:

    Code
    1. =CONCATENATE("C:\Users\",I2,"\Dropbox\Accounts Miqlat\")


    I2 being the username.
    This line works, as I use it in other codes within this project.


    Your 2nd Point, I refer to MyFile = "Cash Management v1.4.xltx", is what I need to do on the xlsx file I also do within this xltx file - Please note that that the xltx file is in folder refered to in I3, and the xlsx files will be in a subfolder "Maureen).


    The xltx and the mentioned xlsx workbooks both have 2 sheets ("Expenses" and "Validate") the template become and xlsx workbook, when completed.


    As requested, please find the entire code.


    Please note that when I posted the original yesterday, I noticed I had forgotten to declare fso as an object.



    Any pointers and help would be greatly appreciated.

    Hi Experts


    I am trying to write some code so that it will loop through all the files in the folder and do certain things is the file name is like...


    My Code is



    I am sure I am missing something, but if someone could point the way, I would be grateful.

    Hi there


    I am trying to count the number of files in a particular folder whereby the filename is like something.


    When I have the total count, I need the code to loop through each file and do something


    I am having problems counting the files though.
    My Code looks like this:



    Please note there may be other files in said folder that do not contain "Cash Management"


    Maybe I don't need to count, I just need to loop through each file

    Hi KjBox.


    Thanks for the code.


    On sheet Validate1,I also have a ListObject, and is just the driving force for Columns 5 & 6 on the 16 sheets I refer to. It is essentially made up of:


    Header Row - Being the Categorys (Column 5)
    DataBodyRange - Being Sub Categorys (Column 6), so column 6 choice depends of the answer of Column 5.


    Wouldn't your code also the check ListObject(1) on the Validate1 Sheet?


    Hope that make sense?

    Hi There,


    Thanks graha_karya I have implemented your solution, and managed to sort the issue. I am sure there is a better/quicker (runtime) than this, so if anyone has a suggestion I would be happy to read.


    My final code is:


    Please note the second "For Each" statement is looking at the sheet and not the ListObject within that sheet (which was my ideal).

    Hi, and Thanks for your comments.


    Just to be clear, aside from the 3 sheets which are in the aforementioned Array, I have another 10 sheets each with an Excel Table (Listobject), where by I would need to locate combobox1.value within the 5th Column of that List object (ideally just within Databodyrange).


    Therefore I am unable to change the above code, as that would just look at column 5 of the original sheets in the Array.


    Apologies if I did not make myself clear.

    Thanks Carim,


    The final code was

    Code
    1. For Each Sht In Worksheets(Array("Centre", "Consolidated", "Validate1"))
    2. Sht.Cells.Replace what:=ComboBox1.Value, Replacement:=TextBox1.Value, _
    3. LookAt:=xlWhole, SearchOrder:=xlByColumns, MatchCase:=True, _
    4. SearchFormat:=False, ReplaceFormat:=False
    5. Next Sht


    the error was simply the sheet name was "Consolidated" and not "Consolidate"


    However, I now have a much larger problem, and looking for the quickest way for the code to work.


    I have 10 sheets, all with Excel Tables, and I would like the code to only check a certain column (always column 5) and do the same find replace. (Table per sheet)


    So how do I get it to loop through these 10 tables (please note within the workbook there are more than 10 tables.


    Thanks for your help

    Hi,


    I am trying to write code that will find something based on Combobox, and replace with the textbox value. I am trying to do this over an array of sheets.


    I am using the following code:

    Code
    1. For Each Sheet In Array("Centre", "Consolidate", "Validate1")
    2. Sheet.Cells.Replace what:=ComboBox1.Value, Replacement:=TextBox1.Value, _
    3. LookAt:=xlWhole, SearchOrder:=xlByColumns, MatchCase:=True, _
    4. SearchFormat:=False, ReplaceFormat:=False
    5. Next Sheet


    however I get the object required error on the

    Code
    1. sheet.cells.replace…

    line


    once I get this working I will also need to do the same on multiple excel tables (10 of them).


    Thanks for your help

    All Solved, I changed the code so it looked at a filesystemobject, therefore the new code is:


    Code
    1. If fso.fileexists(SvPath & FileN & ".pdf") = True Then
    2. 'If Dir(SvPath & FileN & ".pdf") <> "" Then
    3. Do
    4. n = n + 1
    5. NewFile = FileN & " (v" & n & ")" 'New File if Exists
    6. Loop Until Not fso.fileexists(SvPath & NewFile & ".pdf")

    Hi


    I am trying to get save as PDF and the actual file, and if it the file already exists, then give it a version 1 (or more).


    My code is as follows:


    However, the coding is happy to give a (v1), but then says file already exists and does not increase it to (v2).


    If I change

    Code
    1. loop until NewFile = ""

    it just keeps scrolling through the loop


    Any suggestions please?

    Carim


    Of course, I was being silly and used DataBodyRange before the ListColumns.


    Sorry, one last question, where I choose field 7 as the filter column, can I refer again to the Column Name ("End Date")


    Something like


    Code
    1. Sheets("Input").ListObjects(1).Range.AutoFilter field:=ListColumns("End Date"), Criteria1:=""

    Hi,


    I trust someone will be able to help me,


    I currently have a piece of code, which refers to an Excel Table, and I filter a column to give me a new range, but I am currently filtering using the column number;


    What I would like to do is use the refer to the column name, but I keep getting Run Time Error 13.


    The code I have is:

    Code
    1. Dim FRng As Range
    2. Sheets("Input").ListObjects(1).Range.AutoFilter field:=7, Criteria1:=""
    3. Set FRng = Sheets("Input").ListObjects(1).DataBodyRange.Columns(2).SpecialCells(xlCellTypeVisible)


    The error is the final line - I want to change columns(2) to Columns("Employee").


    I have also tried ListColumns("Employee"), but that gives Run Time Error 438


    The only reason for the change so that if columns are added to the left in the future, it will still use the correct column

    Hi There,


    I am sure there is a better, and faster way for my code to work. What I am trying to do, is loop through 4 different ranges, and clear contents.


    What I have at present is:


    Please can someone suggest a better way - I am sure it has to do looping through the ranges.


    Thanks in advance