Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copy between worksheets does not copy formulae just values | Excel Discussion (Misc queries) | |||
Copy/Paste how to avoid the copy of formula cells w/o calc values | Excel Discussion (Misc queries) | |||
copy values generated by conditional formula in one sheet to the other work sheet as values | Excel Worksheet Functions | |||
compare values between workbooks and copy values | Excel Programming | |||
Range COPY function - how to copy VALUES and not formulas | Excel Programming |