Posts by xsmurf

Carim

So double checked everything and your nr. 1 suggestion was the solution.

I now made sure that the array shows an 0 when the cell is empty.

All is good now, again thank you for the help

I tried but I cannot replicate the issue, I seem too only have it in my original report.

I never seen this before

Carim Just a quick update:

I have successfully implemented the solution, see code below, and it works perfect. All the information is displayed how it should.

=IFERROR(INDEX('Database'!$T$1:$T$2002,LARGE(IF(F28&G28&$AW$8='Database'!$B$1:$B$2002&'Database'!$C$1:$C$2002&'Database'!$X$1:$X$2002,ROW('Database'!$C$1:$C$2002),0),SUM(('Database'!$B$1:$B$2002=F28)*('Database'!$C$1:$C$2002=G28)*('Database'!$X$1:$X$2002=$AW$8))-I28+1)),"")

The retrieval of the information is working without any problems, however when I try to SUM the results it gives me a #value error.

The code I am using, see below, works perfectly when I type in the value manually, but when used with the first code it gives me an #Value error.

I tried to change the condition of the cells to Number / General, but nothing seems to help.

=SUM(($AO$28:$AO$55+$AP$28:$AP$55+$AQ$28:$AQ$55)*(--($D$28:$D$55=D24)))/60

Would you be able to assist ?

Thank for any help you can give me.

Carim

Holy moly, that did the trick. I made it work in the original file, it works like a charm.

You made my day, thank you so much

Carim

I adjusted my example file and put all the info in there.

Hope it makes sense.

Thank you for the help.

Production Reports_WIP.zip

Carim thank you for your reply, I just saw your message.

I will recheck my example and give a proper explanation of what I want.

I appreciate the help.

wondering if anybody could help me with this, I would really appreciate it.

Thank you

Hello,

This forum has also helped me a lot over the years and I hope this will be the case now.

I know there are a lot of ways to show duplicates values in excel, but I have not seen this yet so I don't know if this is possible.
Let's say I have a range from C5 to X70, within that range I would like to see duplicates marked red.
But within that range there a columns and rows that need to be excluded from turning red (so duplicates are aloud in these columns & rows)
The code should only look for doubles in columns C / E / G / I / K / M / O
The code should ignore doubles in row 55 / 56 / 57 / 58
So if there is a name John in column C, but also a John in column K, both John's should highlight RED.
A John in row 56 should NOT turn RED, because this is aloud.

I want to use this with a Worksheet_Change event in VBA, and prefer not to use conditional formatting, because there will be a lot of copy & paste happing in this sheet.

Can anybody help me out with my problem?

A big thanks in advance for all the help that will be offered, I really appreciate it.

Here is my test file, with my issue.

Info is in the excel file.

Thank you for the help

Test file.xlsm

Anybody has any thoughts about my problem?

Hey,

Thank you all for replying. I am working on a sample file so you guys can see how it works.

The ID will be generate by the userform, but haven't implemented that code yet.

When I have the file ready I will upload it here. (have to remove the confidential information first)

Again thank you for the replies so far.

Hello,

I use an userform to populate a sheet, and this works fine.

I have added the option to edit / delete data from the data-sheet, and that works, however it updates the wrong rows when there are empty rows in the data-sheet.

Every row of data has an serial number, their are no doubles.

The code that I am using, just for updating (found on a forum and adapted to my needs)

Private Sub CommandButton6_Click() ' Update button

Dim SLNO As Integer

