Posts by Joe Derr

Re: Insert row with formulas conditional formating and data validation

SMC,

Thanks for the reply.

Sorry for the late reply, I took two days of vacation on Monday and Tuesday, and was away from my program when I received this.

I entered your code and it doesn't insert a new row that spans from A-O. When I double click the line, it does ask me if I want to insert a line, and when I click yes, it looks like it is going to do something, but then nothing happens.

I entered my "shift down" code and it inserted the line as needed, but nothing was copied down and filled down as needed.

Any ideas?

Thanks!

Here is the code I use in the worksheet object. You double click to insert a line below the selected line.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
  Dim LR As Integer
  If Target.Column > 1 Then Exit Sub
  Cancel = True
  If MsgBox("Do you want to insert a new row below this row?", vbYesNo) = vbYes Then
    With Target
  Range(ActiveCell.Offset(0, 0), ActiveCell.Offset(0, 14)).Select
  Selection.Offset(1).Insert shift:=xlDown, CopyOrigin:=xlFormatFromRightOrBelow
Exit Sub

That part works... however in column's E, J and M and N I have the following that won't copy and fill down accordingly...

E - Formula
J - Conditional Format
M - Formula
N - Data Validation

The formula's are as follows

E - =IF(ISBLANK(D10),(""),ROUNDDOWN((MINUTE(V10)-MINUTE(T10))/60,1))
M - =IF(ISBLANK(R10),(""),(CONCATENATE(R10,"",W10)))

The conditional format in J are:

If the cell value = "REP" then Orange text applies to =$J$10
If the cell value = "REC" then Red text applies to = $J$10

The Data validation in N is based off of cell M (a formula)

The data validation is "allow list" and source is "=INDIRECT($M$10)"

How do I copy these to the new lnserted line and then fill down to the end of my document, which will be no further than Row 150?

When I insert a line, the formula for instance in M goes like this

Line Above - =IF(ISBLANK(R10),(""),(CONCATENATE(R10,"",W10)))
Inserted Line - (blank nothing there)
Line Below - =IF(ISBLANK(R11),(""),(CONCATENATE(R11,"",W11)))

I need it to fill this way.. for this column

Line Above - =IF(ISBLANK(R10),(""),(CONCATENATE(R10,"",W10)))
Inserted Line - =IF(ISBLANK(R11),(""),(CONCATENATE(R11,"",W11)))
Line Below - =IF(ISBLANK(R12),(""),(CONCATENATE(R12,"",W12)))

I hope I explained it well enough... basically I want to insert a line, and it copy and fill down the data accordingily...

thanks in advance!!

Re: Convert Time Spans To Specified Times

Ok, I think I attached a file. I have comment box's explaining things.

I don't know if I described it wrong, but see the chart in the file to see how it should calculate. The last help, did calculate every 10 minutes.. but again.. please see the chart to see how I am trying to get it to add up.

Basically... after 58 minutes, you add an hour then start counting the .0 , .1, .2, .3 etc again.

I hope this is more clear, I apologize.

Re: Convert Time Spans To Specified Times

That was getting the tenths of minutes... I guess now I need to couple this with including the hours. My original formula did it, but at the time intervals where it changed from a .0 to a .1, a .1 to a .2 etc, it was always off. Is there a way to adapt your formula to include hours? This has been so illusive of a formula.. thanks for your help!

Re: Convert Time Spans To Specified Times

Quote from snb;513579

replace the semicolon by comma:

=FLOOR((MINUTe(A1)-MINUTe(B1))/60,0,1)

Thanks for replying again.

I did as you suggested and it came back with a "Youv'e entered too many arguments for this function" error and highlited the 1. I tried to take out one of the numbers. One gives me a Div/0 error and the other does calculate it, but it still shows 0.0 rather than 0.1 for the 3 minute mark.

Thanks again.. for your time and your help.

Re: Convert Time: HH:MM to HH and tenths

Quote from snb;513080

=FLOOR((MINUTe(A1)-MINUTe(B1))/60;0,1)

Thanks for replying. However when I tried it, it kept saying the formula has an error. I tried to adjust brackets, tried capitalizing the e in Minute, I tried turning the semi-colon into a comma, it really didn't like that.

Any help would be appreciated! I will keep trying it, and thanks again for replying.

I have the following formula:

=IF(ISBLANK(D19),(""),(V19-T19)*24)

D19 is just looking to see if time is entered, if not this cell will be blank.

T19 is Take Off time entered in cell (for example) 12:00

V19 is Land Time entered in cell (for example) 12:03

This would give a time of 3 minutes... which should convert to .1, but with my formula, still converts to .0

Here is the chart for the conversion...

1 - 2 Min = .0 Hr
3 - 8 Min = .1 Hr
9 - 14 Min = .2 Hr
15 - 20 Min = .3 Hr
21 - 26 Min = .4 Hr
27 - 33 Min = .5 Hr
34 - 39 Min = .6 Hr
40 - 45 Min = .7 Hr
46 - 51 Min = .8 Hr
52 - 57 Min = .9 Hr
58 - 60 Min = 1.0 Hr

