Results 1 to 3 of 3

Macro to AutoComplete but skip blanks

Threaded View

CindyGreen Macro to AutoComplete but... 10-12-2010, 01:53 PM
blane245 Re: Macro to AutoComplete but... 10-12-2010, 02:16 PM
CindyGreen Re: Macro to AutoComplete but... 10-12-2010, 02:36 PM
  1. #1
    Registered User
    Join Date
    07-16-2010
    Location
    Kinburn, Canada
    MS-Off Ver
    Excel 2003
    Posts
    4

    Macro to AutoComplete but skip blanks

    Although I searched this forum for an answer, I couldn't find anything directly applicable. I have a macro that will populate a column based on the criteria in the previous column, as follows:

    Sub AutoComplete()
    Dim rngY As Range
    Dim rng As Range
    
    Set rngY = Range("D12:D" & Range("D" & Range("A:A").Rows.Count).End(xlUp).Row)
     
    Application.ScreenUpdating = False
    Application.EnableEvents = False
     
    For Each rng In rngY
     
    If LCase(rng.Value) = "0" Or LCase(rng.Value) = "" Then
            rng.Offset(0, 1).Value = "Not Applicable"       End If
     
     If LCase(rng.Value) >= 1 Then
            rng.Offset(0, 1).Value = "Please Complete" 
          End If
    Next rng
     
    Application.ScreenUpdating = True
    Application.EnableEvents = True
     
    End Sub
    This code is great, however it populates every cell in the column, even when the adjacent cell in column D is blank. I'm hoping for an adjustment so that, if the relevant cell in column D is unpopulated or blank, the adjacent cell in column E also remains blank.

    I've overshot my macro knowledge and am getting nowhere fast - any suggestions would be appreciated.

    Cindy
    Last edited by CindyGreen; 10-12-2010 at 02:37 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