| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop through Row Range, Excel 2000, 2003
Hello,
My BEGIN row range is "B8,D8:E8" END row range is "B15,D15:E15". I need to check each row value and if the row is all blank I want to exit sub, if the row range has all data then I want go to continue macro, if a row has a blank cell I want to continue macro. This is a example code I've got so far but I do not know how to get it to loop the the rows: If Range("B8").Value & Range("D8").Value & Range("E8").Value = "" Then Exit Sub Thank you for your help in advance, jfcby |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Loop through Row Range, Excel 2000, 2003
try for i=8 to 15 if range("B & i &",d"& i & ":e" & i & ")="" then next i or for each c in range("b8:b15") if c="" and c.offset(,2)="" and c.offset(,3)="" then next -- Don Guillett SalesAid Software "jfcby" wrote in message ups.com... Hello, My BEGIN row range is "B8,D8:E8" END row range is "B15,D15:E15". I need to check each row value and if the row is all blank I want to exit sub, if the row range has all data then I want go to continue macro, if a row has a blank cell I want to continue macro. This is a example code I've got so far but I do not know how to get it to loop the the rows: If Range("B8").Value & Range("D8").Value & Range("E8").Value = "" Then Exit Sub Thank you for your help in advance, jfcby |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Loop through Row Range, Excel 2000, 2003
sub demo()
For i = 8 To 15 If Cells(i, "D").Value & Cells(i, "E").Value & Cells(i, "B").Value = "" Then Exit Sub End If Next End Sub -- Gary's Student "jfcby" wrote: Hello, My BEGIN row range is "B8,D8:E8" END row range is "B15,D15:E15". I need to check each row value and if the row is all blank I want to exit sub, if the row range has all data then I want go to continue macro, if a row has a blank cell I want to continue macro. This is a example code I've got so far but I do not know how to get it to loop the the rows: If Range("B8").Value & Range("D8").Value & Range("E8").Value = "" Then Exit Sub Thank you for your help in advance, jfcby |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Loop through Row Range, Excel 2000, 2003
I think Gary's Student was actually concatenating all those cells into a single
string and then comparing that concatenated string to "". If the OP wanted to use AND, then it would look more like: if cells(i,"D").value = "" _ and cells(i,"E").value = "" _ and cells(i, "B").value = "" then ..... Don Guillett wrote: Fully tested ? For i = 8 To 15 If Cells(i, "D").Value & Cells(i, "E").Value & Cells(i, "B").Value = "" If Cells(i, "D").Value AND Cells(i, "E").Value AND & Cells(i, "B").Value = "" -- Don Guillett SalesAid Software "Gary''s Student" wrote in message ... sub demo() For i = 8 To 15 If Cells(i, "D").Value & Cells(i, "E").Value & Cells(i, "B").Value = "" Then Exit Sub End If Next End Sub -- Gary's Student "jfcby" wrote: Hello, My BEGIN row range is "B8,D8:E8" END row range is "B15,D15:E15". I need to check each row value and if the row is all blank I want to exit sub, if the row range has all data then I want go to continue macro, if a row has a blank cell I want to continue macro. This is a example code I've got so far but I do not know how to get it to loop the the rows: If Range("B8").Value & Range("D8").Value & Range("E8").Value = "" Then Exit Sub Thank you for your help in advance, jfcby -- Dave Peterson |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Loop through Row Range, Excel 2000, 2003
Hello,
Thanks Don Guillett & Gary's Student with your help I finally got my code to work! jfcby Gary''s Student wrote: sub demo() For i = 8 To 15 If Cells(i, "D").Value & Cells(i, "E").Value & Cells(i, "B").Value = "" Then Exit Sub End If Next End Sub -- Gary's Student "jfcby" wrote: Hello, My BEGIN row range is "B8,D8:E8" END row range is "B15,D15:E15". I need to check each row value and if the row is all blank I want to exit sub, if the row range has all data then I want go to continue macro, if a row has a blank cell I want to continue macro. This is a example code I've got so far but I do not know how to get it to loop the the rows: If Range("B8").Value & Range("D8").Value & Range("E8").Value = "" Then Exit Sub Thank you for your help in advance, jfcby |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Loop through Row Range, Excel 2000, 2003
Thank you Dave for your help!
jfcby Dave Peterson wrote: I think Gary's Student was actually concatenating all those cells into a single string and then comparing that concatenated string to "". If the OP wanted to use AND, then it would look more like: if cells(i,"D").value = "" _ and cells(i,"E").value = "" _ and cells(i, "B").value = "" then .... Don Guillett wrote: Fully tested ? For i = 8 To 15 If Cells(i, "D").Value & Cells(i, "E").Value & Cells(i, "B").Value = "" If Cells(i, "D").Value AND Cells(i, "E").Value AND & Cells(i, "B").Value = "" -- Don Guillett SalesAid Software "Gary''s Student" wrote in message ... sub demo() For i = 8 To 15 If Cells(i, "D").Value & Cells(i, "E").Value & Cells(i, "B").Value = "" Then Exit Sub End If Next End Sub -- Gary's Student "jfcby" wrote: Hello, My BEGIN row range is "B8,D8:E8" END row range is "B15,D15:E15". I need to check each row value and if the row is all blank I want to exit sub, if the row range has all data then I want go to continue macro, if a row has a blank cell I want to continue macro. This is a example code I've got so far but I do not know how to get it to loop the the rows: If Range("B8").Value & Range("D8").Value & Range("E8").Value = "" Then Exit Sub Thank you for your help in advance, jfcby -- Dave Peterson |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Loop through Row Range, Excel 2000, 2003
Maybe " I " should test.
-- Don Guillett SalesAid Software "Dave Peterson" wrote in message ... I think Gary's Student was actually concatenating all those cells into a single string and then comparing that concatenated string to "". If the OP wanted to use AND, then it would look more like: if cells(i,"D").value = "" _ and cells(i,"E").value = "" _ and cells(i, "B").value = "" then .... Don Guillett wrote: Fully tested ? For i = 8 To 15 If Cells(i, "D").Value & Cells(i, "E").Value & Cells(i, "B").Value = "" If Cells(i, "D").Value AND Cells(i, "E").Value AND & Cells(i, "B").Value = "" -- Don Guillett SalesAid Software "Gary''s Student" wrote in message ... sub demo() For i = 8 To 15 If Cells(i, "D").Value & Cells(i, "E").Value & Cells(i, "B").Value = "" Then Exit Sub End If Next End Sub -- Gary's Student "jfcby" wrote: Hello, My BEGIN row range is "B8,D8:E8" END row range is "B15,D15:E15". I need to check each row value and if the row is all blank I want to exit sub, if the row range has all data then I want go to continue macro, if a row has a blank cell I want to continue macro. This is a example code I've got so far but I do not know how to get it to loop the the rows: If Range("B8").Value & Range("D8").Value & Range("E8").Value = "" Then Exit Sub Thank you for your help in advance, jfcby -- Dave Peterson |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Loop through Row Range, Excel 2000, 2003
<vbg
Don Guillett wrote: Maybe " I " should test. -- Don Guillett SalesAid Software "Dave Peterson" wrote in message ... I think Gary's Student was actually concatenating all those cells into a single string and then comparing that concatenated string to "". If the OP wanted to use AND, then it would look more like: if cells(i,"D").value = "" _ and cells(i,"E").value = "" _ and cells(i, "B").value = "" then .... Don Guillett wrote: Fully tested ? For i = 8 To 15 If Cells(i, "D").Value & Cells(i, "E").Value & Cells(i, "B").Value = "" If Cells(i, "D").Value AND Cells(i, "E").Value AND & Cells(i, "B").Value = "" -- Don Guillett SalesAid Software "Gary''s Student" wrote in message ... sub demo() For i = 8 To 15 If Cells(i, "D").Value & Cells(i, "E").Value & Cells(i, "B").Value = "" Then Exit Sub End If Next End Sub -- Gary's Student "jfcby" wrote: Hello, My BEGIN row range is "B8,D8:E8" END row range is "B15,D15:E15". I need to check each row value and if the row is all blank I want to exit sub, if the row range has all data then I want go to continue macro, if a row has a blank cell I want to continue macro. This is a example code I've got so far but I do not know how to get it to loop the the rows: If Range("B8").Value & Range("D8").Value & Range("E8").Value = "" Then Exit Sub Thank you for your help in advance, jfcby -- Dave Peterson -- Dave Peterson |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Loop through Row Range, Excel 2000, 2003
Hello,
The code provided works great but I'm tring to insert it into this code2 but it is not working right. It does not go to the next cell it stays at row 8 and does not go to row 9 and so on. How can this code1 be inserted into code2 to work right? code1 Dim c As Variant For c = 8 To 15 If Cells(c, "B").Value & Cells(c, "D").Value & Cells(c, "E").Value _ = "" Then Exit Sub Next c code2 Sub ErrorCheckData() 'Tom Ogilvy Dim i As Long Dim Cell As Range, cell1 As Range Dim Rng As Range Dim msg(1 To 3) As String msg(1) = "ERROR Date is empty" msg(2) = "ERROR Description is empty" msg(3) = "ERROR Type is empty" For Each Cell In Range("B8:B15") Set Rng = Cell.Range("A1,C1:D1") i = 1 For Each cell1 In Rng If cell1 = "" Then cell1.Interior.ColorIndex = 15 MsgBox msg(i) & ": " & Cell.Address End End If i = i + 1 Next cell1 Next Cell End Sub The code I tried and did not work right: Sub ErrorCheckData() 'Tom Ogilvy Dim i As Long Dim c As Variant Dim Cell As Range, cell1 As Range Dim Rng As Range Dim msg(1 To 3) As String msg(1) = "ERROR Date is empty" msg(2) = "ERROR Description is empty" msg(3) = "ERROR Type is empty" For Each Cell In Range("B8:B15") Set Rng = Cell.Range("A1,C1:D1") i = 1 For c = 8 To 15 If Cells(c, "B").Value & Cells(c, "D").Value & Cells(c, "E").Value _ = "" Then Exit Sub For Each cell1 In Rng If cell1 = "" Then cell1.Interior.ColorIndex = 15 MsgBox msg(i) & ": " & Cell.Address End End If i = i + 1 Next cell1 Next c Next Cell End Sub Thank you for your help, jfcby Dave Peterson wrote: <vbg Don Guillett wrote: Maybe " I " should test. -- Don Guillett SalesAid Software "Dave Peterson" wrote in message ... I think Gary's Student was actually concatenating all those cells into a single string and then comparing that concatenated string to "". If the OP wanted to use AND, then it would look more like: if cells(i,"D").value = "" _ and cells(i,"E").value = "" _ and cells(i, "B").value = "" then .... Don Guillett wrote: Fully tested ? For i = 8 To 15 If Cells(i, "D").Value & Cells(i, "E").Value & Cells(i, "B").Value = "" If Cells(i, "D").Value AND Cells(i, "E").Value AND & Cells(i, "B").Value = "" -- Don Guillett SalesAid Software "Gary''s Student" wrote in message ... sub demo() For i = 8 To 15 If Cells(i, "D").Value & Cells(i, "E").Value & Cells(i, "B").Value = "" Then Exit Sub End If Next End Sub -- Gary's Student "jfcby" wrote: Hello, My BEGIN row range is "B8,D8:E8" END row range is "B15,D15:E15". I need to check each row value and if the row is all blank I want to exit sub, if the row range has all data then I want go to continue macro, if a row has a blank cell I want to continue macro. This is a example code I've got so far but I do not know how to get it to loop the the rows: If Range("B8").Value & Range("D8").Value & Range("E8").Value = "" Then Exit Sub Thank you for your help in advance, jfcby -- Dave Peterson -- Dave Peterson |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Loop through Row Range, Excel 2000, 2003
As previously posted:
Sub ErrorCheckTEST() Dim i as Long Dim cell as Range, cell1 as Range Dim rng as Range Dim msg(1 to 3) as String msg(1) = "ERROR Date is empty" msg(2) = "ERROR Description is empty" msg(3) = "ERROR Type is empty" for each cell in Range("B8:B15") set rng = cell.Range("A1,C1:D1") i = 1 if application.CountBlank(rng) = 3 then exit sub for each cell1 in rng if cell1 = "" then cell1.Interior.ColorIndex = 15 msgbox msg(i) & ": " & cell.Address exit sub end if i = i + 1 Next cell1 Next cell End Sub -- Regards, Tom Ogilvy "jfcby" wrote in message oups.com... Hello, The code provided works great but I'm tring to insert it into this code2 but it is not working right. It does not go to the next cell it stays at row 8 and does not go to row 9 and so on. How can this code1 be inserted into code2 to work right? code1 Dim c As Variant For c = 8 To 15 If Cells(c, "B").Value & Cells(c, "D").Value & Cells(c, "E").Value _ = "" Then Exit Sub Next c code2 Sub ErrorCheckData() 'Tom Ogilvy Dim i As Long Dim Cell As Range, cell1 As Range Dim Rng As Range Dim msg(1 To 3) As String msg(1) = "ERROR Date is empty" msg(2) = "ERROR Description is empty" msg(3) = "ERROR Type is empty" For Each Cell In Range("B8:B15") Set Rng = Cell.Range("A1,C1:D1") i = 1 For Each cell1 In Rng If cell1 = "" Then cell1.Interior.ColorIndex = 15 MsgBox msg(i) & ": " & Cell.Address End End If i = i + 1 Next cell1 Next Cell End Sub The code I tried and did not work right: Sub ErrorCheckData() 'Tom Ogilvy Dim i As Long Dim c As Variant Dim Cell As Range, cell1 As Range Dim Rng As Range Dim msg(1 To 3) As String msg(1) = "ERROR Date is empty" msg(2) = "ERROR Description is empty" msg(3) = "ERROR Type is empty" For Each Cell In Range("B8:B15") Set Rng = Cell.Range("A1,C1:D1") i = 1 For c = 8 To 15 If Cells(c, "B").Value & Cells(c, "D").Value & Cells(c, "E").Value _ = "" Then Exit Sub For Each cell1 In Rng If cell1 = "" Then cell1.Interior.ColorIndex = 15 MsgBox msg(i) & ": " & Cell.Address End End If i = i + 1 Next cell1 Next c Next Cell End Sub Thank you for your help, jfcby Dave Peterson wrote: <vbg Don Guillett wrote: Maybe " I " should test. -- Don Guillett SalesAid Software "Dave Peterson" wrote in message ... I think Gary's Student was actually concatenating all those cells into a single string and then comparing that concatenated string to "". If the OP wanted to use AND, then it would look more like: if cells(i,"D").value = "" _ and cells(i,"E").value = "" _ and cells(i, "B").value = "" then .... Don Guillett wrote: Fully tested ? For i = 8 To 15 If Cells(i, "D").Value & Cells(i, "E").Value & Cells(i, "B").Value = "" If Cells(i, "D").Value AND Cells(i, "E").Value AND & Cells(i, "B").Value = "" -- Don Guillett SalesAid Software "Gary''s Student" wrote in message ... sub demo() For i = 8 To 15 If Cells(i, "D").Value & Cells(i, "E").Value & Cells(i, "B").Value = "" Then Exit Sub End If Next End Sub -- Gary's Student "jfcby" wrote: Hello, My BEGIN row range is "B8,D8:E8" END row range is "B15,D15:E15". I need to check each row value and if the row is all blank I want to exit sub, if the row range has all data then I want go to continue macro, if a row has a blank cell I want to continue macro. This is a example code I've got so far but I do not know how to get it to loop the the rows: If Range("B8").Value & Range("D8").Value & Range("E8").Value = "" Then Exit Sub Thank you for your help in advance, jfcby -- Dave Peterson -- Dave Peterson |
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Loop through Row Range, Excel 2000, 2003
Hello Tom,
When I tried your code it give me a error message: Run Time Error '13' Type Mismatch and highlights this line of code: If Application.CountBlank(rng) = 3 Then Exit Sub Thank you for your help, jfcby Tom Ogilvy wrote: As previously posted: Sub ErrorCheckTEST() Dim i as Long Dim cell as Range, cell1 as Range Dim rng as Range Dim msg(1 to 3) as String msg(1) = "ERROR Date is empty" msg(2) = "ERROR Description is empty" msg(3) = "ERROR Type is empty" for each cell in Range("B8:B15") set rng = cell.Range("A1,C1:D1") i = 1 if application.CountBlank(rng) = 3 then exit sub for each cell1 in rng if cell1 = "" then cell1.Interior.ColorIndex = 15 msgbox msg(i) & ": " & cell.Address exit sub end if i = i + 1 Next cell1 Next cell End Sub -- Regards, Tom Ogilvy "jfcby" wrote in message oups.com... Hello, The code provided works great but I'm tring to insert it into this code2 but it is not working right. It does not go to the next cell it stays at row 8 and does not go to row 9 and so on. How can this code1 be inserted into code2 to work right? code1 Dim c As Variant For c = 8 To 15 If Cells(c, "B").Value & Cells(c, "D").Value & Cells(c, "E").Value _ = "" Then Exit Sub Next c code2 Sub ErrorCheckData() 'Tom Ogilvy Dim i As Long Dim Cell As Range, cell1 As Range Dim Rng As Range Dim msg(1 To 3) As String msg(1) = "ERROR Date is empty" msg(2) = "ERROR Description is empty" msg(3) = "ERROR Type is empty" For Each Cell In Range("B8:B15") Set Rng = Cell.Range("A1,C1:D1") i = 1 For Each cell1 In Rng If cell1 = "" Then cell1.Interior.ColorIndex = 15 MsgBox msg(i) & ": " & Cell.Address End End If i = i + 1 Next cell1 Next Cell End Sub The code I tried and did not work right: Sub ErrorCheckData() 'Tom Ogilvy Dim i As Long Dim c As Variant Dim Cell As Range, cell1 As Range Dim Rng As Range Dim msg(1 To 3) As String msg(1) = "ERROR Date is empty" msg(2) = "ERROR Description is empty" msg(3) = "ERROR Type is empty" For Each Cell In Range("B8:B15") Set Rng = Cell.Range("A1,C1:D1") i = 1 For c = 8 To 15 If Cells(c, "B").Value & Cells(c, "D").Value & Cells(c, "E").Value _ = "" Then Exit Sub For Each cell1 In Rng If cell1 = "" Then cell1.Interior.ColorIndex = 15 MsgBox msg(i) & ": " & Cell.Address End End If i = i + 1 Next cell1 Next c Next Cell End Sub Thank you for your help, jfcby Dave Peterson wrote: <vbg Don Guillett wrote: Maybe " I " should test. -- Don Guillett SalesAid Software "Dave Peterson" wrote in message ... I think Gary's Student was actually concatenating all those cells into a single string and then comparing that concatenated string to "". If the OP wanted to use AND, then it would look more like: if cells(i,"D").value = "" _ and cells(i,"E").value = "" _ and cells(i, "B").value = "" then .... Don Guillett wrote: Fully tested ? For i = 8 To 15 If Cells(i, "D").Value & Cells(i, "E").Value & Cells(i, "B").Value = "" If Cells(i, "D").Value AND Cells(i, "E").Value AND & Cells(i, "B").Value = "" -- Don Guillett SalesAid Software "Gary''s Student" wrote in message ... sub demo() For i = 8 To 15 If Cells(i, "D").Value & Cells(i, "E").Value & Cells(i, "B").Value = "" Then Exit Sub End If Next End Sub -- Gary's Student "jfcby" wrote: Hello, My BEGIN row range is "B8,D8:E8" END row range is "B15,D15:E15". I need to check each row value and if the row is all blank I want to exit sub, if the row range has all data then I want go to continue macro, if a row has a blank cell I want to continue macro. This is a example code I've got so far but I do not know how to get it to loop the the rows: If Range("B8").Value & Range("D8").Value & Range("E8").Value = "" Then Exit Sub Thank you for your help in advance, jfcby -- Dave Peterson -- Dave Peterson |
|
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Loop through Row Range, Excel 2000, 2003
Merry xmas eve
I corrected the 1st one from the original post and either of these will work. Your problem is that you want to EXIT the sub if all are blank. Of course, if the first row is blank then the sub will NOT loop. What do you want? Sub checkblanksinrow() For i = 8 To 15 If Range("B" & i & ",d" & i & ":e" & i) = "" Then MsgBox Cells(i, "B").Row Next i End Sub Sub checkblankinrow1() For Each c In Range("b8:b15") If c = "" And c.Offset(, 2) = "" And c.Offset(, 3) = "" Then MsgBox c.Row Next End Sub -- Don Guillett SalesAid Software "jfcby" wrote in message ups.com... Hello, My BEGIN row range is "B8,D8:E8" END row range is "B15,D15:E15". I need to check each row value and if the row is all blank I want to exit sub, if the row range has all data then I want go to continue macro, if a row has a blank cell I want to continue macro. This is a example code I've got so far but I do not know how to get it to loop the the rows: If Range("B8").Value & Range("D8").Value & Range("E8").Value = "" Then Exit Sub Thank you for your help in advance, jfcby |
|
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Loop through Row Range, Excel 2000, 2003
My error, here is a correction:
Sub ErrorCheckTEST() Dim i As Long Dim cell As Range, cell1 As Range Dim rng As Range Dim msg(1 To 3) As String msg(1) = "ERROR Date is empty" msg(2) = "ERROR Description is empty" msg(3) = "ERROR Type is empty" For Each cell In Range("B8:B15") Set rng = cell.Range("A1,C1:D1") i = 1 If Application.CountBlank(cell.Range("A1")) + _ Application.CountBlank(cell.Range("C1:D1")) = 3 _ Then Exit Sub For Each cell1 In rng If cell1 = "" Then cell1.Interior.ColorIndex = 15 MsgBox msg(i) & ": " & cell.Address Exit Sub End If i = i + 1 Next cell1 Next cell End Sub -- Regards, Tom Ogilvy "jfcby" wrote in message ups.com... Hello Tom, When I tried your code it give me a error message: Run Time Error '13' Type Mismatch and highlights this line of code: If Application.CountBlank(rng) = 3 Then Exit Sub Thank you for your help, jfcby Tom Ogilvy wrote: As previously posted: Sub ErrorCheckTEST() Dim i as Long Dim cell as Range, cell1 as Range Dim rng as Range Dim msg(1 to 3) as String msg(1) = "ERROR Date is empty" msg(2) = "ERROR Description is empty" msg(3) = "ERROR Type is empty" for each cell in Range("B8:B15") set rng = cell.Range("A1,C1:D1") i = 1 if application.CountBlank(rng) = 3 then exit sub for each cell1 in rng if cell1 = "" then cell1.Interior.ColorIndex = 15 msgbox msg(i) & ": " & cell.Address exit sub end if i = i + 1 Next cell1 Next cell End Sub -- Regards, Tom Ogilvy "jfcby" wrote in message oups.com... Hello, The code provided works great but I'm tring to insert it into this code2 but it is not working right. It does not go to the next cell it stays at row 8 and does not go to row 9 and so on. How can this code1 be inserted into code2 to work right? code1 Dim c As Variant For c = 8 To 15 If Cells(c, "B").Value & Cells(c, "D").Value & Cells(c, "E").Value _ = "" Then Exit Sub Next c code2 Sub ErrorCheckData() 'Tom Ogilvy Dim i As Long Dim Cell As Range, cell1 As Range Dim Rng As Range Dim msg(1 To 3) As String msg(1) = "ERROR Date is empty" msg(2) = "ERROR Description is empty" msg(3) = "ERROR Type is empty" For Each Cell In Range("B8:B15") Set Rng = Cell.Range("A1,C1:D1") i = 1 For Each cell1 In Rng If cell1 = "" Then cell1.Interior.ColorIndex = 15 MsgBox msg(i) & ": " & Cell.Address End End If i = i + 1 Next cell1 Next Cell End Sub The code I tried and did not work right: Sub ErrorCheckData() 'Tom Ogilvy Dim i As Long Dim c As Variant Dim Cell As Range, cell1 As Range Dim Rng As Range Dim msg(1 To 3) As String msg(1) = "ERROR Date is empty" msg(2) = "ERROR Description is empty" msg(3) = "ERROR Type is empty" For Each Cell In Range("B8:B15") Set Rng = Cell.Range("A1,C1:D1") i = 1 For c = 8 To 15 If Cells(c, "B").Value & Cells(c, "D").Value & Cells(c, "E").Value _ = "" Then Exit Sub For Each cell1 In Rng If cell1 = "" Then cell1.Interior.ColorIndex = 15 MsgBox msg(i) & ": " & Cell.Address End End If i = i + 1 Next cell1 Next c Next Cell End Sub Thank you for your help, jfcby Dave Peterson wrote: <vbg Don Guillett wrote: Maybe " I " should test. -- Don Guillett SalesAid Software "Dave Peterson" wrote in message ... I think Gary's Student was actually concatenating all those cells into a single string and then comparing that concatenated string to "". If the OP wanted to use AND, then it would look more like: if cells(i,"D").value = "" _ and cells(i,"E").value = "" _ and cells(i, "B").value = "" then .... Don Guillett wrote: Fully tested ? For i = 8 To 15 If Cells(i, "D").Value & Cells(i, "E").Value & Cells(i, "B").Value = "" If Cells(i, "D").Value AND Cells(i, "E").Value AND & Cells(i, "B").Value = "" -- Don Guillett SalesAid Software "Gary''s Student" wrote in message ... sub demo() For i = 8 To 15 If Cells(i, "D").Value & Cells(i, "E").Value & Cells(i, "B").Value = "" Then Exit Sub End If Next End Sub -- Gary's Student "jfcby" wrote: Hello, My BEGIN row range is "B8,D8:E8" END row range is "B15,D15:E15". I need to check each row value and if the row is all blank I want to exit sub, if the row range has all data then I want go to continue macro, if a row has a blank cell I want to continue macro. This is a example code I've got so far but I do not know how to get it to loop the the rows: If Range("B8").Value & Range("D8").Value & Range("E8").Value = "" Then Exit Sub Thank you for your help in advance, jfcby -- Dave Peterson -- Dave Peterson |
|
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Loop through Row Range, Excel 2000, 2003
Just a heads up, but
Sub checkblanksinrow() For i = 8 To 15 If Range("B" & i & ",d" & i & ":e" & i) = "" Then MsgBox Cells(i, "B").Row Next i End Sub only checks for blanks in column B. -- Regards, Tom Ogilvy "Don Guillett" wrote in message ... Merry xmas eve I corrected the 1st one from the original post and either of these will work. Your problem is that you want to EXIT the sub if all are blank. Of course, if the first row is blank then the sub will NOT loop. What do you want? Sub checkblanksinrow() For i = 8 To 15 If Range("B" & i & ",d" & i & ":e" & i) = "" Then MsgBox Cells(i, "B").Row Next i End Sub Sub checkblankinrow1() For Each c In Range("b8:b15") If c = "" And c.Offset(, 2) = "" And c.Offset(, 3) = "" Then MsgBox c.Row Next End Sub -- Don Guillett SalesAid Software "jfcby" wrote in message ups.com... Hello, My BEGIN row range is "B8,D8:E8" END row range is "B15,D15:E15". I need to check each row value and if the row is all blank I want to exit sub, if the row range has all data then I want go to continue macro, if a row has a blank cell I want to continue macro. This is a example code I've got so far but I do not know how to get it to loop the the rows: If Range("B8").Value & Range("D8").Value & Range("E8").Value = "" Then Exit Sub Thank you for your help in advance, jfcby |
|
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Loop through Row Range, Excel 2000, 2003
Hello Tom,
Thank you for your help! The code works great! One more questions: How do you reset the color to No Fill after the blank cells has data in them? Thanks for your help, jfcby Tom Ogilvy wrote: My error, here is a correction: Sub ErrorCheckTEST() Dim i As Long Dim cell As Range, cell1 As Range Dim rng As Range Dim msg(1 To 3) As String msg(1) = "ERROR Date is empty" msg(2) = "ERROR Description is empty" msg(3) = "ERROR Type is empty" For Each cell In Range("B8:B15") Set rng = cell.Range("A1,C1:D1") i = 1 If Application.CountBlank(cell.Range("A1")) + _ Application.CountBlank(cell.Range("C1:D1")) = 3 _ Then Exit Sub For Each cell1 In rng If cell1 = "" Then cell1.Interior.ColorIndex = 15 MsgBox msg(i) & ": " & cell.Address Exit Sub End If i = i + 1 Next cell1 Next cell End Sub -- Regards, Tom Ogilvy "jfcby" wrote in message ups.com... Hello Tom, When I tried your code it give me a error message: Run Time Error '13' Type Mismatch and highlights this line of code: If Application.CountBlank(rng) = 3 Then Exit Sub Thank you for your help, jfcby Tom Ogilvy wrote: As previously posted: Sub ErrorCheckTEST() Dim i as Long Dim cell as Range, cell1 as Range Dim rng as Range Dim msg(1 to 3) as String msg(1) = "ERROR Date is empty" msg(2) = "ERROR Description is empty" msg(3) = "ERROR Type is empty" for each cell in Range("B8:B15") set rng = cell.Range("A1,C1:D1") i = 1 if application.CountBlank(rng) = 3 then exit sub for each cell1 in rng if cell1 = "" then cell1.Interior.ColorIndex = 15 msgbox msg(i) & ": " & cell.Address exit sub end if i = i + 1 Next cell1 Next cell End Sub -- Regards, Tom Ogilvy "jfcby" wrote in message oups.com... Hello, The code provided works great but I'm tring to insert it into this code2 but it is not working right. It does not go to the next cell it stays at row 8 and does not go to row 9 and so on. How can this code1 be inserted into code2 to work right? code1 Dim c As Variant For c = 8 To 15 If Cells(c, "B").Value & Cells(c, "D").Value & Cells(c, "E").Value _ = "" Then Exit Sub Next c code2 Sub ErrorCheckData() 'Tom Ogilvy Dim i As Long Dim Cell As Range, cell1 As Range Dim Rng As Range Dim msg(1 To 3) As String msg(1) = "ERROR Date is empty" msg(2) = "ERROR Description is empty" msg(3) = "ERROR Type is empty" For Each Cell In Range("B8:B15") Set Rng = Cell.Range("A1,C1:D1") i = 1 For Each cell1 In Rng If cell1 = "" Then cell1.Interior.ColorIndex = 15 MsgBox msg(i) & ": " & Cell.Address End End If i = i + 1 Next cell1 Next Cell End Sub The code I tried and did not work right: Sub ErrorCheckData() 'Tom Ogilvy Dim i As Long Dim c As Variant Dim Cell As Range, cell1 As Range Dim Rng As Range Dim msg(1 To 3) As String msg(1) = "ERROR Date is empty" msg(2) = "ERROR Description is empty" msg(3) = "ERROR Type is empty" For Each Cell In Range("B8:B15") Set Rng = Cell.Range("A1,C1:D1") i = 1 For c = 8 To 15 If Cells(c, "B").Value & Cells(c, "D").Value & Cells(c, "E").Value _ = "" Then Exit Sub For Each cell1 In Rng If cell1 = "" Then cell1.Interior.ColorIndex = 15 MsgBox msg(i) & ": " & Cell.Address End End If i = i + 1 Next cell1 Next c Next Cell End Sub Thank you for your help, jfcby Dave Peterson wrote: <vbg Don Guillett wrote: Maybe " I " should test. -- Don Guillett SalesAid Software "Dave Peterson" wrote in message ... I think Gary's Student was actually concatenating all those cells into a single string and then comparing that concatenated string to "". If the OP wanted to use AND, then it would look more like: if cells(i,"D").value = "" _ and cells(i,"E").value = "" _ and cells(i, "B").value = "" then .... Don Guillett wrote: Fully tested ? For i = 8 To 15 If Cells(i, "D").Value & Cells(i, "E").Value & Cells(i, "B").Value = "" If Cells(i, "D").Value AND Cells(i, "E").Value AND & Cells(i, "B").Value = "" -- Don Guillett SalesAid Software "Gary''s Student" wrote in message ... sub demo() For i = 8 To 15 If Cells(i, "D").Value & Cells(i, "E").Value & Cells(i, "B").Value = "" Then Exit Sub End If Next End Sub -- Gary's Student "jfcby" wrote: Hello, My BEGIN row range is "B8,D8:E8" END row range is "B15,D15:E15". I need to check each row value and if the row is all blank I want to exit sub, if the row range has all data then I want go to continue macro, if a row has a blank cell I want to continue macro. This is a example code I've got so far but I do not know how to get it to loop the the rows: If Range("B8").Value & Range("D8").Value & Range("E8").Value = "" Then Exit Sub Thank you for your help in advance, jfcby -- Dave Peterson -- Dave Peterson |
|
#17
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Loop through Row Range, Excel 2000, 2003
Hello Tom,
Thank you for your help once again! My previous question I think the solution is add Else in the if statement like below is this correct? I tried it and the colored cells were reset to No Fill. Sub ErrorCheckData() 'Tom Ogilvy Dim i As Long Dim cell As Range, cell1 As Range Dim rng As Range Dim msg(1 To 3) As String msg(1) = "ERROR Date is empty" msg(2) = "ERROR Description is empty" msg(3) = "ERROR Type is empty" For Each cell In Range("B8:B15") Set rng = cell.Range("A1,C1:D1") i = 1 If Application.CountBlank(cell.Range("A1")) + _ Application.CountBlank(cell.Range("C1:D1")) = 3 _ Then Exit Sub For Each cell1 In rng If cell1 = "" Then cell1.Interior.ColorIndex = 15 MsgBox msg(i) & ": " & cell.Address End 'Exit Sub Else If cell1 "" Then cell1.Interior.ColorIndex = xlNone End If End If i = i + 1 Next cell1 Next cell End Sub Merry Christmas! jfcby jfcby wrote: Hello Tom, Thank you for your help! The code works great! One more questions: How do you reset the color to No Fill after the blank cells has data in them? Thanks for your help, jfcby Tom Ogilvy wrote: My error, here is a correction: Sub ErrorCheckTEST() Dim i As Long Dim cell As Range, cell1 As Range Dim rng As Range Dim msg(1 To 3) As String msg(1) = "ERROR Date is empty" msg(2) = "ERROR Description is empty" msg(3) = "ERROR Type is empty" For Each cell In Range("B8:B15") Set rng = cell.Range("A1,C1:D1") i = 1 If Application.CountBlank(cell.Range("A1")) + _ Application.CountBlank(cell.Range("C1:D1")) = 3 _ Then Exit Sub For Each cell1 In rng If cell1 = "" Then cell1.Interior.ColorIndex = 15 MsgBox msg(i) & ": " & cell.Address Exit Sub End If i = i + 1 Next cell1 Next cell End Sub -- Regards, Tom Ogilvy "jfcby" wrote in message ups.com... Hello Tom, When I tried your code it give me a error message: Run Time Error '13' Type Mismatch and highlights this line of code: If Application.CountBlank(rng) = 3 Then Exit Sub Thank you for your help, jfcby Tom Ogilvy wrote: As previously posted: Sub ErrorCheckTEST() Dim i as Long Dim cell as Range, cell1 as Range Dim rng as Range Dim msg(1 to 3) as String msg(1) = "ERROR Date is empty" msg(2) = "ERROR Description is empty" msg(3) = "ERROR Type is empty" for each cell in Range("B8:B15") set rng = cell.Range("A1,C1:D1") i = 1 if application.CountBlank(rng) = 3 then exit sub for each cell1 in rng if cell1 = "" then cell1.Interior.ColorIndex = 15 msgbox msg(i) & ": " & cell.Address exit sub end if i = i + 1 Next cell1 Next cell End Sub -- Regards, Tom Ogilvy "jfcby" wrote in message oups.com... Hello, The code provided works great but I'm tring to insert it into this code2 but it is not working right. It does not go to the next cell it stays at row 8 and does not go to row 9 and so on. How can this code1 be inserted into code2 to work right? code1 Dim c As Variant For c = 8 To 15 If Cells(c, "B").Value & Cells(c, "D").Value & Cells(c, "E").Value _ = "" Then Exit Sub Next c code2 Sub ErrorCheckData() 'Tom Ogilvy Dim i As Long Dim Cell As Range, cell1 As Range Dim Rng As Range Dim msg(1 To 3) As String msg(1) = "ERROR Date is empty" msg(2) = "ERROR Description is empty" msg(3) = "ERROR Type is empty" For Each Cell In Range("B8:B15") Set Rng = Cell.Range("A1,C1:D1") i = 1 For Each cell1 In Rng If cell1 = "" Then cell1.Interior.ColorIndex = 15 MsgBox msg(i) & ": " & Cell.Address End End If i = i + 1 Next cell1 Next Cell End Sub The code I tried and did not work right: Sub ErrorCheckData() 'Tom Ogilvy Dim i As Long Dim c As Variant Dim Cell As Range, cell1 As Range Dim Rng As Range Dim msg(1 To 3) As String msg(1) = "ERROR Date is empty" msg(2) = "ERROR Description is empty" msg(3) = "ERROR Type is empty" For Each Cell In Range("B8:B15") Set Rng = Cell.Range("A1,C1:D1") i = 1 For c = 8 To 15 If Cells(c, "B").Value & Cells(c, "D").Value & Cells(c, "E").Value _ = "" Then Exit Sub For Each cell1 In Rng If cell1 = "" Then cell1.Interior.ColorIndex = 15 MsgBox msg(i) & ": " & Cell.Address End End If i = i + 1 Next cell1 Next c Next Cell End Sub Thank you for your help, jfcby Dave Peterson wrote: <vbg Don Guillett wrote: Maybe " I " should test. -- Don Guillett SalesAid Software "Dave Peterson" wrote in message ... I think Gary's Student was actually concatenating all those cells into a single string and then comparing that concatenated string to "". If the OP wanted to use AND, then it would look more like: if cells(i,"D").value = "" _ and cells(i,"E").value = "" _ and cells(i, "B").value = "" then .... Don Guillett wrote: Fully tested ? For i = 8 To 15 If Cells(i, "D").Value & Cells(i, "E").Value & Cells(i, "B").Value = "" If Cells(i, "D").Value AND Cells(i, "E").Value AND & Cells(i, "B").Value = "" -- Don Guillett SalesAid Software "Gary''s Student" wrote in message ... sub demo() For i = 8 To 15 If Cells(i, "D").Value & Cells(i, "E").Value & Cells(i, "B").Value = "" Then Exit Sub End If Next End Sub -- Gary's Student "jfcby" wrote: Hello, My BEGIN row range is "B8,D8:E8" END row range is "B15,D15:E15". I need to check each row value and if the row is all blank I want to exit sub, if the row range has all data then I want go to continue macro, if a row has a blank cell I want to continue macro. This is a example code I've got so far but I do not know how to get it to loop the the rows: If Range("B8").Value & Range("D8").Value & Range("E8").Value = "" Then Exit Sub Thank you for your help in advance, jfcby -- Dave Peterson -- Dave Peterson |
|
#18
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Loop through Row Range, Excel 2000, 2003
Thank you everyone for your help!
Merry Christmas and Happy New Year! jfcby Frankie jfcby wrote: Hello Tom, Thank you for your help! The code works great! One more questions: How do you reset the color to No Fill after the blank cells has data in them? Thanks for your help, jfcby Tom Ogilvy wrote: My error, here is a correction: Sub ErrorCheckTEST() Dim i As Long Dim cell As Range, cell1 As Range Dim rng As Range Dim msg(1 To 3) As String msg(1) = "ERROR Date is empty" msg(2) = "ERROR Description is empty" msg(3) = "ERROR Type is empty" For Each cell In Range("B8:B15") Set rng = cell.Range("A1,C1:D1") i = 1 If Application.CountBlank(cell.Range("A1")) + _ Application.CountBlank(cell.Range("C1:D1")) = 3 _ Then Exit Sub For Each cell1 In rng If cell1 = "" Then cell1.Interior.ColorIndex = 15 MsgBox msg(i) & ": " & cell.Address Exit Sub End If i = i + 1 Next cell1 Next cell End Sub -- Regards, Tom Ogilvy "jfcby" wrote in message ups.com... Hello Tom, When I tried your code it give me a error message: Run Time Error '13' Type Mismatch and highlights this line of code: If Application.CountBlank(rng) = 3 Then Exit Sub Thank you for your help, jfcby Tom Ogilvy wrote: As previously posted: Sub ErrorCheckTEST() Dim i as Long Dim cell as Range, cell1 as Range Dim rng as Range Dim msg(1 to 3) as String msg(1) = "ERROR Date is empty" msg(2) = "ERROR Description is empty" msg(3) = "ERROR Type is empty" for each cell in Range("B8:B15") set rng = cell.Range("A1,C1:D1") i = 1 if application.CountBlank(rng) = 3 then exit sub for each cell1 in rng if cell1 = "" then cell1.Interior.ColorIndex = 15 msgbox msg(i) & ": " & cell.Address exit sub end if i = i + 1 Next cell1 Next cell End Sub -- Regards, Tom Ogilvy "jfcby" wrote in message oups.com... Hello, The code provided works great but I'm tring to insert it into this code2 but it is not working right. It does not go to the next cell it stays at row 8 and does not go to row 9 and so on. How can this code1 be inserted into code2 to work right? code1 Dim c As Variant For c = 8 To 15 If Cells(c, "B").Value & Cells(c, "D").Value & Cells(c, "E").Value _ = "" Then Exit Sub Next c code2 Sub ErrorCheckData() 'Tom Ogilvy Dim i As Long Dim Cell As Range, cell1 As Range Dim Rng As Range Dim msg(1 To 3) As String msg(1) = "ERROR Date is empty" msg(2) = "ERROR Description is empty" msg(3) = "ERROR Type is empty" For Each Cell In Range("B8:B15") Set Rng = Cell.Range("A1,C1:D1") i = 1 For Each cell1 In Rng If cell1 = "" Then cell1.Interior.ColorIndex = 15 MsgBox msg(i) & ": " & Cell.Address End End If i = i + 1 Next cell1 Next Cell End Sub The code I tried and did not work right: Sub ErrorCheckData() 'Tom Ogilvy Dim i As Long Dim c As Variant Dim Cell As Range, cell1 As Range Dim Rng As Range Dim msg(1 To 3) As String msg(1) = "ERROR Date is empty" msg(2) = "ERROR Description is empty" msg(3) = "ERROR Type is empty" For Each Cell In Range("B8:B15") Set Rng = Cell.Range("A1,C1:D1") i = 1 For c = 8 To 15 If Cells(c, "B").Value & Cells(c, "D").Value & Cells(c, "E").Value _ = "" Then Exit Sub For Each cell1 In Rng If cell1 = "" Then cell1.Interior.ColorIndex = 15 MsgBox msg(i) & ": " & Cell.Address End End If i = i + 1 Next cell1 Next c Next Cell End Sub Thank you for your help, jfcby Dave Peterson wrote: <vbg Don Guillett wrote: Maybe " I " should test. -- Don Guillett SalesAid Software "Dave Peterson" wrote in message ... I think Gary's Student was actually concatenating all those cells into a single string and then comparing that concatenated string to "". If the OP wanted to use AND, then it would look more like: if cells(i,"D").value = "" _ and cells(i,"E").value = "" _ and cells(i, "B").value = "" then .... Don Guillett wrote: Fully tested ? For i = 8 To 15 If Cells(i, "D").Value & Cells(i, "E").Value & Cells(i, "B").Value = "" If Cells(i, "D").Value AND Cells(i, "E").Value AND & Cells(i, "B").Value = "" -- Don Guillett SalesAid Software "Gary''s Student" wrote in message ... sub demo() For i = 8 To 15 If Cells(i, "D").Value & Cells(i, "E").Value & Cells(i, "B").Value = "" Then Exit Sub End If Next End Sub -- Gary's Student "jfcby" wrote: Hello, My BEGIN row range is "B8,D8:E8" END row range is "B15,D15:E15". I need to check each row value and if the row is all blank I want to exit sub, if the row range has all data then I want go to continue macro, if a row has a blank cell I want to continue macro. This is a example code I've got so far but I do not know how to get it to loop the the rows: If Range("B8").Value & Range("D8").Value & Range("E8").Value = "" Then Exit Sub Thank you for your help in advance, jfcby -- Dave Peterson -- Dave Peterson |
|
#19
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Loop through Row Range, Excel 2000, 2003
Oops, Thanks Tom
-- Don Guillett SalesAid Software "Tom Ogilvy" wrote in message ... Just a heads up, but Sub checkblanksinrow() For i = 8 To 15 If Range("B" & i & ",d" & i & ":e" & i) = "" Then MsgBox Cells(i, "B").Row Next i End Sub only checks for blanks in column B. -- Regards, Tom Ogilvy "Don Guillett" wrote in message ... Merry xmas eve I corrected the 1st one from the original post and either of these will work. Your problem is that you want to EXIT the sub if all are blank. Of course, if the first row is blank then the sub will NOT loop. What do you want? Sub checkblanksinrow() For i = 8 To 15 If Range("B" & i & ",d" & i & ":e" & i) = "" Then MsgBox Cells(i, "B").Row Next i End Sub Sub checkblankinrow1() For Each c In Range("b8:b15") If c = "" And c.Offset(, 2) = "" And c.Offset(, 3) = "" Then MsgBox c.Row Next End Sub -- Don Guillett SalesAid Software "jfcby" wrote in message ups.com... Hello, My BEGIN row range is "B8,D8:E8" END row range is "B15,D15:E15". I need to check each row value and if the row is all blank I want to exit sub, if the row range has all data then I want go to continue macro, if a row has a blank cell I want to continue macro. This is a example code I've got so far but I do not know how to get it to loop the the rows: If Range("B8").Value & Range("D8").Value & Range("E8").Value = "" Then Exit Sub Thank you for your help in advance, jfcby |
|
#20
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Loop through Row Range, Excel 2000, 2003
I would approach it like this:
Sub ErrorCheckTEST() Dim i As Long Dim cell As Range, cell1 As Range Dim rng As Range Dim msg(1 To 3) As String msg(1) = "ERROR Date is empty" msg(2) = "ERROR Description is empty" msg(3) = "ERROR Type is empty" Range("B8:B15","D8:E15").Interior.ColorIndex = xlNone For Each cell In Range("B8:B15") Set rng = cell.Range("A1,C1:D1") i = 1 If Application.CountBlank(cell.Range("A1")) + _ Application.CountBlank(cell.Range("C1:D1")) = 3 _ Then Exit Sub For Each cell1 In rng If cell1 = "" Then cell1.Interior.ColorIndex = 15 MsgBox msg(i) & ": " & cell.Address Exit Sub End If i = i + 1 Next cell1 Next cell End Sub -- Regards, Tom Ogilvy "jfcby" wrote in message ups.com... Hello Tom, Thank you for your help! The code works great! One more questions: How do you reset the color to No Fill after the blank cells has data in them? Thanks for your help, jfcby Tom Ogilvy wrote: My error, here is a correction: Sub ErrorCheckTEST() Dim i As Long Dim cell As Range, cell1 As Range Dim rng As Range Dim msg(1 To 3) As String msg(1) = "ERROR Date is empty" msg(2) = "ERROR Description is empty" msg(3) = "ERROR Type is empty" For Each cell In Range("B8:B15") Set rng = cell.Range("A1,C1:D1") i = 1 If Application.CountBlank(cell.Range("A1")) + _ Application.CountBlank(cell.Range("C1:D1")) = 3 _ Then Exit Sub For Each cell1 In rng If cell1 = "" Then cell1.Interior.ColorIndex = 15 MsgBox msg(i) & ": " & cell.Address Exit Sub End If i = i + 1 Next cell1 Next cell End Sub -- Regards, Tom Ogilvy "jfcby" wrote in message ups.com... Hello Tom, When I tried your code it give me a error message: Run Time Error '13' Type Mismatch and highlights this line of code: If Application.CountBlank(rng) = 3 Then Exit Sub Thank you for your help, jfcby Tom Ogilvy wrote: As previously posted: Sub ErrorCheckTEST() Dim i as Long Dim cell as Range, cell1 as Range Dim rng as Range Dim msg(1 to 3) as String msg(1) = "ERROR Date is empty" msg(2) = "ERROR Description is empty" msg(3) = "ERROR Type is empty" for each cell in Range("B8:B15") set rng = cell.Range("A1,C1:D1") i = 1 if application.CountBlank(rng) = 3 then exit sub for each cell1 in rng if cell1 = "" then cell1.Interior.ColorIndex = 15 msgbox msg(i) & ": " & cell.Address exit sub end if i = i + 1 Next cell1 Next cell End Sub -- Regards, Tom Ogilvy "jfcby" wrote in message oups.com... Hello, The code provided works great but I'm tring to insert it into this code2 but it is not working right. It does not go to the next cell it stays at row 8 and does not go to row 9 and so on. How can this code1 be inserted into code2 to work right? code1 Dim c As Variant For c = 8 To 15 If Cells(c, "B").Value & Cells(c, "D").Value & Cells(c, "E").Value _ = "" Then Exit Sub Next c code2 Sub ErrorCheckData() 'Tom Ogilvy Dim i As Long Dim Cell As Range, cell1 As Range Dim Rng As Range Dim msg(1 To 3) As String msg(1) = "ERROR Date is empty" msg(2) = "ERROR Description is empty" msg(3) = "ERROR Type is empty" For Each Cell In Range("B8:B15") Set Rng = Cell.Range("A1,C1:D1") i = 1 For Each cell1 In Rng If cell1 = "" Then cell1.Interior.ColorIndex = 15 MsgBox msg(i) & ": " & Cell.Address End End If i = i + 1 Next cell1 Next Cell End Sub The code I tried and did not work right: Sub ErrorCheckData() 'Tom Ogilvy Dim i As Long Dim c As Variant Dim Cell As Range, cell1 As Range Dim Rng As Range Dim msg(1 To 3) As String msg(1) = "ERROR Date is empty" msg(2) = "ERROR Description is empty" msg(3) = "ERROR Type is empty" For Each Cell In Range("B8:B15") Set Rng = Cell.Range("A1,C1:D1") i = 1 For c = 8 To 15 If Cells(c, "B").Value & Cells(c, "D").Value & Cells(c, "E").Value _ = "" Then Exit Sub For Each cell1 In Rng If cell1 = "" Then cell1.Interior.ColorIndex = 15 MsgBox msg(i) & ": " & Cell.Address End End If i = i + 1 Next cell1 Next c Next Cell End Sub Thank you for your help, jfcby Dave Peterson wrote: <vbg Don Guillett wrote: Maybe " I " should test. -- Don Guillett SalesAid Software "Dave Peterson" wrote in message ... I think Gary's Student was actually concatenating all those cells into a single string and then comparing that concatenated string to "". If the OP wanted to use AND, then it would look more like: if cells(i,"D").value = "" _ and cells(i,"E").value = "" _ and cells(i, "B").value = "" then .... Don Guillett wrote: Fully tested ? For i = 8 To 15 If Cells(i, "D").Value & Cells(i, "E").Value & Cells(i, "B").Value = "" If Cells(i, "D").Value AND Cells(i, "E").Value AND & Cells(i, "B").Value = "" -- Don Guillett SalesAid Software "Gary''s Student" wrote in message ... sub demo() For i = 8 To 15 If Cells(i, "D").Value & Cells(i, "E").Value & Cells(i, "B").Value = "" Then Exit Sub End If Next End Sub -- Gary's Student "jfcby" wrote: Hello, My BEGIN row range is "B8,D8:E8" END row range is "B15,D15:E15". I need to check each row value and if the row is all blank I want to exit sub, if the row range has all data then I want go to continue macro, if a row has a blank cell I want to continue macro. This is a example code I've got so far but I do not know how to get it to loop the the rows: If Range("B8").Value & Range("D8").Value & Range("E8").Value = "" Then Exit Sub Thank you for your help in advance, jfcby -- Dave Peterson -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Nested If Loop Limitation for Excel 2003 | Excel Discussion (Misc queries) | |||
| Insert Data Last Row Loop through cells Excel 2000 & 2003 | Excel Programming | |||
| Upgrade from Excel 2000 to Excel 2003 without MS Office 2003? | Excel Discussion (Misc queries) | |||
| Optimize VBA Excel 2003 NextFor loop | Excel Programming | |||
| Excel 2000 VBA - Set Print Range in dynamic range | Excel Programming | |||
![jfcby[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
