Hi all,

I have a problem and don't know how to fix it.
I will explain what i want to do with the macro.

I have a form where users can fill in a beginweek and an endweek.
Now i want excel to place the beginweek in cell B10 and the next wee
in cell C10 and so on. After excel did that i want excel to delete al
the columns behind the endweek.

Example: beginweek is week 10 and endweek is week 21. So cell B1
should be 10, cell C10 should be 11, cell C12 should be 12 and so o
till cell M10 is 21.
Excel then should delete every column behind column M, so from colum
N.

Is this possible with a macro??

Then, is it also possible when i have a beginweek of week 48 and i
ends in week 5 of the next year...to have it automatically, after wee
52 or 53, start with week 1? I hope this is possible.

Many thanks in advance!!!

Greets Han

--
Hans
-----------------------------------------------------------------------
Hans_'s Profile: http://www.excelforum.com/member.php...fo&userid=2561
View this thread: http://www.excelforum.com/showthread.php?threadid=39031

Dim b as Long, c as Long
Dim i as Long
b = clng(Userform2.BeginWeek.Text)
c = clng(Userform2.EndWeek.Text)

i = 0
do
cells(10,2 + i).Value = b
b = b + 1
if b 52 then b = 1
i = i + 1
loop until b = c
set rng = Range(cells(10,2+i),cells(10,256))
rng.Entirecolumn.Delete

Test this on a copy of your worksheet.

--
Regards,
Tom Ogilvy

"Hans_" wrote in
message ...

Hi all,

I have a problem and don't know how to fix it.
I will explain what i want to do with the macro.

I have a form where users can fill in a beginweek and an endweek.
Now i want excel to place the beginweek in cell B10 and the next week
in cell C10 and so on. After excel did that i want excel to delete all
the columns behind the endweek.

Example: beginweek is week 10 and endweek is week 21. So cell B10
should be 10, cell C10 should be 11, cell C12 should be 12 and so on
till cell M10 is 21.
Excel then should delete every column behind column M, so from column
N.

Is this possible with a macro??

Then, is it also possible when i have a beginweek of week 48 and it
ends in week 5 of the next year...to have it automatically, after week
52 or 53, start with week 1? I hope this is possible.

Many thanks in advance!!!

Greets Hans


--
Hans_
------------------------------------------------------------------------
Hans_'s Profile:
http://www.excelforum.com/member.php...o&userid=25618
View this thread: http://www.excelforum.com/showthread...hreadid=390317

Thnx Tom, it works almost perfect!!

But, when i fill in an EndWeek of 4

--
Hans
-----------------------------------------------------------------------
Hans_'s Profile: http://www.excelforum.com/member.php...fo&userid=2561
View this thread: http://www.excelforum.com/showthread.php?threadid=39031

Thnx Tom, it works almost perfect!!

But, when i fill in an EndWeek of 48 it deletes from week 48, so the
last week i see is week 47.

Something you know i can do about that?


--
Hans_
------------------------------------------------------------------------
Hans_'s Profile: http://www.excelforum.com/member.php...o&userid=25618
View this thread: http://www.excelforum.com/showthread...hreadid=390317

Dim b as Long, c as Long
Dim i as Long
b = clng(Userform2.BeginWeek.Text)
c = clng(Userform2.EndWeek.Text)

i = 0
do
cells(10,2 + i).Value = b
b = b + 1
if b 52 then b = 1
i = i + 1
loop until b = c
' change 2 to 3 then
set rng = Range(cells(10,3+i),cells(10,256))
rng.Entirecolumn.Delete

--
Regards,
Tom Ogilvy

"Hans_" wrote in
message ...

Thnx Tom, it works almost perfect!!

But, when i fill in an EndWeek of 48 it deletes from week 48, so the
last week i see is week 47.

Something you know i can do about that?


--
Hans_
------------------------------------------------------------------------
Hans_'s Profile:
http://www.excelforum.com/member.php...o&userid=25618
View this thread: http://www.excelforum.com/showthread...hreadid=390317

Hi Tom,

No, this didn't work either. Now i didn't see a number in the las
cell, so i didn't see EndWeek.

But i found out how it works:

Dim b As Long, c As Long
Dim i As Long
b = CLng(UserForm1.TextBox15.Text)
c = CLng(UserForm1.TextBox16.Text)

i = 0
Do
Cells(10, 2 + i).Value = "Week " & b
b = b + 1
If b 52 Then b = 1
i = i + 1
Loop Until b = c + 1
Set rng = Range(Cells(10, 2 + i), Cells(10, 256))
rng.EntireColumn.Delete

So, at Loop Until i said b= c + 1. That works perfect!!

Thanks for your help!

--
Hans
-----------------------------------------------------------------------
Hans_'s Profile: http://www.excelforum.com/member.php...fo&userid=2561
View this thread: http://www.excelforum.com/showthread.php?threadid=39031