Posts by tqv1969

Re: Convert schedule into database

Quote from mhabib

Hi tqv1969,

I tried the following code in your workbook and it seemed to work fine.

Code

Sub ScheduleToDatabase()
    Dim rngcell As Range
    Sheets("Database").Activate
    Range("A4", Range("A4").SpecialCells(xlCellTypeLastCell)) _
        .ClearContents
    
    Sheets("Schedule").Activate
    For Each rngcell In Range _
        ("B4", Range("B4").SpecialCells(xlCellTypeLastCell))
        
        If Not IsEmpty(rngcell) Then
            With Sheets("Database").Range("A65536").End(xlUp).Offset(1, 0)
                .Offset(0, 0).Value = Cells(rngcell.Row, 1).Value
                .Offset(0, 1).Value = Cells(3, rngcell.Column).Value
                .Offset(0, 2).Value = rngcell.Value
            End With
        End If
    Next
End Sub

Display More

HTH

m

Display More

Hi,
One more thing is they some time change the schedule by some spots so how can I input just those changes and keep those didn't change in the final database- Means the program will note the cancelled spot but keep in the database for future checking or note the "changed version" in case of changing of version of spot only? You can see in the example final result for this case in file Book3-Change.xls in Zip file.

Re: Convert schedule into database

Quote from mhabib

Hi tqv1969,

I tried the following code in your workbook and it seemed to work fine.

Code

Sub ScheduleToDatabase()
    Dim rngcell As Range
    Sheets("Database").Activate
    Range("A4", Range("A4").SpecialCells(xlCellTypeLastCell)) _
        .ClearContents
    
    Sheets("Schedule").Activate
    For Each rngcell In Range _
        ("B4", Range("B4").SpecialCells(xlCellTypeLastCell))
        
        If Not IsEmpty(rngcell) Then
            With Sheets("Database").Range("A65536").End(xlUp).Offset(1, 0)
                .Offset(0, 0).Value = Cells(rngcell.Row, 1).Value
                .Offset(0, 1).Value = Cells(3, rngcell.Column).Value
                .Offset(0, 2).Value = rngcell.Value
            End With
        End If
    Next
End Sub

Display More

HTH

m

Display More

Wow that is really great. Thank you so much. You are my god.
1- I tried to apply into my detail schedule by modifying the code to get more information but I don't know how to put the name of brand, period for those records.
2- You can see the name of station column. If I input the name for each row in the schedule I can get them in database but not in case of VTV3 they just apply for the row with station name only. How can I just type one row but the data will have all like HTV7 result case?
I attached the file in detail for your review and format of the result I'like to have. Please help. The file is Book3-More Detail.xls
3- One other issue is the file like that is a lot from many others send to me. How can I do it automatically?
Thank for your help:)

Re: Convert schedule into database

Quote from JF

How/Where do you get the data from initially? is the spreadsheet something that you create or do you receive it from somewhere else?

Dear Sir,
I've got the sheet from others and my duty is to input into database for my boss making more analysis. They are a lots!
Usually they will prepare those sheet and print them out to send to media owner and they send to me those sheet in file.
Thank for your attention.
Best regards,
TQV

Re: Convert schedule into database

Quote from royUK

Welcome to ozgrid
Can you explain a little more fully what your schedule is and how you want to achieve the database.

Dear RoyUK,

Do you get my message?

Re: Convert schedule into database

Quote from royUK

Welcome to ozgrid
Can you explain a little more fully what your schedule is and how you want to achieve the database.

Dear Roy,
The schedule in the schedule sheet is the spot of advertising that I want to put on one station at that date and time. And I've got two different version of advertising commercials(called TVC - television commercial) named A and B.
Usually I send that schedule to station for their preparation to air my TVC but I need to input those paper sheet into that database for internal management. I did that currently manually and it take so long for eg. some thoudsands spot(TVC) per month.
That is the reason I would like to do that automatically from schedule into database to save time.

Thank for your attention to this topic.
Best regards,
TQV1969

:rock: Dear all,
Please help me to convert the schedule into the database with column and row. In the file I attached you can see the sample of schedule and the database format that I would like to have from that schedule.
Many thanks!!!