VBA: How to refer to the right worksheet
Posted
by stanigator
on Stack Overflow
See other posts from Stack Overflow
or by stanigator
Published on 2010-05-11T07:00:49Z
Indexed on
2010/05/11
7:04 UTC
Read the original article
Hit count: 319
Sub Macro1()
'
' Macro1 Macro
'
'
Worksheets("Drop-down").Select
n = Cells(1, 1).End(xlDown).Row
For i = 1 To n
ActiveSheet.Cells(i, 2).Select
If Worksheets("Misc").Cells(2, i).Value <> "" Then
If Worksheets("Misc").Cells(3, i).Value <> "" Then
Set validationRange = Range(Worksheets("Misc").Cells(2, i), Worksheets("Misc").Cells(2, i).End(xlDown))
Else
Set validationRange = Worksheets("Misc").Cells(2, i)
End If
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:=validationRange.Address
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
End If
Next i
End Sub
The lines after ActiveSheet.Cells(i,2).select within the for loop is not referring to the correct worksheet I want when I rechecked the settings for the validation drop-down menu. What is the easiest way of correcting this setback? Thanks in advance.
© Stack Overflow or respective owner