Posts by NoSparks

Re: Auto enter data time

The way I understand the situation, maybe this

Private Sub Worksheet_Change(ByVal Target As Range)
    'limit to single cell
        If Target.Count > 1 Then Exit Sub
    'limit to row 5
        If Target.Row <> 5 Then Exit Sub
    'limit to even numbered columns
        If Target.Column Mod 2 = 1 Then Exit Sub
        
    'prevent this procedure from calling itself
        Application.EnableEvents = False
    'put date and time in cell on right
        Target.Offset(0, 1).Value = Now
    're-enable events
        Application.EnableEvents = True
End Sub

Re: VBA works on step through, but not when I run

It won't help other than letting you know that execution has stopped at that point in the sub.

Maybe we need a definition or executing.
First thing you have is a message box, did I miss you saying it is displayed?

Re: VBA works on step through, but not when I run

Quote

However when I want to carry out a test run the code doesn't execute. I can't see why.

How do you initiate a test run ?
What happens if you put STOP as the first line of code in the sub ?

Re: Search working but not correct!

I don't know what to change because I can't see what's happening.

I suggest you put a break point, F9, on the r=1 line then step through the code one line at a time with the F8 key and flip flop back and forth between the VBE and the sheets to see what actually happens when each line of code is executed.
You should then see what's being copied, where and what is being pasted, etc.

Re: Search working but not correct!

Appears to me you are copying the entire rows and should be getting everything.
Do you have merged cells ?

Re: Time sheet in Excel

Only the OP knows all the forums she posted the same request to.

There is at least these 3 others
https://www.mrexcel.com/forum/…n-reached.html?highlight=
https://www.excelguru.ca/forums/showthread.php?8157-overtime
http://www.msofficeforums.com/…rtime-after-40-hours.html

Re: Time sheet in Excel

@ tasha2515

What happens if the employee takes a 3 hour lunch?

Based on the formula you have in your attached 'Copy of Express Time Sheet new 1.0 Master.xlsx' at another forum, regular time excludes the lunch break.
The somewhat similar solution I posted 3 days ago at that forum took this into consideration.

Had you cross posted in the appropriate manner, as advised on at least 3 forums, I wouldn't have wasted my time.

Anyway, would you please now have the courtesy to go to all your cross posts and inform you now have a solution and give credit where credit is due.

Thanks.

Re: Assigning Values in Array, VBA

Isn't the array zero based.

'
    y = 0
     
    For x = 6 To TotalRows + 6
        ReportInfo(y, 0) = Cells(x, 10) ' Activity
        ReportInfo(y, 1) = Cells(x, 2) ' Name
        ReportInfo(y, 2) = Cells(x, 6) ' Hours
        y = y + 1
    Next x
'

Re: DataBodyRange Combine 2 Columns

I think something like this will work

Dim oLo As ListObject
Dim i As Integer
    
Set oLo = ActiveSheet.ListObjects("Temp")


With oLo
    For i = 1 To .ListRows.Count
        .DataBodyRange(i, 11) = .DataBodyRange(i, 12) & .DataBodyRange(i, 7)
    Next i
End With

but I'd just use a formula in the first data row and double click the drag handle
=[@[Chq No]]&[@Desc]

Re: link a page in a multipage userform to a worksheet

@ StevegSmith

Ever acknowledge responses to your cross posts ?

Re: Display a certain Worksheet based on drop down list

By chance did you use Worksheet_SelectionChange instead of Worksheet_Change ?

Re: Overlap dates to highlight

sathishsusa, be sure you go to all cross posting of this question and give credit for the solution.

Re: VBA to Refresh Pivot Tables when Any Value in Range Changes

Do J22:L24 contain formulas ?
If so they will not trigger the Worksheet_Change event.

Re: Update cell based on value in another cell

If you're happy, I'm happy.

It just didn't make sense to me for items that aren't even started to show as Overdue.

Don't know if you noticed the one line macro in the sheet module I used to change the formulas.
When I entered the formula and dragged it down the colors went goofy so I used the macro to change them all in one go.

Re: Update cell based on value in another cell

Always test on a copy of the original and make sure you have a backup.

When you change L11 from Overdue to something else you'll need to blank I11 to prevent L11 changing back to Overdue.
This could be done with Worksheet_Change similar to SO's example in post #4 to remove the formula and blank I11.

Good luck with your project
NoSparks

Re: Update cell based on value in another cell

Hi josh

Have a look and see if this will work.

I changed the formulas down column "I" to do away with all those days over due in the yet to be used rows.

=IF(OR(ISBLANK(F11),ISBLANK(G11),ISBLANK(H11)),"",IF(TODAY()>H11,TODAY()-H11,""))

Because column I is formulas, need to use the Worksheet_Calculate event.
I've used column Z to hold a formula in Z7

=SUMPRODUCT(--(I9:I219<>Z9:Z219))

and a copy of I9:I219 in Z9:Z219.
If this location is a problem it could be made to use a separate sheet if need be.

If Z7 is 0, the two ranges match so it wasn't an I column calculation that triggered the event.
If Z7 isn't zero some value in column I is different so the macro proceeds.

Private Sub Worksheet_Calculate()


If [Z7] = 0 Then Exit Sub


    Dim CurCel As String
    Dim cel As Range
    
On Error GoTo CleanExit:


'for relocating cursor after the copy
CurCel = Replace(ActiveCell.Address, "$", "")


Application.ScreenUpdating = False
Application.EnableEvents = False


Range("I9:I219").Copy
Range("Z9:Z219").PasteSpecial _
    Paste:=xlPasteValues, _
    Operation:=xlNone, _
    SkipBlanks:=False, _
    Transpose:=False
Application.CutCopyMode = False


For Each cel In Range("I9:I219")
    If cel.Value <> "" Then
        cel.Offset(0, 3).Value = "Overdue"
    End If
Next cel


Range(CurCel).Activate


Application.ScreenUpdating = True
Application.EnableEvents = True


Exit Sub
     
CleanExit:
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    Err.Clear


End Sub

Is changing L11 to 'Complete' (or anything else) after being automatically changed to 'Overdue' something you would normally do?
If so we'll need to address that too.

Re: Update cell based on value in another cell

Correct me if I'm mistaken, but isn't it column I the OP's wanting to fire the event?
Which won't fire Worksheet_change due to being a formula.

Re: Update cell based on value in another cell

Can you post a sample worksheet that exhibits the issue. Including the macro.

Re: VBA loop through array but skipping blank cells

deal with the data rather than the loop number
something along the lines of this

For x = 1 To y
     
    If Cells(x, WhateverColumn).value <> "" Then
        ' Get cell data...
    End If
    
Next

Re: Add new row according to the number key in

Try this... it uses an extra column to keep track of the 'previous' numbers of column A in order to know how many rows for deletions.
I've used column P as the extra.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
     
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
     
    If Not Intersect(Target, Range("M6:M10000")) Is Nothing Then
        Cancel = True 'Prevent going into Edit Mode
        prev = Cells(Target.Row, "P").Value     'col A previous number
        cur = Cells(Target.Row, "A").Value  'the new number
        If cur > prev Then
            Target.Font.Name = "Marlett"
            Target = "a"
            Target.Offset(1).Resize(cur).EntireRow.Insert
        ElseIf cur < prev Then
            Target = vbNullString
            Target.Offset(1).Resize(prev - cur).EntireRow.Delete
        End If
        ' preserve the col A values as prev
        Range("A2:A" & Cells(Rows.Count, "A").End(xlUp).Row).Copy Range("P2")
    End If
     
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    
End Sub