Posts by gijsmo

This is clearly an extension of the original request.

May be best to start a new thread and also include a sample spreadsheet with a mock-up of the final result you are trying to achieve.

Roy is, as always, correct !

AutoFilter is a much faster way to do this type of copying between sheets.

If you want to use that instead of looping, revised version using AutoFilter is attached.

Customer Call Plan 18-4-22 v4.xlsm

The loop in the NewCopy macro that has the "Like" statements in it is created as a series of "ElseIf"s.

This means that, for example, a Call Month value of "December / April / August" will only copy to April because April gets tested before August or December.

If you want to copy data for a Call Month value of "December / April / August" to each of the December, April & August sheets then the loop needs to be redesigned. The attached version does this. Bear in mind that this loop now takes a longer to run and it has to test each row 12 times for each month, not skip to the next row like it did before when one of the ElseIf conditions was met.

Customer Call Plan 18-4-22 v3.xlsm

There's a bunch of ways to do this. One way is in the attached revised copy of your original workbook.

I made a few other changes as well to streamline the code a bit.

I also added a Clear Data button to the Main sheet if you want the option to do this in one go - as the name implies this will clear the data from all the "Month" sheets (except the header row).

Customer Call Plan 18-4-22 v2.xlsm

The codeword is "Like", you still have "=" in the code

If c Like "*January*" Then

NOT

If c = "*January*" Then

Maybe instead of

If c = "January" Then

try

If c Like "*January*" Then

and likewise for other months

Thanks !

You're welcome.

The dynamic range called Teachers becomes invalid when you delete cell C3 since it is tied to that cell.

Maybe change the Worksheet_SelectionChange code as follows to reset that range when you delete in that column:

 'Move up teachers
  Application.EnableEvents = False
  Rng.Delete Shift:=xlUp
 'Reset the dynamic range
  ActiveWorkbook.Names("Teachers").RefersTo = "=OFFSET(Teachers!$C$3, 0, 0, COUNTA(Teachers!$C:$C))"
  Application.EnableEvents = True

Also best to add the EnableEvents False/True code otherwise you'll trigger the Worksheet_Change event whenever you delete a cell.

You're welcome

To do what I think you are trying to do, maybe move the Exit For:

Function CharFinder(str As String) As Boolean
  For I = 1 To Len(str)
   ch = Mid(str, I, 1)
   Select Case ch
    Case ",", ";", ".", "#", "*"
     CharFinder = False
     Exit For
    Case Else
     CharFinder = True
   End Select
  Next
End Function

You are welcome. Hopefully this will move you forward.

The way the code is currently written, it is only looking forward at any point in time ie, it is not looking at the whole set of data to determine the count for each row of data. This is why any countif code takes so long to run.

Your code currently provides accurate results if the rows of matching data are sequential eg rows 1935 and 1936.

This code would probably work as you require it if the data is sorted first, assuming data sorting does not interfere with anything else you may be doing.

You will also speed up processing by adding the following at the start of the macro

  With Application
    .ScreenUpdating = False: .DisplayAlerts = False: .EnableEvents = False
  End With

and the corresponding code at the end of the macro:

  With Application
    .ScreenUpdating = True: .DisplayAlerts = True: .EnableEvents = True
  End With

I suspect COUNTIF is not providing the correct answers because you have a mixture of numbers and numbers stored as text.

You should be able to work around this by forcing a formatted count. As an excel formula, this would look something like:

 =COUNTIF($A$2:$A$349397,TEXT($A12,"#"))

One way to do this in VBA is something like:

Sub AltCountIf()
  Dim x, y
  Dim i As Long
 
  With Application
    .ScreenUpdating = False: .DisplayAlerts = False: .EnableEvents = False
  End With
  
  With Sheet1.Cells(1).CurrentRegion
    'assume column A contains the data values to be counted
    x = .Columns(1).Value
    'assume column B will hold the results
    y = .Columns(2).Value 'don't really need values, will just initialise y
    
    'skip the header row and loop through to do the countifs
    For i = 2 To UBound(x, 1)
      y(i, 1) = Application.CountIf(.Columns(1), Format(x(i, 1), "#"))
    Next
    
    'write the results back to column B
    .Columns(2).Value = y
  End With

  With Application
    .ScreenUpdating = True: .DisplayAlerts = True: .EnableEvents = True
  End With
  
  MsgBox "Completed"  
End Sub

This loads the entire dataset to be counted into an array and then places the countif values into a second array.

It may be more efficient than other types of loops however with nearly 350,000 rows this is still going to be very slow.

Also, loading all the data into arrays first may be more efficient but it will be memory intensive.

If you want to test it first maybe try using a smaller loop eg:

For i = 2 to 100

The error seems to be occurring in the C:\Windows\System32 folder which is (or can be) a protected Windows folder. You may need special permissions to safe files in this folder.

Does the error occur if you save the file in a non Windows protected folder?

Glad it worked for you.

Try adding the following code as one of the last steps in the macro:

Application.CutCopyMode = False

You're welcome, if you're having date issues (particularly in VBA) it is most likely a date has been interpreted as mm dd yy.

Excel has a habit of interpreting dates as mm dd yy (even if that is not your local date setting).

You generally need to code around it, maybe try:

Private Sub CbDuration_Change()
If CbDuration.Value = "28 Days" Then
TxReview.Value = Format(DateAdd("d", 28, TxDate.Value), "dd mmm yy")
End If
If CbDuration.Value = "2 Months" Then
TxReview.Value = Format(DateAdd("m", 2, TxDate.Value), "dd mmm yy")
End If
If CbDuration.Value = "3 Months" Then
TxReview.Value = Format(DateAdd("m", 3, TxDate.Value), "dd mmm yy")
End If
TxReview_AfterUpdate
End Sub

You are welcome...all the best

When using an array with RemoveDuplicates, it needs to be a zero based integer array.

In the example file you posted there are only 11 columns, so the code snippet for this example would be:

 Dim ColumnNumbers As Variant 
 
 ReDim ColumnNumbers(0 To 7)
 ColumnNumbers = Array(1, 2, 3, 4, 6, 9, 10, 11)
 
 Set rngDataRange = ActiveSheet.Cells(1, 1).CurrentRegion
 With rngDataRange
   Select Case .Columns.Count
   Case 11
   .RemoveDuplicates Columns:=(ColumnNumbers), Header:=xlYes 
  
   End Select
 End With

Obviously, you can expand this if your actual workbook has more columns but the principal will be the same.