Help to copy values
 

Dim rng as Range
Dim rng1 as Range
Dim i as long
Dim j as Long
set rng = Range("AA74:AA100").specialCells(xlConstants,xlNum bers)
set rng1 = Intersect(rng.Entirerow,Columns(26))
i = 0
j = 3
for each cell in rng1
i = i + 1
j = j + 1
cells(70,i).Value = cell.Value
if i = 10 then exit for
Next


If the numbers in AA are produced by formulas, then change xlConstants to
xlformulas

--
Regards,
Tom Ogilvy


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
 

cells(70,i).Value = cell.Value

should be

cells(70,j).Value = cell.Value

--
Regards,
Tom Ogilvy

Richard wrote in message
...
Hi Tom,
Thankyou for your help.
I think your code is just about right but a slight problem.
It runs through without a problem but copies the results
to start from A70 not C70 across, thereby deleting data I
have stored in A70 and B70.

Attached is all the code. Could you have a quick look at
it. Some parameter is not quite right I think.
Thanks for your assistance.
Regards.

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


Dim r, c As Integer
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

''Toms code to copy to range C70:L70
Dim rng As Range
Dim rng1 As Range
Dim i As Long
Dim j As Long
Set rng = Range("AA74:AA97").SpecialCells(xlConstants,
xlNumbers)
Set rng1 = Intersect(rng.EntireRow, Columns(26))
i = 0
j = 3
For Each cell In rng1
i = i + 1
j = j + 1
Cells(70, i).Value = cell.Value
If i = 10 Then Exit For
Next


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-----
Dim rng as Range
Dim rng1 as Range
Dim i as long
Dim j as Long
set rng = Range("AA74:AA100").specialCells
(xlConstants,xlNumbers)
set rng1 = Intersect(rng.Entirerow,Columns(26))
i = 0
j = 3
for each cell in rng1
i = i + 1
j = j + 1
cells(70,i).Value = cell.Value
if i = 10 then exit for
Next


If the numbers in AA are produced by formulas, then
change xlConstants to
xlformulas

--
Regards,
Tom Ogilvy


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 Tom,
Have made that correction but still a bit of a problem.
The values in Z74.AA97 are fine, but when run, the macro
puts a blank in cell C70. If I run it again i.e. a second
time without clearing everything first, it duplicates the
number in Z74 in cells C70 and C71. Examples below:-
Any thoughts on this:
Richard

B C D E F G H I J K
70 4 1 10 2 7 9 8 5 6

4 Target
74 1 3.7
75 10 4.7
76 2 5.1
77 7 8.2
78 9 12.7
79 8 17.4
80 5 20.9
81 6 21.2
82 11
83 12

Running it a second time leaving the above cells as they
are produces:


B C D E F G H I J K
70 4 1 1 10 2 7 9 8 5 6 Repeats the 1 number

4 Target This table remains identical
74 1 3.7
75 10 4.7
76 2 5.1
77 7 8.2
78 9 12.7
79 8 17.4
80 5 20.9
81 6 21.2
82 11
83 12

-----Original Message-----
cells(70,i).Value = cell.Value

should be

cells(70,j).Value = cell.Value

--
Regards,
Tom Ogilvy

Richard wrote in message
...
Hi Tom,
Thankyou for your help.
I think your code is just about right but a slight
problem.
It runs through without a problem but copies the results
to start from A70 not C70 across, thereby deleting data
I
have stored in A70 and B70.

Attached is all the code. Could you have a quick look at
it. Some parameter is not quite right I think.
Thanks for your assistance.
Regards.

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


Dim r, c As Integer
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

''Toms code to copy to range C70:L70
Dim rng As Range
Dim rng1 As Range
Dim i As Long
Dim j As Long
Set rng = Range("AA74:AA97").SpecialCells(xlConstants,
xlNumbers)
Set rng1 = Intersect(rng.EntireRow, Columns(26))
i = 0
j = 3
For Each cell In rng1
i = i + 1
j = j + 1
Cells(70, i).Value = cell.Value
If i = 10 Then Exit For
Next


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-----
Dim rng as Range
Dim rng1 as Range
Dim i as long
Dim j as Long
set rng = Range("AA74:AA100").specialCells
(xlConstants,xlNumbers)
set rng1 = Intersect(rng.Entirerow,Columns(26))
i = 0
j = 3
for each cell in rng1
i = i + 1
j = j + 1
cells(70,i).Value = cell.Value
if i = 10 then exit for
Next


If the numbers in AA are produced by formulas, then
change xlConstants to
xlformulas

--
Regards,
Tom Ogilvy


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
 

Change j = 3 to j = 2

the only problem was it always wrote to column D first rather than C because
I incremented J before it did the writing. So initializing j to 2 causes it
to start in column C. any observed change to C70 was caused by other than
my code - although now it will start in C70 rather than D70.


Sub Tester1()
Dim rng As Range
Dim rng1 As Range
Dim i As Long
Dim j As Long
Range("C70:L70").ClearContents
set rng = Range("AA74:AA100").SpecialCells(xlConstants, xlNumbers)
Set rng1 = Intersect(rng.EntireRow, Columns(26))
i = 0
j = 2
For Each cell In rng1
i = i + 1
j = j + 1
Cells(70, j).Value = cell.Value
If i = 10 Then Exit For
Next


End Sub



--
Regards,
Tom Ogilvy

Richard wrote in message
...
Thanks Tom,
Have made that correction but still a bit of a problem.
The values in Z74.AA97 are fine, but when run, the macro
puts a blank in cell C70. If I run it again i.e. a second
time without clearing everything first, it duplicates the
number in Z74 in cells C70 and C71. Examples below:-
Any thoughts on this:
Richard

