Loop to check each cell for a value

Hi all, another issue..

Trying to create a loop to check through a series of cells in a column to check for values, then if there is a value add that value to a string that will be sent to Word. I have a feeling that my lack of knowledge of VBA syntax is screwing me up here, but I can't seem to get it. Here's the code minus any kind of loop:

Private Sub CommandButton7_Click()
Dim WordObject As Object
Dim CellValueStr As Variant


Set WordObject = CreateObject("Word.Application")
CellValue = "B2"

WordObject.Visible = True

With WordObject
.Documents.Open ("C:\(path)\Report 01.docm")

If IsNull(ActiveSheet.Range(CellValue).Text) = False Then
.ActiveDocument.Bookmarks("Text1").Range.Text = ActiveSheet.Range(CellValue).Value
End If

End With


End Sub

I guess what I'm really asking is, how can I take a string and an integer (i.e. "B2") and turn them into a value that can be input into ActiveSheet.Range().Value?

Thanks,
-Danno

If you are looping (especially through columns) then you'll probably find the Cells property more useful than the Range property.

Example:

Sub foo()

Dim r As Long, c As Long

For r = 2 To 5
For c = 1 To 2
Debug.Print Cells(r, c).Address
Next c
Next r

End Sub


By the way, the Range.Text property accomodates the formatting of the cell ie. how the data in the cell is presented. A good way to check if a cell is truly empty is to use the VBA.IsEmpty function.

If Not VBA.IsEmpty(Range("B2")) Then
MsgBox "Range B2 is not empty"
End If


You also have a typo in your variable declarations which should be addressed by using an Option Explicit directive. I posted some advice about Option Explicit on post #4 on your other thread (http://www.xtremevbtalk.com/showthread.php?t=317090).

Hi Colin,

Thanks for reply(s)! I'll work with this and repost.. Sorry for pseudo-double post..

-Danno

Okay so, not to double post, but..

I thought it might be better to post what I'm trying to do.

I have a series of cells in one column, some that will have text in them and some that won't. I want to create a button that will loop through the cells in the column, find the ones that have something in them, and add that content to a bookmark in Word. Something like this:



For Each Cell in Range (B:B)

If Cell has Content Then Add to ActiveDocument Bookmarks("Text1")



Thanks!
-Danno

Never mind, I think I have it! For future reference:

Private Sub CommandButton7_Click()

Dim WordObject As Object
Dim HoldingTank As String

Set WordObject = CreateObject("Word.Application")
HoldingTank = ""

WordObject.Visible = True

With WordObject
.Documents.Open ("C:\(document path)\Report 01.docm")

For Each c In ActiveSheet.Range("B:B").Cells
If Not VBA.IsEmpty(c) Then HoldingTank = HoldingTank & c.Value & vbNewLine
Next

.ActiveDocument.Bookmarks("Text1").Range.Text = HoldingTank
End With

End Sub

Well done, danno. :)

Sure, there are some possible improvements but you've had a fair crack at it.

The only thing I'm going to mention is that you still haven't used Option Explicit. I know this because you have an undeclared variable, c, in your code. I keep banging on about it because it is really, really important. ;)

I keep banging on about it because it is really, really important. ;)

why?

if you don't mind me hijacking the thread :)

why?

if you don't mind me hijacking the thread :)

I'll let you off, this time. ;)


It enforces variable declaration. That together with then declaring your variables with an appropriate type results in the following benefits:

It improves your knowledge as a VBA programmer because you gain a better understanding of how code works
Typos in your code are identified at compile time. Typos can be very hard to spot when debugging, so this will save you hours of headaches.
It will also make it easier to avoid or identify other bugs in your code in a variety of situations
Variant types, as a general rule of thumb, will make your code run more slowly. If you are explicitly declaring your variables as more appropriate data types such as a Range, a String, a Long Integer, rather than having all your variables implicitly declared as variants, will mean that your code will run more quickly. There are, of course, situations where Variant types are entirely appropriate.


If you search this forum for my posts containing "Option Explicit", I reckon you'll find at least 50 threads where using it immediately revealed the problem.