Posts by aisietie

Re: Check if data in columns is complete

Pike, Nilem,

What else can I say or write to thank the both of you, than "Thank You Very much".

Both codes are 100% OK and let me continuing the job on this Holliday.

aisietie

Re: Check if data in columns is complete

Thanks to both,

I put the code in two buttons, Button 1 is pike's code, button 2 is nilem's code.
The logic that I wants is that when I click a button in a column, the messagebox returns the missing items that are in the list sheet.

With pike's code, the return is only one missing item, with nilem's code, the answer is right, but now I must split the code, so one button controls only one column.

But already a great thanks to both,

aisietieforum.ozgrid.com/index.php?attachment/47783/

Hello,

I want to check in columns in a sheet if all the values are set in compare to the values in a list.

In the attachment the example file.

Thanks for the help

aisietie

Hello,

I need a code that:
When a workbook is active :
1. Open an other workbook (\\Belg\VoorbeeldAA.xlsm) -ignore macro's
2. Copy range A1:S60 from sheet ("Test") from this workbook
3. Close workbook VoorbeeldAA.xls without saving
4. Paste the sheet as new sheet ("AA") in the active workbook
5. Open workbook (\\Belg\VoorbeeldBB.xlsm) -ignore macro's
6. Copy range A1:S60 fromsheet ("Test") from this workbook
7. Close workbook VoorbeeldBB.xls without saving
8. Paste the sheet as new sheet ("BB") in the active workbook

I tried it with the macro recorder, but the code:

Workbooks.Open FileName:= _
        "\\Belg\AA.xlsm"
    Range("A1:S60").Select
    Selection.copy
    ActiveWindow.Close
    Range("A1").Select
    ActiveSheet.Paste

Gives me problems renaming the pasted sheets, and to deactivate the clipboard warning, and save changes warnings.

Thanks in advance,

aisietie

Re: VBA Find highest value in textboxes

Mike,

Thanks, that is fantastic.

aisietie

Hello,

I have 5 textboxes in a userform, in each there is a time value.
How can I find the textbox with the highest value, and then copy the value?

aisietie

Re: Search - Count - Multiply Combination

OK, now I see

Muchas Gracias

Grtz

Re: Search - Count - Multiply Combination

Ok, Thanks, but I only see that you added a pivot table....

Re: Search - Count - Multiply Combination

Hello,

I am sorry, but I cannot find or make the solution that is proposed.
Can somebody help please.

Grtz

aisietie

Hello,

I need a formula to count how many times a value is in a column, and when the corresponding cell is "weekday", multiply by 5, when it is a day of the week, count how many times the value is in the column.

Please see the attachment with the example, and what the result is that I want.

Thanks

aisietie

Re: If Sum Exceeds Certain Value Display Message Else Sum Values

Thanks again for the reply,

but I think this code will only check cell A1 in the sheets.
This is not enough.
I have in sheet 1 numbers in the range A1:D1000, also in sheet 2 numbers in the range A1:D1000
In the total sheet I have the same range A1:D1000 with in each cell in the range, the sum of the corresponding cells in the other sheets.
Here I want that the sum is max 20.
So when in sheet 1 in cell D5 the value is "19" and I put "2" in cell D5 in sheet 2, a messagebox pop-up with the warning that in sheet "total" the limit is reached.

Hope this is possible,

aisietie

Re: Control the sum of values in a cell

Thx for the quick reply, but I want to be warned in the sheet I am working.
So, when the sheet halo is the active sheet, and I enter a value in a cell in the sheet halo, and the result is that the SUM in sheet total is reached, a messagebox give me a warning.

It have it working now with a function

Function IfMsg(Test, ifTrue)
    If Test = True Then
        IfMsg = ifTrue
            Else
        IfMsg = "Te Groot"
        MsgBox "Totaal is te groot" & vbCr & "Maximum is overschreden"
           End If
End Function

and than in each cell of the sheet total:
a formula

=IfMsg(SOM(test!F17;halo!F17;rapport!F17)<=F$13;SOM(test!F17;halo!F17;rapport!F17))

but this slows the calculation because the formule is in more than 5000 cells, and every time a cell is changed, it controlls all the cells if not the value is to high.

It is the slowness of the function that is the problem, and because I am searching for an alternative.

aisietie

Hello,

I have on sheet(total) in each cell the formule sum of values in corresponding cells in other sheets.
Something like =SUM(test!A1;rapport!A1;halo!A1)
I want to allow a max in each cell in sheet total let us say max = "20"
Is it possible to give a warning (messagebox) when for instance
Sheet test cell A1 has value "19"
In sheet halo in cell A1 "2" is inputted, and then a messagebox pop-up "value in Sum in sheet Total is to big".
because the SUM is more than "20"

Is it possible?
Can somebody give advise?

aisietie

Re: Problem when only 1 row in range using Copy xlDown

Hello,

In the attachment an example.
The 2 ways are in 2 macro's.

But still, the part that needs to copy the range where only one row must be copied does not work.
Running the macro's step by step let me see what the code do wrong, but I cannot find what the reason can be.

Aisietie

Re: Problem when only 1 row in range using Copy xlDown

Thanks for the quick reply, but......
The same problem.......

Hello,

I use the code to copy the first 3 columns, starting at row 9 to the last used row.

Sheets("Test").Range("A9:C9", Sheets("Test").Range("A9").End(xlDown)).Copy

No problem when there is more than 1 row, but when the range contains only 1 row, and sometimes this happens, the code copies to the last row of the sheet, the xlDown is not recognized.

What is there to do, so the code works, no matter how many rows there are?

Thx

aisietie

Re: Show first cell in column (on Row A)

Thanks Jindon.
Buy instead of Entirerow, I need to use Entirecolumn, and then it works.

Thanks a lot

aisietie

Hello,

I try to show the value in row A.

With the code

Dim FindString As String
Dim Rng As Range
FindString = strSheetname
    If Trim(FindString) <> "" Then
        With Sheets(strSheetname).Range(dat1, dat2)
            Set Rng = .Find(What:=FindString, _
                            After:=.Cells(.Cells.Count), _
                            LookIn:=xlValues, _
                            lookat:=xlWhole, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlNext, _
                            MatchCase:=False)
            If Not Rng Is Nothing Then
               
               MsgBox ("en " & Rng.End(xlUp))
            Else
                MsgBox "Nothing found"
            End If
        End With
    End If

I want that the cell value in rowA is shown, but the code returns the first non blanc cell above the found range in the column, and not the top cell (on RowA)in the column.
What is "stronger" then End(xlUp), and goes to the top of the column?

aisietie

Re: Paste row in first free row in Range.

Dave, Jindon,

Thanks.
With the code SpecialCells(4)(1) it works perfect.

aisietie

Re: Paste row in first free row in Range.

Thanks for the quick reply,
but I want to paste the row in the first free row in the range between A101 and A200 starting in A101
It is possible that the first row 101 is emty, and then the row must be pasted in row 101

aisietie