Reading each cell in a selected row.

[Dave]

Hi,

My problem is this... I want the user to click on a row number (any
row) - just so the entire row is selected. Then I want to position the
code at column A of that selected row, then select each cell along the
entire row.

I will be using offsets to select the cells - as I am assuming I know
where each piece of data is on the row.

I so far have this...

Dim c As Range

For Each c In Selection.EntireRow
msgbox("Cell")
Next c

Not much I know, I'm not even convinced that 'c' in this case selects
each cell - as I only get one msgbox appearing. Can anyone help me
fill in the blanks please?

Thanks,
Dave
++++++++++++

"Your mouse has moved. Windows will now restart for changes to take effect."

++++++++++++

 

[merjet]

You could use a RefEdit control on a UserForm to get the user to
select a row. After selection, you can strip the row number from
RefEdit.Value.

Hth,
Merjet

 

[Guest]

Hi Dave,

A Range object can actually be a lot of things other than a single cell and
unfortunately, as far as I know, Excel does not have a Cell object. However,
you can use the Cells property to check all the cells in a range, such as
EntireRow. Try something like this:

Sub GetCellContents()

Dim lngQuit As Long 'User response to messagebox
Dim rngCell As Excel.Range

For Each rngCell In Selection.EntireRow.Cells
lngQuit = MsgBox(rngCell.Value, vbOKCancel, "Found a cell")
If lngQuit = vbCancel Then GoTo Quit
Next rngCell

Quit:
Set rngCell = Nothing

End Sub

This sample adds adn checks for a cancel option for the message box so that,
once you know if it works, you don't have to keep clicking OK for 255 cells.
;-D

I hope this is what you were looking for,
-jeff

 

[merjet]

You can even set a range like this:
Set rng = Range(RefEdit1.Value)

Merjet

 

[Dave]

On Wed, 14 Feb 2007 13:35:13 -0800, Jeff S

Hi Jeff,

My sincere apologies for not replying sooner - I was called away.

Your help was invaluable here. Many thanks - it worked a treat.

I just broke out of the loop...

Dim c As Range

For Each c In Selection.EntireRow.Cells
c.Activate
GoTo Nxt
Next c

Nxt:

Once my A cell was active I just hopped along using offsets.

Excelllent.

Kind regards,
Dave

Hi Dave,

A Range object can actually be a lot of things other than a single cell and
unfortunately, as far as I know, Excel does not have a Cell object. However,
you can use the Cells property to check all the cells in a range, such as
EntireRow. Try something like this:

Sub GetCellContents()

Dim lngQuit As Long 'User response to messagebox
Dim rngCell As Excel.Range

For Each rngCell In Selection.EntireRow.Cells
lngQuit = MsgBox(rngCell.Value, vbOKCancel, "Found a cell")
If lngQuit = vbCancel Then GoTo Quit
Next rngCell

Quit:
Set rngCell = Nothing

End Sub

This sample adds adn checks for a cancel option for the message box so that,
once you know if it works, you don't have to keep clicking OK for 255 cells.
;-D

I hope this is what you were looking for,
-jeff

++++++++++++

"Your mouse has moved. Windows will now restart for changes to take effect."

++++++++++++