+ Reply to Thread
Page 9 of 17 FirstFirst ... 7 8 9 10 11 ... LastLast
Results 121 to 135 of 243

How to post a range - headers and data?

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

    Nice work and one request from my end. if possible please keep the addin in a separate thread instead of having it in a long going discussion area.. If possible stick that thread on top because it's going to help the users of this forum.

    Just wanted to add one more piece of suggestion from my end whether is is possible to get the background color too...?


    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

  2. #122
    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 :) Sixthsense :) View Post
    Just wanted to add one more piece of suggestion from my end whether is is possible to get the background color too...?
    Hi Sixthsense,

    I tried many, many..., ways to set the bgcolor and, as far as i know, it's not possible with the current version of BB Code.
    Marcelo Branco

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

    No issues... just wanted to convey it. Your current version is more than enough for us....

    Thanks a lot

  4. #124
    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 :) Sixthsense :) View Post
    No issues... just wanted to convey it. Your current version is more than enough for us....

    Thanks a lot
    Thank you very much for your kind words.

  5. #125
    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?

    You're Welcome

    One more suggestion it is possible to ignore the hidden columns?

    Because in the below table the L:Q is the hidden columns which is coming in visible while converstion...


    E
    F
    G
    H
    I
    J
    K
    L
    M
    N
    O
    P
    Q
    R
    3
    y y y y y
    3
    4
    y y y y y
    2
    5
    y y y y
    1

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

    An update...

    When I use Alt+; or Ctrl+G>>Special>>Visible Cells Only then using the addin is ignoring the hidden columns but unfortunately it is moving the data after the hidden columns to a new table like the below...

    E
    F
    G
    H
    I
    J
    K
    3
    y y y y y
    4
    y y y y y
    5
    y y y y
    3
    3
    4
    2
    5
    1

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

    Testing


    A
    B
    C
    D
    E
    F
    G
    H
    I
    J
    K
    R
    1
    21/ago
    28/ago
    04/set
    11/set
    18/set
    25/set
    2
    Adams
    Shawn
    New
    y
    y
    y
    2
    3
    Return
    y
    y
    y
    1
    4
    Adams
    Maribeth
    New
    y
    y
    y
    y
    1
    5
    Return
    y
    y
    y
    3

  8. #128
    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?

    Testing with Row 3 = hidden and columns L:Q hidden


    A
    B
    C
    D
    E
    F
    G
    H
    I
    J
    K
    R
    1
    21/ago
    28/ago
    04/set
    11/set
    18/set
    25/set
    2
    Adams
    Shawn
    New
    y
    y
    y
    2
    4
    Adams
    Maribeth
    New
    y
    y
    y
    y
    1
    5
    Return
    y
    y
    y
    3

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

    BTW nice formula in
    http://www.excelforum.com/excel-form...her-blank.html

    This is my version (but I was late...)

    R2
    =IFERROR(COUNTIF(INDEX($F2:$Q2,LOOKUP(2,1/(($F2:$Q2="")*(COLUMN($A2:$L2)<MAX(($F2:$Q2="Y")*COLUMN($A2:$L2)))=1),COLUMN($A2:$L2))):$Q2,"y"),COUNTIF($F2:$Q2,"y"))

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

    Sixthsense,

    Follows a test version to deal with rows/columns hidden

    HTML Code: 
    Sub CopyRngToBBCode()
        Dim DataObj As Object, strTable As String
        
        Set DataObj = GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
        'Application.CutCopyMode = False
        strTable = "[size=2]" & RngToBBCode(ActiveWindow.Selection) & "[/size]"
        DataObj.SetText strTable
        DataObj.PutInClipboard
    End Sub
    
    Public Function RngToBBCode(rInput As Range, Optional bHeaders As Boolean = True) As String
        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][CENTER][b]" & ColLetters(rCell.Column) & "[/b][/CENTER][/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][CENTER][b]" & rRow.Row & "[/b][/CENTER][/td]"
                End If
                
                For Each rCell In rRow.Cells
                    If rCell.EntireColumn.Hidden = False Then 'check if the column is hidden
                    
                        '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]"
                        
                    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

  11. #131
    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?

    Thanks for it...

    You just post yours in the same thread so that we let the OP to pick the one whichever is convenient for him.

    I believe your post #128 seems that you have rectified the hidden col/row issue... Is there any modification in the code?

    If so then request the same to implement in the Addin....

    Edit: You posted the revised code already and thanks for it...

  12. #132
    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 :) Sixthsense :) View Post
    Thanks for it...

    You just post yours in the same thread so that we let the OP to pick the one whichever is convenient for him.

    I believe your post #128 seems that you have rectified the hidden col/row issue... Is there any modification in the code?

    If so then request the same to implement in the Addin....

    Edit: You posted the revised code already and thanks for it...
    This is NOT the code of the add-in. I was just testing a new code.

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

    Quote Originally Posted by mlcb View Post
    This is NOT the code of the add-in. I was just testing a new code.
    Oh! Ok.. since I have not gone through the code of that addin and assumed that this is the fine tuned code!

  14. #134
    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?

    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.

    You might use:

    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][center][b]" & Join(Evaluate("transpose(char(64+row(1:" & rInput.Columns.Count & ")))"), "[/b][/center][/td][td][center][b]") & "[/b][/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][b]" & Chr(j + 64) & "[/b][/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
        
        F_RngToBBCode = "[Table=""class: grid""]" & Replace(Replace(Replace(c00, "~1]", "left]"), "~8]", "center]"), "~2]", "right]") & "[/table]"
    End Function
    Last edited by snb; 09-08-2013 at 07:13 AM.



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

    Test code from post #130...

    Columns D and E are supposed to be hidden.


    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.

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