Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old January 29th 20, 12:21 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2015
Posts: 192
Default Copy multi-line variable to first empty row

I have a string variable 'tAll_VBA' in my workbook 'WalkIndex.xlsm',
sheet 'Target'. It contains 21 values on 21 separate lines, all ending
in a CR/LF character. I want to copy all except the first two lines to
19 specified columns of the first empty row of the worksheet (i.e col A
empty). The variable and target cols are as follows:

Destination Source
WalkIndex Line number
column in tVBA_All
---------- ------------
No copy 2
A 4
B 5
C 3
H 11
I 9
J 6
K 7
L 8
M 10
N 12
O 13
P 14
R 15
S 16
T 17
U 19
V 18
W 20
Y 21
Not copied 1

I'm not sure how that mapping will be displayed after pressing Send so
here it is again in Line order:

Col Line
1
2
C 3
A 4
B 5
J 6
K 7
L 8
I 9
M 10
H 11
N 12
O 13
P 14
R 15
S 16
T 17
V 18
U 19
W 20
Y 21

I have previously used the following successfully in another macro to
get the last row:

Set wi = Workbooks("WalkIndex.xlsm")
Set ws = wi.Sheets("Target")

'Find the first empty row (col A) in WalkIndex
With ws
'Find first empty row (last used + 1)
nr = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
'MsgBox nr

Any pointers on how to code this would be much appreciated please.

Terry, UK

  #2   Report Post  
Old January 29th 20, 02:44 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,762
Default Copy multi-line variable to first empty row

Hi Terry,

Am Wed, 29 Jan 2020 11:21:00 +0000 schrieb Terry Pinnell:

I have a string variable 'tAll_VBA' in my workbook 'WalkIndex.xlsm',
sheet 'Target'. It contains 21 values on 21 separate lines, all ending
in a CR/LF character. I want to copy all except the first two lines to
19 specified columns of the first empty row of the worksheet (i.e col A
empty). The variable and target cols are as follows:


Col Line
1
2
C 3
A 4
B 5
J 6
K 7
L 8
I 9
M 10
H 11
N 12
O 13
P 14
R 15
S 16
T 17
V 18
U 19
W 20
Y 21


try:

strCols = "C,A,B,J,K,L,I,M,H,N,O,P,R,S,T,V,U,W,Y"
varCols = Split(strCols, ",")
varTmp = Split(tAll_VBA, Chr(10))
For i = 2 To UBound(varTmp)
Sheets("Target").Cells(i + 1, varCols(i - 2)) = varTmp(i)
Next


Regards
Claus B.
--
Windows10
Office 2016
  #3   Report Post  
Old January 29th 20, 06:49 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2015
Posts: 192
Default Copy multi-line variable to first empty row

Claus Busch wrote:

Hi Terry,

Am Wed, 29 Jan 2020 11:21:00 +0000 schrieb Terry Pinnell:

I have a string variable 'tAll_VBA' in my workbook 'WalkIndex.xlsm',
sheet 'Target'. It contains 21 values on 21 separate lines, all ending
in a CR/LF character. I want to copy all except the first two lines to
19 specified columns of the first empty row of the worksheet (i.e col A
empty). The variable and target cols are as follows:


Col Line
1
2
C 3
A 4
B 5
J 6
K 7
L 8
I 9
M 10
H 11
N 12
O 13
P 14
R 15
S 16
T 17
V 18
U 19
W 20
Y 21


try:

strCols = "C,A,B,J,K,L,I,M,H,N,O,P,R,S,T,V,U,W,Y"
varCols = Split(strCols, ",")
varTmp = Split(tAll_VBA, Chr(10))
For i = 2 To UBound(varTmp)
Sheets("Target").Cells(i + 1, varCols(i - 2)) = varTmp(i)
Next


Regards
Claus B.


Thanks Claus, very grateful for your help. I'm confident your code is
VERY close to working but it's giving some incorrect results at present.
I've included a link to the test workbook WalkIndex-Claus.xlsm so that
you can see what I mean in detail. Should have done that in my original
post, sorry.

https://www.dropbox.com/s/xbwgtqs23z...aus.xlsm?raw=1

