+ Reply to Thread
Page 11 of 17 FirstFirst ... 9 10 11 12 13 ... LastLast
Results 151 to 165 of 243

How to post a range - headers and data?

  1. #151
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    41,417

    Re: How to post a range - headers and data?

    Thanks for the link Joseph, although I would have nooo idea what to do with that. (1 of the last posts said that the fix didnt work for them, but it's still wayyy above my pay-grade lol)
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think some-1 helped you

    Regards
    Ford

  2. #152
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,320

    Re: How to post a range - headers and data?

    the only issue there was that the poster didn't know how to alter the code for 64bit Office-it's not that hard ;-)
    Josie

    if at first you don't succeed try doing it the way your wife told you to

  3. #153
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    41,417

    Re: How to post a range - headers and data?

    flying a plane isnt hard either - if you know how lol

  4. #154
    Forum Guru jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2007 2003
    Posts
    12,477

    Re: How to post a range - headers and data?

    Been trying to figure this out for AGES...thanks shg...Post #108

    A
    B
    C
    D
    1
    name prices prices
    2
    A0059
    41.5
    41.5
    3
    C0103
    14.7
    14.7
    4
    E0530
    11.9
    11.9
    5
    E0530
    23.3
    23.3
    6
    G7004
    14.9
    14.9
    7
    C0103
    19.6
    19.6
    8
    C0103
    38.7
    38.7
    9
    A0059
    30.3
    30.3
    10
    E0530
    33.1
    33.1
    John

    If you have issues with Code I've provided, I appreciate your feedback.

    In the event Code provided resolves your issue, please mark your Thread as SOLVED.

    If you're satisfied by any members response to your issue please use the star icon at the lower left of their post.

  5. #155
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,892

    Re: How to post a range - headers and data?

    Align everything to the right...

    Data Range
    A
    B
    C
    D
    E
    1
    Dates
    Numbers
    Text
    Logicals
    Errors
    2
    5/15/2004
    -0.255
    Ya
    TRUE
    #N/A
    3
    12/26/2004
    16
    Ba
    FALSE
    #REF!
    4
    11/7/2001
    1.00E+100
    Da
    #NUM!
    5
    8/18/2007
    2
    Ba
    #VALUE!
    6
    2/8/2011
    8888
    Do
    #NULL!
    7
    12/25/2005
    2.814
    I
    #NAME?
    8
    6/15/2013
    7
    C
    9
    1/28/2007
    1
    U
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  6. #156
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    41,417

    Re: How to post a range - headers and data?

    This thread has become so long and involved, it's hard to find where the actual (latest?) code and usage instructions are now

    Any change of starting a new thread with the code/instructions as the very 1st post, so that new-comers (and others) can find it easier?

    Also, I mentioned in an earlier post on this thread (no idea where, this already has 155+ posts and 11 pages - hence my request) that with Win8, the code does not always work, sometimes just get 2 small squares instead of what I copied. I was told it was a W8 problem, wondering if anyone else had the same issue, and if anyone could fix it?

  7. #157
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,892

    Re: How to post a range - headers and data?

    Quote Originally Posted by FDibbins View Post
    This thread has become so long and involved, it's hard to find where the actual (latest?) code and usage instructions are now

    Any change of starting a new thread with the code/instructions as the very 1st post, so that new-comers (and others) can find it easier?
    I agree.

    However, which "latest" code should be the code?

    Each "latest" code did something a bit different from the previous "latest" code and not everyone would want to use the last "latest" code. For example, the code I use is slightly different from any of the versions in the thread.

    Haven't seen Marcelo on the site in a while.

  8. #158
    Forum Contributor
    Join Date
    12-22-2010
    Location
    Rio, Brazil
    MS-Off Ver
    Excel 2010, 2016
    Posts
    209

    Re: How to post a range - headers and data?

    Hello Friends

    Lately I've been here a few times.

    This is the code (Personal Workbook) I'm currently using

    Please Login or Register  to view this content.

    There is also the Add-In provided ​​by shg

    Link
    https://app.box.com/s/soezox25h3w0q5s4rcyl

    Usage
    See the Add-Ins tab for the menu item.


    All the best

    Marcelo
    Last edited by arlu1201; 04-25-2014 at 08:56 AM.
    Marcelo Branco

  9. #159
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,301

    Re: How to post a range - headers and data?

    Perhaps it just needs a settings form with the most used options in it, so the same add-in will work for most anyone. The code is unlocked anyway I think, so it can always be tweaked if someone has a particular need.
    Remember what the dormouse said
    Feed your head

  10. #160
    Forum Contributor
    Join Date
    12-22-2010
    Location
    Rio, Brazil
    MS-Off Ver
    Excel 2010, 2016
    Posts
    209

    Re: How to post a range - headers and data?

    My code in #158 is truncated

    New attempt

    HTML Code: 
    Sub CopyRngToBBCode()
        Dim DataObj As Object, strTable As String
        
        Set DataObj = GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
        strTable = "[size=1]" & RngToBBCode(ActiveWindow.Selection) & "[/size]"
        DataObj.SetText strTable
        DataObj.PutInClipboard
    End Sub
    
    Public Function RngToBBCode(rInput As Range, Optional bHeaders As Boolean = True) As String
        'Version that deals with hidden columns/rows and empty cells
        'row numbers and column letters in Blue
        Dim rRow As Range, rcell As Range, sReturn As String, strAux As String, strColor As String
        Dim strAlign As String
        
        sReturn = "[Table=""class: grid""]"
        
        If bHeaders Then
            sReturn = sReturn & "[tr][td] [/td]" 'top left cell
            
            For Each rcell In rInput.Rows(1).Cells
                If rcell.EntireColumn.Hidden = False Then
                    sReturn = sReturn & "[td][color=blue][CENTER][b]" & ColLetters(rcell.Column) & "[/b][/CENTER][/color][/td]"
                End If
            Next rcell
            sReturn = sReturn & "[/tr]"
            
        End If
        
        For Each rRow In rInput.Rows
            If rRow.EntireRow.Hidden = False Then 'Check if the row is hidden
                sReturn = sReturn & vbNewLine & "[tr]"
                If bHeaders Then
                    sReturn = sReturn & "[td][color=blue][CENTER][b]" & rRow.Row & "[/b][/CENTER][/color][/td]"
                End If
                
                For Each rcell In rRow.Cells
                    If rcell.EntireColumn.Hidden = False Then 'check if the column is hidden
                    
                    If Len(CStr(rcell)) > 0 Then 'check if cell is not empty
                        'Set horizontal aligment
                        With rcell
                            Select Case .HorizontalAlignment
                                Case xlLeft
                                    strAlign = "LEFT"
                                Case xlCenter
                                    strAlign = "CENTER"
                                Case xlRight
                                    strAlign = "RIGHT"
                                Case Else
                                    Select Case VarType(.Value2)
                                        Case 8 'Sring
                                            strAlign = "LEFT"
                                        Case 10, 11 'Error or Boolean
                                            strAlign = "CENTER"
                                        Case Else 'Others
                                            strAlign = "RIGHT"
                                    End Select
                            End Select
                        End With
                             
                        'Set colour
                        strAux = Right("000000" & Hex(rcell.Font.Color), 6)
                        strColor = "#" & Right(strAux, 2) & Mid(strAux, 3, 2) & Left(strAux, 2)
                        
                        'Build the BBCode string
                        sReturn = sReturn & "[td][COLOR=""" & strColor & """][" & strAlign & "]" & _
                            rcell.Text & "[/" & strAlign & "][/COLOR][/td]"
                        
                    Else
                        'Build the BBCode string for an empty cell
                        sReturn = sReturn & "[td] [/td]"
                    End If ' close Len>0
                        
                    End If ' close check column hidden
                Next rcell
            End If ' close check row hidden
            sReturn = sReturn & "[/tr]" & vbNewLine
        Next rRow
        
        sReturn = sReturn & "[/table]"
        RngToBBCode = " " & vbNewLine & sReturn
    End Function
    
    Function ColLetters(lCol As Long) As String
        With ActiveSheet.Columns(lCol)
            ColLetters = Left(.Address(False, False), InStr(.Address(False, False), ":") - 1)
        End With
    End Function
    M.

  11. #161
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,892

    Re: How to post a range - headers and data?

    Posting fake email addresses and having the forum NOT create links:



    Data Range
    A
    1
    2
    fake1@fakeremailaddress.com
    3
    fake2@fakeremailaddress.com
    4
    fake3@fakeremailaddress.com
    5
    fake4@fakeremailaddress.com
    6

  12. #162
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,892

    Re: How to post a range - headers and data?

    Success!

  13. #163
    Registered User
    Join Date
    06-25-2013
    Location
    San Diego, CA
    MS-Off Ver
    Excel 2010
    Posts
    51

    Re: How to post a range - headers and data?

    I realize this is an ancient thread tht probably should not be revived. If so, and I should start a new thread, that will be fine. I noticed a link in Biff's signature re how to post dta (something to that effect.)

    Because I blindly assume Headers argument would be a range, I had error when using this formula. Turned out, function was not running at all.
    =RngToBBCode(D2:E7,D1:E1)

    Note, if this needs to be deleted, please either explain to me steps necessary, or feel free to delete it (assuming you have the power to do so, like I would assume a Forum Moderator would be able to.)

    Okay, I see what I did wrong. I finally figured out that 2nd argument was a boolean while I was trying to use it as a range. Apparently, if you want headers, you include the line that would be your header in the range you select. Duh!

    Revised formula should be =RngToBBCode(D2:E7,1)
    Or true instead of 1. I seem to still be having problems with a Beginning and Ending " but that would be easy enough to ignore or manually delete.
    "
    D
    E
    1
    Before
    After
    2
    2.5
    2.50-
    3
    2.5
    2.50-
    4
    5
    5.00-
    5
    10
    10.00-
    6
    2.5
    2.50-
    7
    10
    10.00-
    "

    Note: when I used the code from about 2 posts ago, was having problems with table displaying the words [COLOR} with formatting and [/COLOR] displaying in the table rather than actually displaying the text in color.

    I attempted to remove references to Color in the code and was apparently mostly successful based on my tests.
    Last edited by klvaughnsd; 05-09-2014 at 01:48 PM. Reason: Deleting previoous attempts of tables

  14. #164
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,892

    Re: How to post a range - headers and data?

    If you're using Excel 2002 as your profile indicates...

    Copy the code in post #160.

    Paste it into a general module.

    Then, to use it...

    Let's assume you want to post the range A1:C10 as a table into a forum reply.

    Select the range A1:C10
    Goto the menu Tools>Macro>Macros
    Select the macro name: CopyRngToBBCode
    Click the Run button

    The macro will generate the bb code and it will be copied to your clipboard.

    In Excel Forum, just paste the code into your reply. It might look something like this**:

    Data Range
    A
    B
    C
    1
    Header1
    Header2
    Header3
    2
    Data
    Data
    Data
    3
    Data
    Data
    Data
    4
    Data
    Data
    Data
    5
    Data
    Data
    Data
    6
    Data
    Data
    Data
    7
    Data
    Data
    Data
    8
    Data
    Data
    Data
    9
    Data
    Data
    Data
    10
    Data
    Data
    Data


    ** the code I use is slightly different as I have modified it to suit my own preferences.

  15. #165
    Registered User
    Join Date
    06-25-2013
    Location
    San Diego, CA
    MS-Off Ver
    Excel 2010
    Posts
    51

    Re: How to post a range - headers and data?

    I did use code from post 160, but was using the UDF. Let's see what happens with macro:


    D
    E
    1
    Before
    After
    2
    2.5
    2.50-
    3
    2.5
    2.50-
    4
    5
    5.00-
    5
    10
    10.00-
    6
    2.5
    2.50-
    7
    10
    10.00-


    Cool. That looks nice. Thanks very much!

+ Reply to Thread
Page 11 of 17 FirstFirst ... 9 10 11 12 13 ... LastLast

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1