fill a variable sized range
 

I have data elements in cols AC:AF. Data will be in rows from 5 to whatever.
I need to add a value from another named cell in the worksheet into col AB.
How do I do it so I only add values in AB that have data in AC?

fill a variable sized range
 

Have you tried using:

If activecell.offset(0,1).Value<"" then

Where activecell is the cell in column AB?

--
Frank

"Do or do not; There is no try"
-Yoda


"Joe" wrote:

I have data elements in cols AC:AF. Data will be in rows from 5 to whatever.
I need to add a value from another named cell in the worksheet into col AB.
How do I do it so I only add values in AB that have data in AC?

fill a variable sized range
 

Your question is not clear to me. Initially you say you want to add a value
(singular) from a named cell (do you mean a cell with a Defined Name, or do
you just mean you want to specify the cell address?) but then you go on to
say you want to add values (plural) in AB that have data in AC... is there
any linkage between what you want to put in AB and what is in AC, or do you
just want to put the value(s?) in the first blank cell in AB where AC next
to it is not blank? I guess my main problem is I can't visualize your setup
from your description.

--
Rick (MVP - Excel)


"Joe" wrote in message
...
I have data elements in cols AC:AF. Data will be in rows from 5 to
whatever.
I need to add a value from another named cell in the worksheet into col
AB.
How do I do it so I only add values in AB that have data in AC?

fill a variable sized range
 

I can use that but if I have that formula it create blank cells which i don't
want. If I have 5 active cells in AC I only what 5 values in AB so my
database doesn't have empty rows.


"Phuelgod" wrote:

Have you tried using:

If activecell.offset(0,1).Value<"" then

Where activecell is the cell in column AB?

--
Frank

"Do or do not; There is no try"
-Yoda


"Joe" wrote:

I have data elements in cols AC:AF. Data will be in rows from 5 to whatever.
I need to add a value from another named cell in the worksheet into col AB.
How do I do it so I only add values in AB that have data in AC?

fill a variable sized range
 

I am using a macro to extract records from a database into columns AC:AF.
The number of records change based on criteria selection. I then need to add
another value (Defined Range Name) to each row in AB. The value will be the
same and is stored elsewhere in the worksheet. If AC:AF has 5 rows I only
want to put the AB value on those same 5 rows, if AC:AF has 10 rows I want to
add it to those 10 rows. So how to I control the macro to add the value in
AB for just those rows with a value in AC.

I hope that helps

"Rick Rothstein" wrote:

Your question is not clear to me. Initially you say you want to add a value
(singular) from a named cell (do you mean a cell with a Defined Name, or do
you just mean you want to specify the cell address?) but then you go on to
say you want to add values (plural) in AB that have data in AC... is there
any linkage between what you want to put in AB and what is in AC, or do you
just want to put the value(s?) in the first blank cell in AB where AC next
to it is not blank? I guess my main problem is I can't visualize your setup
from your description.

--
Rick (MVP - Excel)


"Joe" wrote in message
...
I have data elements in cols AC:AF. Data will be in rows from 5 to
whatever.
I need to add a value from another named cell in the worksheet into col
AB.
How do I do it so I only add values in AB that have data in AC?

.

fill a variable sized range
 

Okay, I think I see what you want. Give this macro a try...

Sub FillColumnAB()
Dim X As Long, LastRow As Long
Const StartRow = 2 '<<==assumes Row 1 is a header row
LastRow = Cells(Rows.Count, "AC").End(xlUp).Row
Range("DefinedName").Copy Range("AB" & StartRow & ":AB" & LastRow)
End Sub

Change my example "DefinedName" to your own defined range name (make sure to
keep the quotes); change the StartRow constant if need as well.

--
Rick (MVP - Excel)


"Joe" wrote in message
...
I am using a macro to extract records from a database into columns AC:AF.
The number of records change based on criteria selection. I then need to
add
another value (Defined Range Name) to each row in AB. The value will be
the
same and is stored elsewhere in the worksheet. If AC:AF has 5 rows I only
want to put the AB value on those same 5 rows, if AC:AF has 10 rows I want
to
add it to those 10 rows. So how to I control the macro to add the value
in
AB for just those rows with a value in AC.

I hope that helps

"Rick Rothstein" wrote:

Your question is not clear to me. Initially you say you want to add a
value
(singular) from a named cell (do you mean a cell with a Defined Name, or
do
you just mean you want to specify the cell address?) but then you go on
to
say you want to add values (plural) in AB that have data in AC... is
there
any linkage between what you want to put in AB and what is in AC, or do
you
just want to put the value(s?) in the first blank cell in AB where AC
next
to it is not blank? I guess my main problem is I can't visualize your
setup
from your description.

--
Rick (MVP - Excel)


"Joe" wrote in message
...
I have data elements in cols AC:AF. Data will be in rows from 5 to
whatever.
I need to add a value from another named cell in the worksheet into col
AB.
How do I do it so I only add values in AB that have data in AC?

.