Posts by aisietie

  • Difference in Months between dates

    Hello,
    I want to calculate the difference in months between dates.
    In the table what I want as result.
    Start End Result
    10/02/2014 14/09/2014 7
    11/02/2014 14/09/2014 7
    12/02/2014 14/09/2014 7
    13/02/2014 14/09/2014 7
    14/02/2014 14/09/2014 7
    15/02/2014 14/09/2014 6
    16/02/2014 14/09/2014 6
    17/02/2014 14/09/2014 6
    18/02/2014 14/09/2014 6
    19/02/2014 14/09/2014 6
    10/02/2014 14/09/2015 18
    20/02/2014 14/09/2015 17
    Can this be done with a formula?
    thx aisietie

  • Go to cell if cell in corresponding column contains X, else wait

    Re: Go to cell if cell in corresponding column contains X, else wait


    Hello,


    The logic that I need is:
    If column C contains "X" and time in column A is <= now, than next row
    if column C contains nothing and time in column A is <= now, than wait
    if column C contains "X" and time in column A is > now, than wait
    if column C contains nothing and time in column A > now, than wait.


    Thanks in advance,


    aisietie

  • Work with 3 users on 3 different PC's in the same excelfile, see changes real time

    Re: Work with 3 users on 3 different PC's in the same excelfile, see changes real tim


    So the idea sending a textfile to a Workstation with the value and range of the cell that is updated, and an application.ontime that does a check if a file is recieved, import the textfile and places the info in the cell, is not feasible.


    Problem is that there are other macro's in the sheet, and google docs does not support vba macro's.


    aisietie

  • Work with 3 users on 3 different PC's in the same excelfile, see changes real time

    Hello,


    When a excel file is shared, multi users on different PC's can work in the same excelfile.
    Problem is that there is no possibility to see the changes in real time.
    Situation:
    UserA changes only values in column F, user B changes only values in Column G, userC changes only values in Column H
    The file is on a central share.
    Because sharing the file does not do what I want, I am looking for an alternatif.


    Method:
    When userA changes a value in a cell in column F, the change must be "immediate" seen by Users B and C.
    So I am looking for a method that when a cell is changed, a package is send to the other computers - Users with te file open.
    I was thinking on a ontime event that controls let us say every 5 seconds if a textfile is recieved, and when the package is recieved, the text file is read, and the cell is updated.
    Is there somebody with experience about sending a textfile to workstations - users, with the info wich cell is changed, and what the new value is.
    And then add the change in the cell?
    Or is there an other workarround?


    Thanks


    aisietie

  • Hide Row if corresponding cell in column C is not empty

    Hello,


    I want to hide the row when value "x" is entered in column C


    I tried this code in the sheet, but nothing happens.......


    Code
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column = 3 Then
        ThisRow = Target.Row
        If Not Target.Value = "" Then
ThisRow.EntireRow.Hidden = True
End If
End If
End Sub


    So when a "x" is entered in cell (115,C), row 115 must be hidden.
    When a "x" is entered in cell((9,C) row 9 must be hidden to,
    and so on.
    When a cell in an other column is changed, the macro does not need to start.


    Thanks for the help.


    aisietie

  • vba Find duplicates in range

    Hello,


    How can i find, with vba, duplicates in a range of cells.
    So when I have in Range("A1:A6")
    A1 = 1
    A2 = 2
    A3 = 3
    A4 = 1
    A5 = 5
    A6 = 3
    by clicking a commandbutton, a messagebox pops up with "1 & 3 are used more than once"


    Thanks in advance


    aisietie

  • Make a range with address cell

    Hello,


    With the code

    Code
    Dim r As Range
        Set r = ActiveSheet.Shapes(Application.Caller).TopLeftCell


    r is the cell where the shape is placed and clicked


    There are 200 shapes on the sheet
    Now, when the r address is $C$350 I need the range C3:C300
    .
    .When the r address is $Z$350 I need the range Z3:Z300


    .When the r address is $AI$350 I need the range AI3:AI300
    .
    .
    In other words, I need everytime the Range 3:300 in the column where the shape is clicked.


    Hope this is possible.


    aisietie

  • Open workbook - copy range - paste in

    Re: Open workbook - copy range - paste in


    OK,


    I changed the variables, and now I use the variable wbSrc in all the lines.
    Thanks for the tip!
    The workbooks where the sheets need to be copied, are always in the same folder as the Destination workbook.
    So as I understand, there is a possibility to use a loop.
    The part \\xyz.net\fs\2013 FTE is always the same, files are like \\xyz.net\fs\2013 FTE AAL.xlsm, \\xyz.net\fs\2013 FTE AALT.xlsm, \\xyz.net\fs\2013 FTE BEV.xlsm, ....


    The range is always the same.
    The name pairs are known:
    \\xyz.net\fs\2013 FTE AAL.xlsm to sheet AAL in ThisWorkbook
    \\xyz.net\fs\2013 FTE AALT.xlsm to sheet AALT in ThisWorkbook
    \\xyz.net\fs\2013 FTE BEV.xlsm to sheet BEV in ThisWorkbook
    ... and so on


    I wonder, when using a loop, will this speed up the macro?


    grtz


    aisietie








    What

  • Open workbook - copy range - paste in

    Re: Open workbook - copy range - paste in


    Norie,


    The code that you wrote works, the worksheet is added, but it was not needed.
    The code that I use is:



    Now it is done for copying from 3 workbooks, and I must do it for 15.
    So there will be 15 wbSrc


    Grtz


    aisietie

  • Open workbook - copy range - paste in

    Re: Open workbook - copy range - paste in


    Can I try to explain it otherwise:


    I have Workbook Allo.xlsm open
    The workbook contains Sheets AA en BB
    I want that a macro opens the workbook voorbeeldAA.xlsm and copies the sheet ("Test") (including the layout)
    Then closes Workbook and paste the sheet ("Test") in Workbook Allo.xlsm in the sheet ("AA")


    Greetz


    aisietie