B C D E F G H I J K
70 4 1 10 2 7 9 8 5 6

4 Target
74 1 3.7
75 10 4.7
76 2 5.1
77 7 8.2
78 9 12.7
79 8 17.4
80 5 20.9
81 6 21.2
82 11
83 12

Running it a second time leaving the above cells as they
are produces:


B C D E F G H I J K
70 4 1 1 10 2 7 9 8 5 6 Repeats the 1 number

4 Target This table remains identical
74 1 3.7
75 10 4.7
76 2 5.1
77 7 8.2
78 9 12.7
79 8 17.4
80 5 20.9
81 6 21.2
82 11
83 12

-----Original Message-----
cells(70,i).Value = cell.Value

should be

cells(70,j).Value = cell.Value

--
Regards,
Tom Ogilvy

Richard wrote in message
...
Hi Tom,
Thankyou for your help.
I think your code is just about right but a slight
problem.
It runs through without a problem but copies the results
to start from A70 not C70 across, thereby deleting data
I
have stored in A70 and B70.

Attached is all the code. Could you have a quick look at
it. Some parameter is not quite right I think.
Thanks for your assistance.
Regards.

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


Dim r, c As Integer
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

''Toms code to copy to range C70:L70
Dim rng As Range
Dim rng1 As Range
Dim i As Long
Dim j As Long
Set rng = Range("AA74:AA97").SpecialCells(xlConstants,
xlNumbers)
Set rng1 = Intersect(rng.EntireRow, Columns(26))
i = 0
j = 3
For Each cell In rng1
i = i + 1
j = j + 1
Cells(70, i).Value = cell.Value
If i = 10 Then Exit For
Next


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-----
Dim rng as Range
Dim rng1 as Range
Dim i as long
Dim j as Long
set rng = Range("AA74:AA100").specialCells
(xlConstants,xlNumbers)
set rng1 = Intersect(rng.Entirerow,Columns(26))
i = 0
j = 3
for each cell in rng1
i = i + 1
j = j + 1
cells(70,i).Value = cell.Value
if i = 10 then exit for
Next


If the numbers in AA are produced by formulas, then
change xlConstants to
xlformulas

--
Regards,
Tom Ogilvy


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 Tom, appreciate your help,
Regards,
Richard


-----Original Message-----
Change j = 3 to j = 2

the only problem was it always wrote to column D first
rather than C because
I incremented J before it did the writing. So
initializing j to 2 causes it
to start in column C. any observed change to C70 was
caused by other than
my code - although now it will start in C70 rather than
D70.


Sub Tester1()
Dim rng As Range
Dim rng1 As Range
Dim i As Long
Dim j As Long
Range("C70:L70").ClearContents
set rng = Range("AA74:AA100").SpecialCells(xlConstants,
xlNumbers)
Set rng1 = Intersect(rng.EntireRow, Columns(26))
i = 0
j = 2
For Each cell In rng1
i = i + 1
j = j + 1
Cells(70, j).Value = cell.Value
If i = 10 Then Exit For
Next


End Sub



--
Regards,
Tom Ogilvy

Richard wrote in message
...
Thanks Tom,
Have made that correction but still a bit of a problem.
The values in Z74.AA97 are fine, but when run, the macro
puts a blank in cell C70. If I run it again i.e. a
second
time without clearing everything first, it duplicates
the
number in Z74 in cells C70 and C71. Examples below:-
Any thoughts on this:
Richard

B C D E F G H I J K
70 4 1 10 2 7 9 8 5 6

4 Target
74 1 3.7
75 10 4.7
76 2 5.1
77 7 8.2
78 9 12.7
79 8 17.4
80 5 20.9
81 6 21.2
82 11
83 12

Running it a second time leaving the above cells as they
are produces:


B C D E F G H I J K
70 4 1 1 10 2 7 9 8 5 6 Repeats the 1 number

4 Target This table remains identical
74 1 3.7
75 10 4.7
76 2 5.1
77 7 8.2
78 9 12.7
79 8 17.4
80 5 20.9
81 6 21.2
82 11
83 12

-----Original Message-----
cells(70,i).Value = cell.Value

should be

cells(70,j).Value = cell.Value

--
Regards,
Tom Ogilvy

Richard wrote in message
...
Hi Tom,
Thankyou for your help.
I think your code is just about right but a slight
problem.
It runs through without a problem but copies the
results
to start from A70 not C70 across, thereby deleting
data
I
have stored in A70 and B70.

Attached is all the code. Could you have a quick
look at
it. Some parameter is not quite right I think.
Thanks for your assistance.
Regards.

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


Dim r, c As Integer
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

''Toms code to copy to range C70:L70
Dim rng As Range
Dim rng1 As Range
Dim i As Long
Dim j As Long
Set rng = Range("AA74:AA97").SpecialCells
(xlConstants,
xlNumbers)
Set rng1 = Intersect(rng.EntireRow, Columns(26))
i = 0
j = 3
For Each cell In rng1
i = i + 1
j = j + 1
Cells(70, i).Value = cell.Value
If i = 10 Then Exit For
Next


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-----
Dim rng as Range
Dim rng1 as Range
Dim i as long
Dim j as Long
set rng = Range("AA74:AA100").specialCells
(xlConstants,xlNumbers)
set rng1 = Intersect(rng.Entirerow,Columns(26))
i = 0
j = 3
for each cell in rng1
i = i + 1
j = j + 1
cells(70,i).Value = cell.Value
if i = 10 then exit for
Next


If the numbers in AA are produced by formulas, then
change xlConstants to
xlformulas

--
Regards,
Tom Ogilvy


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



.



.



.