Page 2 of 2 FirstFirst 12
Results 11 to 20 of 20

Thread: Just testing a before a possible Thread post. No reply needed

  1. #11
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,122
    Rep Power
    10
    Hi Alan,

    Why don't you post the codes in the original thread ? Or are you testing the code over here ?

    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    https://www.youtube.com/watch?v=vSjTzhoJFdk&lc=UgzTC8V4jCzDHbmfCHF4AaABAg.9zaUSUoUUYs9zciSZa95 9d
    https://www.youtube.com/watch?v=vSjTzhoJFdk&lc=UgzTC8V4jCzDHbmfCHF4AaABAg.9zaUSUoUUYs9zckCo1tv PO
    https://www.youtube.com/watch?v=vSjTzhoJFdk&lc=UgwMsgdKKlhr2YPpxXl4AaABAg
    https://www.youtube.com/watch?v=XQAIYCT4f8Q&lc=UgwTUdEgR4bdt6crKXF4AaABAg.9xmkXGSciKJ9xonTti2s Ix
    https://www.youtube.com/watch?v=XQAIYCT4f8Q&lc=UgwWw16qBFX39JCRRm54AaABAg.9xnskBhPnmb9xoq3mGxu _b
    https://www.youtube.com/watch?v=XQAIYCT4f8Q&lc=UgzgWvzV-kvC4TJ8O414AaABAg.9xnFzCj8HRM9xon1p2ImxO
    https://www.youtube.com/watch?v=XQAIYCT4f8Q&lc=UgybZfNJd3l4FokX3cV4AaABAg.9xm_ufqOILb9xooIlv5P LY
    https://www.youtube.com/watch?v=XQAIYCT4f8Q&lc=UgzgWvzV-kvC4TJ8O414AaABAg.9xnFzCj8HRM9y38bzbSqaG
    https://www.youtube.com/watch?v=XQAIYCT4f8Q&lc=UgyWm8nL7syjhiHtpBF4AaABAg.9xmt8i0IsEr9y3FT9Y9F eM
    https://www.youtube.com/watch?v=bRd4mJglWiM&lc=UgxRmh2gFhpmHNnPemR4AaABAg.A0opm95t2XEA0q3Kshmu uY
    https://www.youtube.com/watch?v=bRd4mJglWiM&lc=UgxRmh2gFhpmHNnPemR4AaABAg
    https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=314837#p314837
    https://www.eileenslounge.com/viewtopic.php?f=21&t=40701&p=314836#p314836
    https://www.eileenslounge.com/viewtopic.php?f=27&t=40621&p=314621#p314621
    https://www.eileenslounge.com/viewtopic.php?f=27&t=40621&p=314619#p314619
    https://www.eileenslounge.com/viewtopic.php?f=27&t=40621&p=314600#p314600
    https://www.eileenslounge.com/viewtopic.php?f=27&t=40621&p=314599#p314599
    https://www.eileenslounge.com/viewtopic.php?f=27&t=40621&p=314274#p314274
    https://www.eileenslounge.com/viewtopic.php?f=27&t=40621&p=314229#p314229
    https://www.eileenslounge.com/viewtopic.php?f=27&t=40621&p=314195#p314195
    https://www.eileenslounge.com/viewtopic.php?f=36&t=39706&p=314110#p314110
    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    Last edited by DocAElstein; 03-19-2024 at 01:23 PM.
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  2. #12
    Fuhrer, Vierte Reich DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    9,270
    Rep Power
    10
    Hi Admin
    Yes, just testing it here.
    I often edit and re use the post.
    I sometimes re use, edit a bit etc.
    So just testing in this Thread.
    Another Thread I use here for the codes I use in answering Threads. That helps to keep the Thread less clutered for longer codes.
    I post most in the original Threads.
    Alan
    Last edited by DocAElstein; 02-06-2017 at 02:34 PM.
    ….If you are my competitor, I will try all I can to beat you. But if I do, I will not belittle you. I will Salute you, because without you, I am nothing.
    If you are my enemy, we will try to kick the fucking shit out of you…..
    Winston Churchill, 1939
    Save your Forum..._
    _...KILL A MODERATOR!!

  3. #13
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,122
    Rep Power
    10
    Quote Originally Posted by DocAElstein View Post
    Hi Admin
    Yes, just testing it here.
    I often edit and re use the post.
    I sometimes re use, edit a bit etc.
    So just testing in this Thread.
    Another Thread I use here for the codes I use in answering Threads. That helps to keep the Thread less clutered for longer codes.
    I post most in the original Threads.
    Alan
    No issues
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  4. #14
    Fuhrer, Vierte Reich DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    9,270
    Rep Power
    10

    Excel VBA comma point thousand decimal separator number problem.

    Inserting code lines
    The line number of the code to which I am talking about here is , as defined by, or rather as held internally by, and accessed in code coding by, a sequential integer starting at 1 at the top of the code window and counting by +1 for every successive line/row. In other words , VBA holds somewhere in memory a set of numbers like pseudo the 1 2 3 and 4 here:
    1 Option Explicit
    2 Sub MyCode()
    3 ‘Code
    4 End Sub

    These numbers we do not see and they are independent of any code lines which we may add: ( We can in addition , use any line numbers or labels as we choose. ( We can choose to use the same line numbers as those held internally, which I do in some places below, just to aid in the demonstration. Therefore those numbers which I use can be regarded , for demonstration purposes, as those held internally ) )

    Single lines
    Important to note here is that a virgin code module has no code lines. It cannot be thought of in terms of a spreadsheet of rows waiting to be filed in. You cannot reference any row in the code module until either
    _ typed lines are present
    or
    _ at least the rows “exist” as for example , by hitting the Enter key.
    However, if you try to insert lines/ rows above the current maximum row, using coding, then the coding will not error: it will add a new line at one line above the last used line.
    If you insert lines/rows at up to and including the last used row, then all existing lines get shifted down
    Because of these facts, it is easy to get disorientated in coding that inserts lines.
    Here an example: we insert lines at arbitrary non existent line numbers well above the last line, here the code before and after running the code:
    Before:
    Code:
    ' Line 1 Note: I add these numbers just for demonstration to represent the numbers held internally by VBA for referring to lines by number in a code module: We may use any numbers in any order. But they have nothing to do with the internally held numbers. I choose to use the same value as the internally held corresponding numbers for the line here just for demonstration purposes 
    Sub Insuerts() ' Line 2
    3 Dim CodMod As Object: Set CodMod = ThisWorkbook.VBProject.VBComponents.VBE.ActiveCodePane.CodeModule
    4 CodMod.insertlines Line:=2000, String:="' Line 9"
    5 CodMod.insertlines Line:=15, String:="' Line 10"
    6 CodMod.insertlines Line:=159, String:="' Line " & CodMod.countoflines + 1 ' CodMod.countoflines evaluates to 10 after lines 9 and 10 were added in the last two lines , and then this line is added at the end so becomes line 11
    End Sub ' Line 7
    ' line 8 This is the last line used Before the code is run
    After:
    Code:
    ' Line 1
    Sub Insuerts()
    3 Dim CodMod As Object: Set CodMod = ThisWorkbook.VBProject.VBComponents.VBE.ActiveCodePane.CodeModule
    4 CodMod.insertlines Line:=2000, String:="' Line 9"
    5 CodMod.insertlines Line:=15, String:="' Line 10"
    6 CodMod.insertlines Line:=159, String:="' Line " & CodMod.countoflines + 1 ' CodMod.countoflines evaluates to 10 after lines 9 and 10 were added in the last two lines , and then this line is added at the end so becomes line 11
    End Sub
    ' line 8 
    ' Line 9
    ' Line 10
    ' Line 11
    The above shows us that “inserting” above the last existing code line will actually add a single code line at the next line/row above the last existing line. Looping to add lines at the end of a code module is therefore somewhat redundant as a code line such as the following would ensure that lines are added sequentially
    __.insertlines Line:=__ .countoflines + 1, String:=" ' This will be at the next free line. "
    In that code line, any number >=1 can be used in place of 1
    Care must be taken when “adding” code lines using insertlines . For example if the first insertlines from the last ( After ) , code is modified to insert/add at the last line such:…_
    Code:
    ' Line 1 
    Sub Insuerts()
    3 Dim CodMod As Object: Set CodMod = ThisWorkbook.VBProject.VBComponents.VBE.ActiveCodePane.CodeModule
    4 CodMod.insertlines Line:=CodMod.countoflines, String:="' New Line" ' this will insert at line 11 – currently CodMod.countoflines=11 
    '5 CodMod.insertlines Line:=15, String:="' Line 10"
    '6 CodMod.insertlines Line:=159, String:="' Line " & CodMod.countoflines + 1 11
    End Sub
    ' line 8
    ' Line 9
    ' Line 10
    ' Line 11
    _.....then the results after running that above code will be as follows…._
    Code:
    ' Line 1
    Sub Insuerts()
    3 Dim CodMod As Object: Set CodMod = ThisWorkbook.VBProject.VBComponents.VBE.ActiveCodePane.CodeModule
    4 CodMod.insertlines Line:=CodMod.countoflines, String:="' New Line"
    '5 CodMod.insertlines Line:=15, String:="' Line 10"
    '6 CodMod.insertlines Line:=159, String:="' Line " & CodMod.countoflines + 1 ' CodMod.countoflines evaluates to 10 , and then this line is added at the end so beacomes line 11
    End Sub
    ' line 8
    ' Line 9
    ' Line 10
    ' New Line
    ' Line 11
    _.... as we see the code at ( and above if there had been ) the insert line is shifted down. In this case we inserted at the last line, rather than, as previously , trying to insert above the last line

    Multi line inserting. In one go. Single string
    Like many seemingly “page” type things in computers, the “page text” is actually held in a single long string. By analysing the string in detail , ( SLLHkjsjhfkJHFhfsajkhfaskjfhakjfhkjfhkjfhskjfhs ) , we often find that we have a “character” or characters of this form : vbCr & vbLf . This is a throw back to old computer printer things and this forces a carriage return and line feed. In other word it “makes a new line. We find that these constants are in the string held for a spreadsheet multi line range as well as that for the text in a code module. It can therefore be convenient to use a string of that form in a single string insert line to copy a multi line range into a code module. http://www.eileenslounge.com/viewtop...=31395#p242941

    For this Thread we will insert/add single lines at a time, to allow some formatting of each line.


    Multi line inserting. Via looping.
    If lines are inserted in a loop, then things occur in a simple way, (We are considering here that each loop inserts one line ): a line is inserted exactly as indicated in the argument line:=. All existing lines are shifted upwards. So finally all lines above the code section inserted by the looping will be at a line number equall to its original number + the number of loops done.
    Inserting, or rather adding, lines beyond the current last line is a bit more subtle to understand.
    As noted previously, we can’t actually directly add a line or insert above the last existing line. If we try to add/insert above the last line then a new line is added. This means that the actual line number given will be ignored, and the added code section will be directly above the previous coding. However, if we want our given line number to “pseudo” define the line number finally of the added lines, then we can do that if we give the start line number that of one more than the current last line number.
    Here a demo example
    Before:
    Code:
    'line 1 Note: I add these numbers just for demonstration to represent the numbers held internally by VBA for referring to lines by number in a code module: We may use any numbers in any order. But they have nothing to do with the internally held numbers. I choose to use the same value as the internally held corresponding numbers for the line here just for demonstration purposes
    'Line 2
    Sub LoopYinLinesTiddlyHiFoo()
    4 Dim VBIDEVBAProj As Object
    5 Set VBIDEVBAProj = ThisWorkbook.VBProject.VBE.ActiveCodePane.codemodule
    6 Dim LineNo As Long, StartLine As Long, StopLine As Long
    7 Let StartLine = VBIDEVBAProj.countoflines + 1: Let StopLine = StartLine + 4
    8 For LineNo = StartLine To StopLine
    9 VBIDEVBAProj.insertlines Line:=LineNo, String:="'Line " & LineNo
    10 Next LineNo
    End Sub
    After:
    Code:
    'line 1
    'Line 2
    Sub LoopYinLinesTiddlyHiFoo()
    4 Dim VBIDEVBAProj As Object
    5 Set VBIDEVBAProj = ThisWorkbook.VBProject.VBE.ActiveCodePane.codemodule
    6 Dim LineNo As Long, StartLine As Long, StopLine As Long
    7 Let StartLine = VBIDEVBAProj.countoflines + 1: Let StopLine = StartLine + 4
    8 For LineNo = StartLine To StopLine
    9 VBIDEVBAProj.insertlines Line:=LineNo, String:="'Line " & LineNo
    10 Next LineNo
    End Sub
    'Line 12
    'Line 13
    'Line 14
    'Line 15
    'Line 16
    The important point to note here is that we have not defined where the lines go directly in terms of the .insertlines Line:= given. We have simply adjusted the numbers used in the loop so that it appears that way.
    Just to help make that point clear. Consider the same experiment again, with just one change. This time in the Before we are attempting to insert lines way down in the code module, by adjusting the number given in .insertlines Line:= by 100
    Before:
    Code:
    'line 1 
    'Line 2
    Sub LoopYinLinesTiddlyHiFoo()
    4 Dim VBIDEVBAProj As Object
    5 Set VBIDEVBAProj = ThisWorkbook.VBProject.VBE.ActiveCodePane.codemodule
    6 Dim LineNo As Long, StartLine As Long, StopLine As Long
    7 Let StartLine = VBIDEVBAProj.countoflines + 1: Let StopLine = StartLine + 4
    8 For LineNo = StartLine To StopLine
    9 VBIDEVBAProj.insertlines Line:=LineNo + 100, String:="'Line " & LineNo
    10 Next LineNo
    End Sub
    The corresponding After , in terms of the added lines is exactly the same as before:
    Code:
    'line 1
    'Line 2 
    Sub LoopYinLinesTiddlyHiFoo()
    4 Dim VBIDEVBAProj As Object
    5 Set VBIDEVBAProj = ThisWorkbook.VBProject.VBE.ActiveCodePane.codemodule
    6 Dim LineNo As Long, StartLine As Long, StopLine As Long
    7 Let StartLine = VBIDEVBAProj.countoflines + 1: Let StopLine = StartLine + 4
    8 For LineNo = StartLine To StopLine
    9 VBIDEVBAProj.insertlines Line:=LineNo + 100, String:="'Line " & LineNo
    10 Next LineNo
    End Sub
    'Line 12
    'Line 13
    'Line 14
    'Line 15
    'Line 16
    The point may still not be clear, so just to put in words again the issue:
    Because we give a line number in the argument .insertlines Line:= of greater than the current last line number, then that actual number given bears no relation to the actual line number of the code line at which it will be added. ( The line number of the code I am talking about here is , as defined by, or rather as held internally by, and accessed in code coding, by a sequential integer starting at 1 at the top of the code window and counting by +1 for every successive line/row )
    Because we give a line number in the argument .insertlines Line:= of greater than the current last line number, then lines will always be added at the next free line, that is to say one line above the last used line. The actual number we give is irrelevant, for numbers we give which are greater than that of the current last used line in the code module.
    By choosing carefully the number of, in particular the variable StartLine , I am, however, able to predict the actual line number at which each code line is added.
    Attached Images Attached Images
    Last edited by DocAElstein; 12-21-2018 at 05:54 PM.
    ….If you are my competitor, I will try all I can to beat you. But if I do, I will not belittle you. I will Salute you, because without you, I am nothing.
    If you are my enemy, we will try to kick the fucking shit out of you…..
    Winston Churchill, 1939
    Save your Forum..._
    _...KILL A MODERATOR!!

  5. #15
    Fuhrer, Vierte Reich DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    9,270
    Rep Power
    10

    Test

    Example
    Post #3 Copy table contents to VBE module

    The idea of these codes are to allow for a temporary storage of spreadsheet values to a code module.
    _ One code will add a table of values to the end of a code module, and an extra start and end line will be added which contains range and date info. This code uses the selected range. So a range must be selected before running this code.
    and
    _ a second code can be used to paste those values back to the same range.
    Optionally a date of entry can be given to search for, otherwise all table values are pasted in

    The codes are somewhat detailed and inefficient. They work in many places “line by line”. There are some more simpler and more efficient codes here. http://www.eileenslounge.com/viewtop...=31395#p243002

    Sub PubProliferous_Let_RngAsString__()
    This code puts a selected range of values into the code module in which the code at the position of just after this coding. The table data values will be added to ( or taken from in following codes ) the module in which the code is run.
    Here a brief walk through the code. There are more details in the 'comments
    Rem 0 Sets for convenience, a variable to the code module in which the code is placed/ run from
    Rem 1
    A code module used for storing a table will be given the extension to its code name of "_txt" No special reason for doing this, I just thought it might be useful for later reference to know that the code module is being used in such a way. ( If no more table data is in the code module, then the extension , "_txt" , will be removed. This will be done , for example in following codes after the data is removed.)
    Rem 2
    Before running the code, a spreadsheet range should be selected. The range is copied to the clipboard, and the text of that put in a string variable, strIn
    Rem 3
    The string format is changes slightly to allow better display in this code ( and to aid in manipulation in the codes which re copy the data back to the spreadsheet, http://www.eileenslounge.com/viewtop...=31395#p242941
    Rem 4
    This adds an “extra line” to the start of the string with range information and current date, and an extra line at the end of the string with the same date. (I use date format of 10 digits as DD MM YYYY. ( This needs to be added to the data retrieving codes if looking for data from a specific date ) )
    As demo example, say I copied a 2 row x 2 cell range, B2:C3 to the clipboard. Say the range looked like this
    A B
    C D
    At this point in the routine, after Rem 4 , I will have in strIn , something of this approximate/pseudo form, ( say for a date of 12th December, 2018 ):
    Code:
    = " '_-20 12 2018 Worksheets("Tabelle2").Range("B2:C3") "  & vbCr & vbLf &  “|” &  “A”  &  “|” & “B” & vbCr & vbLf & “|” &  “C”  &  “|” & “D” & vbCr & vbLf &  “  '_-20 12 2018 “
    Rem 5
    The above string is converted to an array by splitting by the & vbCr & vbLf & . So effectively , I would end up with something of this form , for the above example , a 4 element, 1 Dimensional array:
    { SpltRws(0) , SpltRws(1) , SpltRws(2) , SpltRws(3) }
    where
    SpltRws(0) = " '_-20 12 2018 Worksheets("Tabelle2").Range("B2:C3") "
    SpltRws(1) = “|” & “A” & “|” & “B”
    SpltRws(2) = “|” & “C” & “|” & “D”
    SpltRws(3) = “ '_-20 12 2018 “



    Alan
    Last edited by DocAElstein; 12-21-2018 at 07:37 PM.
    ….If you are my competitor, I will try all I can to beat you. But if I do, I will not belittle you. I will Salute you, because without you, I am nothing.
    If you are my enemy, we will try to kick the fucking shit out of you…..
    Winston Churchill, 1939
    Save your Forum..._
    _...KILL A MODERATOR!!

  6. #16
    Fuhrer, Vierte Reich DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    9,270
    Rep Power
    10

    Testing

    test test



    askjfhsakjhfaskfhah



    Example
    Post #3 Copy table contents to VBE module (Post#4 Re Paste back to spreadsheet)
    3) Copy table contents to VBE module


    The idea of these codes are to allow for a temporary storage of spreadsheet values to a code module.
    _ One code will add a table of values to the end of a code module, and an extra start and end line will be added which contains range and date info. This code uses the selected range. So a range must be selected before running this code.
    and
    _ a second code can be used to paste those values back to the same range.
    Optionally a date of entry can be given to search for, otherwise all table values are pasted in

    The codes are somewhat detailed and inefficient. They work in many places “line by line”. There are some more simpler and more efficient codes here. http://www.eileenslounge.com/viewtop...=31395#p243002

    Sub PubProliferous_Let_RngAsString__()
    This code puts a selected range of values into the code module in which the code at the position of just after this coding. The table data values will be added to ( or taken from in following codes ) the module in which the code is run.
    Here a brief walk through the code. There are more details in the 'comments
    Rem 0 Sets for convenience, a variable to the code module in which the code is placed/ run from
    Rem 1
    A code module used for storing a table will be given the extension to its code name of "_txt" No special reason for doing this, I just thought it might be useful for later reference to know that the code module is being used in such a way. ( If no more table data is in the code module, then the extension , "_txt" , will be removed. This will be done , for example in following codes after the data is removed.)
    Rem 2
    Before running the code, a spreadsheet range should be selected. The range is copied to the clipboard, and the text of that put in a string variable, strIn
    Rem 3
    The string format is changes slightly to allow better display in this code ( and to aid in manipulation in the codes which re copy the data back to the spreadsheet, http://www.eileenslounge.com/viewtop...=31395#p242941
    Rem 4
    This adds an “extra line” to the start of the string with range information and current date, and an extra line at the end of the string with the same date. (I use date format of 10 digits as DD MM YYYY. ( This needs to be added to the data retrieving codes if looking for data from a specific date ) )
    As demo example, say I copied a 2 row x 2 cell range, B2:C3 to the clipboard. Say the range looked like this
    A B
    C D
    At this point in the routine, after Rem 4 , I will have in strIn , something of this approximate/pseudo form, ( say for a date of 12th December, 2018 ):
    Code:
    = " '_-20 12 2018 Worksheets("Tabelle2").Range("B2:C3") "  & vbCr & vbLf &  “|” &  “A”  &  “|” & “B” & vbCr & vbLf & “|” &  “C”  &  “|” & “D” & vbCr & vbLf &  “  '_-20 12 2018 “
    Rem 5
    The above string is converted to an array by splitting by the & vbCr & vbLf & . So effectively , I would end up with something of this form , for the above example , a 4 element, 1 Dimensional array:
    { SpltRws(0) , SpltRws(1) , SpltRws(2) , SpltRws(3) }
    where
    SpltRws(0) = " '_-20 12 2018 Worksheets("Tabelle2").Range("B2:C3") "
    SpltRws(1) = “|” & “A” & “|” & “B”
    SpltRws(2) = “|” & “C” & “|” & “D”
    SpltRws(3) = “ '_-20 12 2018 “

    Rem 6
    We examine the dimensions of the array and determine which lines will be used at the end of the code module
    Rem 7
    '7a) '7d)
    The extra start and end lines are added as simple lines.
    '7b) This is the main loop for inserting/(actually adding**) data lines
    '7c) this second inner loop builds up the actual line to be outputted from the columns/cells: The row is first split into each cell/column , and then a string of all cell/columns rebuilt. This is done so that we can add some formatting to each cell/column. In this code we effectively add each cell/column string into a fixed length string variable, so that in the VB editor it looks a bit neater with the cell/columns being of equal with regardless of how many characters are in each cell/column
    **Note: As discussed in the last post ( ,asqhfkhhsfhakfhfh ) , we use the code line of this form:
    ___.insertlines Line:=____, String:=”____
    We actually use a value in the argument Line:=____ which is 1 greater than the last line. It is not possible to “Insert” a line where no line currently is. But for any value 1 or more greater than the last line, the code line appears to add** a new line. That line will then eventually / indirectly become the line number which is 1 greater than the last line number. So we achieve what we want, but are not doing it as directly as it might appear at first glance:- We can only insert lines at up to and including the last current line. If we try to insert anywhere above the last current line, then the ___.insertlines _ will add** a new line directly after the last current line.

    _.___________________

    Example of code run.
    Consider that this spreadsheet range is highlighted,…._
    _____ Workbook: Uebersicht Aktuelle.xls ( Using Excel 2007 32 bit )
    Row\Col
    A
    B
    C
    D
    E
    F
    2492
    24 11 2018 GH GT 2136.25 30 30.04
    2493
    25 11 2018 GH GT
    2494
    26 11 2018 GH GT 2150.17 51 51.1
    2495
    27 11 2018 GH GT 2146.47 31 31
    Worksheet: Tabelle1
    _... and then run the code Sub PubProliferous_Let_RngAsString__()
    After doing this, you should see this towards the end of the code module in which the code is placed
    Code:
    '7d) End row
     VBIDEVBAProj.insertlines Line:=CdTblStp, String:=SpltRws(UBound(SpltRws())) ' Note: this line would not go further than last line, so it must be done here ***
    End Sub
    
    '_-21 12 2018 Worksheets("Tabelle1").Range("$A$2492:$F$2495") 
     '_-24 11 201 | GH        | GT        | 2136.25   | 30        | 30.04 
     '_-25 11 201 | GH        | GT        |           |           | 
     '_-26 11 201 | GH        | GT        | 2150.17   | 51        | 51.1 
     '_-27 11 201 | GH        | GT        | 2146.47   | 31        | 31 
     '_-21 12 2018
    You will note that you have lost a digit in the column A dates – the 8 in 2018 is missing. This is because the formatting is set to 9 characters by this variable
    __TabulatorSyncrenator = "123456789" ' any lengthed string will do
    You can overcome that problem by increasing the character length of that variable by 1 digit. Any character will do, for example
    __TabulatorSyncrenator = "1234W56789"


    Re run the code on the same range and you will now see this:
    Why Tabulator SinkCranartor.JPG : https://imgur.com/i6VsFRP
    ( TabulatorSyncrenator = "12345Y6789" )
    Code:
    End Sub
    
    '_-21 12 2018 Worksheets("Tabelle1").Range("$A$2492:$F$2495")
    '_-24 11 201 | GH        | GT        | 2136.25   | 30        | 30.04
    '_-25 11 201 | GH        | GT        |           |           |
    '_-26 11 201 | GH        | GT        | 2150.17   | 51        | 51.1
    '_-27 11 201 | GH        | GT        | 2146.47   | 31        | 31
    '_-21 12 2018
    
    '_-21 12 2018 Worksheets("Tabelle1").Range("$A$2492:$F$2495")
    '_-24 11 2018 | GH         | GT         | 2136.25    | 30         | 30.04
    '_-25 11 2018 | GH         | GT         |            |            |
    '_-26 11 2018 | GH         | GT         | 2150.17    | 51         | 51.1
    '_-27 11 2018 | GH         | GT         | 2146.47    | 31         | 31
    '_-21 12 2018


    Codes are here:
    Well they will be, you betcha




    _._____________________-

    In the next posts we consider ways to Get at that data range in a code module, and re paste it into the spreadsheet at the original range
    ext posts we consider ways to Get at that data range in a code module, and re paste it into the spreadsheet at the original range
    Last edited by DocAElstein; 12-21-2018 at 09:46 PM.
    ….If you are my competitor, I will try all I can to beat you. But if I do, I will not belittle you. I will Salute you, because without you, I am nothing.
    If you are my enemy, we will try to kick the fucking shit out of you…..
    Winston Churchill, 1939
    Save your Forum..._
    _...KILL A MODERATOR!!

  7. #17
    Fuhrer, Vierte Reich DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    9,270
    Rep Power
    10
    scratch left testie

    Scrolling testie...


    Example
    Copy table contents to VBE module Post#4 Re Paste back to spreadsheet
    4) Paste range values held in VBE module back to


    So the last code, having been run when a range, such as this was selected, …._ _____ Workbook: Uebersicht Aktuelle.xls ( Using Excel 2007 32 bit )
    Row\Col
    G
    H
    2503
    223.38 FH
    2504
    194.67 FH
    Worksheet: Tabelle1_... would result in the '_-commented lines like these appearing at the last rows of the code module in which that code was run
    Code:
    End Sub
    
    '_-23 12 2018 Worksheets("Tabelle1").Range("$G$2503:$H$2504")
    '_-223.38    | FH
    '_-194.67    | FH
    '_- EOF 23 12 2018
    In this post a code is presented for re pasting the original data values back into the original spreadsheet range. ( The code also removes the data from the code module )

    Code description.
    It is a bit difficult to give a detailed walk through description as the code goes around in circles ( it does Do Loops) a bit. So here is just a general description of the full code:
    It is convenient to work backwards, deleting the lines in the VB Editor as we go: Unlike in a spreadsheet we always know in a code module that the last line is the last line of data, in other words the pseudo code module sheet has a last cell at the last data entry: We don’t have empty rows. Rows only exist as data is/ was entered
    __1A Main outer Loop keeps going While we are not at the End of a routine ===============
    _____Immediately within that is another Loop which keeps going While we have not reached the start line of a data section ( A start of a data section will look like this sort of form '_-21 12 2018 Worksheets("Sht_1").Range("$B$15:$D$16") )

    It is not obvious from the routine layout, but the main action which is done first at every loop and initially after the routine begins is that which does the reading of the next line back from the end, in other words the current last line
    ____Let ReedLineIn = VBIDEVBAProj.Lines(StartLine:=VBIDEVBAProj.countof lines, Count:=1)
    Immediately after that, the code will end if a routine End _____ type statement was read, or Else the line is deleted. Initially when the code starts we are directed immediately to this lower part of the code by virtue of
    ____If ReedLineIn = "" Then
    'for an empty line we do nothing apart from having already deleted it ( for all but the first time here at the code start)
    At this lower routine section, a code line is read, we will quickly leave the routine If we have read an End __ type statement. This is because the Loop While condition to keep looping is no longer met because we set the Boolean variable, EndOFSub , to True:
    Code:
            Let ReedLineIn = VBIDEVBAProj.Lines(StartLine:=VBIDEVBAProj.countoflines, Count:=1)
                If ReedLineIn = "End Sub" Or ReedLineIn = "End Function" Then
                 Let EndOFSub = True
                Else ' after reading in any line, we delete it, unless it was the End of a routine
                 VBIDEVBAProj.DeleteLines StartLine:=VBIDEVBAProj.countoflines, Count:=1
                End If
            Loop While Not EndOFSub = True And FOB = False 
        Loop While EndOFSub = False ' 
    End Sub
    If we do not have the ReedLineIn = "" condition then we are at the part where things are done in each inner loop, ----|

    The first thing that is done is to see
    ____If we have got, ( as a result of looping backwards “down” the code module occupied lines), to the point of a start section of a data section which will have a code line something like '_-22 12 2018 Worksheets("Tabelle1").Range("$A$2515") If that is the case Then we are at the ' Section to prepare data for, and to do, the paste out of a data value range
    Various manipulation is then done to convert the collected information so far ( in String variable arrOut ) , into a form which will paste out to a spreadsheet range from the clipboard
    ____The Else situation here is ( If we are not at an end line of the data range ( like '_- EOF 22 12 2018 ) ) is simply to collect all the range text as a continual string, held in variable arrOut
    ____ We have now arrived at the end of doing things in the inner loop, and the code goes on to reading the next line, then checking for If at an End __ of routine, Else then deleting the line and
    ____We are now at the point of moving to the next loop of action to analyse and possibly do something with the last copied line held now in string variable ReedLineIn

    _.___________________-

    4b) Multi range data
    The same code will paste out many ranges held in the code module. All ranges will be pasted back to their original spreadsheet range, and be removed from the code module
    So for example , with this data in the same code as the routine Sub PubProliferous_Get_Rng__AsString() , will result in the following worksheet ranges being filled, (and also the data from the code module will be removed)
    Worksheet "Tabelle1" , Range F2504:I2505
    Worksheet "Tabelle2" , Range B392:D394
    Worksheet "Globies" , Range F24:G25
    Code:
        Loop While EndOFSub = False ' ================================================================
    End Sub
    
    '_-23 12 2018 Worksheets("Tabelle2").Range("$B$392:$D$394")
    '_-gramms    | Kcal      | Fett
    '_-300g      | 198Kcal   | 0
    '_-51g       | 183,09K   | 4,08
    '_- EOF 23 12 2018
    
    '_-23 12 2018 Worksheets("Tabelle1").Range("$F$2504:$I$2505")
    '_-41.09     | 194.67    | FH        | WH
    '_-55.07     | 233.32    | FH        | WH
    '_- EOF 23 12 2018
    
    '_-23 12 2018 Worksheets("Globies").Range("$F$24:$G$25")
    '_-GF  F     | GG  G
    '_-Eiweiß    | Koh
    '_- EOF 23 12 2018
    After running the routine, Sub PubProliferous_Get_Rng__AsString() , the end of that code module will then look like this:
    Code:
        Loop While EndOFSub = False ' ================================================================
    End Sub

    _.______________

    Final routine is here:



    In the next post, a slightly different routine will be presented. This routine will
    not remove the range value data in the code module
    and
    only look for data corresponding to a given date.
    Attached Images Attached Images
    Last edited by DocAElstein; 12-23-2018 at 04:44 PM.
    ….If you are my competitor, I will try all I can to beat you. But if I do, I will not belittle you. I will Salute you, because without you, I am nothing.
    If you are my enemy, we will try to kick the fucking shit out of you…..
    Winston Churchill, 1939
    Save your Forum..._
    _...KILL A MODERATOR!!

  8. #18
    Fuhrer, Vierte Reich DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    9,270
    Rep Power
    10

    Just testing.....

    Test

    مرحبا كيف حالك

    Example
    Copy table contents to VBE module Post#4 Re Paste back to spreadsheet
    5) Paste range values held in VBE module back to spreadsheet ( without deleting values from code module and selection of data range based on date



    Post #5 Sub PubeProFannyTeas__GLetner("____")

    This posts differs from that in the previous post in that it searches for a data range held towards the end of a code module, based on a given date.
    The date should have a 10 digit “DD MM YYYY” type format.
    For example, for the 23rd December, 2018 a calling code would look like this
    Code:
    Sub TestCall()
     Call PubeProFannyTeas__GLetner("23 12 2018")
    End Sub

    If the following data…._
    Code:
        Next Cnt '    next date range
    End Sub
    
    '_-23 12 2018 Worksheets("Tabelle1").Range("$G$2513:$H$2514")
    '_-91.01     | FH
    '_-163.73    | FH
    '_- EOF 23 12 2018
    
    '_-23 12 2018 Worksheets("Tabelle1").Range("$I$2513:$J$2514")
    '_-WH        | MH
    '_-WH        | MH
    '_- EOF 23 12 2018
    _.... were in the code module, ( for example after running code, Sub PubProliferous_Let_RngAsString__() ), then running the above would result in the range…___
    WH MH
    WH MH

    _... being pasted into worksheet with name “Tabelle1” starting top left at cell I2513. In other words the result would be in the worksheet something like:
    _____ Workbook: Uebersicht Aktuelle.xls ( Using Excel 2007 32 bit )
    Row\Col
    I
    J
    2513
    WH MH
    2514
    WH MH
    Worksheet: Tabelle1
    Notes:
    _ Only values are pasted, so the cells in the above example must have been previously in those text color formats
    _ For more than one matching date, the range furthest down will be used by the code

    Code description: (Sub PubeProFannyTeas__GLetner(ByVal strDte As String) )

    Rem 0 Code module as object
    As in previous codes, for convenience an object variable is set to the active code window.

    Rem 1 complete data range from end of code module
    '1a)
    This section effectively progresses “back down” the code window until an end routine is encountered and thereby counts to determine the number of data rows/lines
    '1b) A similar code line is used as previously for reading single lines, but by choosing more than 1 in the second argument a long single string, strIn , is returned with a vbCr & vbLf pair which are pseudo between*** the lines of data. ( This vbCr & vbLf pair seems to be characteristically “ there “ at the next line which is not in use or counted, such that it gets “tacked on” the start of new lines***
    A major characteristic of this routine is the manipulation of that single string using various VBA Strings collections functions.
    At this point the string might include more than one data range.
    '1c) Because we have a “line free” between data ranges held in the code window, this means that the string at these positions will have the usual pair of vbCr & vbLf pair, but because no data is on the line next part of the string will be the next vbCr & vbLf pair. Hence we have characteristically at this position vbCr & vbLf & vbCr & vbLf pair. Using vbCr & vbLf & vbCr & vbLf as separator is therefore a convenient way to split the complete string into an array where each array element is a single string of a complete data range

    Rem 2 manipulation of found date range
    We start here with a found range ( in a single long string format ) currently in the chosen array of ranges, and for no particular reason put that array element into a simple string variable. (There is no check for no matching date. The code will simple end after all ranges have been looped through.)
    '2a) The range information at the start of the string is easy to obtain from simple string manipulation , - in the 'comments example and detail is given )
    '2b)
    Further manipulation brings the string into the typical for which we find is what Excel gives and takes to paste in a range from the clipboard, For example for a 2 row x 3 column range like this…_
    Cell1 Cell2 Cell3
    _cell4 cell5 cell6
    _


    _... has a simple long single string form of
    Cell1 & vbTab & Cell2 & vbTab & Cell3 & vbCr & vbLf & cell4 & vbTab & cell5 & vbTab & cell6 & vbCr & vbLf

    Rem 3 range output to worksheet range
    The final prepared range string is given to a “Data object” which had a method which we use to put the string into the clipboard.
    A simple Paste is then all that is needed to put the data range of values into the worksheet.








    Rem Ref
    ' http://www.eileenslounge.com/viewtopic.php?f=30&t=31395
    ' http://www.cpearson.com/excel/vbe.aspx
    http://www.excelfox.com/forum/showth...ing-Techniques





    2513 WH MH
    2514 WH MH
    Worksheet: Tabelle1
    Notes:
    _ Only values are pasted, so the cells in the above example must have been previously in those text color formats
    _ For

    مرحبا كيف حالك

    مرحبا كيف حالك
    Last edited by DocAElstein; 12-24-2018 at 01:04 PM.
    ….If you are my competitor, I will try all I can to beat you. But if I do, I will not belittle you. I will Salute you, because without you, I am nothing.
    If you are my enemy, we will try to kick the fucking shit out of you…..
    Winston Churchill, 1939
    Save your Forum..._
    _...KILL A MODERATOR!!

  9. #19
    Fuhrer, Vierte Reich DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    9,270
    Rep Power
    10
    Testing solutions and tables for this Thread,
    http://www.excelfox.com/forum/showth...1124#post11124


    _____ Workbook: Data Sheet.xls ( Using Excel 2007 32 bit )
    Row\Col
    A
    B
    C
    D
    E
    F
    G
    H
    1
    Channel Date AdStart MidBreak Break_Start Break_End Hour
    149
    A NEWS
    15. Nov 17
    19:59:09
    Casual
    19:50:23
    20:00:05
    19
    150
    A NEWS
    15. Nov 17
    20:19:12
    Mid Break-1
    20:19:08
    20:24:07
    20
    151
    A NEWS
    15. Nov 17
    20:19:32
    Mid Break-1
    20:19:08
    20:24:07
    20
    152
    A NEWS
    15. Nov 17
    20:19:49
    Mid Break-1
    20:19:08
    20:24:07
    20
    153
    A NEWS
    15. Nov 17
    20:20:01
    Mid Break-1
    20:19:08
    20:24:07
    20
    154
    A NEWS
    15. Nov 17
    20:20:47
    Mid Break-1
    20:19:08
    20:24:07
    20
    155
    A NEWS
    15. Nov 17
    20:21:10
    Mid Break-1
    20:19:08
    20:24:07
    20
    156
    A NEWS
    15. Nov 17
    20:21:20
    Mid Break-1
    20:19:08
    20:24:07
    20
    157
    A NEWS
    15. Nov 17
    20:21:30
    Mid Break-1
    20:19:08
    20:24:07
    20
    Worksheet: Sheet2



    _____ Workbook: Data Sheet.xls ( Using Excel 2007 32 bit )
    Row\Col
    A
    B
    C
    D
    E
    1
    Date Channels start time New Time
    2
    11.15.2017
    A NEWS
    20:29:00
    20:19:12
    3
    11.15.2017
    A NEWS
    20:59:00
    20:21:20
    4
    11.15.2017
    A NEWS
    21:29:00
    21:13:49
    5
    11.15.2017
    A NEWS
    21:59:00
    21:36:30
    6
    11.15.2017
    A NEWS
    22:29:00
    22:27:36
    7
    11.15.2017
    A NEWS
    22:59:00
    22:48:51
    8
    11.15.2017
    A NEWS
    23:29:00
    23:28:33
    9
    11.15.2017
    A NEWS
    23:58:00
    23:29:55
    10
    11.16.2017
    A NEWS
    20:29:00
    20:23:02
    11
    11.16.2017
    A NEWS
    20:59:00
    20:50:04
    12
    11.16.2017
    A NEWS
    21:29:00
    Worksheet: Sheet1



    test....

    Row\Col
    A
    B
    C
    D
    E
    F
    G
    H
    I
    J
    K
    L
    M
    1
    Date Channels start time New Time
    2
    11.15.2017
    A NEWS
    20:29:00
    20:19:12
    3
    11.15.2017
    A NEWS
    20:59:00
    20:21:20
    Other lines from From sheet2:-
    4
    11.15.2017
    A NEWS
    21:29:00
    21:13:49
    A NEWS
    15. Nov 17
    21:13:49
    Mid Break-1
    21:12:55
    21:15:05
    21
    5
    11.15.2017
    A NEWS
    21:59:00
    21:36:30
    A NEWS
    15. Nov 17
    21:36:30
    Mid Break-2
    21:35:31
    21:40:14
    21
    6
    11.15.2017
    A NEWS
    22:29:00
    22:27:36
    A NEWS
    15. Nov 17
    22:27:36
    Mid Break-1
    22:26:54
    22:33:55
    22
    7
    11.15.2017
    A NEWS
    22:59:00
    22:48:51
    A NEWS
    15. Nov 17
    22:48:51
    Mid Break-2
    22:47:02
    22:54:02
    22
    8
    11.15.2017
    A NEWS
    23:29:00
    23:28:33
    A NEWS
    15. Nov 17
    23:28:33
    Mid Break-1
    23:26:54
    23:34:47
    23
    9
    11.15.2017
    A NEWS
    23:58:00
    23:29:55
    A NEWS
    15. Nov 17
    23:29:55
    Mid Break-1
    23:26:54
    23:34:47
    23
    10
    11.16.2017
    A NEWS
    20:29:00
    20:23:02
    A NEWS
    16. Nov 17
    20:23:02
    Mid Break-1
    20:22:21
    20:24:03
    20
    11
    11.16.2017
    A NEWS
    20:59:00
    20:50:04
    A NEWS
    16. Nov 17
    20:50:04
    Mid Break-3
    20:46:06
    20:53:37
    20
    12
    11.16.2017
    A NEWS
    21:29:00
    Last edited by DocAElstein; 04-20-2019 at 12:40 PM.

  10. #20
    Fuhrer, Vierte Reich DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    9,270
    Rep Power
    10
    I am stumbling to find the logic to match up these rows....

    _____ Workbook: Data Sheet.xls ( Using Excel 2007 32 bit )
    Row\Col
    A
    B
    C
    D
    E
    F
    G
    H
    I
    J
    K
    L
    M
    1
    Date Channels start time New Time Other lines from From sheet2:-
    2
    11.15.2017
    A NEWS
    20:29:00
    20:19:12
    A NEWS
    15. Nov 17
    20:19:12
    Mid Break-1
    20:19:08
    20:24:07
    20
    3
    11.15.2017
    A NEWS
    20:59:00
    20:21:20
    A NEWS
    15. Nov 17
    20:21:20
    Mid Break-1
    20:19:08
    20:24:07
    20
    4
    11.15.2017
    A NEWS
    21:29:00
    21:13:49
    A NEWS
    15. Nov 17
    21:13:49
    Mid Break-1
    21:12:55
    21:15:05
    21
    5
    11.15.2017
    A NEWS
    21:59:00
    21:36:30
    A NEWS
    15. Nov 17
    21:36:30
    Mid Break-2
    21:35:31
    21:40:14
    21
    6
    11.15.2017
    A NEWS
    22:29:00
    22:27:36
    A NEWS
    15. Nov 17
    22:27:36
    Mid Break-1
    22:26:54
    22:33:55
    22
    7
    11.15.2017
    A NEWS
    22:59:00
    22:48:51
    A NEWS
    15. Nov 17
    22:48:51
    Mid Break-2
    22:47:02
    22:54:02
    22
    8
    11.15.2017
    A NEWS
    23:29:00
    23:28:33
    A NEWS
    15. Nov 17
    23:28:33
    Mid Break-1
    23:26:54
    23:34:47
    23
    9
    11.15.2017
    A NEWS
    23:58:00
    23:29:55
    A NEWS
    15. Nov 17
    23:29:55
    Mid Break-1
    23:26:54
    23:34:47
    23
    10
    11.16.2017
    A NEWS
    20:29:00
    20:23:02
    A NEWS
    16. Nov 17
    20:23:02
    Mid Break-1
    20:22:21
    20:24:03
    20
    11
    11.16.2017
    A NEWS
    20:59:00
    20:50:04
    A NEWS
    16. Nov 17
    20:50:04
    Mid Break-3
    20:46:06
    20:53:37
    20
    12
    11.16.2017
    A NEWS
    21:29:00
    Worksheet: Sheet1




    Testing

    excelfox
    Last edited by DocAElstein; 04-21-2019 at 02:18 PM.

Similar Threads

  1. Replies: 184
    Last Post: 03-16-2024, 01:16 PM
  2. Vlookup help needed
    By AbuReem in forum Excel Help
    Replies: 15
    Last Post: 11-12-2013, 11:32 AM
  3. TESTING Column Letter test Sort Last Row
    By alansidman in forum Test Area
    Replies: 0
    Last Post: 10-24-2013, 07:14 PM
  4. formulas needed please
    By paul_pearson in forum Excel Help
    Replies: 5
    Last Post: 03-21-2013, 04:43 PM
  5. Feed / Post Data on Web Page Using VBA
    By in.vaibhav in forum Excel Help
    Replies: 10
    Last Post: 01-10-2013, 05:00 PM

Posting Permissions

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