Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using variable sized range in CountIf() | Excel Discussion (Misc queries) | |||
Copy variable sized range to single column... | Excel Programming | |||
Getting values from a variable sized range into an array | Excel Programming | |||
Variable sized combo box | Excel Programming | |||
Summing a variable sized range programmatically | Excel Programming |