Terry, East Grinstead, UK
  #4   Report Post  
Old January 29th 20, 08:27 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,762
Default Copy multi-line variable to first empty row

Hi Terry,

Am Wed, 29 Jan 2020 17:49:49 +0000 schrieb Terry Pinnell:

Thanks Claus, very grateful for your help. I'm confident your code is
VERY close to working but it's giving some incorrect results at present.
I've included a link to the test workbook WalkIndex-Claus.xlsm so that
you can see what I mean in detail. Should have done that in my original
post, sorry.

https://www.dropbox.com/s/xbwgtqs23z...aus.xlsm?raw=1


try:

Dim objData As New MSForms.DataObject
Dim tAll_VBA
Dim strCols As String
Dim strValues As String
'Types for these arrays?
Dim varTmp As Variant
Dim varCols As Variant
Dim varValues As Variant
Dim LRow As Long
Dim i As Integer

objData.GetFromClipboard
tAll_VBA = objData.GetText()
'For testing
MsgBox tAll_VBA
Debug.Print tAll_VBA

'Now need to get that into a new row of WI

'This block from Claus
strCols = "A,B,C,H,I,J,K,L,M,N,O,P,R,S,T,U,V,W"
varCols = Split(strCols, ",")
strValues = "3,4,2,10,8,5,6,7,9,11,12,13,14,15,16,17,18,19 "
varValues = Split(strValues, ",")
varTmp = Split(tAll_VBA, Chr(10))
With Sheets("Target")
LRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
For i = LBound(varCols) To UBound(varCols)
Select Case varCols(i)
Case "J", "K", "L"
.Cells(LRow, varCols(i)) = Format(varTmp(varValues(i)), "hh:mm")
Case Else
.Cells(LRow, varCols(i)) = varTmp(varValues(i))
End Select
Next
End With

Or download the workbook from he
https://1drv.ms/x/s!AqMiGBK2qniTgel7...Xqp_g?e=ISgyGm


Regards
Claus B.
--
Windows10
Office 2016
  #5   Report Post  
Old January 29th 20, 10:26 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2015
Posts: 192
Default Copy multi-line variable to first empty row

Claus Busch wrote:

Hi Terry,

Am Wed, 29 Jan 2020 17:49:49 +0000 schrieb Terry Pinnell:

Thanks Claus, very grateful for your help. I'm confident your code is
VERY close to working but it's giving some incorrect results at present.
I've included a link to the test workbook WalkIndex-Claus.xlsm so that
you can see what I mean in detail. Should have done that in my original
post, sorry.

https://www.dropbox.com/s/xbwgtqs23z...aus.xlsm?raw=1


try:

Dim objData As New MSForms.DataObject
Dim tAll_VBA
Dim strCols As String
Dim strValues As String
'Types for these arrays?
Dim varTmp As Variant
Dim varCols As Variant
Dim varValues As Variant
Dim LRow As Long
Dim i As Integer

objData.GetFromClipboard
tAll_VBA = objData.GetText()
'For testing
MsgBox tAll_VBA
Debug.Print tAll_VBA

'Now need to get that into a new row of WI

'This block from Claus
strCols = "A,B,C,H,I,J,K,L,M,N,O,P,R,S,T,U,V,W"
varCols = Split(strCols, ",")
strValues = "3,4,2,10,8,5,6,7,9,11,12,13,14,15,16,17,18,19 "
varValues = Split(strValues, ",")
varTmp = Split(tAll_VBA, Chr(10))
With Sheets("Target")
LRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
For i = LBound(varCols) To UBound(varCols)
Select Case varCols(i)
Case "J", "K", "L"
.Cells(LRow, varCols(i)) = Format(varTmp(varValues(i)), "hh:mm")
Case Else
.Cells(LRow, varCols(i)) = varTmp(varValues(i))
End Select
Next
End With

Or download the workbook from he
https://1drv.ms/x/s!AqMiGBK2qniTgel7...Xqp_g?e=ISgyGm


Regards
Claus B.


Excellent, works perfectly now, thanks a lot Claus ;-)

One minor point that probably has a simple answer; did you notice that
the entries in cols N, O and P are red? The cols are not formatted with
red font so I'm puzzled.

