ExcelBanter - Help to copy values

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Help to copy values (https://www.excelbanter.com/excel-programming/277521-re-help-copy-values.html)

Help to copy values

Richard,
Try this
Sub Macro1()
Dim i As Long, j As Long, LR As Long
i = 3
j = 74 'starting row of column Z
LR = Range("Z" & Rows.Count).End(xlUp).Row
Range(Cells(70, 3), Cells(70, 12)).ClearContents
Do Until i = 13 Or j = LR + 1
If Range("Z" & j).Value * Range("AA" & j).Value 0 Then
'if negative numbers are involved change above to
'Abs(Range("Z" & j).Value * Range("AA" & j).Value)
Cells(70, i).Value = Range("Z" & j).Value
i = i + 1
End If
'if "0"s in column Z to be listed include these lines
'If Range("Z" & j).Value = 0 And Range("AA" & j).Value < 0 Then
'Cells(70, i).Value = Range("Z" & j).Value
'i = i + 1
'End If
j = j + 1
Loop
End Sub

HTH
Cecil

"Richard" wrote in message
...
Could anyone help me with this please.
I have a Target range of C70 to L70 (10 cells).

How do I copy the numbers in ColumnZ which are adjacent to
the corresponding numbers in ColumnAA in the table below
to the Range C70 to L70. Sometimes after I run my macro,
there are more or less numbers in ColumnAA and I just want
to copy up to 10 numbers but only those from columnZ that
have a number in the same row beside them.

In my table, I need to copy 10,2,4,7,9,8,5 and 6, (a total
of 8 numbers in this case) to cells commencing with C70
across. Cells K70 and L70 would be blank in this example.
If there are more than 10 rows that have data in both Z
and AA the ones over 10 are ignored.

Row ColZ ColAA
74 10 4.7
75 2 5.1
76 4 6.6
77 7 8.2
78 9 12.7
79 8 17.4
80 5 20.9
81 6 21.2
82 11
83 12
84 13
85 14
86 15
87 16
88 17
89 18
90 19
91 20
92 21
93 22
94 23
95 24
96 25
97 26

Help to copy values

Richard,
all should be numbers, it will crash if there is any text values in the
range.
Cecil

"Richard" wrote in message
...
Hi Cecil,
Thanks for your prompt reply. Just a bit of difficulty.
Your code correctly inserts the numbers in the cell range
C70:L70 but crashes on the line:-
If Range("Z" & j).Value * Range("AA" & j).Value 0 Then
Message id "Type mismatch".

It also leaves the range Z74:AA97 highlighted.
I've enclosed the code if you could take a look and see if
you can identify what is wrong. Apart from that hiccup it
works fine.

Thanks and regards,
Richard

Sub Get10()
ActiveSheet.Unprotect
Application.ScreenUpdating = False
Range("A74:B97").Select
Selection.Copy
Range("Z74").Select
Selection.PasteSpecial Paste:=xlValues,
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

'Alternative Method A works fine
'With Sheets("MySheet")
' .Range("Z74:z97").Find(what:=.Range ("z73").Value, _
' lookat:=xlWhole).Offset(0, 1).ClearContents
'End With

'Method B works fine
Dim r, c As Integer 'This method works as well
r = 74
c = 26

Do While Cells(r, c) ""
If Cells(r, c) = Cells(73, 26) Then
Cells(r, c + 1).ClearContents
End If
r = r + 1
Loop

Selection.Sort Key1:=Range("AA74"),
Order1:=xlAscending, Key2:=Range( _
"Z74"), Order2:=xlAscending, Header:=xlNo,
OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom

'Cecils code to copy to range C70:L70
Dim i As Long, j As Long, LR As Long
i = 3
j = 74 'starting row of column Z
LR = Range("Z" & Rows.Count).End(xlUp).Row
Range(Cells(70, 3), Cells(70, 12)).ClearContents
Do Until i = 13 Or j = LR + 1
If Range("Z" & j).Value * Range("AA" & j).Value 0
Then 'Crashes on this line "Type mismatch"
Cells(70, i).Value = Range("Z" & j).Value
i = i + 1
End If
j = j + 1
Loop

Application.CutCopyMode = False
ActiveWindow.LargeScroll ToLeft:=1
Range("L73").Select

Application.ScreenUpdating = True
ActiveSheet.Protect DrawingObjects:=True,
Contents:=True, Scenarios _
:=True
End Sub

-----Original Message-----
Richard,
Try this
Sub Macro1()
Dim i As Long, j As Long, LR As Long
i = 3
j = 74 'starting row of column Z
LR = Range("Z" & Rows.Count).End(xlUp).Row
Range(Cells(70, 3), Cells(70, 12)).ClearContents
Do Until i = 13 Or j = LR + 1
If Range("Z" & j).Value * Range("AA" & j).Value 0 Then
'if negative numbers are involved change above to
'Abs(Range("Z" & j).Value * Range("AA" & j).Value)
Cells(70, i).Value = Range("Z" & j).Value
i = i + 1
End If
'if "0"s in column Z to be listed include these lines
'If Range("Z" & j).Value = 0 And Range("AA" & j).Value <
0 Then
'Cells(70, i).Value = Range("Z" & j).Value
'i = i + 1
'End If
j = j + 1
Loop
End Sub

HTH
Cecil

"Richard" wrote in message
...
Could anyone help me with this please.
I have a Target range of C70 to L70 (10 cells).

How do I copy the numbers in ColumnZ which are adjacent
to
the corresponding numbers in ColumnAA in the table below
to the Range C70 to L70. Sometimes after I run my macro,
there are more or less numbers in ColumnAA and I just
want
to copy up to 10 numbers but only those from columnZ
that
have a number in the same row beside them.

In my table, I need to copy 10,2,4,7,9,8,5 and 6, (a
total
of 8 numbers in this case) to cells commencing with C70
across. Cells K70 and L70 would be blank in this
example.
If there are more than 10 rows that have data in both Z
and AA the ones over 10 are ignored.

Row ColZ ColAA
74 10 4.7
75 2 5.1
76 4 6.6
77 7 8.2
78 9 12.7
79 8 17.4
80 5 20.9
81 6 21.2
82 11
83 12
84 13
85 14
86 15
87 16
88 17
89 18
90 19
91 20
92 21
93 22
94 23
95 24
96 25
97 26

.

Help to copy values

Thanks Cecil,
Unfortunately, from time to time there will be one or two
letters in the AA column. This must be causing the
problem. Is there any way around this.
Cheers,
Richard.

-----Original Message-----
Richard,
all should be numbers, it will crash if there is any text
values in the
range.
Cecil

"Richard" wrote in message
...
Hi Cecil,
Thanks for your prompt reply. Just a bit of difficulty.
Your code correctly inserts the numbers in the cell
range
C70:L70 but crashes on the line:-
If Range("Z" & j).Value * Range("AA" & j).Value 0 Then
Message id "Type mismatch".

It also leaves the range Z74:AA97 highlighted.
I've enclosed the code if you could take a look and see
if
you can identify what is wrong. Apart from that hiccup
it
works fine.

Thanks and regards,
Richard

Sub Get10()
ActiveSheet.Unprotect
Application.ScreenUpdating = False
Range("A74:B97").Select
Selection.Copy
Range("Z74").Select
Selection.PasteSpecial Paste:=xlValues,
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

'Alternative Method A works fine
'With Sheets("MySheet")
' .Range("Z74:z97").Find(what:=.Range ("z73").Value,
_
' lookat:=xlWhole).Offset(0, 1).ClearContents
'End With

'Method B works fine
Dim r, c As Integer 'This method works as
well
r = 74
c = 26

Do While Cells(r, c) ""
If Cells(r, c) = Cells(73, 26) Then
Cells(r, c + 1).ClearContents
End If
r = r + 1
Loop

Selection.Sort Key1:=Range("AA74"),
Order1:=xlAscending, Key2:=Range( _
"Z74"), Order2:=xlAscending, Header:=xlNo,
OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom

'Cecils code to copy to range C70:L70
Dim i As Long, j As Long, LR As Long
i = 3
j = 74 'starting row of column Z
LR = Range("Z" & Rows.Count).End(xlUp).Row
Range(Cells(70, 3), Cells(70, 12)).ClearContents
Do Until i = 13 Or j = LR + 1
If Range("Z" & j).Value * Range("AA" & j).Value 0
Then 'Crashes on this line "Type mismatch"
Cells(70, i).Value = Range("Z" & j).Value
i = i + 1
End If
j = j + 1
Loop

Application.CutCopyMode = False
ActiveWindow.LargeScroll ToLeft:=1
Range("L73").Select

Application.ScreenUpdating = True
ActiveSheet.Protect DrawingObjects:=True,
Contents:=True, Scenarios _
:=True
End Sub

-----Original Message-----
Richard,
Try this
Sub Macro1()
Dim i As Long, j As Long, LR As Long
i = 3
j = 74 'starting row of column Z
LR = Range("Z" & Rows.Count).End(xlUp).Row
Range(Cells(70, 3), Cells(70, 12)).ClearContents
Do Until i = 13 Or j = LR + 1
If Range("Z" & j).Value * Range("AA" & j).Value 0
Then
'if negative numbers are involved change above to
'Abs(Range("Z" & j).Value * Range("AA" & j).Value)
Cells(70, i).Value = Range("Z" & j).Value
i = i + 1
End If
'if "0"s in column Z to be listed include these lines
'If Range("Z" & j).Value = 0 And Range("AA" & j).Value
<
0 Then
'Cells(70, i).Value = Range("Z" & j).Value
'i = i + 1
'End If
j = j + 1
Loop
End Sub

HTH
Cecil

"Richard" wrote in message
...
Could anyone help me with this please.
I have a Target range of C70 to L70 (10 cells).

How do I copy the numbers in ColumnZ which are
adjacent
to
the corresponding numbers in ColumnAA in the table
below
to the Range C70 to L70. Sometimes after I run my
macro,
there are more or less numbers in ColumnAA and I just
want
to copy up to 10 numbers but only those from columnZ
that
have a number in the same row beside them.

In my table, I need to copy 10,2,4,7,9,8,5 and 6, (a
total
of 8 numbers in this case) to cells commencing with
C70
across. Cells K70 and L70 would be blank in this
example.
If there are more than 10 rows that have data in
both Z
and AA the ones over 10 are ignored.

Row ColZ ColAA
74 10 4.7
75 2 5.1
76 4 6.6
77 7 8.2
78 9 12.7
79 8 17.4
80 5 20.9
81 6 21.2
82 11
83 12
84 13
85 14
86 15
87 16
88 17
89 18
90 19
91 20
92 21
93 22
94 23
95 24
96 25
97 26

.

.

Help to copy values

Thanks Cecil for you assistance,
Regards,
Richard

-----Original Message-----
Richard,
try this
Cecil

Sub Macro1()
Dim i As Long, j As Long, LR As Long
i = 3
j = 74 'starting row of column Z
LR = Range("Z" & Rows.Count).End(xlUp).Row
Range(Cells(70, 3), Cells(70, 12)).ClearContents
Do Until i = 13 Or j = LR + 1
If Not IsEmpty(Range("AA" & j).Value) And _
IsNumeric(Range("AA" & j).Value) Then
If Abs(Range("AA" & j).Value) 0 Then
Cells(70, i).Value = Range("Z" & j).Value
i = i + 1
End If
End If
j = j + 1
Loop
End Sub

"Richard" wrote in message
...
Thanks Cecil,
Unfortunately, from time to time there will be one or
two
letters in the AA column. This must be causing the
problem. Is there any way around this.
Cheers,
Richard.

.

Help to copy values

You are welcome.
Cecil

"Richard" wrote in message
...
Thanks Cecil for you assistance,
Regards,
Richard

-----Original Message-----
Richard,
try this
Cecil

Sub Macro1()
Dim i As Long, j As Long, LR As Long
i = 3
j = 74 'starting row of column Z
LR = Range("Z" & Rows.Count).End(xlUp).Row
Range(Cells(70, 3), Cells(70, 12)).ClearContents
Do Until i = 13 Or j = LR + 1
If Not IsEmpty(Range("AA" & j).Value) And _
IsNumeric(Range("AA" & j).Value) Then
If Abs(Range("AA" & j).Value) 0 Then
Cells(70, i).Value = Range("Z" & j).Value
i = i + 1
End If
End If
j = j + 1
Loop
End Sub

"Richard" wrote in message
...
Thanks Cecil,
Unfortunately, from time to time there will be one or
two
letters in the AA column. This must be causing the
problem. Is there any way around this.
Cheers,
Richard.

.