Re: Problem with this code

Carolyn — Wed, 21 Jun 2006 15:43:14 GMT

quote:
Originally posted by G105ALY

Is it possible to use this with a do loop for a range of cells i.e. b14:014?

Sure.

I'll tell you how I usually handle these kinds of things in Excel VBA. Your data is arranged such that you need it to march across a row to various columns.

Sub temp()
'start at the beginning
Range("B14").Activate
'start the loop
Do While ActiveCell.Value <> ""
'''insert here the relevant code that you want
'''done on each cell
'move to the next cell (horizontally)
ActiveCell.Offset(0, 1).Activate
'end the loop
Loop
End Sub

Note several things. I usually have a block of data and never know how many columns or rows there are. Therefore I make the loop go until it hits a blank. (Value <>"")

Second, the Offset function has two arguments. Offset(row#, column#). In this case we want to stick to the same row and move across horizontally, thus, the row offset is zero and the column offset is 1.

If, instead, you were moving down a single column row by row, the code would say:

ActiveCell.Offset(1, 0).Activate

This scenario is far more common in my work, as I usually need to "process" a table of data and do the same thing to each entry in a given column.

Finally, have you considered using range names instead of addresses? Give B14 a name, and use that name in the Range("B14") statement. That way, should you insert columns or rows in the future the name will move with the insert. Otherwise, you'll have to edit your code to whatever the new range address is. That can be a real drag.

Re: Problem with this code

G105ALY — Wed, 21 Jun 2006 12:45:30 GMT

Many thanks, this now works.

Is it possible to use this with a do loop for a range of cells i.e. b14:014?

Re: Problem with this code

Carolyn — Tue, 20 Jun 2006 15:18:00 GMT

Try this:

Dim myComment As String
myComment = Range("A1").Text
Range("A1").AddComment
Range("A1").Comment.Text Text:=myComment
End Sub

Problem with this code

G105ALY — Mon, 19 Jun 2006 10:17:29 GMT

Hi guys,

I am using VBA in Excel with imported data, can anyone tell me why this code won't work:

Sub Test()
Dim mycomment As String
mycomment = Range("a1")
Range("a1").Comment.Text.mycomment
End Sub

I want it to show the contents of a cell in a comments box. Any help would be most appreciated?

Code

Re: Problem with this code

Re: Problem with this code

Re: Problem with this code

Problem with this code