Terry, UK


  #6   Report Post  
Old January 30th 20, 06:44 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,762
Default Copy multi-line variable to first empty row

Hi Terry,

Am Wed, 29 Jan 2020 21:26:51 +0000 schrieb Terry Pinnell:

One minor point that probably has a simple answer; did you notice that
the entries in cols N, O and P are red? The cols are not formatted with
red font so I'm puzzled.


some of your cells are formatted.
Insert a line into the code (after LRow=...)to clear the formats:

LRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
..Rows(LRow).ClearFormats

What kind of file is your source and what is the name of it?
Perhaps there is an easier way without the clipboard.


Regards
Claus B.
--
Windows10
Office 2016
  #7   Report Post  
Old January 30th 20, 02:38 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2015
Posts: 192
Default Copy multi-line variable to first empty row

Claus Busch wrote:

Hi Terry,

Am Wed, 29 Jan 2020 21:26:51 +0000 schrieb Terry Pinnell:

One minor point that probably has a simple answer; did you notice that
the entries in cols N, O and P are red? The cols are not formatted with
red font so I'm puzzled.


some of your cells are formatted.
Insert a line into the code (after LRow=...)to clear the formats:

LRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
.Rows(LRow).ClearFormats

What kind of file is your source and what is the name of it?
Perhaps there is an easier way without the clipboard.


Regards
Claus B.


You're right, the issue about formatting has arisen because I decided a
few weeks ago to make major simplifications. I try to keep my Excel
questions much simplified, but - although I don't want to lose you due
to boredom! - I think the following background would be useful now.

The previous WalkIndex layout had 54 columns! As you see it now has half
that number. It lists 25 years of walks and has naturally evolved. So
has the method of updating it. For some years that has come from a
complex macro written with Macro Express Pro. That too is evolving. I
supplement it with calls to VBA macros, like the one under discussion.
Where I sacrifice my comfort zone for speed and versatility. With the
same motive I also call Python scripts in PaintShop Pro (with help from
the experts).

Almost all the values in WalkIndex.xlsm originate from the GPX file made
during a walk (currently by my iPhone 6S+). My MX macro works with a
program called GPS Utility to assign string values to all the variables
detailed in my opening post. In future I need to copy those to WalkIndex
as described, and to a small text files, one for each walk,
tTrackName.txt. These are the much simplified replacements for the
individual track sheets I had been making that looked like this:
https://www.dropbox.com/s/qg52nd2bhn...book.jpg?raw=1
(I'm considering eliminating even the text files, as all required data
will be in WalkIndex.)

So the current status is this: when my MX macro has finished its
analysis, and before calling any VBA macro, the data is in two places:
1. tTrackName.txt, where it looks like this
https://www.dropbox.com/s/6nkh9tvpi3...m4.4.txt?raw=1

2. On the clipboard, in more compact form in variable tAll_VBA. That is
ready for access when the MX macro then opens WalkIndex.xlsm (or a test
version of that during our discussion).

I've changed the cell colour of cols N, O & P from red to black.

So far I've listed these outstanding points:

1. Col A was developed in the latest version of my MX macro as a text
string which resists Excel formatting. My previous col A entries were
formatted ddd dd/mm/yy and that's the appearance I want to retain. So I
will re-examine my MX macro. Note that I'm using 'Date of walk' as the
first part of the concatenated string in col R. That's the closest I
could get in MX to my preference of 'Monday 8th July 2019, Mawnan Smith
to Falmouth', which I achieved in my earlier VBA code versions, via that
intermediate track sheet. This is an important requirement, as the next
stage of my MX macro is to completely automate the preparation of a JPG,
in which the title uses col R, like this example:
https://www.dropbox.com/s/6hj3bdblko...m6.5.jpg?raw=1

2. Cols J and K are also presumably suffering from some formatting
issue, as they should be 09:34 and 13:21

3. Cols H to P are not quite centered, although their alignment is set
to Center.

I too wish there was a way to avoid the clipboard. Short of doing it
entirely and glacially slowly by simulating keystrokes and mouse clicks
in my MX macro. It came as a surprise to find that it needs nearly a
hundred lines of code to do a simple paste in Windows 10 Pro, although I
now happily ignore that overhead sitting in a module!

