getting Cell2.value=empty when their are values in the column

[Bud]

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

 

[Sheeloo]

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

 

[JLGWhiz]

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]

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]

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.

 

[ShaneDevenshire]

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.