Consulting

Results 1 to 7 of 7

Thread: [VBA] How can I loop through the value of selected cells in a table?

  1. #1

    [VBA] How can I loop through the value of selected cells in a table?

    I want to get msgbox the value of selected cells in a table.
    Any hint how to do it?
    Thanks.

        Dim Cell As Range
        For Each Cell In Selection.Cells
            MsgBox Cell.Cells.Value
        Next
    Failed attempt. Thanks.

  2. #2
    You need

    Dim oCell As Cell
    Dim oRng As Range
        For Each oCell In Selection.Cells
            Set oRng = oCell.Range 'Set a range to the cell
            oRng.End = oRng.End - 1 'remove the cell end character from the range
            MsgBox oRng.Text 'report the content
        Next oCell
    Graham Mayor - MS MVP (Word)
    Visit my web site for more programming tips and ready made processes
    http://www.gmayor.com

  3. #3

  4. #4
    Microsoft Word MVP 2003-2009 VBAX Guru gmaxey's Avatar
    Joined
    Sep 2005
    Posts
    2,838
    Location
    I you want it reduced to a single message box:

    Sub ReturnCellText()
    Dim strText As String
      strText = Replace(Selection.Range.Text, Chr(13) & Chr(7) & Chr(13) & Chr(7), Chr(13) & Chr(7))
      MsgBox Join(Split(strText, Chr(13) & Chr(7)), vbCr)
    lbl_Exit:
      Exit Sub
    End Sub
    Greg

    Visit my website: http://gregmaxey.com

  5. #5
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    3,585
    Location
    Simpler, if there are no paragraph breaks in the cells:
        Dim Cell As Range
        For Each Cell In Selection.Cells
            MsgBox Split(Cell.Range.Text, vbCr)(0)
        Next
    This could be accumulated to a single output, too.
    Cheers
    Paul Edstein
    [MS MVP - Word]

  6. #6
    Microsoft Word MVP 2003-2009 VBAX Guru gmaxey's Avatar
    Joined
    Sep 2005
    Posts
    2,838
    Location
    Likewise simpler even if there are paragraph breaks in the cells:

    Dim Cell As Cell
      For Each Cell In Selection.Cells
        MsgBox Split(Cell.Range.Text, Chr(7))(0)
      Next
    lbl_Exit:
      Exit Sub
    Paul, did mean Dim oCell as Cell? As is, your code errors on the msgbox line.
    Greg

    Visit my website: http://gregmaxey.com

  7. #7
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    3,585
    Location
    Quote Originally Posted by gmaxey View Post
    Paul, did mean Dim oCell as Cell? As is, your code errors on the msgbox line.
    I simply modified the OP's code without paying too much attention to how the variables had been declared. I agree, though, that reserved names should not be used that way.
    Cheers
    Paul Edstein
    [MS MVP - Word]

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •