# Posts by aisietie

• ## Difference in Months between dates

Re: Difference in Months between dates

Problem is that datedif in formula is not available (Part of Analyses toolpack)
And I cannot find the function that makes the differnce in months.

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

• ## Find value of cells in other colums - same Row as found Cell address

Re: Find value of cells in other colums - same Row as found Cell address

Thanks Mumps,

That will do it

aisietie

• ## Find value of cells in other colums - same Row as found Cell address

Hello,

With

Code
``Location = rFoundCell.Address``

I get the address of a cell as \$NP\$2
Now I want to know what the corresponding value in column A en K is. So I want through VBA know, what is the value in \$A\$2 and \$K\$2.

Thanks for the help.

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.

aisietie

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

Hello,

Column A contains time values minute by minute.
With application goto, always the cell with time now is the first row, and every minut the row scrolls to the next row.

I want that this only is done when the corresponding cell in column C contains a X.forum.ozgrid.com/index.php?attachment/48493/

Thanks for the help,

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

Re: Hide Row if corresponding cell in column C is not empty

Smallman, Pike,

Thanks for the advices, and both codes are working perfect.

Thank you both,

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

Re: vba Find duplicates in range

Robert, SmallMan,

Thanks, both solutions are Werking perfectly.

Thanks

aisietie

• ## vba Find duplicates in range

Re: vba Find duplicates in range

Hello,
I was not complete in my question. Certain values ("V" and "R") can be used more than once.
I tried it with conditional Formating, but all the duplicate values are highlighted, inclusive the "V"'s and "R"'s
Thanks
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"

aisietie

• ## Make a range with address cell

Re: Make a range with address cell

Jasper,

For me it is a big OK
It does the job!

Thanks

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

Norie,

That is a big OK.

Thanks

aisietie

• ## Open workbook - copy range - paste in

Re: Open workbook - copy range - paste in

The line

Code
``````Set wbSrc = Workbooks.Open FileName:= _
("\\Belg\AA.xlsm")``````

gives an error Instruction end expected"

..........

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