Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 11-14-2011, 04:58 AM
Hwyn Hwyn is offline Vba, Is there a better way to test empty cells? Windows Vista Vba, Is there a better way to test empty cells? Office 2007
Novice
Vba, Is there a better way to test empty cells?
 
Join Date: Nov 2011
Location: Cardiff
Posts: 5
Hwyn is on a distinguished road
Default Vba, Is there a better way to test empty cells?

Hello everyone, I have some working Excel 2007 code, but i was wondering if there is a shorter way of testing consecutive cells in a row to see if they are empty rather than testing each cell individually, typically columns "J" to "Q" .This is my first post, apologies if i haven't structured my question properly.



Sub
Dim rng As Range, cell As Range
LastRow = Cells(Rows.Count, "a").End(xlUp).Row
Set rng = Range("a2:a" & LastRow)
For Each cell In rng
If cell.Value Like "*FUJ*" And _
IsEmpty(Cells(cell.Row, "j")) And IsEmpty(Cells(cell.Row, "k")) And _
IsEmpty(Cells(cell.Row, "l")) And IsEmpty(Cells(cell.Row, "m")) And _
IsEmpty(Cells(cell.Row, "n")) And IsEmpty(Cells(cell.Row, "o")) And _
IsEmpty(Cells(cell.Row, "p")) And IsEmpty(Cells(cell.Row, "q")) = True Then
Cells(cell.Row, "u").Value = "Out of Stock"
Else
Cells(cell.Row, "u").Value = "In Stock"
End If
Next
End Sub

Regards Robert
Reply With Quote
  #2  
Old 11-14-2011, 05:46 AM
macropod's Avatar
macropod macropod is offline Vba, Is there a better way to test empty cells? Windows 7 64bit Vba, Is there a better way to test empty cells? Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

Hi Robert,

The following should be more efficient:
Code:
Sub Test()
Application.ScreenUpdating = False
Dim LastRow As Long, Rng As Range, oCel As Range, i As Long, bState As Boolean
LastRow = Cells(Rows.Count, "a").End(xlUp).Row
Set Rng = Range("a2:a" & LastRow)
For Each oCel In Rng
  With oCel
    bState = True
    If .Value Like "*FUJ*" Then
      For i = 10 To 18
        If Not IsEmpty(Cells(.Row, i)) Then
          bState = False
          Exit For
        End If
      Next
    End If
    With Cells(.Row, "u")
      If bState = True Then
        .Value = "Out of Stock"
      Else
        .Value = "In Stock"
      End If
    End With
  End With
Next
Application.ScreenUpdating = True
End Sub
If your range J:Q holds only numeric data, you could possibly make the code even more efficient by simply summing the values in that range and testing for a non-zero result. For example:
Code:
Sub Test1()
Application.ScreenUpdating = False
Dim LastRow As Long, RngID As Range, RngSum As Range, oCel As Range, i As Long, bState As Boolean
LastRow = Cells(Rows.Count, "a").End(xlUp).Row
Set RngID = Range("a2:a" & LastRow)
For Each oCel In RngID
  With oCel
    bState = True
    If .Value Like "*FUJ*" Then
      Set RngSum = Worksheets("Sheet1").Range("J" & .Row & ":" & "Q" & .Row)
      If Application.WorksheetFunction.Sum(RngSum) = 0 Then bState = False
    End If
    With Cells(.Row, "u")
      If bState = True Then
        .Value = "Out of Stock"
      Else
        .Value = "In Stock"
      End If
    End With
  End With
Next
Application.ScreenUpdating = True
End Sub
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #3  
Old 11-14-2011, 11:44 AM
Hwyn Hwyn is offline Vba, Is there a better way to test empty cells? Windows Vista Vba, Is there a better way to test empty cells? Office 2007
Novice
Vba, Is there a better way to test empty cells?
 
Join Date: Nov 2011
Location: Cardiff
Posts: 5
Hwyn is on a distinguished road
Default

Thank you Paul, I'm actually going to use your test1 solution they both work fine but I find the summing solution easier to follow.
Many Thanks for your kind solution
Robert
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Vba, Is there a better way to test empty cells? Empty rules rene.kamphuis@ciz.nl Mail Merge 21 04-15-2011 12:34 AM
Open source DDE test methods? d.rock90 Excel Programming 1 01-05-2011 12:03 PM
Test after presentation? barnkat PowerPoint 0 08-13-2010 10:58 AM
Count range cells eliminating merge cells danbenedek Excel 0 06-15-2010 12:40 AM
how to add test in word document tekle Word 2 05-24-2010 01:26 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 11:56 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft