Posts by NoSparks

Re: New sheet date update

Glad I would help.

Re: New sheet date update

Hope this works.
I put formulas in row 6, E to J and dragged them down.
Will leave the formatting and other formulas to you.

Re: New sheet date update

See if this macro does what you're after

Sub TryThis()
    Dim sName As String
    Dim i As Integer, j As Integer
    Dim ws As Worksheet
    Dim wkEndDate As Date
    
'figure name for new sheet
For Each ws In ThisWorkbook.Sheets
  If Left(ws.Name, 2) = "PP" Then
    i = Mid(ws.Name, 3)
    j = Application.WorksheetFunction.Max(j, i)
  End If
Next ws
sName = "PP" & j + 1


'copy sheet to end of tab lineup
Worksheets("PP1").Copy after:=Sheets(Worksheets.Count)
'new sheet is automatically the active sheet
ActiveSheet.Name = sName
'figure end date for first week on time card
wkEndDate = Sheets("Summary").Range("D2") + 6 + (j * 14)
ActiveSheet.Range("C4").Value = wkEndDate
End Sub

Re: hide and protected sheet macro not working

For me, making a sheet visible does not automatically make it the active sheet. Does it for you ?

Re: Add 1 in "B" Column of Filtered Range

Assuming row 1 is headers, something along the lines of this ?

For Each cel In Range("B2:B5000").SpecialCells(xlCellTypeVisible)
  cel.Value = cel.Value + 1
Next cel

Re: Vb code for a drop list before sheet opens up.

You could use Worksheet_Activate to bring up a user form.

Private Sub Worksheet_Activate()
    UserForm1.Show
End Sub

Use ListBox1_Change to write to A1 and close the user form

Private Sub ListBox1_Change()
    Range("A1").Value = ListBox1.Value
    Unload Me
End Sub

See the attached example when activating sheet 3.

Re: Saving a file to same file name

Here's your file I used.

forum.ozgrid.com/index.php?attachment/70149/

Re: Saving a file to same file name

If I follow what you're wanting, and I'm not sure I do,
maybe adding a sub to a standard module

Public Sub Save_Right_Away()
    ThisWorkbook.Save
End Sub

and adding an instruction to the Workbook_Open sub that will run that sub 3 seconds after the workbook opens ?

Application.OnTime Now + TimeValue("00:00:03"), "Save_Right_Away"

Re: Insert missing time in between rows

After removing the stray items down around row 4500...
try this on your test file

Sub test()
    Dim i As Long, x As Integer
    Dim thisTime As Date, prevTime As Date
    
For i = Range("A" & Rows.Count).End(xlUp).Row To 3 Step -1


    thisTime = Cells(i, 1)
    prevTime = Cells(i - 1, 1)
    
    x = Format((thisTime - prevTime), "s")
    
    If x > 1 Then
    
        Rows(i).Resize(x - 1).Insert
        
        With Cells(i, 1).Resize(x - 1)
            .Formula = "=r[-1]c+""0:00:01"""
            .Value = .Value
            With .Offset(, 1).Resize(x - 1)
                .Formula = "=r[-1]c"
                .Value = .Value
            End With
        End With
    End If
Next i
    
End Sub

Re: Creating Textbox when a checkbox is checked

Mot, you would really increase your chances of assistance with this if you were to post the spreadsheet instead of a picture of the spreadsheet.

Re: want to use single button to goto multiple cell (excel)

Perhaps you could use a macro along the lines of this assigned to the button

Option Explicit
Dim nextClick As Long


Sub WhereToGo()
  If nextClick = 0 Or nextClick > 3 Then nextClick = 1
  Range("D" & Choose(nextClick, 43, 59, 60)).Select
  nextClick = nextClick + 1
End Sub

Re: Count column if NOT a duplicate (Excel 2010)

The array formula the OP shows is for the question as asked.
It's not what the OP wants because of what defines a duplicate.
Duplication is determined by more than just this range and what is determined to be a duplicate is color filled orange, color index 44, via VBA not conditional formatting.

The question should have been:
Is there a formula that will count the non-blank cells in a range that are any color other than orange?

Re: Run-time error '1004'

Quote

ii. If it is "unprotected" the items at columns "BA:BE" will automatically moved to " column AY".

The sheet protection doesn't have anything to do with that, it just stops the macro from running.
Unprotect the sheet and add this

.CurrentRegion.Select
            MsgBox Selection.Address
            Exit Sub

between these two lines and see where you're actually asking for things to happen (comment them out later.)

With .Offset(1, .Columns.Count + 1).Resize(1, 5)
            
            .CurrentRegion.Clear

Re: Filter, Sort, Macro... what to do?

Hey zingle zongle, have you checked your cross posts ?

Re: Need help modifying existing macro to auto count cells

Based on your existing code, I'd do it like this

Sub AddBlankRows()
'
    Dim iRow As Long, iCol As Long
    Dim i As Integer
    Dim oRng As Range


Set oRng = Range("a1")


iRow = oRng.Row
iCol = oRng.Column
i = 1


Do
'
If Cells(iRow + 1, iCol) <> Cells(iRow, iCol) Then
    Cells(iRow + 1, iCol).EntireRow.Insert
    With Cells(iRow, iCol).Offset(1, 0)
        .Value = "Total:  " & i
        .Font.Bold = True
    End With
    i = 1
    iRow = iRow + 2
Else
    i = i + 1
    iRow = iRow + 1
End If
'
Loop While Not Cells(iRow, iCol).Text = ""
'
End Sub

Re: VBA Copy Paste Copy Loop

Try this

Sub copyLoopCopy()
    Dim lr As Long
    Dim rng As Range
    Dim cel As Range
    
With Sheets("Sheet1")
    lr = .Cells(Rows.Count, 1).End(xlUp).Row
    Set rng = .Range("A3:A" & lr)
    For Each cel In rng
        .Range("A2") = cel.Value
        cel.Offset(0, 1).Resize(1, 8) = .Range("B2:I2").Value
    Next cel
End With
End Sub

Re: Find and Replace Carriage Returns to carry out text to column

Try this little piece of code to see what characters you are really dealing with.

Sub CheckOfCharacters()
    Dim i As Integer
    Dim str As String


str = ActiveCell.Value


    For i = 1 To Len(str)
        Debug.Print Mid(str, i, 1) & "  =  " & Asc(Mid(str, i, 1))
    Next


End Sub

Re: Hide an entire row if cell in column A is not blank

I kinda liked #4 on the other forum.