Example
Copy table contents to VBE module Part#4b ( Post #5) Re Paste back to spreadsheet
4b) 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….__.... were in the code module, ( for example after running code, Sub PubProliferous_Let_RngAsString__() ), then running the above would result in the range…___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
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 )
Worksheet: Tabelle1
Row\Col WH MH WH MH
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 simply 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” . That “Data object” has a method , ( PutInClipboard ) , which we use to put that string into the clipboard.
A simple Paste is then all that is needed to put the data range of values into the worksheet.
Final code is here:
http://www.excelfox.com/forum/showth...0849#post10849
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...ll=1#post10971
http://www.excelfox.com/forum/showth...1011#post11011
Reply With Quote
Bookmarks