asked on

INSERT A ROW IN EXCEL SPREADSHEET DEPENDING ON CRITERIA

Hi,

Attached is a sample of the data that I´m working with....is a monthly store's sales resume.

Columna A have the date of sale ...column B have the invoice number...the other columns have details of the sale....one day could have more than one sale or none at all.

What I need is when I press a command button the code inserts a row when there is a day without sale, writing in column C: "NO SALE" ... and when is saturday or sunday (Shop closed) the code have to insert a row for each non workable day and write in column C: "NO WORKING DAY".

In the document attached, in sheet "hoja1" is how I have the data actually and in sheet "hoja2" is how I need it (The final result after the code runs)

I really appreciate your help with this.

Thank you for your time.
Roberto.
SAMPLE.xls

Dave

I have used this code on the third sheet with a button
It will prompt you for a start date which can be earlier than the 2-Feb-09 value (ie to insert your 1-Feb-09 value)
Cheers
Dave

Option Explicit
 
Sub InsertEm()
    Dim rng1 As Range, FirstDate As Date, i As Long
    Set rng1 = Range([a7], Cells(Rows.Count, "A").End(xlUp))
    Application.ScreenUpdating = False
    FirstDate = Application.InputBox("Pls enter start date", Default:=Format([a7].Value, "dd-mmm-yyyy"), Type:=1)
 
    For i = rng1.Rows.Count To 2 Step -1
        If Day(rng1.Cells(i).Value) - Day(rng1.Cells(i).Offset(-1, 0).Value) > 1 Then
            With rng1.Cells(i).Offset(-1, 0)
                .EntireRow.Offset(1, 0).Insert
                .Offset(1, 0).NumberFormat = "dd/mm/yyyy"
                .Offset(1, 0).EntireRow.Font.Bold = True
                .Offset(1, 0).Value = rng1.Cells(i + 1).Value - 1
                If Weekday(.Offset(1, 0).Value) = 7 Or Weekday(.Offset(1, 0).Value) = 6 Then
                    .Offset(1, 0).Offset(0, 2) = "NO WORKING DAY"
                Else
                    .Offset(1, 0).Offset(0, 2) = "NO SALE"
                End If
                i = i + 1
            End With
        End If
    Next
    i = 0
    If FirstDate < Format([a7].Value, "dd-mmm-yyyy") Then
 
        For i = Day([a7].Value) - Day(FirstDate) To 1 Step -1
            Range("a7").Rows.EntireRow.Insert
            Range("A7").Offset(i - 1, 0).Value = Range("A7").Offset(i, 0).Value - 1
            If Weekday(Range("A7").Offset(i - 1, 0).Value) = 7 Or Weekday(Range("A7").Offset(i - 1, 0).Value) = 6 Then
                Range("A7").Offset(i - 1, 0).Offset(0, 2) = "NO WORKING DAY"
            Else
                Range("A7").Offset(i - 1, 0).Offset(0, 2) = "NO SALE"
            End If
        Next
    End If
    Application.ScreenUpdating = True
End Sub

Open in new window

SAMPLE-1----djb.xls

Pabilio

ASKER

Hi Dave,

The code inserts the rows perfectly.

The problem is with the sentences "NO WORKING DAY" and "NO SALE".

In February 1 (which was Sunday) the code wrote "NO SALE". instead of NO WORKING DAY.
In February 6 (Friday) the code wrote "NO WORKING DAY" instead of NO SALE.
saturday 7 is ok "NO WORKING DAY"
Sunday 8 wrote "NO SALE" and then the next weekends, it does the same (looks like the code does not recognize Sunday as a NO working day).

With the exception of friday 6 when the code wrote NO WORKING DAY it manages fine the regular weekdays with the NO SALE sentence.

Thank you very much for your help.

Regards,
Roberto.

ASKER CERTIFIED SOLUTION

Dave

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

Pabilio

ASKER

Thank you Dave, it works perfectly.
I really appreciate your help.
Regards,
Roberto.

Dave

No probs, thx for the grade Roberto :)

Pabilio

ASKER

Hi Dave,

hope you read this....

I got a problem when running the code in the original spreadsheet....could you check it ?

This is the new tread:
https://www.experts-exchange.com/questions/24242499/Help-fixing-VB-Code-in-EXCEL-spreadsheet-is-not-running-when-using-formulas-instead-of-values.html

Regards,
Roberto.