Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
getting Cell2.value=empty when their are values in the column
Hello
When I run through F8 in the following code I am getting Cell2.value is empty when it reads down through columnA. This is true for every row except for the first row. It shows me the value of what is in A1. After that I get EMPTY. The worksheet that I am reading has 3 reports in it and is standard. I only want to process what comes out of the 1st report in that 1 worksheet. I don't care about the other 2 reports in that 1 worksheet. So I am trying to count the number of rows before I reach the 2nd report. the 2nd report starts with "Project assignments". What is wrong with this code? Dim br As Long br = 0 Sheets("SAPTasks").Select Cells.Select Columns("a:a").Select Dim Cell2 As Range For Each Cell2 In Selection If Cell2.Value = "Project assignments" Then Exit For End If br = br + 1 Next Cell2 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
getting Cell2.value=empty when their are values in the column
Code is Ok...
I tested the following with A19 having the string "Project assignments" I got 18 as the value of br Sub test() Dim br As Long br = 0 Sheets("SAPTasks").Select Cells.Select Columns("a:a").Select Dim Cell2 As Range For Each Cell2 In Selection If Cell2.Value = "Project assignments" Then Exit For End If br = br + 1 Next Cell2 MsgBox br End Sub -- Always provide your feedback so that others know whether the solution worked or problem still persists ... "Bud" wrote: Hello When I run through F8 in the following code I am getting Cell2.value is empty when it reads down through columnA. This is true for every row except for the first row. It shows me the value of what is in A1. After that I get EMPTY. The worksheet that I am reading has 3 reports in it and is standard. I only want to process what comes out of the 1st report in that 1 worksheet. I don't care about the other 2 reports in that 1 worksheet. So I am trying to count the number of rows before I reach the 2nd report. the 2nd report starts with "Project assignments". What is wrong with this code? Dim br As Long br = 0 Sheets("SAPTasks").Select Cells.Select Columns("a:a").Select Dim Cell2 As Range For Each Cell2 In Selection If Cell2.Value = "Project assignments" Then Exit For End If br = br + 1 Next Cell2 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
getting Cell2.value=empty when their are values in the column
If you are trying to work with only the first report then this will find that
range for you. You did not specify what you are trying to do with the range, so I wrote this to just select the rows within the first report range. Maybe you can work with that. Sub GetFirstReport() Dim c As Range, s As Long Set c = Cells.Find("Project Assignments") If Not c Is Nothing Then s = c.Row Else Exit Sub End If Set myRange = Range("A2:A" & s - 1) myRange.EntireRow.Select End Sub "Bud" wrote: Hello When I run through F8 in the following code I am getting Cell2.value is empty when it reads down through columnA. This is true for every row except for the first row. It shows me the value of what is in A1. After that I get EMPTY. The worksheet that I am reading has 3 reports in it and is standard. I only want to process what comes out of the 1st report in that 1 worksheet. I don't care about the other 2 reports in that 1 worksheet. So I am trying to count the number of rows before I reach the 2nd report. the 2nd report starts with "Project assignments". What is wrong with this code? Dim br As Long br = 0 Sheets("SAPTasks").Select Cells.Select Columns("a:a").Select Dim Cell2 As Range For Each Cell2 In Selection If Cell2.Value = "Project assignments" Then Exit For End If br = br + 1 Next Cell2 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
getting Cell2.value=empty when their are values in the column
This worked just fine...but why did your code work and mine didn't?
I guess the following id the reason but why and why did you put it below the other set? Set myRange = Range("A2:A" & s - 1) myRange.EntireRow.Select Also, I am interested in knowing more about macros...I bought a Excel 2003 power programming and it helped but not enough....what is a really good book for understanding all this stuff or what else would you recommend "JLGWhiz" wrote: If you are trying to work with only the first report then this will find that range for you. You did not specify what you are trying to do with the range, so I wrote this to just select the rows within the first report range. Maybe you can work with that. Sub GetFirstReport() Dim c As Range, s As Long Set c = Cells.Find("Project Assignments") If Not c Is Nothing Then s = c.Row Else Exit Sub End If Set myRange = Range("A2:A" & s - 1) myRange.EntireRow.Select End Sub "Bud" wrote: Hello When I run through F8 in the following code I am getting Cell2.value is empty when it reads down through columnA. This is true for every row except for the first row. It shows me the value of what is in A1. After that I get EMPTY. The worksheet that I am reading has 3 reports in it and is standard. I only want to process what comes out of the 1st report in that 1 worksheet. I don't care about the other 2 reports in that 1 worksheet. So I am trying to count the number of rows before I reach the 2nd report. the 2nd report starts with "Project assignments". What is wrong with this code? Dim br As Long br = 0 Sheets("SAPTasks").Select Cells.Select Columns("a:a").Select Dim Cell2 As Range For Each Cell2 In Selection If Cell2.Value = "Project assignments" Then Exit For End If br = br + 1 Next Cell2 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
getting Cell2.value=empty when their are values in the column
The code you posted executes an action only if the Cell2 value = "Project
Assignments" and that action is to exit the loop. It appears that this was only part of the actual procedure so I really can't say definitely why it was not returning any other values. If you have John Walkenbach's book on Power Programming, it should explain the For...Next loop. It is one of the better books in VBA programming. I have his Excel VBA programming for Dummies and it is a pretty good reference. Actually, I use the standard VBA help files more than anything else for understanding how different methods work. "Bud" wrote: This worked just fine...but why did your code work and mine didn't? I guess the following id the reason but why and why did you put it below the other set? Set myRange = Range("A2:A" & s - 1) myRange.EntireRow.Select Also, I am interested in knowing more about macros...I bought a Excel 2003 power programming and it helped but not enough....what is a really good book for understanding all this stuff or what else would you recommend "JLGWhiz" wrote: If you are trying to work with only the first report then this will find that range for you. You did not specify what you are trying to do with the range, so I wrote this to just select the rows within the first report range. Maybe you can work with that. Sub GetFirstReport() Dim c As Range, s As Long Set c = Cells.Find("Project Assignments") If Not c Is Nothing Then s = c.Row Else Exit Sub End If Set myRange = Range("A2:A" & s - 1) myRange.EntireRow.Select End Sub "Bud" wrote: Hello When I run through F8 in the following code I am getting Cell2.value is empty when it reads down through columnA. This is true for every row except for the first row. It shows me the value of what is in A1. After that I get EMPTY. The worksheet that I am reading has 3 reports in it and is standard. I only want to process what comes out of the 1st report in that 1 worksheet. I don't care about the other 2 reports in that 1 worksheet. So I am trying to count the number of rows before I reach the 2nd report. the 2nd report starts with "Project assignments". What is wrong with this code? Dim br As Long br = 0 Sheets("SAPTasks").Select Cells.Select Columns("a:a").Select Dim Cell2 As Range For Each Cell2 In Selection If Cell2.Value = "Project assignments" Then Exit For End If br = br + 1 Next Cell2 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
getting Cell2.value=empty when their are values in the column
Hi,
You asked what's causing your code to fail - well in the code you selected there is nothing that would make if fail to run. The real question is does it do what you want, apparently not. A few observations: You are not using the variable br in the portion of the code you gave us so this will just increment. The command Cells.Select if superulous since the next command selects the entire columns. You can use cell as a variable rather than Cell2 because Excel has no keywords, objects or commands called cell. You can also drop the .Value modifier for "cell", since cell is a range, and the default is the value of the cell (range). I'm not sure how other programmers feel about this last point. Assuming that column A has data before the first occurance of "Project assignments", if you step through your code using F8, during each loop if you hover your mouse over the variable "cell" you should see a screen tip with the value of cell. Dim br As Long br = 0 Sheets("SAPTasks").Activate Columns("a:a").Select Dim Cell As Range For Each Cell In Selection If Cell = "Project assignments" Then Exit For End If br = br + 1 Next Cell So, there is nothing that would make your macro fail within the given block of code. -- Thanks, Shane Devenshire "Bud" wrote: This worked just fine...but why did your code work and mine didn't? I guess the following id the reason but why and why did you put it below the other set? Set myRange = Range("A2:A" & s - 1) myRange.EntireRow.Select Also, I am interested in knowing more about macros...I bought a Excel 2003 power programming and it helped but not enough....what is a really good book for understanding all this stuff or what else would you recommend "JLGWhiz" wrote: If you are trying to work with only the first report then this will find that range for you. You did not specify what you are trying to do with the range, so I wrote this to just select the rows within the first report range. Maybe you can work with that. Sub GetFirstReport() Dim c As Range, s As Long Set c = Cells.Find("Project Assignments") If Not c Is Nothing Then s = c.Row Else Exit Sub End If Set myRange = Range("A2:A" & s - 1) myRange.EntireRow.Select End Sub "Bud" wrote: Hello When I run through F8 in the following code I am getting Cell2.value is empty when it reads down through columnA. This is true for every row except for the first row. It shows me the value of what is in A1. After that I get EMPTY. The worksheet that I am reading has 3 reports in it and is standard. I only want to process what comes out of the 1st report in that 1 worksheet. I don't care about the other 2 reports in that 1 worksheet. So I am trying to count the number of rows before I reach the 2nd report. the 2nd report starts with "Project assignments". What is wrong with this code? Dim br As Long br = 0 Sheets("SAPTasks").Select Cells.Select Columns("a:a").Select Dim Cell2 As Range For Each Cell2 In Selection If Cell2.Value = "Project assignments" Then Exit For End If br = br + 1 Next Cell2 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Delete Rows with Empty Cells with empty column 1 | Excel Programming | |||
Find Empty Column and paste cell values | Excel Programming | |||
SOS VBA Code Emergency: need to copy tell to empty cell direct below where values in adjacent cells in different column are equal to each other. | Excel Programming |