Input box to allow user to select column
 

I am trying to create a prompt that will allow the user to select the
column by asking them to click which month they wish to update.... The
names of the month are in Row 1 starting at column B. The
information for each month will be filled down each row for that
column. What are some ways to code this and then add an offset to
allow a copy/paste scheme I have created to be pasted in. The column
the user selects would be the Month.. i.e. B, C, D, E, F.... L, M.


so Range on mSheet in this instance would be C for February:

I would need the input box month selection to of mCol offset(2,0) to
drop 2 rows to begin the first name and then would offset(1,0) 4 times
and resave mCol till i reached the last row for that person

Then it would offset(3,0) to 3 rows when I reach a new name and then
resave that to the value of mCol

How would I replace mCol select to offset then resave mCol?


mSheet.Select
Range(mCol).Select
Sheets("Employees").Select
Range("C2").Select
Selection.Copy
mSheet.Select
Range("C3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Sheets("Employees").Select
Range("D2").Select
Selection.Copy
mSheet.Select
Range("C4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Sheets("Employees").Select
Range("E2").Select
Selection.Copy
mSheet.Select
Range("C5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Sheets("Employees").Select
Range("F2").Select
Selection.Copy
mSheet.Select
Range("C6").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

Input box to allow user to select column
 

Dim rng as Range, r as Range
On error Resume Next
set rng = Application.InputBox("Select Month in row 1",type:=8)
On Error goto 0
if rng is nothing then
Msgbox "You hit cancel"
exit sub
End if

if rng.count 1 or rng.row < 1 or rng.column = 1 then
msgbox "Bad Selection"
exit sub
End if

set r = rng.offset(1,0).Resize(10,1)


.. . .

You should not have data validation which uses the "Formula Is" form of data
validation as there is a bug associated with this and the
application.Inputbox function.



--
Regards,
Tom Ogilvy




"Marcusdmc" wrote:

I am trying to create a prompt that will allow the user to select the
column by asking them to click which month they wish to update.... The
names of the month are in Row 1 starting at column B. The
information for each month will be filled down each row for that
column. What are some ways to code this and then add an offset to
allow a copy/paste scheme I have created to be pasted in. The column
the user selects would be the Month.. i.e. B, C, D, E, F.... L, M.


so Range on mSheet in this instance would be C for February:

I would need the input box month selection to of mCol offset(2,0) to
drop 2 rows to begin the first name and then would offset(1,0) 4 times
and resave mCol till i reached the last row for that person

Then it would offset(3,0) to 3 rows when I reach a new name and then
resave that to the value of mCol

How would I replace mCol select to offset then resave mCol?


mSheet.Select
Range(mCol).Select
Sheets("Employees").Select
Range("C2").Select
Selection.Copy
mSheet.Select
Range("C3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Sheets("Employees").Select
Range("D2").Select
Selection.Copy
mSheet.Select
Range("C4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Sheets("Employees").Select
Range("E2").Select
Selection.Copy
mSheet.Select
Range("C5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Sheets("Employees").Select
Range("F2").Select
Selection.Copy
mSheet.Select
Range("C6").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

Input box to allow user to select column
 

On Sep 26, 9:18 am, Tom Ogilvy
wrote:
Dim rng as Range, r as Range
On error Resume Next
set rng = Application.InputBox("Select Month in row 1",type:=8)
On Error goto 0
if rng is nothing then
Msgbox "You hit cancel"
exit sub
End if

if rng.count 1 or rng.row < 1 or rng.column = 1 then
msgbox "Bad Selection"
exit sub
End if

set r = rng.offset(1,0).Resize(10,1)

. . .

You should not have data validation which uses the "Formula Is" form of data
validation as there is a bug associated with this and the
application.Inputbox function.

--
Regards,
Tom Ogilvy



"Marcusdmc" wrote:
I am trying to create a prompt that will allow the user to select the
column by asking them to click which month they wish to update.... The
names of the month are in Row 1 starting at column B. The
information for each month will be filled down each row for that
column. What are some ways to code this and then add an offset to
allow a copy/paste scheme I have created to be pasted in. The column
the user selects would be the Month.. i.e. B, C, D, E, F.... L, M.

so Range on mSheet in this instance would be C for February:

I would need the input box month selection to of mCol offset(2,0) to
drop 2 rows to begin the first name and then would offset(1,0) 4 times
and resave mCol till i reached the last row for that person

Then it would offset(3,0) to 3 rows when I reach a new name and then
resave that to the value of mCol

How would I replace mCol select to offset then resave mCol?

mSheet.Select
Range(mCol).Select
Sheets("Employees").Select
Range("C2").Select
Selection.Copy
mSheet.Select
Range("C3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Sheets("Employees").Select
Range("D2").Select
Selection.Copy
mSheet.Select
Range("C4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Sheets("Employees").Select
Range("E2").Select
Selection.Copy
mSheet.Select
Range("C5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Sheets("Employees").Select
Range("F2").Select
Selection.Copy
mSheet.Select
Range("C6").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False- Hide quoted text -

- Show quoted text -

Thank you! That went a long way towards getting the results I needed!

-Marcus