+ Reply to Thread
Page 10 of 17 FirstFirst ... 8 9 10 11 12 ... LastLast
Results 136 to 150 of 243

How to post a range - headers and data?

  1. #136
    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?

    Made the row/column headers red...


    A
    B
    C
    F
    G
    H
    1
    Dates
    Numbers
    Text
    Logicals
    Errors
    2
    9/5/2007
    -0.00025
    Some Here
    TRUE
    #VALUE!
    3
    5/14/2004
    1.00E+100
    More
    4
    3/4/2000
    1
    FALSE
    #REF!
    5
    4/6/2007
    0
    Ice Cream
    TRUE
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  2. #137
    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?

    I think the default font size =2.

    In this line of code:

    HTML Code: 
    strTable = "[size=2]" & RngToBBCode(ActiveWindow.Selection) & "[/size]"
    Is it necessary to set the size to 2?

    Here's the result after removing the size tags:


    A
    B
    C
    F
    G
    H
    1
    Dates
    Numbers
    Text
    Logicals
    Errors
    2
    9/5/2007
    -0.00025
    Some Here
    TRUE
    #VALUE!
    3
    5/14/2004
    1.00E+100
    More
    4
    3/4/2000
    1
    FALSE
    #REF!
    5
    4/6/2007
    0
    Ice Cream
    TRUE

  3. #138
    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?

    Quote Originally Posted by snb View Post
    In order to show the code correctly in this thread I had to replace a [ 5 times by a {
    Please replace the indicated { by [ in your VBA code.
    Try HTML tags instead of CODE tags as below

    HTML Code: 
    Function RngToBBCode(rInput As Range, Optional bHeaders As Boolean = True) As String
        If bHeaders Then c00 = "[tr][td][center]" & Join(Evaluate("transpose(char(64+row(1:" & rInput.Columns.Count & ")))"), "[/center][/td][td][center]") & "[/center][/td][/tr]"
        sn = rInput
        
        For j = 1 To rInput.Columns.Count
          If Not rInput.Columns(j).Hidden Then
            c01 = c01 & " " & j
          Else
           c00 = Replace(c00, "[td][center]" & Chr(j + 64) & "[/center][/td]", "")
          End If
        Next
        sn = Application.Index(sn, Evaluate("Row(1:" & UBound(sn) & ")"), Split(Trim(c01)))
        
        For j = 1 To UBound(sn)
            If Not rInput.Rows(j).Hidden Then
                For jj = 1 To UBound(sn, 2)
                    y = rInput.Cells(j, jj).HorizontalAlignment
                    If y & VarType(sn(j, jj)) = "15" Or y & VarType(sn(j, jj)) = "17" Then y = 2
                    If InStr("128", Right(y, 1)) Then sn(j, jj) = Replace("[~]" & sn(j, jj) & "[/~]", "~", "~" & Right(y, 1))
                    
                    If rInput.Cells(j, jj).Font.Color > 0 Then
                        x3 = rInput.Cells(j, jj).Font.Color
                        sn(j, jj) = "[color=""" & Format(Hex(x3 Mod 256), "00") & Format(Hex((x3 Mod 256 ^ 2) \ 256), "00") & Format(Hex(x3 \ 256 ^ 2), "00") & """]" & sn(j, jj) & "[/color]"
                    End If
                Next
                c00 = c00 & "[/tr][tr][td]" & Join(Application.Index(sn, j, 0), "[/td][td]") & "[/td]"
            End If
        Next
        
        RngToBBCode = "[size=2][Table=""class: grid""]" & Replace(Replace(Replace(c00, "~1]", "left]"), "~8]", "center]"), "~2]", "right]") & "[/table][/size]"
    End Function
    Marcelo Branco

  4. #139
    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?

    Quote Originally Posted by Tony Valko View Post
    I think the default font size =2.

    In this line of code:

    HTML Code: 
    strTable = "[size=2]" & RngToBBCode(ActiveWindow.Selection) & "[/size]"
    Is it necessary to set the size to 2?
    Tony,

    Not sure, but I think in other forums, or using different browsers, the default is not 2.

    I remember many times, especially in Mr Excel Forum, the size being set to 3.

    So, just in case, I prefer to put in the code.

    M.

  5. #140
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,632

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

    Revised edition:
    HTML Code: 
    Sub CopyRngToBBCode()
        With New dataobject
           .SetText F_RngToBBCode(Selection)
           .PutInClipboard
        End With
    End Sub
    HTML Code: 
    Function F_RngToBBCode(rInput As Range) As String
        c00 = "[tr][td] [/td][td][center][color=#1F497D][b]" & Join(Evaluate("transpose(char(64+row(1:" & rInput.Columns.Count & ")))"), "[/b][/color][/center][/td][td][center][color=#1F497D][b]") & "[/b][/color][/center][/td]"
        sn = rInput
        
        For j = 1 To UBound(sn)
            If Not rInput.Rows(j).Hidden Then
                For jj = 1 To UBound(sn, 2)
                    y = rInput.Cells(j, jj).HorizontalAlignment
                    If y & VarType(sn(j, jj)) = "15" Or IsDate(sn(j, jj)) Then y = 52
                    If Len(y) > 1 Then sn(j, jj) = Replace("[~]" & sn(j, jj) & "[/~]", "~", "~" & Right(y, 1))
                    
                    If rInput.Cells(j, jj).Font.Color > 0 Then
                        x = Right("00000" & Hex(rInput.Cells(j, jj).Font.Color), 6)
                        sn(j, jj) = "[color=#" & Right(x, 2) & Mid(x, 3, 2) & Left(x, 2) & "]" & sn(j, jj) & "[/color]"
                    End If
                    
                    If j = 1 Then
                        If rInput.Columns(jj).Hidden Then
                          c00 = Replace(c00, "[td][center][color=#1F497D][b]" & Chr(jj + 64) & "[/b][/color][/center][/td]", "")
                        Else
                          c01 = c01 & " " & jj
                        End If
                    End If
                Next
                If j = 1 Then sn = Application.Index(sn, Evaluate("Row(1:" & UBound(sn) & ")"), Split(Trim(c01)))
                
                c00 = c00 & "[/tr][tr][td][color=#1F497D][b]" & j & "[/b][/color][/td][td]" & Join(Application.Index(sn, j, 0), "[/td][td]") & "[/td]"
            End If
        Next
        
        F_RngToBBCode = "[Table=""class: grid""]" & Replace(Replace(Replace(Replace(c00, "~1]", "left]"), "~8]", "center]"), "~2]", "right]"), "/tr]", "/tr]" & vbLf) & "[/tr][/table]"
    End Function



  6. #141
    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?

    OK, that sounds like a good idea!

  7. #142
    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?

    OK strange.

    At work, I have win 7 and 2007, and the add-in works fine there
    At home, I have win 8 and 2007, and it worked fine here for a while.

    Just installed 2010 (still have 2007) and now when I try to run the add-ing, on excel side, it all still seems fine, but when I copy here, all I get is 2 blank lil squares, like this (IF it will show)...
    ￿￿

    ANyone got any suggestions/ideas?
    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

  8. #143
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India
    MS-Off Ver
    2003 To 2010
    Posts
    12,233

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

    Whether you installed the addin in both 2007 & 2010 separately?


    If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
    Mark your thread as Solved


    If the suggestion helps you, then Click *below to Add Reputation

  9. #144
    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?

    OK this is from 2010

    A
    1
    aa
    2
    bb
    3
    cc


    and this is from 2007...
    M
    5
    95
    6
    53
    7
    37
    8
    20
    9
    25
    10
    67

  10. #145
    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?

    OK very strange, it was not doing that before, and I had closed 2007 completely, tried again - nothing. then removed the add-in (even from the personal folder), put it back, and it still wouldnt work

  11. #146
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India
    MS-Off Ver
    2003 To 2010
    Posts
    12,233

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

    What I suggest is Keep Two Copies of that Addin.

    Name 1 copy like For2007 and another copy like For2010.

    Seems to be senseless approach but this is what my sense asking me to suggest.

    Open your 2007 Excel and Press Alt+T+I (Not in VBA mode) and see whether previously installed addin resides there. If not then Click Browse button and locate the For2007 addin and give ok…

    Please repeat the above method for 2010 and install the For2010 Addin.

    Now check whether this problem still continues…

  12. #147
    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?

    I've seen reports of sporadic issues when using the MSForms.DataObject in Win8. I think that's what you're running into.
    Remember what the dormouse said
    Feed your head

  13. #148
    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?

    @ Six, thanks for the input, I have done that (cant rename the actual macro that appears when you add the addin to the ribbon though)
    2007 gives this...
    ￿￿
    (again grrr)
    2010 gives me this...
    ￿￿
    sooo back where we started from LOL

    I think Rory may be right, I dont have this problem on Win 7 with 2007, just at home with Win 8 and 2007/2010

  14. #149
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India
    MS-Off Ver
    2003 To 2010
    Posts
    12,233

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

    @ FDibbins,

    Thanks for testing even though the suggestion is not valid and giving the feedback

    I am running with XP and not encountered this problem

  15. #150
    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?

    does seem like an acknowledged bug: http://social.msdn.microsoft.com/For...d?forum=isvvba
    there are a couple links to api code fixes in one of the later posts
    Josie

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

+ Reply to Thread
Page 10 of 17 FirstFirst ... 8 9 10 11 12 ... 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