button to show  week Pattern

ADRIANA P
ADRIANA P used Ask the Experts™
on
in the DDWEEK sheet

button to show  all weeks up to TODAY'S
 week would be added. as  the pattern shown

example

34W2017      B
35W2017      A
35W2017      B

follows
36W2017       A
36W2017      B
date_follow_to.xlsx
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
I need more information about what you need.
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Nevermind, I think I understand.
ADRIANA PACCOUNTING ASSISTANT

Author

Commented:
Martin
When  click the button  the weeks 1 to 52 should be added.
by years

a great way could be  enter or select the year
then it create the pattern
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

ADRIANA PACCOUNTING ASSISTANT

Author

Commented:
when i say the pattern i  mean

in this case this way

34W2017      B
35W2017      A
35W2017      B

follows
36W2017       A
36W2017      B


this file have some sample data but  
the data should be create with the button itself
Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015
Commented:
You may try something like this...

Sub DDWEEK()
Dim ws As Worksheet
Dim num As Long, i As Long, j As Long, n As Long, lr As Long
Dim numYear As Long
Dim x()

numYear = Application.InputBox("Enter the Year", "Year!", 2018, Type:=1)
If Len(numYear) <> 4 Then
    MsgBox "Enter the Year in four digits.", vbExclamation
    Exit Sub
End If

Set ws = Sheets("DDWEEK")
num = 52
ReDim x(1 To num * 2, 1 To 2)

For i = 1 To num * 2 Step 2
    n = n + 1
    For j = 1 To 1
        x(i, 1) = n & "W" & numYear
        x(i, 2) = "A"
        x(i + j, 1) = n & "W" & numYear
        x(i + j, 2) = "B"
    Next j
Next i
lr = ws.Cells(Rows.Count, 1).End(xlUp).Row
If lr > 2 Then ws.Range("A3:B" & lr).Clear
ws.Range("A3").Resize(UBound(x, 1), 2).Value = x
End Sub

Open in new window

In the attached, click the button called "Week Pattern" to run the code.
date_follow_to.xlsm
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Try this.
29120762.xlsm
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
In my workbook you don't need to choose the year.
ADRIANA PACCOUNTING ASSISTANT

Author

Commented:
Superior work.

Great  Expert !
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
Glad it worked as desired. Thanks for the feedback.
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
I'm sorry but I don't understand. In your question you said "button to show  all weeks up to TODAY'S" which since today's week is 40 that you wanted to add rows to the existing data up to 40 and then stopping there. Nerraj's code doesn't do that. It always replaces the data with a completely new years worth of data.

Lo siento pero no entiendo En su pregunta, dijo "botón para mostrar todas las semanas hasta HOY", que desde la semana de hoy es 40, usted quiso agregar filas a los datos existentes hasta 40 y luego detenerse allí. El código de Nerraj no hace eso. Siempre reemplaza los datos con un valor de años completamente nuevo.
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
The workbook that I attached had a problem if you already had a full year of data and this corrects that.
29120762a.xlsm
ADRIANA PACCOUNTING ASSISTANT

Author

Commented:
Martin

Great JOb

i explain here

c1111.PNG
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Well I didn't notice that change from your original question, but please be aware that in Neeraj's code if you already have a full year of 2018 weeks and you say the year you want is 2019, the existing weeks will be replaced by 2019 weeks rather than adding 2019 weeks to the existing data.

No noté el cambio en su pregunta original, pero tenga en cuenta que en el código de Neeraj, si ya tiene un año completo de 2018 semanas y dice que el año que quiere es 2019, las semanas existentes se reemplazarán por 2019 semanas en lugar de que agregar 2019 semanas a los datos existentes.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial