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

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

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

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



.

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



.

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



.

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



.



.

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



.



.

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