Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Input Box that allows user to select range | Excel Programming | |||
Trying to select a specific range based on the time value of user form input | New Users to Excel | |||
using input from user to select wk1 to wk52 | Excel Programming | |||
CODE to select range based on User Input or Value of Input Field | Excel Programming | |||
select data based on user input | Excel Programming |