Best wishes,

Terry, East Grinstead, UK


  #8   Report Post  
Old January 30th 20, 06:40 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2015
Posts: 192
Default Copy multi-line variable to first empty row

Claus Busch wrote:

Hi Terry,

Am Wed, 29 Jan 2020 21:26:51 +0000 schrieb Terry Pinnell:

One minor point that probably has a simple answer; did you notice that
the entries in cols N, O and P are red? The cols are not formatted with
red font so I'm puzzled.


some of your cells are formatted.
Insert a line into the code (after LRow=...)to clear the formats:

LRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
.Rows(LRow).ClearFormats

What kind of file is your source and what is the name of it?
Perhaps there is an easier way without the clipboard.


Regards
Claus B.


One small simplification:
https://www.dropbox.com/s/7iwg59xtzz...laus.jpg?raw=1

Terry, East Grinstead, UK
  #9   Report Post  
Old January 30th 20, 10:18 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2015
Posts: 192
Default Copy multi-line variable to first empty row

Claus Busch wrote:

Hi Terry,

Am Wed, 29 Jan 2020 21:26:51 +0000 schrieb Terry Pinnell:

One minor point that probably has a simple answer; did you notice that
the entries in cols N, O and P are red? The cols are not formatted with
red font so I'm puzzled.


some of your cells are formatted.
Insert a line into the code (after LRow=...)to clear the formats:

LRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
.Rows(LRow).ClearFormats

What kind of file is your source and what is the name of it?
Perhaps there is an easier way without the clipboard.


Regards
Claus B.


No success so far with the first of my 'outstanding points', getting the
col A date in form 'ddd dd/mm/yy'.

This test macro works OK, but after many attempts I still haven't
succeded in doing the same within your macro, as Case "A"

Sub TestSplit_1()

Dim tDate1 As String
Dim tDate1Array() As String

tDate1 = "Thursday 19 September 2019"
tDate1Array() = Split(tDate1)

tDate1 = tDate1Array(1) & " " & tDate1Array(2) & " " &
tDate1Array(3)
MsgBox tDate1
Range("A800") = tDate1

End Sub

Terry, UK
  #10   Report Post  
Old January 30th 20, 11:24 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2015
Posts: 1,152
Default Copy multi-line variable to first empty row

Terry,
Couple of things:

Date formatting and its display format are 2 separate steps:

1a. Format colA as Date

1b. Then choose 'Custom' to set the display format how you want it:
[$-409]ddd dd/mm/yy;@ Wed 01/01/20
OR
[$-409]ddd dd-mm-yy;@ Wed 01-01-20
OR
[$-409]ddd-ddmmyy;@ Wed-010120


Nix on the 100+ lines of code! A text file being used as a database should be
properly configured as a database so its content can be more simply managed.

2a. Your text file is problematic because its worksheet source uses rows
for spacing rather than RowHeight, which makes using code to import its
data a rather daunting task of checking for empty lines and other stuff
that makes managing the data into the target worksheet way more complex
than it needs to be.

2b. Using ADODB to read the text file into a recordset requires only 1 line
of code to dump the data into the worksheet cols in 1 shot.

2c. Reading the data into an array will also enable dumping it into the
sheet in 1 shot.

2d. It's a rather simple task to manage how the target sheet handles your
preferred formatting of the data after (or even before) importing to the
worksheet.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Variable series multi line chart? cherman Charts and Charting in Excel 1 February 17th 10 10:29 PM
Macro to validate is cell is not empty and copy the line Steff[_3_] Excel Programming 2 December 10th 07 10:16 PM
Copy row to next empty line [email protected] Excel Programming 9 November 9th 07 02:19 PM
Copy and Paste in the first empty available line. Etienne Excel Programming 5 August 3rd 06 03:49 PM
copy/paste excel to texteditor -> multi-line text cells gain unwanted double-quotes niz Excel Discussion (Misc queries) 1 October 14th 05 02:06 PM


All times are GMT +1. The time now is 01:28 AM.

Powered by vBulletin® Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright 2004-2020 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017