#1
|
|||
|
|||
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 |
#2
|
||||
|
||||
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 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] |
#3
|
|||
|
|||
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 |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
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 |