a script/macro to copy a block of cells next to specified cells

  • Thread starter Thread starter z.entropic
  • Start date Start date
Z

z.entropic

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
 
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,
Click to expand...

A macro can do almost anything....

HTH,
Bernie
MS Excel MVP
 
set rng = Range(Range("B3"),Cells(rows.count,1).End(xlup).offset(0,1))
rng.Filldown


Don't understand you question 2.
 
-----Original Message-----
set rng = Range(Range("B3"),Cells(rows.count,1).End (xlup).offset(0,1))
rng.Filldown


Don't understand you question 2.
Click to expand...

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
 
-----Original Message-----
z. enthalpic,
Click to expand...

My Excel knowledge is more disorganizaed than that... said:
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
Click to expand...
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.
A macro can do almost anything....
Click to expand...

Yes, but not a simple one? <g>

z.entropic
 
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
 
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
 
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
 
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
 
Back
Top