So as I was saying..

3 Minutes should be .1 but it shows .0 still with my formula....
9 Minutes should be .2 but it shows .1 still with my formula....

etc.. I don't know what I am doing wrong..


Any help would be appreciated.

Joe

Re: Macro To Color 2 Column Areas

Dave,

Thanks for the reply, and your code does get it closer, colors the columns, but I need just the row selected by myRow, this colors the whole column.

Is there a way to combine your lCol with myRow ? to color each column except 4 and 5 with said color?

I will toy with it too.

Joe

:Edited:

Ok, I got it to work, just combined our two.. here is the code incase someone else needs to do this.

Sub Referral()
'Written by:
'Dave Hawley
'Administrator - OzGrid Forums
'http://www.ozgrid.com/forum/showthread.php?t=102427
    
    Dim lCol As Long
    With Sheet1 ' CodeName
        For lCol = 1 To 16
            .Cells(myRow, lCol).Select
                With Selection.Interior
                    .Color = RGB(255, 128, 128)
                    .Pattern = xlSolid
                End With
            If lCol = 3 Then lCol = 5
        Next lCol
    End With


End Sub

Hey all,

Does anyone know how to make this color columns 1-3 and 6-16 (basically skipping columns 4 and 5).. the code below that I use will do one column at a time.

If you drop the ",1" and just put "(myRow).Select", it will color the whole row.. but I need columns D and E to remain untouched.

I figure I can repeat the code 14 times, but there has to be a quicker way..

myRow is determined by a ComboBox.

Sub Referral()
With Worksheets("Tracker").Range("A:A")
    .Cells(myRow, 1).Select     'Name
    With Selection.Interior
        .Color = RGB(255, 128, 128)
        .Pattern = xlSolid
    End With
End With
End Sub

Thanks!

Joe

Re: Copy Rows Where Date In Column Is Greater Than Today

Thanks Dave!

Ok attached is the test file.

When the userform pops up..

Button 1 - should filter the data to show any past current date that isn;t highlighted the bright green color. That button is essentually going to be a "Reports Overdue" button. The bright green means I have seen the report and thus isn't over due, that is why I need to filter that shade out.

Button 2 - This is going to be reports due to me next week. Right now it works from current date plus 7 days. Is there a way to modify the code to show this week/remainder there of, and the next week. So if you click this button, say on monday, you will get this week and next. If you click it on a thrusday you will get the remainder of this week and next.

Button 3 - this shows that I can filter the bright green color, now to use this code and filter it out not in.

Button 4 - this is the normal color, shows that the report is out to the section.

Button 5 - this will reset the filter, to show all the data again.

My two problems are..
1. Filter the Bright green *out* of button 1's filter
2. Modify to show the current week or part of and the following week for any reports coming due.. currently it just give me current date plus the next 7 days.. so if ran on Tuesday, it would only run from Tuesday to Tuesday.. I need it to run from Tuesday to the following weeks Friday.

Thanks for looking!!

Dave thanks again for the thread title change.

Joe

Re: Copy Rows Where Date In Column Is Greater Than Today

Apart from Excel 2007 as far as I am aware there is no standard function to filter by color.

Is this what you are trying to do?

Sorry for the delayed reply, I was given two huge projects to work, both priority #1.. lol
I am currently using 2007, since the company upgraded, so is sorting by color only 2007 and not supported by earlier versions?

I am trying to get it to sort by two criteria, first by date that is less than (older than) the current date, and then the second criteria will be by color. Let me explain.

First criteria will sort out the dates and remove all the dates that are in the future, just the current date and older, then the desired effect will be Criteria two will then take out all that have that exact color in the cell, which I had the wrong code in there, should be 0,255,0 or the green color.

What this leaves is anything that is highlighted red, or has a light green color.

This is a report tracker, red report is late, green color the report is on-time/already passed my department, and the lime green or light green color is that the report has not come back though me yet or was just sent out.

I will see if I can generate a test file so you can see what I am trying to explain.

Thanks guys.

Joe

Re: AutoFilter/Advanced Filter Date In Column Is Greater Than Today & Cell Color

Advanced filters confuse me. I will have to look more into it, I am doing this via VBA userform, so that makes it that much more difficult, this thread has gone from copying to another sheet, to a Autofilter (which works) but I just need one more criteria to make it work. Here is the code I used the Macro Recorder to get.

'Get today and convert to serial value
    
    d = CLng(Date)
    
' Sets up Auto Filters to show just the current date and the
' any days prior.




    With Sheet1
        .AutoFilterMode = False


        .Range("E3:E3").AutoFilter Field:=5, Criteria1:="<" & d _
        , Criteria2:=RGB(204, 255, 204), Operator:=xlFilterCellColor, VisibleDropDown:=False
    End With

But it errors with the message "Autofilter method of Range class failed"

Again, I am sure I would need to use a "AdvancedFilter" but even recording the macro while I set up an advanced filter doesn't take into effect the color of the cell.

Re: Copy Rows Where Date In Column Is Greater Than Today

Dave,

