| 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
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 |
|
#7
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 |
|
#8
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 |
|
#9
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 |
|
#10
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 |
|
#11
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 |
|
#12
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 |
| 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)
Hybrid Mode