Sheets("Reports").Select
If Me.TextBox27.Value = "" Then
MsgBox "Shift Can Not be Blank!!!", vbExclamation, "SL No"
Exit Sub
End If
SLNO = Me.TextBox27.Value
'Sheets("Reports").Select
Dim rowselect As Double
Dim msg As String
Dim ans As String
rowselect = Me.TextBox27.Value
rowselect = rowselect + 1
Rows(rowselect).Select
Cells(rowselect, 3) = Me.TextBox1.Value
Cells(rowselect, 6) = Me.ComboBox7.Value
Cells(rowselect, 2) = Me.ComboBox3.Value
Cells(rowselect, 4) = Me.ComboBox4.Value
Cells(rowselect, 7) = Me.TextBox4.Value
Cells(rowselect, 8) = Me.TextBox5.Value
Cells(rowselect, 9) = Me.Label76.Caption
Cells(rowselect, 34) = Me.TextBox6.Value
Cells(rowselect, 35) = Me.TextBox22.Value
Cells(rowselect, 14) = Me.TextBox9.Value
Cells(rowselect, 15) = Me.TextBox10.Value
Cells(rowselect, 16) = Me.TextBox11.Value
Cells(rowselect, 17) = Me.TextBox12.Value
Cells(rowselect, 18) = Me.TextBox13.Value
Cells(rowselect, 19) = Me.TextBox14.Value
Cells(rowselect, 24) = Me.TextBox15.Value
Cells(rowselect, 29) = Me.TextBox16.Value
Cells(rowselect, 36) = Me.TextBox17.Value
Cells(rowselect, 20) = Me.TextBox18.Value
Cells(rowselect, 25) = Me.TextBox19.Value
Cells(rowselect, 30) = Me.TextBox20.Value
Cells(rowselect, 37) = Me.TextBox21.Value
Cells(rowselect, 21) = Me.ComboBox6.Value
Cells(rowselect, 26) = Me.ComboBox5.Value
Cells(rowselect, 31) = Me.ComboBox8.Value
Cells(rowselect, 38) = Me.ComboBox9.Value
Cells(rowselect, 22) = Me.ComboBox12.Value
Cells(rowselect, 27) = Me.ComboBox11.Value
Cells(rowselect, 32) = Me.ComboBox13.Value
Cells(rowselect, 39) = Me.ComboBox10.Value
Cells(rowselect, 23) = Me.ComboBox16.Value
Cells(rowselect, 28) = Me.ComboBox15.Value
Cells(rowselect, 33) = Me.ComboBox17.Value
Cells(rowselect, 40) = Me.TextBox26.Value

rowselect = rowselect - 1
msg = "Shift Info " & rowselect & "  Successfully Updated...Continue?"
Unload Me
ans = MsgBox(msg, vbYesNo, "Update")
If ans = vbYes Then
Shift.Show
Else
Sheets("Reports").Select
End If

End Sub

How can I change the code so it will ignore the empty rows (if there are any).

Overwriting the wrong rows is not an option.

Hope someone can help me, really appreciate all the help.

Hi,

I hope to get some help here with a problem that I have.

I have a workbook that retrieves information from different workbooks, this all works well.

The main problem I have is when there is more then 1 result.

Please see attached file with a sample.

Just to clarify the sample is retrieving on a different sheet within the same workbook. The solution needs to be that it needs to retrieve that information from a different workbook.

Hope you guys can help me out, I would really appreciated it.

Thanks

Production Reports_WIP.zip

[USER="33159"]KjBox[/USER] Thanks for your answer.
So Im guessing I can't do it without using cells. I have to have a reference from a sheet.

Again thank you.

Hello,

I was wondering if something was possible.

If I make a list on a sheet in cel B3 through the option " Data Validation", and fill it for example with the options "test;test1;test2"
would it be possible to make userform that reads the list options in cell B3, show them in the userform and be able to add or remove an option?
So if you want to remove the option "test2" you can do that from within the userform, same if you want to add a option.

I know you can have a reference to a range of cells, but I want to know if it is possible?

Thank you.

Hello,

I have a question, because I have a problem regarding a VBA code

I found a code that does only partially what I want.
I'm trying to highlight dates that are equal or lower then the current date (this date is on a different sheet, called Setup and the date is in cell "F3")
If there is no date in the range given no formatting should take place.
And only the range given should be highlighted, and not the rows in between the range.

Can somebody assist me with this problem, it would be highly appreciated.

The Code:

Sub ChangeColor()
    Dim myDate As Date
    Dim rngCell As Range
    Dim lrow  As Long
     'format the date excluding time
    myDate = FormatDateTime(Now, 2)


    For Each rngCell In Range("C3:BB3", "C27:BB27")


   Select Case DateDiff("d", FormatDateTime(rngCell.Value, 2), myDate)
        Case Is >= 0
        rngCell.Interior.ColorIndex = 44
        rngCell.Font.ColorIndex = 55
        Case Is = Empty
        rngCell.Interior.ColorIndex = 48
        rngCell.Font.ColorIndex = 58
   End Select
    Next


    Exit Sub


End Sub

Re: Copy values from one workbook to another with merged cells

jolivanes
I mixed the codes ( the one I aleady was using, and the one you provided) and it got the whole thing working. Again thank you for your help.
Happy holidays

Re: Copy values from one workbook to another with merged cells

jolivanes
sorry for the late response, just came back from a little trip.
But that looks indeed in what i was looking for, thank you so much.
I will try to adapt it to my worksheets, and hopefully my problems are gone.
If not, I will be back :wink:

Re: Copy values from one workbook to another with merged cells

jolivanes
Thanks for a possible solution, but where do I put this code in. Will that be instead of the arrays I use or is this a extra piece of code that I need.

Re: Copy values from one workbook to another with merged cells

KjBox
as requested
The main.xls file is where I collect the data, the test file is where I put the data in

forum.ozgrid.com/index.php?attachment/70905/ forum.ozgrid.com/index.php?attachment/70906/