Thanks for the guidance.. I got it to sort all the dates and limit the ones displayed to just any date prior to today. However, I never could get the Today() to work, I had to set the date in a variable and then go that way. Below is the code:

'Get today and convert to serial value
    
    d = CLng(Date)
    
' Sets up Auto Filters to show just the current date and the
' any days prior.


    With Sheet1
        .AutoFilterMode = False
        .Range("D3:D3").AutoFilter Field:=5, Criteria1:="<" & d, Operator:=xlAnd, VisibleDropDown:=False
    End With

Now I have to figure out what criteria to further sort it so any thing without a certain colored cell would be omitted as well, as in a bright green would be eliminated, but light green, pink and red would show up. Back to the search box.

Thanks again Dave.

All,

I have been working on this issue for some time, searches let me down paths to tell me of the color of the cell, but can't put all the pieces together.

What I am trying to do, is upon Clicking Command Button 1 it will go row by row of column D (there are 2 headers so D3 would be the first fillable data) looking for dates that is past todays date, if past, it will color the cell red then copy it to the next available row in sheet2 then continue, date past due, color red, copy entire row to sheet 2 looping until the end is reached.

Any help solving this one would be greatly appreciated.

Joe

Re: Combobox Rowsource Additem To 2nd Combobox On Selection

It sure does..

I can see that mine still had the ComboBox4_Change Event still.. that was the reason I wasn't working.. Now I have to figure out why that was keeping it from working.

Reafidy, thanks for your persistant Help!!!

I will implement this in the main form tomorrow.

Joe

Re: Combobox Rowsource Additem To 2nd Combobox On Selection

Reafidy,

Thanks for continuing to work with me.

Attached is the mods as you suggested, but it still isn't populating the bottom ComboBox when you select Bob. The others populate fine, as expected.

Bob has the different Cell Value than the loaded bottom ComboBox, the others match, thus is shown.

Maybe its not possible.. I am sure there is something I am missing.

I do like your simplified version of the rest of the code, I will see if I can get it into the main program.

*scratching head*

Joe

Re: Combobox Rowsource Additem To 2nd Combobox On Selection

It is confusing..

About ComboBox4 (I have other ComboBox's on the dropdown, 4 of them) the ones that effected by this is ComboBox 1 and ComboBox 4 .. so I have 2 ComboBoxes..

About the title, I did think about it.. I have had chats with Dave on Thread Titles and I tried to use relavent words that describe the problem, and not what I think the answer is.

Here is a file with what I am talking about. Click the button to launch the UserForm

The top dropdown is the names, if you select Bob, the ComboBox4 will not display the data, because if you look at the sheet next to Bob you will find how the date is meshed with the Corr/Hold for C/O and it will not display, but the other 3 will display fine.

That is what I am trying to figure out how to get CB4 to display the selected text based off of CB1 selection.

Re: Combobox Rowsource Additem To 2nd Combobox On Selection

I am not trying to make a dependent ComboBox, The one is loaded is fine for the selections, but when I select a name, it puts a sheet value in/on the combobox.value, if that value isn't the same as what is loaded, it will not allow it to be displayed.

For instance.. the ComboBox is populated as one of the selections

CORR/HOLD FOR C/O

But.. on the sheet when you select a name, it reads in the cell that has that plus a date added to it. so the cell reads...

CORR/HOLD FOR C/O 21 May 08

Because the date is now on it (which is variable) it will now put that in ComboBox4 when it reads in all the TextBoxs etc.

I am not trying to load a new list in that ComboBox, I am trying to to just have it display what is pulled from the sheet.

The ComboBox has to be rowsourced so it can read in generic Remarks, that can be changed and then that row is updated.

Any ideas would be very helpful.

All,

I have two comboboxs on a userform, both are populated like this:
(ComboBox1 is a different sheet/column)

' Sets Remarks in ComboBox4 Contents
    With Worksheets("Data")
      Set rng = .Range(.Cells(1, "C"), .Cells(Rows.Count, "C").End(xlUp))
    End With
    With ComboBox4
      .RowSource = rng.Address(external:=True)
    End With

The function is that ComboBox1 will populate the names on lets say Sheet1, Column A, and when selected will populate by offset all the other Textboxs, and ComboBoxs.

This is fine except on a few entries, I combine the ComboBox info with a date from another textbox.

Textbox1 is a date
ComboBox4 is populated off of items from the data sheet
ComboBox1 is populated off of sheet1 and provides names, then fills the userform fields

In populating the Userform, it fills Combobox4.value by the offset value of the selected name.

That cell does not contain the same info that was loaded into the ComboBox initally, and it does not show it. All other ComboBox entries match preloaded values, and show.

How do I get the ComboBox to display what is in the OffSet cell value, rather than blank because its different?

I hope I didn't confuse anyone.

Re: Color Cells Based On Date Conditions/Scope

Well I could do it based on a data sheet, because there isn't a spot available on the main sheet for it. The normal sheet is A-K and is printable, other rows would make it cluttered.

If it can't be done via VBA, my option is to use the formulas I found, based off a data sheet.

Is there a way to execute a formula in VBA? I remember doing it once, but can't find my reference for it.

Thanks Dave.