Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
a script/macro to copy a block of cells next to specified cells
Here's what I'd like to do:
1. copy cell b3 all the way down to b(n), where n is the number of data in column a; this produces some cells in column b with a numerical value or character; 2. copy a block of cells, e.g., b1-c2, where cell b1 would be placed in column c next to every occurrence of the previous numerical value. I believe a simple macro can't do it, only a VBA scipt could, but am no expert at it... Help would be much appreciated. z.entropic |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
a script/macro to copy a block of cells next to specified cells
z. enthalpic,
1) Range("B3").AutoFill Destination:=Range _ (Range("B3"), Range("A65536").End(xlUp)(1, 2)) 2) Not sure what you mean. Does the formula from step 1 return different values? I believe a simple macro can't do it, A macro can do almost anything.... HTH, Bernie MS Excel MVP "z.entropic" wrote in message ... Here's what I'd like to do: 1. copy cell b3 all the way down to b(n), where n is the number of data in column a; this produces some cells in column b with a numerical value or character; 2. copy a block of cells, e.g., b1-c2, where cell b1 would be placed in column c next to every occurrence of the previous numerical value. I believe a simple macro can't do it, only a VBA scipt could, but am no expert at it... Help would be much appreciated. z.entropic |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
a script/macro to copy a block of cells next to specified cells
set rng = Range(Range("B3"),Cells(rows.count,1).End(xlup).of fset(0,1))
rng.Filldown Don't understand you question 2. -- Regards, Tom Ogilvy "z.entropic" wrote in message ... Here's what I'd like to do: 1. copy cell b3 all the way down to b(n), where n is the number of data in column a; this produces some cells in column b with a numerical value or character; 2. copy a block of cells, e.g., b1-c2, where cell b1 would be placed in column c next to every occurrence of the previous numerical value. I believe a simple macro can't do it, only a VBA scipt could, but am no expert at it... Help would be much appreciated. z.entropic |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
a script/macro to copy a block of cells next to specified cells
-----Original Message----- set rng = Range(Range("B3"),Cells(rows.count,1).End (xlup).offset(0,1)) rng.Filldown Don't understand you question 2. Yes, it's clumsy... so let me try again, in a slightly less clumsy way: 2. I have a block of cells in, e.g., c1, c2, d1 and d2, which I'd like this macro to copy next to the individual value/marker calculated in column b by the first part of the macro. This marker (it can be a character) is the result of a TRUE/FALSE test on the data from column a, thus only a small percentage of cells in column b will be filled. That's where, i.e., next to it in col. c, I'd like the macro to copy the block of formulas, which would use data in column a to givea block of new data. Thanks for your help, z.entropic -- Regards, Tom Ogilvy "z.entropic" wrote in message ... Here's what I'd like to do: 1. copy cell b3 all the way down to b(n), where n is the number of data in column a; this produces some cells in column b with a numerical value or character; 2. copy a block of cells, e.g., b1-c2, where cell b1 would be placed in column c next to every occurrence of the previous numerical value. I believe a simple macro can't do it, only a VBA scipt could, but am no expert at it... Help would be much appreciated. z.entropic . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
a script/macro to copy a block of cells next to specified cells
-----Original Message-----
z. enthalpic, My Excel knowledge is more disorganizaed than that... <GGG 1) Range("B3").AutoFill Destination:=Range _ (Range("B3"), Range("A65536").End(xlUp)(1, 2)) 2) Not sure what you mean. Does the formula from step 1 return different values? No, it's a TRUE/FALSE test. If TRUE, a value/character is returned, if not, a "". Of course, it can be somthing else. Please take a look at my attempt at a better explanation in response to Tom Ogilvy's reply. I believe a simple macro can't do it, A macro can do almost anything.... Yes, but not a simple one? <g z.entropic HTH, Bernie MS Excel MVP "z.entropic" wrote in message ... Here's what I'd like to do: 1. copy cell b3 all the way down to b(n), where n is the number of data in column a; this produces some cells in column b with a numerical value or character; 2. copy a block of cells, e.g., b1-c2, where cell b1 would be placed in column c next to every occurrence of the previous numerical value. I believe a simple macro can't do it, only a VBA scipt could, but am no expert at it... Help would be much appreciated. z.entropic . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
a script/macro to copy a block of cells next to specified cells
Dim rng as Range
Marker as Variant Dim cell as Range Marker = "some value" set rng = Range(Range("B3"), _ Cells(rows.count,1).End(xlup).offset(0,1)) rng.Filldown for each cell in rng if cell.value = marker then Range("C1:D1").copy Destination:=cell.offset(0,1) end if Next -- Regards, Tom Ogilvy wrote in message ... -----Original Message----- set rng = Range(Range("B3"),Cells(rows.count,1).End (xlup).offset(0,1)) rng.Filldown Don't understand you question 2. Yes, it's clumsy... so let me try again, in a slightly less clumsy way: 2. I have a block of cells in, e.g., c1, c2, d1 and d2, which I'd like this macro to copy next to the individual value/marker calculated in column b by the first part of the macro. This marker (it can be a character) is the result of a TRUE/FALSE test on the data from column a, thus only a small percentage of cells in column b will be filled. That's where, i.e., next to it in col. c, I'd like the macro to copy the block of formulas, which would use data in column a to givea block of new data. Thanks for your help, z.entropic -- Regards, Tom Ogilvy "z.entropic" wrote in message ... Here's what I'd like to do: 1. copy cell b3 all the way down to b(n), where n is the number of data in column a; this produces some cells in column b with a numerical value or character; 2. copy a block of cells, e.g., b1-c2, where cell b1 would be placed in column c next to every occurrence of the previous numerical value. I believe a simple macro can't do it, only a VBA scipt could, but am no expert at it... Help would be much appreciated. z.entropic . |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
a script/macro to copy a block of cells next to specified cells
Per your post, I tried to run the following code:
===========begin code======================== Sub PasteBlock() Dim rng As Range Dim cell As Range Marker As Variant Marker = "X" Set rng = Range(Range("L10"), _ Cells(Rows.Count, 1).End(xlUp).Offset(0, 1)) rng.FillDown For Each cell In rng If cell.Value = Marker Then Range("M6:S14").Copy Destination:=cell.Offset(0, 1) End If Next End Sub ==============end code===================== where in my particular situation the markers, X, are placed in column L from row 10 down to 10,000 or so, and the block to copy is M6:S14. Unfortunately, I get an error message "Compile error; Statement outside Type block" with 'Marker as Variant' highlighted in black and Sub PasteBlock() in yellow. I'm lost... M_e -----Original Message----- Dim rng as Range Marker as Variant Dim cell as Range Marker = "some value" set rng = Range(Range("B3"), _ Cells(rows.count,1).End(xlup).offset(0,1)) rng.Filldown for each cell in rng if cell.value = marker then Range("C1:D1").copy Destination:=cell.offset(0,1) end if Next -- Regards, Tom Ogilvy wrote in message ... -----Original Message----- set rng = Range(Range("B3"),Cells(rows.count,1).End (xlup).offset(0,1)) rng.Filldown Don't understand you question 2. Yes, it's clumsy... so let me try again, in a slightly less clumsy way: 2. I have a block of cells in, e.g., c1, c2, d1 and d2, which I'd like this macro to copy next to the individual value/marker calculated in column b by the first part of the macro. This marker (it can be a character) is the result of a TRUE/FALSE test on the data from column a, thus only a small percentage of cells in column b will be filled. That's where, i.e., next to it in col. c, I'd like the macro to copy the block of formulas, which would use data in column a to givea block of new data. Thanks for your help, z.entropic -- Regards, Tom Ogilvy "z.entropic" wrote in message ... Here's what I'd like to do: 1. copy cell b3 all the way down to b(n), where n is the number of data in column a; this produces some cells in column b with a numerical value or character; 2. copy a block of cells, e.g., b1-c2, where cell b1 would be placed in column c next to every occurrence of the previous numerical value. I believe a simple macro can't do it, only a VBA scipt could, but am no expert at it... Help would be much appreciated. z.entropic . . |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
a script/macro to copy a block of cells next to specified cells
Sub PasteBlock()
Dim rng As Range Dim cell As Range Dim Marker As Variant Marker = "X" Set rng = Range(Range("L10"), _ Cells(Rows.Count, 1).End(xlUp).Offset(0, 11)) rng.FillDown For Each cell In rng If cell.Value = Marker Then Range("M6:S14").Copy Destination:=cell.Offset(0, 1) End If Next End Sub -- Regards, Tom Ogilvy z.entropic wrote in message ... Per your post, I tried to run the following code: ===========begin code======================== Sub PasteBlock() Dim rng As Range Dim cell As Range Marker As Variant Marker = "X" Set rng = Range(Range("L10"), _ Cells(Rows.Count, 1).End(xlUp).Offset(0, 1)) rng.FillDown For Each cell In rng If cell.Value = Marker Then Range("M6:S14").Copy Destination:=cell.Offset(0, 1) End If Next End Sub ==============end code===================== where in my particular situation the markers, X, are placed in column L from row 10 down to 10,000 or so, and the block to copy is M6:S14. Unfortunately, I get an error message "Compile error; Statement outside Type block" with 'Marker as Variant' highlighted in black and Sub PasteBlock() in yellow. I'm lost... M_e -----Original Message----- Dim rng as Range Marker as Variant Dim cell as Range Marker = "some value" set rng = Range(Range("B3"), _ Cells(rows.count,1).End(xlup).offset(0,1)) rng.Filldown for each cell in rng if cell.value = marker then Range("C1:D1").copy Destination:=cell.offset(0,1) end if Next -- Regards, Tom Ogilvy wrote in message ... -----Original Message----- set rng = Range(Range("B3"),Cells(rows.count,1).End (xlup).offset(0,1)) rng.Filldown Don't understand you question 2. Yes, it's clumsy... so let me try again, in a slightly less clumsy way: 2. I have a block of cells in, e.g., c1, c2, d1 and d2, which I'd like this macro to copy next to the individual value/marker calculated in column b by the first part of the macro. This marker (it can be a character) is the result of a TRUE/FALSE test on the data from column a, thus only a small percentage of cells in column b will be filled. That's where, i.e., next to it in col. c, I'd like the macro to copy the block of formulas, which would use data in column a to givea block of new data. Thanks for your help, z.entropic -- Regards, Tom Ogilvy "z.entropic" wrote in message ... Here's what I'd like to do: 1. copy cell b3 all the way down to b(n), where n is the number of data in column a; this produces some cells in column b with a numerical value or character; 2. copy a block of cells, e.g., b1-c2, where cell b1 would be placed in column c next to every occurrence of the previous numerical value. I believe a simple macro can't do it, only a VBA scipt could, but am no expert at it... Help would be much appreciated. z.entropic . . |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
a script/macro to copy a block of cells next to specified cells
Hurrrah! Thanks a lot, Tom. Your help was extremely
valuable. Unfortunaltely, such useful and, on the surface, simple tricks are beyond my programing capability... Will have to rely, from time to time, on good souls like you and Bernie. Best regards, z.entropic -----Original Message----- Sub PasteBlock() Dim rng As Range Dim cell As Range Dim Marker As Variant Marker = "X" Set rng = Range(Range("L10"), _ Cells(Rows.Count, 1).End(xlUp).Offset(0, 11)) rng.FillDown For Each cell In rng If cell.Value = Marker Then Range("M6:S14").Copy Destination:=cell.Offset(0, 1) End If Next End Sub -- Regards, Tom Ogilvy |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to copy large block of cells but paste into one column | Excel Worksheet Functions | |||
Set range to block of cells in Macro | Excel Discussion (Misc queries) | |||
copy a block of cells | Excel Discussion (Misc queries) | |||
How to copy block of cells and keep grouping? | Excel Discussion (Misc queries) |