Posts by SiaoLang

    Hello there! The error you are facing is most probably due to VBA unable to convert your text string into a double formatted variable using Cdbl function. Perhaps using the DateValue will help you achieve your goal! :)

    Try using the below code.... i only made slight modifications to your original code :)

    MODERATOR NOTE: please don't quote whole posts.

    Hello there Tino,

    Heres the code i have managed to come up with. Its a little long since there were quite a few requirements you needed:

    To summarise your requirements
    1) Open up all the workbooks originating from your workbook's folder and do the following:
    2) Run through all cells in each worksheet and delete all #N/A cells
    3) Run through each worksheet again and look for empty rows AND columns and delete them where-ever they occur
    4) Re-save the workbook back to the location which the workbook originated (Here: i resaved the file under a different name, i.e., if the workbook is "workbook1", it is resaved as "workbook1-cleaned", in case you need to refer to the original file...)

    For the requirement (3), my code in red below is a little bit lengthy; im not sure if anyone else in the forum might have a more concised solution, but it still works at least when i tried on some dummy data.

    Try the below and let us know :)

    Run the macro in a workbook that is saved in a folder where the other workbooks you need to clean up are in :)

    Hello, does this code work?

    Assuming that your "standard form from another workbook" has the worksheet name "Blank Diagram". (if not, you can change the name in red below)

    Sub Get_Diagram()


    Ahh that is very smart :)

    I feel i am learning alot quickly from you guys in this forum :eureka:

    Hello there,

    Can i just ask:
    1) When the check box is ticked, what should cell D3 give? Are you expecting the code to insert a value into cell D3 which overwrites the formula
    "D3 formula : =IF(D3<>"",IF(AND(F3<>"",CELL("address")=ADDRESS(ROW(D3),COLUMN(D3))),NOW(),IF(CELL("address")<>ADDRESS(ROW(D3),COLUMN(D3)),F3,NOW())),"")"

    2) Similarly, for cell F3, the output for cell D3 will not provide a "break" string, so the F3 cell will always be blank?

    Maybe it will be helpful if you provide a test file for us to understand what you are trying to acheive :)?

    Hello there, good day. Based on your explanation, try this :)

    Question: It seems weird, but is your source data always copied from cell B3:G20 in the GroupTrackerMaster file regardless of the destination workbook or worksheet?

    Hello there!

    Good day! My guess is that because you have 2 Find methods already used above, so when FindNext is used in the Do Loop, it kindda confuses which FindNext parameters to used?

    Interestingly, when i moved the 2 set variables ItemRange1 and ItemRange2 around, then your code will work normally. Thus, I guess the FindNext method only references to the last-used Find method?

    Good day,

    Try this. Simply highlight any range of cells you want to evaluate and run the code :)

    Basically, as long as your cell contains the work "yes", it will match and highlight the row

    Hello Steve,

    When you meant copying data over, do you mean copying the entire row of data into the Table in Risk&Hazard Register worksheet, or just a single data (cell)?

    If it is the former, i have attached a sample; see if it meets your needs or not.

    Simply input any number of rows of data from Column A to N (as you have advised in your first post), and click the copy data to copy into the new worksheet table :)


    Hello there, for this part of the code

    Set klant = .Range("A:P").Find((Range("D6")), after:=Sheets("tarieflijst").Range("A2"), SearchOrder:=xlByRows, searchdirection:=xlPrevious)

    Is there a reason you set the parameters as "SearchOrder:=xlByRows, SearchDirection:=xlPrevious"? It seems weird you are running the find by rows method from row 2 but in reverse order... perhaps you can use this instead?

    Set klant = .Range("A:P").Find(what:=(Range("D6"), after:=Sheets("tarieflijst").Range("A2"), SearchOrder:=xlByRows, searchdirection:=xlNext)