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

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

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

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

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

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

My macro is now working sweetly, thanks to Claus, using an intermediate
text file to transfer the variables from the external source, my macro
written with Macro Express Pro.

I've just come across the SaveSetting and GetSetting VBA functions to
save and recover data from the registry. MX Pro also has similar
commands. So would this be a viable and possibly superior alternative
method?

Terry, East Grinstead, UK