Need VBA stript for column selection

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Chris D - 25 May 2008 16:28 GMT

I have a variable named " number", which can range from 1 to 10( entered via
inputbox
Now I need a VBA script which, depending on the value of " number", selects
a series of columns. Example: if "number" =1 than columns T:Z to be selected,
if "number"= 2 than columns U: Z to be selected, if 3 than V:Z to be selected
VBA code I have so far:
Sub Calculationstart()

number = InputBox("How Many Days in this Report??")
Range("U2").Select
ActiveCell.FormulaR1C1 = Daynumber

'Columns("T:Z").Select
' ActiveWindow.SmallScroll Down:=-63

Can anyone help me, maybe function IF can be used but I have not done this
before.

thanks

Reply to this Message

Gary Keramidas - 25 May 2008 16:33 GMT

maybe this will gt you started:

Sub test()
Dim result As Long

result = Application.InputBox("Enter Number of Days", "Days in Report")

Select Case result
Case 1
Columns("T:Z").Select
Case 2
Columns("U:Z").Select
Case 3
Columns("V:Z").Select
Case Else
MsgBox "please enter a valied number"
End Select

End Sub

Signature

Gary

>I have a variable named " number", which can range from 1 to 10( entered via
> inputbox
[quoted text clipped - 15 lines]
>
> thanks

Reply to this Message

Gary Keramidas - 25 May 2008 16:49 GMT

sorry about the spelling errors<g>

Signature

Gary

> maybe this will gt you started:
>
[quoted text clipped - 35 lines]
>>
>> thanks

Reply to this Message

Don Guillett - 25 May 2008 17:16 GMT

try
Sub selectcolums()
mc = InputBox("enter number") + 19
Range(Cells(1, mc), Cells(1, "z")).EntireColumn.Select
End Sub

Signature

Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com

>I have a variable named " number", which can range from 1 to 10( entered
>via
[quoted text clipped - 19 lines]
>
> thanks

Reply to this Message

Rick Rothstein (MVP - VB) - 25 May 2008 17:32 GMT

I had thought about posting code something like that, but I didn't because I
wasn't sure what was supposed to happen when mc equaled 8, 9 or 10.

Rick

> try
> Sub selectcolums()
[quoted text clipped - 25 lines]
>>
>> thanks

Reply to this Message

Don Guillett - 25 May 2008 18:00 GMT

Rick,
Guess we'll have to have the OP let us know
Happy Indy day.

Signature

Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com

>I had thought about posting code something like that, but I didn't because
>I wasn't sure what was supposed to happen when mc equaled 8, 9 or 10.
[quoted text clipped - 30 lines]
>>>
>>> thanks

Reply to this Message

Chris D - 25 May 2008 18:35 GMT

Thanks guys, this works.
Amazing what VBA can do, and will save us a lot of time. Now we only have to
press a button( once I worked it all out) instead of copy-pasting for days to
get a summary of dayreports.

Thanks!

Chris

> try
> Sub selectcolums()
[quoted text clipped - 24 lines]
> >
> > thanks

Reply to this Message

Don Guillett - 25 May 2008 18:41 GMT

Post your final code for comments

Signature

Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com

> Thanks guys, this works.
> Amazing what VBA can do, and will save us a lot of time. Now we only have
[quoted text clipped - 36 lines]
>> >
>> > thanks

Reply to this Message