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
-----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 . |
#4
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 |
#5
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 . |
#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 . . |
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) |