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

Filed under:
|
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

Related posts about vba

Related posts about worksheet