Posts by gijsmo

Maybe try:

  Columns("A:A").Replace What:="Example Text", Replacement:="AS & TNT", _
    LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
    SearchFormat:=False, ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2

Based on the description, E7 is the date and E9 appears to be the value for a full month as you want the full value of E9 if it's the end of the month.

If so then without C13 you can determine the fraction of the month passed using just the date value in E7 with the following:

=DAY(E7)/DAY(EOMONTH(E7,0))

When the date is the end of the month, that formula will result in a value of 1.

Then all you need to do is multiply that factor by E9:

=DAY(E7)/DAY(EOMONTH(E7,0))*E9

To get the value proportionate to the number of days in the month relative to the date in E7.

You could extend this slightly if you want to round the resulting value to 2 decimal places with the following:

=ROUND(DAY(E7)/DAY(EOMONTH(E7,0))*E9,2)

As this question seems to come up quite often, I creted a generic file copy/merger application in Excel some time ago.

The attached file can merge multiple Excel or CSV files into a single output Excel file.

You can specify one of more sheets to be copied (for Excel files).

You can also specify if you only want to copy the header from the first workbook.

The interface should be fairly self-explanatory.

MultiWorkbookCopy v3.xlsm

If you invert your lookup table, you could get the result you are after with an INDEX/MATCH formula:

I'm still not entirely sure what would be causing this behaviour on one PC but I'm glad you were able to get it working in another location.

Thanks for the kind words Bikke, glad we were able to help.

When you select a cell on my last workbook can you see what conditional formatting is associated with the cell eg:

And can you also confirm that this bit of code in the Worksheet_SelectionChange sub in the Existencia module is being executed when you select a new cell (put a breakpoint in the code):

Maybe step through the DeleteTempCF code as well to see if this line is being executed:

Other than that I remain stumped, if it is related to your language version of Excel I do not know how to fix it.

I don't know either Bikke - it works for me 🙂

In the Worksheet_SelectionChange sub the following line should exist:

.FormatConditions.Add Type:=xlExpression, Formula1:="=-1"

And it seems to be working because you are getting yellow cells.

These yellow cells are "restored" to their original colour when the DeleteTempCF sub is called in Módulo1 by this code:

        If .FormatConditions(LoopCounter).Formula1 = "=-1" Then
            .FormatConditions(LoopCounter).Delete
        End If

This is not language dependent.

If both these bits of code exist, it should in theory work for you.

I have uploaded the workbook again here for completeness.

Existencia.Geral_V1_2023_V4.xls

Of course cytop! A much simpler solution.

Revised version attached.

Existencia.Geral_V1_2023_V4.xls

Hi Bikke, as per my previous message:

Quote

...the code would need to be changed to reflect this - the sub DeleteTempCF in Module 1 has "TRUE" in the code as does the Worksheet_SelectionChange sub in the sheet code.

It looks like you have not modified the DeleteTempCF sub in your version.

If I have done this correctly, the attached updated version will check the language code when you open the workbook and will modify the 2 subs for the Portuguese language (based on the this article). You may be able to use this code for other similar projects where Excel VBA is language specific.

Existencia.Geral_V1_2023_V4.xls

No problem Bikke, I had a feeling it might be language related but I wasn't sure how that would affect the code.

I'm intrigued that the word "TRUE" has to be replaced with "VERDADEIRO" in your version of Excel.

Glad you worked it out, obviously the code would need to be changed to reflect this - the sub DeleteTempCF in Module 1 has "TRUE" in the code as does the Worksheet_SelectionChange sub in the sheet code.

I cannot explain that Bikke - have you moved to other cells to see if there is any difference eg, I can see:

Maybe also try changing the value of the colour in the following line of code in the Worksheet_SelectionChange sub:

.FormatConditions(1).Interior.Color = 13434879

I'm not sure what you're seeing Bikke, I specifically coded it to have the yellow background colour that you had in your original example:

I have added cytop's example code into the previous workbook - this file is attached below.

I have made some minor changes in line with how the original workbook was operating.

Also, I added some code that removes all the temporary conditional formatting in the Workbook_BeforeClose sub. This means the DeleteTempCF sub has been moved to Module 1. So all temporary conditional formatting is removed before the workbook is closed (and re-saved if required).

Existencia.Geral_V1_2023_V2.xls

If it's just the same colour in each cell then you should only need to change the following code:

  lTarget.Interior.ColorIndex = 0

The ColorIndex value would then just need to be changed to whatever that single colour is.

It is theoretically possible and would be relatively straightforward if all the original background colours were the same.

One way would maybe be to create a matrix of all the cell colours in the table when the workbook is opened and then use that as a reference when restoring the background colour.

However, you also seem to have conditional formatting some of which changes the background colour and this will probably override your Worksheet_SelectionChange code ie, you won't see the colour change in the cell you select if the conditional formatting rule applies to the cell.

You are welcome.

Try changing the line of code from:

arr(C, n) = Format(CDate(ar1(R, C)), "dd/mm/yyyy")

to:

arr(C, n) = CLng(DateSerial(Year(ar1(R, C)), Month(ar1(R, C)), Day(ar1(R, C))))

and maybe add some formatting eg, you already have formatting for column C so add for column E as well:

    ws2.Columns("C:C").NumberFormat = "@"
    ws2.Columns("E:E").NumberFormat = "dd/mm/yyyy"

Hi Bikke - taking a quick look at your workbook, the problem I think is being caused by the worksheet Existencia being protected.

Your code works fine if the sheet is not protected. Assuming your sheet protection password is "blank", the following should work:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Unprotect ""

    If Not lTarget Is Nothing Then
        lTarget.Interior.ColorIndex = 0
    End If

    Target.Interior.ColorIndex = 19

    Set lTarget = Target
    
    Protect ""
End Sub

Maybe try the following (your AppointmentWeekday function will not be required):

Function IsReminderNeeded(appointmentDate As Date) As Boolean
  Dim dRemDate As Date
  
  Select Case Weekday(appointmentDate - 2, vbMonday)
    Case vbSaturday, vbSunday
      dRemDate = appointmentDate - 4
    Case Else
      dRemDate = appointmentDate - 2
  End Select

  IsReminderNeeded = Int(dRemDate) = Int(Now)
End Function

Thanks Bikke, my example was wrong - I meant to say 1-May to 30-Jun.

However, the macro will now start from the 1st day of the month specified in the Início value and finish on the last day of the month specified in the Fim value - this could be 1 month, 2 months or more, you can decide how many months to use.