Results 1 to 3 of 3

changing range to range in row immediately below in a loop

Threaded View

DFrank231 changing range to range in... 12-14-2011, 09:02 PM
rylo Re: changing range to range... 12-14-2011, 09:23 PM
DFrank231 Re: changing range to range... 12-14-2011, 11:40 PM
  1. #1
    Registered User
    Join Date
    11-17-2011
    Location
    Columbus, OH
    MS-Off Ver
    Excel 2007
    Posts
    43

    changing range to range in row immediately below in a loop

    my objective is to do data validation for all cells in columns B through E in which there is a value in column A. i am able to get it to do the first row, but when it comes to changing the range to be used (B:E) i cannot seem to get it to adjust for the row that it is in. i think the fix is relatively simple i just cannot think of it. any ideas?

    Dim i, n As Integer
    
    n = Range("A:A").Cells.SpecialCells(xlCellTypeConstants).Count
    n = n - 1
    
    Range("B2").Select
    
    Dim ran As Range
    
    
        Do While IsEmpty(ActiveCell.Offset(0, -1)) = False
    
          With ActiveCell.Validation
            .Delete
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
            xlBetween, Formula1:="=$N$2:$N$3"
            .IgnoreBlank = True
            .InCellDropdown = True
            .InputTitle = ""
            .ErrorTitle = ""
            .InputMessage = ""
            .ErrorMessage = ""
            .ShowInput = True
            .ShowError = True
             End With
        
        Selection.AutoFill Destination:=Range("B:E"), Type:=xlFillDefault
        ActiveCell.Offset(1, 0).Select
    
        Loop
    Last edited by DFrank231; 12-14-2011 at 11:40 PM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1