Mixing
Why is my array not including prexisting drop down values?
1
I'm trying to cut a master file. Columns P and R have data in some rows, and some do not. I want to tell VBA, if there's data in that row, do not create a drop down list, but if there is no data, then create a drop down option for the manager.
How would I alter my data validation module to say if there is data already in there, ignore creating a drop down, but create a drop down option for any blank cells in those two columns
Here is my original module:
Sub DataValidation()
lastrow = ActiveWorkbook.Worksheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
With Range("P2:P" & lastrow).Validation
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, _
Formula1:="Yes - Regularly Works Eligible Shift,No - Does Not Regularly Work Eligible Shift"
.IgnoreBlank = True
.InCellDropdown = True
End With
With Range("R2:R" & lastrow).Validation
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:="8%,10%,12%,15%"
.IgnoreBlank = True
.InCellDropdown = True
End With
End Sub
excel vba excel-vba
edited Nov 20 '18 at 7:18
Pᴇʜ
20.2k42650
asked Nov 19 '18 at 15:54
nick lanta
1267
add a comment |
1
I'm trying to cut a master file. Columns P and R have data in some rows, and some do not. I want to tell VBA, if there's data in that row, do not create a drop down list, but if there is no data, then create a drop down option for the manager.
How would I alter my data validation module to say if there is data already in there, ignore creating a drop down, but create a drop down option for any blank cells in those two columns
Here is my original module:
Sub DataValidation()
lastrow = ActiveWorkbook.Worksheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
With Range("P2:P" & lastrow).Validation
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, _
Formula1:="Yes - Regularly Works Eligible Shift,No - Does Not Regularly Work Eligible Shift"
.IgnoreBlank = True
.InCellDropdown = True
End With
With Range("R2:R" & lastrow).Validation
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:="8%,10%,12%,15%"
.IgnoreBlank = True
.InCellDropdown = True
End With
End Sub
excel vba excel-vba
edited Nov 20 '18 at 7:18
Pᴇʜ
20.2k42650
asked Nov 19 '18 at 15:54
nick lanta
1267
add a comment |
1
1
1
I'm trying to cut a master file. Columns P and R have data in some rows, and some do not. I want to tell VBA, if there's data in that row, do not create a drop down list, but if there is no data, then create a drop down option for the manager.
How would I alter my data validation module to say if there is data already in there, ignore creating a drop down, but create a drop down option for any blank cells in those two columns
Here is my original module:
Sub DataValidation()
lastrow = ActiveWorkbook.Worksheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
With Range("P2:P" & lastrow).Validation
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, _
Formula1:="Yes - Regularly Works Eligible Shift,No - Does Not Regularly Work Eligible Shift"
.IgnoreBlank = True
.InCellDropdown = True
End With
With Range("R2:R" & lastrow).Validation
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:="8%,10%,12%,15%"
.IgnoreBlank = True
.InCellDropdown = True
End With
End Sub
excel vba excel-vba
edited Nov 20 '18 at 7:18
Pᴇʜ
20.2k42650
asked Nov 19 '18 at 15:54
nick lanta
1267
I'm trying to cut a master file. Columns P and R have data in some rows, and some do not. I want to tell VBA, if there's data in that row, do not create a drop down list, but if there is no data, then create a drop down option for the manager.
How would I alter my data validation module to say if there is data already in there, ignore creating a drop down, but create a drop down option for any blank cells in those two columns
Here is my original module:
Sub DataValidation()
lastrow = ActiveWorkbook.Worksheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
With Range("P2:P" & lastrow).Validation
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, _
Formula1:="Yes - Regularly Works Eligible Shift,No - Does Not Regularly Work Eligible Shift"
.IgnoreBlank = True
.InCellDropdown = True
End With
With Range("R2:R" & lastrow).Validation
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:="8%,10%,12%,15%"
.IgnoreBlank = True
.InCellDropdown = True
End With
End Sub
excel vba excel-vba
excel vba excel-vba
edited Nov 20 '18 at 7:18
Pᴇʜ
20.2k42650
asked Nov 19 '18 at 15:54
nick lanta
1267
edited Nov 20 '18 at 7:18
Pᴇʜ
20.2k42650
asked Nov 19 '18 at 15:54
nick lanta
1267
edited Nov 20 '18 at 7:18
Pᴇʜ
20.2k42650
edited Nov 20 '18 at 7:18
Pᴇʜ
20.2k42650
edited Nov 20 '18 at 7:18
Pᴇʜ
20.2k42650
20.2k42650
asked Nov 19 '18 at 15:54
nick lanta
1267
asked Nov 19 '18 at 15:54
nick lanta
1267
asked Nov 19 '18 at 15:54
nick lanta
1267
1267
add a comment |
add a comment |
2 Answers
2
active
oldest
votes
2
Try code such as the following to target only those blank cells:
Sub DataValidation()
lastrow = ActiveWorkbook.Worksheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
With Range("P2:P" & lastrow).SpecialCells(xlCellTypeBlanks).Validation
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:="Yes - Regularly Works Eligible Shift,No - Does Not Regularly Work Eligible Shift"
.IgnoreBlank = True
.InCellDropdown = True
End With
With Range("R2:R" & lastrow).SpecialCells(xlCellTypeBlanks).Validation
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:="8%,10%,12%,15%"
.IgnoreBlank = True
.InCellDropdown = True
End With
End Sub
EDIT: There is one error to trap for. If the entire Range("P2:P" & lastRow) is blank, then the code will return an error. This is because the .SpecialCells method call returns a Range object, excepting at least 1 cell.
One work around is to use error handling, and store the range into a variable. If all of the cells are blank, then they all need validation. Otherwise, only validate those cells that are blank (if you truly never foresee this happening, then you can skip this). Code below:
Sub DataValidation()
Dim rng As Range
lastrow = ActiveWorkbook.Worksheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
On Error Resume Next
Set rng = Range("P2:P" & lastrow).SpecialCells(xlCellTypeBlanks)
On Error GoTo 0
If rng Is Nothing Then
With Range("P2:P" & lastrow).Validation
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:="Yes - Regularly Works Eligible Shift,No - Does Not Regularly Work Eligible Shift"
.IgnoreBlank = True
.InCellDropdown = True
End With
Else
With rng.Validation
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:="Yes - Regularly Works Eligible Shift,No - Does Not Regularly Work Eligible Shift"
.IgnoreBlank = True
.InCellDropdown = True
End With
End If
'Reset to reuse with the R column
Set rng = Nothing
On Error Resume Next
Set rng = Range("R2:R" & lastrow).SpecialCells(xlCellTypeBlanks)
On Error GoTo 0
If rng Is Nothing Then
With Range("R2:R" & lastrow).Validation
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:="8%,10%,12%,15%"
.IgnoreBlank = True
.InCellDropdown = True
End With
Else
With rng.Validation
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:="8%,10%,12%,15%"
.IgnoreBlank = True
.InCellDropdown = True
End With
End If
End Sub
answered Nov 19 '18 at 16:00
user3561813
3,8561817
SaysRuntime error 1004 - No Cell Were Foundand is highlighting the firstWithStatement
– nick lanta
Nov 19 '18 at 16:07
is there something I can replace the.IgnoreBlank = Truepart and have it just ignore cells with data in there?
– nick lanta
Nov 19 '18 at 16:07
@nicklanta See my edit to the code. The error was being thrown because the entire range was blank. You actually left your comment while I was in the middle of editing my post.
– user3561813
Nov 19 '18 at 16:12
Ok so this worked perfectly, here's one other question: Say a cell has data in it, is there a way to keep that data, but also allow it to be changed again via drop down? Currently, this only allows blank cells to have a drop down, and doesn't allow drop down changes to be made to preexisting data
– nick lanta
Nov 19 '18 at 16:23
is that addition possible?
– nick lanta
Nov 19 '18 at 16:39
|
show 2 more comments
0
You could do something like this
With Range("I1:I" & lastrow).SpecialCells(xlCellTypeBlanks).Validation
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:="8%,10%,12%,15%"
.IgnoreBlank = True
.InCellDropdown = True
End With
answered Nov 19 '18 at 15:59
Nathan_Sav
5,4981618
SaysRuntime error 1004 - No Cell Were Foundand is highlighting the lineWith.Special Cellsline
– nick lanta
Nov 19 '18 at 16:06
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53378334%2fwhy-is-my-array-not-including-prexisting-drop-down-values%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
2
Try code such as the following to target only those blank cells:
Sub DataValidation()
lastrow = ActiveWorkbook.Worksheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
With Range("P2:P" & lastrow).SpecialCells(xlCellTypeBlanks).Validation
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:="Yes - Regularly Works Eligible Shift,No - Does Not Regularly Work Eligible Shift"
.IgnoreBlank = True
.InCellDropdown = True
End With
With Range("R2:R" & lastrow).SpecialCells(xlCellTypeBlanks).Validation
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:="8%,10%,12%,15%"
.IgnoreBlank = True
.InCellDropdown = True
End With
End Sub
EDIT: There is one error to trap for. If the entire Range("P2:P" & lastRow) is blank, then the code will return an error. This is because the .SpecialCells method call returns a Range object, excepting at least 1 cell.
One work around is to use error handling, and store the range into a variable. If all of the cells are blank, then they all need validation. Otherwise, only validate those cells that are blank (if you truly never foresee this happening, then you can skip this). Code below:
Sub DataValidation()
Dim rng As Range
lastrow = ActiveWorkbook.Worksheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
On Error Resume Next
Set rng = Range("P2:P" & lastrow).SpecialCells(xlCellTypeBlanks)
On Error GoTo 0
If rng Is Nothing Then
With Range("P2:P" & lastrow).Validation
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:="Yes - Regularly Works Eligible Shift,No - Does Not Regularly Work Eligible Shift"
.IgnoreBlank = True
.InCellDropdown = True
End With
Else
With rng.Validation
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:="Yes - Regularly Works Eligible Shift,No - Does Not Regularly Work Eligible Shift"
.IgnoreBlank = True
.InCellDropdown = True
End With
End If
'Reset to reuse with the R column
Set rng = Nothing
On Error Resume Next
Set rng = Range("R2:R" & lastrow).SpecialCells(xlCellTypeBlanks)
On Error GoTo 0
If rng Is Nothing Then
With Range("R2:R" & lastrow).Validation
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:="8%,10%,12%,15%"
.IgnoreBlank = True
.InCellDropdown = True
End With
Else
With rng.Validation
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:="8%,10%,12%,15%"
.IgnoreBlank = True
.InCellDropdown = True
End With
End If
End Sub
answered Nov 19 '18 at 16:00
user3561813
3,8561817
SaysRuntime error 1004 - No Cell Were Foundand is highlighting the firstWithStatement
– nick lanta
Nov 19 '18 at 16:07
is there something I can replace the.IgnoreBlank = Truepart and have it just ignore cells with data in there?
– nick lanta
Nov 19 '18 at 16:07
@nicklanta See my edit to the code. The error was being thrown because the entire range was blank. You actually left your comment while I was in the middle of editing my post.
– user3561813
Nov 19 '18 at 16:12
Ok so this worked perfectly, here's one other question: Say a cell has data in it, is there a way to keep that data, but also allow it to be changed again via drop down? Currently, this only allows blank cells to have a drop down, and doesn't allow drop down changes to be made to preexisting data
– nick lanta
Nov 19 '18 at 16:23
is that addition possible?
– nick lanta
Nov 19 '18 at 16:39
|
show 2 more comments
2
Try code such as the following to target only those blank cells:
Sub DataValidation()
lastrow = ActiveWorkbook.Worksheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
With Range("P2:P" & lastrow).SpecialCells(xlCellTypeBlanks).Validation
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:="Yes - Regularly Works Eligible Shift,No - Does Not Regularly Work Eligible Shift"
.IgnoreBlank = True
.InCellDropdown = True
End With
With Range("R2:R" & lastrow).SpecialCells(xlCellTypeBlanks).Validation
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:="8%,10%,12%,15%"
.IgnoreBlank = True
.InCellDropdown = True
End With
End Sub
EDIT: There is one error to trap for. If the entire Range("P2:P" & lastRow) is blank, then the code will return an error. This is because the .SpecialCells method call returns a Range object, excepting at least 1 cell.
One work around is to use error handling, and store the range into a variable. If all of the cells are blank, then they all need validation. Otherwise, only validate those cells that are blank (if you truly never foresee this happening, then you can skip this). Code below:
Sub DataValidation()
Dim rng As Range
lastrow = ActiveWorkbook.Worksheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
On Error Resume Next
Set rng = Range("P2:P" & lastrow).SpecialCells(xlCellTypeBlanks)
On Error GoTo 0
If rng Is Nothing Then
With Range("P2:P" & lastrow).Validation
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:="Yes - Regularly Works Eligible Shift,No - Does Not Regularly Work Eligible Shift"
.IgnoreBlank = True
.InCellDropdown = True
End With
Else
With rng.Validation
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:="Yes - Regularly Works Eligible Shift,No - Does Not Regularly Work Eligible Shift"
.IgnoreBlank = True
.InCellDropdown = True
End With
End If
'Reset to reuse with the R column
Set rng = Nothing
On Error Resume Next
Set rng = Range("R2:R" & lastrow).SpecialCells(xlCellTypeBlanks)
On Error GoTo 0
If rng Is Nothing Then
With Range("R2:R" & lastrow).Validation
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:="8%,10%,12%,15%"
.IgnoreBlank = True
.InCellDropdown = True
End With
Else
With rng.Validation
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:="8%,10%,12%,15%"
.IgnoreBlank = True
.InCellDropdown = True
End With
End If
End Sub
answered Nov 19 '18 at 16:00
user3561813
3,8561817
SaysRuntime error 1004 - No Cell Were Foundand is highlighting the firstWithStatement
– nick lanta
Nov 19 '18 at 16:07
is there something I can replace the.IgnoreBlank = Truepart and have it just ignore cells with data in there?
– nick lanta
Nov 19 '18 at 16:07
@nicklanta See my edit to the code. The error was being thrown because the entire range was blank. You actually left your comment while I was in the middle of editing my post.
– user3561813
Nov 19 '18 at 16:12
Ok so this worked perfectly, here's one other question: Say a cell has data in it, is there a way to keep that data, but also allow it to be changed again via drop down? Currently, this only allows blank cells to have a drop down, and doesn't allow drop down changes to be made to preexisting data
– nick lanta
Nov 19 '18 at 16:23
is that addition possible?
– nick lanta
Nov 19 '18 at 16:39
|
show 2 more comments
2
2
2
Try code such as the following to target only those blank cells:
Sub DataValidation()
lastrow = ActiveWorkbook.Worksheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
With Range("P2:P" & lastrow).SpecialCells(xlCellTypeBlanks).Validation
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:="Yes - Regularly Works Eligible Shift,No - Does Not Regularly Work Eligible Shift"
.IgnoreBlank = True
.InCellDropdown = True
End With
With Range("R2:R" & lastrow).SpecialCells(xlCellTypeBlanks).Validation
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:="8%,10%,12%,15%"
.IgnoreBlank = True
.InCellDropdown = True
End With
End Sub
EDIT: There is one error to trap for. If the entire Range("P2:P" & lastRow) is blank, then the code will return an error. This is because the .SpecialCells method call returns a Range object, excepting at least 1 cell.
One work around is to use error handling, and store the range into a variable. If all of the cells are blank, then they all need validation. Otherwise, only validate those cells that are blank (if you truly never foresee this happening, then you can skip this). Code below:
Sub DataValidation()
Dim rng As Range
lastrow = ActiveWorkbook.Worksheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
On Error Resume Next
Set rng = Range("P2:P" & lastrow).SpecialCells(xlCellTypeBlanks)
On Error GoTo 0
If rng Is Nothing Then
With Range("P2:P" & lastrow).Validation
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:="Yes - Regularly Works Eligible Shift,No - Does Not Regularly Work Eligible Shift"
.IgnoreBlank = True
.InCellDropdown = True
End With
Else
With rng.Validation
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:="Yes - Regularly Works Eligible Shift,No - Does Not Regularly Work Eligible Shift"
.IgnoreBlank = True
.InCellDropdown = True
End With
End If
'Reset to reuse with the R column
Set rng = Nothing
On Error Resume Next
Set rng = Range("R2:R" & lastrow).SpecialCells(xlCellTypeBlanks)
On Error GoTo 0
If rng Is Nothing Then
With Range("R2:R" & lastrow).Validation
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:="8%,10%,12%,15%"
.IgnoreBlank = True
.InCellDropdown = True
End With
Else
With rng.Validation
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:="8%,10%,12%,15%"
.IgnoreBlank = True
.InCellDropdown = True
End With
End If
End Sub
answered Nov 19 '18 at 16:00
user3561813
3,8561817
Try code such as the following to target only those blank cells:
Sub DataValidation()
lastrow = ActiveWorkbook.Worksheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
With Range("P2:P" & lastrow).SpecialCells(xlCellTypeBlanks).Validation
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:="Yes - Regularly Works Eligible Shift,No - Does Not Regularly Work Eligible Shift"
.IgnoreBlank = True
.InCellDropdown = True
End With
With Range("R2:R" & lastrow).SpecialCells(xlCellTypeBlanks).Validation
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:="8%,10%,12%,15%"
.IgnoreBlank = True
.InCellDropdown = True
End With
End Sub
EDIT: There is one error to trap for. If the entire Range("P2:P" & lastRow) is blank, then the code will return an error. This is because the .SpecialCells method call returns a Range object, excepting at least 1 cell.
One work around is to use error handling, and store the range into a variable. If all of the cells are blank, then they all need validation. Otherwise, only validate those cells that are blank (if you truly never foresee this happening, then you can skip this). Code below:
Sub DataValidation()
Dim rng As Range
lastrow = ActiveWorkbook.Worksheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
On Error Resume Next
Set rng = Range("P2:P" & lastrow).SpecialCells(xlCellTypeBlanks)
On Error GoTo 0
If rng Is Nothing Then
With Range("P2:P" & lastrow).Validation
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:="Yes - Regularly Works Eligible Shift,No - Does Not Regularly Work Eligible Shift"
.IgnoreBlank = True
.InCellDropdown = True
End With
Else
With rng.Validation
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:="Yes - Regularly Works Eligible Shift,No - Does Not Regularly Work Eligible Shift"
.IgnoreBlank = True
.InCellDropdown = True
End With
End If
'Reset to reuse with the R column
Set rng = Nothing
On Error Resume Next
Set rng = Range("R2:R" & lastrow).SpecialCells(xlCellTypeBlanks)
On Error GoTo 0
If rng Is Nothing Then
With Range("R2:R" & lastrow).Validation
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:="8%,10%,12%,15%"
.IgnoreBlank = True
.InCellDropdown = True
End With
Else
With rng.Validation
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:="8%,10%,12%,15%"
.IgnoreBlank = True
.InCellDropdown = True
End With
End If
End Sub
answered Nov 19 '18 at 16:00
user3561813
3,8561817
edited Nov 19 '18 at 16:11
answered Nov 19 '18 at 16:00
user3561813
3,8561817
answered Nov 19 '18 at 16:00
user3561813
3,8561817
answered Nov 19 '18 at 16:00
user3561813
3,8561817
3,8561817
SaysRuntime error 1004 - No Cell Were Foundand is highlighting the firstWithStatement
– nick lanta
Nov 19 '18 at 16:07
is there something I can replace the.IgnoreBlank = Truepart and have it just ignore cells with data in there?
– nick lanta
Nov 19 '18 at 16:07
@nicklanta See my edit to the code. The error was being thrown because the entire range was blank. You actually left your comment while I was in the middle of editing my post.
– user3561813
Nov 19 '18 at 16:12
Ok so this worked perfectly, here's one other question: Say a cell has data in it, is there a way to keep that data, but also allow it to be changed again via drop down? Currently, this only allows blank cells to have a drop down, and doesn't allow drop down changes to be made to preexisting data
– nick lanta
Nov 19 '18 at 16:23
is that addition possible?
– nick lanta
Nov 19 '18 at 16:39
|
show 2 more comments
SaysRuntime error 1004 - No Cell Were Foundand is highlighting the firstWithStatement
– nick lanta
Nov 19 '18 at 16:07
is there something I can replace the.IgnoreBlank = Truepart and have it just ignore cells with data in there?
– nick lanta
Nov 19 '18 at 16:07
@nicklanta See my edit to the code. The error was being thrown because the entire range was blank. You actually left your comment while I was in the middle of editing my post.
– user3561813
Nov 19 '18 at 16:12
Ok so this worked perfectly, here's one other question: Say a cell has data in it, is there a way to keep that data, but also allow it to be changed again via drop down? Currently, this only allows blank cells to have a drop down, and doesn't allow drop down changes to be made to preexisting data
– nick lanta
Nov 19 '18 at 16:23
is that addition possible?
– nick lanta
Nov 19 '18 at 16:39
Says
Runtime error 1004 - No Cell Were Found and is highlighting the first With Statement– nick lanta
Nov 19 '18 at 16:07
Says
Runtime error 1004 - No Cell Were Found and is highlighting the first With Statement– nick lanta
Nov 19 '18 at 16:07
is there something I can replace the
.IgnoreBlank = True part and have it just ignore cells with data in there?– nick lanta
Nov 19 '18 at 16:07
is there something I can replace the
.IgnoreBlank = True part and have it just ignore cells with data in there?– nick lanta
Nov 19 '18 at 16:07
@nicklanta See my edit to the code. The error was being thrown because the entire range was blank. You actually left your comment while I was in the middle of editing my post.
– user3561813
Nov 19 '18 at 16:12
@nicklanta See my edit to the code. The error was being thrown because the entire range was blank. You actually left your comment while I was in the middle of editing my post.
– user3561813
Nov 19 '18 at 16:12
Ok so this worked perfectly, here's one other question: Say a cell has data in it, is there a way to keep that data, but also allow it to be changed again via drop down? Currently, this only allows blank cells to have a drop down, and doesn't allow drop down changes to be made to preexisting data
– nick lanta
Nov 19 '18 at 16:23
Ok so this worked perfectly, here's one other question: Say a cell has data in it, is there a way to keep that data, but also allow it to be changed again via drop down? Currently, this only allows blank cells to have a drop down, and doesn't allow drop down changes to be made to preexisting data
– nick lanta
Nov 19 '18 at 16:23
is that addition possible?
– nick lanta
Nov 19 '18 at 16:39
is that addition possible?
– nick lanta
Nov 19 '18 at 16:39
|
show 2 more comments
0
You could do something like this
With Range("I1:I" & lastrow).SpecialCells(xlCellTypeBlanks).Validation
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:="8%,10%,12%,15%"
.IgnoreBlank = True
.InCellDropdown = True
End With
answered Nov 19 '18 at 15:59
Nathan_Sav
5,4981618
SaysRuntime error 1004 - No Cell Were Foundand is highlighting the lineWith.Special Cellsline
– nick lanta
Nov 19 '18 at 16:06
add a comment |
0
You could do something like this
With Range("I1:I" & lastrow).SpecialCells(xlCellTypeBlanks).Validation
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:="8%,10%,12%,15%"
.IgnoreBlank = True
.InCellDropdown = True
End With
answered Nov 19 '18 at 15:59
Nathan_Sav
5,4981618
SaysRuntime error 1004 - No Cell Were Foundand is highlighting the lineWith.Special Cellsline
– nick lanta
Nov 19 '18 at 16:06
add a comment |
0
0
0
You could do something like this
With Range("I1:I" & lastrow).SpecialCells(xlCellTypeBlanks).Validation
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:="8%,10%,12%,15%"
.IgnoreBlank = True
.InCellDropdown = True
End With
answered Nov 19 '18 at 15:59
Nathan_Sav
5,4981618
You could do something like this
With Range("I1:I" & lastrow).SpecialCells(xlCellTypeBlanks).Validation
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:="8%,10%,12%,15%"
.IgnoreBlank = True
.InCellDropdown = True
End With
answered Nov 19 '18 at 15:59
Nathan_Sav
5,4981618
answered Nov 19 '18 at 15:59
Nathan_Sav
5,4981618
answered Nov 19 '18 at 15:59
Nathan_Sav
5,4981618
answered Nov 19 '18 at 15:59
Nathan_Sav
5,4981618
5,4981618
SaysRuntime error 1004 - No Cell Were Foundand is highlighting the lineWith.Special Cellsline
– nick lanta
Nov 19 '18 at 16:06
add a comment |
SaysRuntime error 1004 - No Cell Were Foundand is highlighting the lineWith.Special Cellsline
– nick lanta
Nov 19 '18 at 16:06
Says
Runtime error 1004 - No Cell Were Found and is highlighting the line With.Special Cells line– nick lanta
Nov 19 '18 at 16:06
Says
Runtime error 1004 - No Cell Were Found and is highlighting the line With.Special Cells line– nick lanta
Nov 19 '18 at 16:06
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53378334%2fwhy-is-my-array-not-including-prexisting-drop-down-values%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
