Posts by 7absinth

    Wow. I didn't expect it will be so complicated script. However I found an error in extraction sheet, where B values with 0 in the front are disappeared:


    0278 278 FALSE
    0057 57 FALSE
    0025 25 FALSE
    0127 127 FALSE
    0306 306 FALSE
    0051 51 FALSE
    0217 217 FALSE
    0963 963 FALSE


    I formatted entire sheet as text and after that B column values were shown correctly.


    Thanks a lot! :thumbup::):):)

    This is very simple, but I am still struggling how to write the script correctly. So I have a table that have information in multiple columns in following layout



    A B C B C B C
    10119794
    5281 0 0278 1 2392 2
    1066246 5281 10 0278 24 2392 10
    1047076 5281 15 0278 9 2392 15



    The goal is find the correct VBA routine to cut/extract data from columns to rows in following layout, keeping only table title in another sheet:


    A B C
    10119794 5281 0
    1066246 5281 10
    1047076 5281 15
    B C
    10119794 0278 1
    1066246 0278 24
    1047076 0278 9
    B C
    10119794 2392 2
    1066246 2392 10
    1047076 2392 15



    Maybe there are the solution?


    Very appreciate any help.


    Thanks

    Files

    • Book1.xlsx

      (55.12 kB, downloaded 21 times, last: )

    Thanks a lot, Barry. I tested it and just noticed small errors under:


    Code
    1. If x = 21 Then
    2. x = [B][COLOR=#FF8C00]2[/COLOR][/B]
    3. c = c + 1
    4. End If


    But overall works well. Also needed to change this part too:


    Code
    1. With Me
    2. [B][COLOR=#FF0000].Columns.Delete[/COLOR][/B]


    because it doesnt delete colors when sheet list is reduced. Again thanks so much for your time. I will subscribe to your profile and like your post.


    God bless you.

    I have a following code that list and link all my worksheets in one long list. Now I come to the fact that this linked list is too long to me and I wonder if it's possible to split long linked lists into columns. I have seen separate codes that just copy out the values and split list into columns but in my case I want these cells linked too. So I am stuck and no idea how to implement code in this macro. Below is my macro.





    How to do that?

    Try:


    It is not what I am looking for and it doesnt work correctly.


    Again - I have a vertical list with values ie A1, A2, A3. I want these values pasted/distributed in each sheet in cell D3 separately. This means to paste next value from the list in n-sheet cell D3- ie in Sheet2 in cell D3 will be value A1, in Sheet3 in cell D3 will be value A2 and in Sheet4 in cell D3 will be value A3 and so on.

    I have a list with values and I want to copy and paste each value in each sheet in the same location. I tried to create code by myself and searched information but I am still stuck to figure out how to do this.


    Below is code.



    Where is the problem? This code go through all sheets to paste the LAST cell from the list. And this is not that I want.

    I could not to find the best thread for my issue anywhere.


    I have a following code (see below) that Index sheets dynamically. Now is a problem to implement the code that color Index cells when I color that tab. For example when I color T1 tab in green it will color relevant cell in the INDEX tab. In this example it will be A2. When I color T3 tab code will color A3 cell in the INDEX tab and so on. See picture.



    [VBA]Private Sub Worksheet_Activate()


    Dim wSheet As Worksheet, l As Long


    Application.ScreenUpdating = False


    Me.Move Before:=Sheets(1)
    l = 1


    With Me
    .Columns(1).ClearContents
    .Cells(1, 1) = "INDEX"
    .Cells(1, 1).Name = "Index"
    .Cells(1, 1).Font.Bold = 1
    End With


    For Each wSheet In Worksheets
    If wSheet.Name <> Me.Name Then
    l = l + 1
    With wSheet
    ' Uncomment for the first time then comment again
    ' .Rows("1:1").Insert Shift:=xlUp
    ' .Rows("1:1").Clear
    .Range("A1").Name = "Start_" & .Index
    .Hyperlinks.Add Anchor:=.Range("A1"), Address:="", _
    SubAddress:="Index", TextToDisplay:="Back to Index"
    .Range("A1").Font.ColorIndex = xlAutomatic
    Me.Hyperlinks.Add Anchor:=Me.Cells(l, 1), Address:="", _
    SubAddress:="Start_" & .Index, TextToDisplay:=wSheet.Name
    End With
    End If
    Me.Columns(1).AutoFit
    Next wSheet


    Application.ScreenUpdating = True


    End Sub[/VBA]


    [ATTACH=JSON]{"alt":"Click image for larger version Name:\tSample.JPG Views:\t1 Size:\t24.1 KB ID:\t1193524","data-align":"none","data-attachmentid":"1193524","data-size":"full","title":"Sample.JPG"}[/ATTACH]

    I have two spreadsheets that are in two tables. In each tab is same table format. And here is the problem. In each table has date and data column. And I just try to create a formula that sums values between two dates where the first date is the oldest date that I dont want to enter (so it must to be in formula) and date that I input manually. So only one criteria (TO date) need to enter, but I am confused how to calculate from two tables if sumif sumproduct and sum doesnt work as I expected. Is this possible in general to resolve? I have attached a sample.

    Files

    • Book1.xlsx

      (31.51 kB, downloaded 101 times, last: )

    I found the difficulties to find the right approach in Excel HOW I can to format inserted object (picture) with line borders and colors etc.


    Re: Count Chars/Words from Comments and TextBox VBA


    Quote from pike;771152

    and shortened [..]
    End Sub[/CODE]


    I didnt think of about type for this part. All looks cool till I encountered it doesnt count TxtBox Words correctly. Now its counts TxtBox as Object. I added 1 text box and it counts as 1 word. No matter how many words are written inside.

    Re: Count Chars/Words from Comments and TextBox VBA


    Quote from pike;771084

    all depend what controls are used make a dummy example reflecting the question and attach


    I have added the code on post #1 and I just need the modification that counts char/words from TxtBox and Comments. If I launch code separately (for example only for Comments) its works fine without any controls added. Same situation is with TextBox. I dont add any additional controls. Only that are provided by Default.