Locating variable range to copy

I am an ordinary accountant but not familiar with macro & VBA.  I have a
file, I need to copy a source formula to a certain range of which
starting position has fixed already and ending position would be
altered regarding to condition changed.
For example, say
source formula :   locating at cell G11
conditions  :   range of row containing data in column F starting
from F11

Says now, there are 10 data starting from F11 to F20, therefore I need
to copy the source formula from G11 to G20.

Same as, when there are 100 data starting from F11 to F110, I need to
copy the source formula from G11 to G110.

Can someone advise me how to specify the range in performing a macro in
Excel v2002.  Thank you very much.

Eric C


-- 
Eric C
0
8/11/2005 4:45:26 PM
excel.newusers 15348 articles. 2 followers. Follow

2 Replies
200 Views

Similar Articles

[PageSpeed] 33

For a non-VBA solution, double-click the right-bottom corner of G11.  
The later versions of XL will automatically fill G for as many rows as 
there is data in F.

For a VBA solution, see example 4 in
Beyond Excel's recorder 
http://www.tushar-
mehta.com/excel/vba/beyond_the_macro_recorder/index.htm

-- 
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article <Eric.C.1tm4mw@news.excelbanter.com>, 
Eric.C.1tm4mw@news.excelbanter.com says...
> 
> I am an ordinary accountant but not familiar with macro & VBA.  I have a
> file, I need to copy a source formula to a certain range of which
> starting position has fixed already and ending position would be
> altered regarding to condition changed.
> For example, say
> source formula :   locating at cell G11
> conditions  :   range of row containing data in column F starting
> from F11
> 
> Says now, there are 10 data starting from F11 to F20, therefore I need
> to copy the source formula from G11 to G20.
> 
> Same as, when there are 100 data starting from F11 to F110, I need to
> copy the source formula from G11 to G110.
> 
> Can someone advise me how to specify the range in performing a macro in
> Excel v2002.  Thank you very much.
> 
> Eric C
> 
> 
> -- 
> Eric C
> 
0
8/12/2005 3:28:03 AM
-- 
j.kasselman@atlantic.net.remove_2nd_at.  Randburg, Gauteng, South Africa


"Tushar Mehta" wrote:

> For a non-VBA solution, double-click the right-bottom corner of G11.  
> The later versions of XL will automatically fill G for as many rows as 
> there is data in F.
> 
> For a VBA solution, see example 4 in
> Beyond Excel's recorder 
> http://www.tushar-mehta.com/excel/vba/beyond_the_macro_recorder/index.htm
> 
> -- 
> Regards,
> 
> Tushar Mehta
> www.tushar-mehta.com
> Excel, PowerPoint, and VBA add-ins, tutorials
> Custom MS Office productivity solutions
> 
> In article <Eric.C.1tm4mw@news.excelbanter.com>, 
> Eric.C.1tm4mw@news.excelbanter.com says...
> > 
> > I am an ordinary accountant but not familiar with macro & VBA.  I have a
> > file, I need to copy a source formula to a certain range of which
> > starting position has fixed already and ending position would be
> > altered regarding to condition changed.
> > For example, say
> > source formula :   locating at cell G11
> > conditions  :   range of row containing data in column F starting
> > from F11
> > 
> > Says now, there are 10 data starting from F11 to F20, therefore I need
> > to copy the source formula from G11 to G20.
> > 
> > Same as, when there are 100 data starting from F11 to F110, I need to
> > copy the source formula from G11 to G110.
> > 
> > Can someone advise me how to specify the range in performing a macro in
> > Excel v2002.  Thank you very much.
> > 
> > Eric C
> > 
> > 
> > -- 
> > Eric C
> > 
> 
0
8/12/2005 9:23:01 AM
Reply:

Similar Artilces:

Lose Percent format when copying a chart and breaking the link
I have a line chart with the data table showing. The data is in the percent format. I copy the chart and paste it into a new workbook, then break the links by doing Edit, Links - then break the links. The percent format in the data table changes to decimal format. Is there a way to get the format to stay as a percent? Or to change it back to a percent? Thanks for any help! If the chart is just for display and you don't need to edit it in any way a quick fix would be select the chart, hold down shift and do edit>copy as picture, select as screen, then paste it into the new workboo...

Copy to Location
I have a worksheet in workbookA that I need to copy the rows from Row 9 to the last used row in the workbook to workbookB. The insertion point in WorkbookB for the copied rows would be to the row where Column "E" has the words "Accruals". Any help would be appreciated. TIA Greg Hi Greg Is workbook B open or must the macro do that -- Regards Ron de Bruin http://www.rondebruin.nl "GregR" <gregrivet@gmail.com> wrote in message news:1120146158.531430.234460@f14g2000cwb.googlegroups.com... >I have a worksheet in workbookA that I need to copy the row...

Difficulty with the property .Value of a Range
Hi, Is someone knows why this sub works well Sub test() Dim MyDate As Date, X MyDate = CDate("14/08/2008") '******************* X = Application.Match(CLng(LaDate), Range("A1:A25"), 0) '******************* End Sub And if i add the property ".value" to the object "Range", an error is generated. Sub test() Dim MyDate As Date, X MyDate = CDate("14/08/2008") ' French format -> French Version Excel. '******************* X = Application.Match(CLng(LaDate), Range("A1:A25").Value, 0) '******************* ...

Excel Finding instances of a value in defined range
Can some one help me with a small excel problem.. I have a excel sheet in which I have.... wait... why dont you check th attached file and then tell me how do I find out the repeated instance of a check.. For example , I want column H to say - "Instance Correct" if a chec no. X has been found 2 times in the defined range A1-F7 , and if th amount mentioned under deposit column of Bank is same as amount mentio in Deposit column of company.. If the check number is found 2 times bu the amount is different then column H should say - "Instance Wrong", i the check number is foun...

Must declare the scalar variable "@myDB@"
I am encountering this error and I am not sure why. In my code I create the sql string and SQLParameter to deal with the SQL variables. I am not strong with SQL so I may be missing something here. Any advice is greatly appreciated. Web.config: <add key="myDB" value="DBServ1" /> <add key="myInfo" value="select pTime from @XSearhDB@.dbo.tblServInfo where service= @myData"/> CS file: string query = ConfigurationManager.AppSettings["myInfo"]; query = query.Replace("@myDB@", ConfigurationManag...

Count number of lines of text in a range/value.
To start, 1) Add a form w/ a textbox to your VBA project. 2) Set the textbox's AUTOSIZE property to False. 3) Set the textbox's MULTILINE property to False. In your code... 4) Call on the Form's SHOW method (modal or modeless is irrelevant). 5) Set the textbox's TEXT property to the (trimmed) value you are working with. 6) Set the textbox's AUTOSIZE property to True. 7) Set the textbox's MULTILINE property to True. 8) Save/get the textbox's LINECOUNT property. 9) Call on the form's HIDE method. btw, I assume no responsibility for the code. Use it at your own...

Copying a Range of Data Dynamically?
I am building a spreadsheet that tracks the status of projects in m department. Status is determined by a *Status * column on the *Data worksheet that lists the project as -Complete,- -In progress-, -O hold,- etc. When a project is complete, we copy the rows containing i from the *Data * worksheet to the *Completed * worksheet on the sam spreadsheet. However, the rows containing the completed projects mus remain on the Data worksheet and therein lies the problem�the tw worksheets are not dynamic and the same data has to be entered in tw places. I want to make the two worksheets dynamic. Wha...

Copy row2 cells down to last cell where the whole row isn't all blank
Hi Colo, I tried to run your code to copy formula in cell K2 (I place the cursor on cell K2) down to last cell in column K, it doesn't copy down the formula. Is there anything missing from the code ? Please help and thanks very much! Sub Cell2CopyToEnd() Dim col As Long col = ActiveCell.column Range(Cells(2, col), Cells(Rows.Count, col).End(xlUp)).Formula = _ Cells(2, col).Formula End Sub --- Message posted from http://www.ExcelForum.com/ I put a value in K99 (so the macro knew when to quit) and I put a formula in K2. With K2 selected, I ran the code and it worked fine. The co...

How to copy and entire price level
Dear Gurus, I would like to know if there is a way to copy an entire price level. The copy of the price level would then have a different name. I saw the "copy" function in the price list utilities, but that copies by item number. I want to copy an entire price level. Our price levels are very complicated. Each price level involves almost 2500 different items spread across 35 item class codes. We have around 600 customers, most with their own price level. Right now, we are going through some major repricing and I am having to update price levels for a couple hundred cust...

How can I copy big ranges of cells without drag or copy/paste?
I need to copy formulas in a big range of cells and I'm sure there should be a way different than dragging until the last cell I want to copy it. I'm talking of hundreds of cells where I want to copy the formulas. Thanks, Use 'Go To' on the Edit menu (CTRL + G). In reference field enter the range you want to copy like A1:F25, and click ok. Now the range is selected and ready to be copied. Hopes this helps. .... Per On 22 Mar., 22:20, Ricardo Julio <Ricardo Ju...@discussions.microsoft.com> wrote: > I need to copy formulas in a big range of cells and I...

Dual Copies of Email ??
Every once in a while I will see dual copies of all email coming in. I have heard others mention this as well. What causes this? Does it have to do with a changed setting in Outlook or Outlook Express, or possibly something on the ISP end???? Sometimes this is fixed by telling OE not to go directly to the inbox on startup and / or telling it not to send and receive at startup under Tools | Options. steve "Al Franz" <albert@nospam.netmation.com> wrote in message news:O8yyer8hDHA.2420@TK2MSFTNGP10.phx.gbl... > Every once in a while I will see dual copies of all email c...

i have a list i need to copy onto another sheet one by one
I have created an invoice on an excel spreadsheet for a new business and my plan was to use a sales list on another sheet linked to the invoice , using the same fields it worked perfectly the first time using basic = sign, then i discovered that second and subsequent entries to the list would have to be set up again . can excel handle this so that I don,t need to retype after each entry. If desired, send your file to my address below. I will only look if: 1. You send a copy of this message on an inserted sheet 2. You give me the newsgroup and the subject line ...

Lost my copy of money 2005
Recently, I had problems with my hard drive and started receiving a message when I clicked on the MSN Money icon saying; "cannot locate registration library (DLL)." The technician had to reload the original version of money (2003) that came with the computer, however, I was using a downloaded version (2005). How can I download this version again without getting charged and will I be able to retrieve my data? -- Edwin I. Lugo Try seeing if the download from Microsoft. The link can be found on http://money.mvps.org/faq/article/132.aspx Note that once you download the Money 200...

how to copy cell ranges
Hi, I have a spreadsheet like so: Worksheet 1: -------------------------------- MAGIC | CONTACT | EMAIL 17 | Foo | bar@baz.gargle .... Worksheet 2: -------------------------------- MAGIC | COMPANY | QTY | BLAH 12 | Bambi | 3 | foz .... I want to lookup the `MAGIC' field of Worksheet 2 and copy the `CONTACT' and `EMAIL' fields for the matching row in Worksheet 1 to Worksheet 2. I've tried using the DGET macro which seemed to be perfect for this sort of thing, but the problem is that it requires the cell containing the column name to be _above_ the value to ma...

Copy/Paste #2
I need to delay the the follwing copy/paste operations in a macro with a time interval of 30 seconds. Is this possible? Sheets("Show").Select Application.CutCopyMode = False Selection.Copy Sheets("Chartdata").Select Range("B2").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Sheets("Show").Select Application.CutCopyMode = False Selection.Copy Sheets("Chartdata").Select Range("C2").Select Selection.PasteSpecial Paste:=xl...

copy formula as series
I enter data in a column e.g. A1 : A50 How do I then in another sheet , by using the cell references, enter the data as a row in sheet 2 without having to do each formula manually? In sheet 2 when I try to copy the formula =sheet1!A1 across the row it obviously puts in the relative value. I have tried fixing the column ref by using =sheet1!$A1. That correctly fixes the clumn as A across the series but fails to increment the row reference down the column. That is fixed as 1 where as I want it as a series A1: A50 in the row in sheet 2. Help Quercus, Send me a message privately, and I ...

Selecting Rows for Copying
Hi, I've been happily using a macro that has been copying and pasting data for me. However, I have recently encountered an issue where the sheets I am copying my data from have merged cells. As far as I can tell, Selection.Rows.Count will not work as desired when some columns are merged, but others are not. Basically it is counting what may be 100 rows of data as one row, due to the cell merging. ' select all lines except title Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1).Select Is there another way to select all of the data, excluding the header row, whe...

Select bottom right hand cell in range
Hi Is there a way to select the bottom right hand cell in a named range? I am using a dynamic range and would like to make the bootom right hand cell the ActiveCell =OFFSET(Solo!$C$5,0,0,COUNT(Solo!$C$5:$C$350),4) So if the above selected C5:F12, how would I make select F12 and make it active, bearing in mind that the range will vary depending on how may of the cells in Column C are filled. Any suggestions? Thanks. Jim Jim, Is this in VBA? If so, try this range("Solo")(range("Solo").Rows.count,range("Solo").Columns.Count).select -- HTH Bob Phillip...

stopping column increment in formula copy
Sorry for the img post, but its easier to articulate the problem with picture... I have a column of data (distances) B5 thru b14 that I'm trying t multiply by two different company prices, which are in C1 and C2. Whe I put the first equation in B5 (=c1*B5) and then try to copy/paste th equation down the column to B14, the C1 cell value in the equatio increments as it goes down the column. Same thing happens when I do i for company B. So I guess I'm doing something wrong. How do I easily paste th functions down the column while still having the equation use the sam multiplier (C1)? I...

Copied data not treated same way as typed in data
Hi there. I am new to this newsgroup - but I wonder if someone might be able to help. I have an Excel spreadsheet (MS Excel'97 .xls Workbook) which contains a whole load of data. I am trying to use the FREQUENCY command [e.g. =FREQUENCY($N$7:$N$2000,C60:C61)] to summarise certain columns of data - to tell me the number of times that certain codes appear. I have used the FREQUENCY command before in another spreadsheet without any problem. In my current spreadsheet I have setup the FREQUENCY commands in exactly the same way, but all the output fields appear as zero. Interestingly thoug...

address book location?????
I've just installed a new hard drive and reinstalled windows xp. What i need to do is move my outlook express address book and all of my save e-mails from my old drive (which is now configured as my d: drive) to the new drive. I have tried to import but that does not seem to work. If anyone has any ideas please let me know how i can do this. thanks This is a group to support Outlook from the Office group of programs. Outlook Express is a part of Internet Explorer and is a quite different program despite its similar name.. You will probably get a faster and more expert answer if you po...

Pasting a long range
Can a formula be copied down a long range without dragging? Suppose I had a formula in B1 that I wanted copied down to B8000. I used to do this years ago in Supercalc as follows: /(C)opy,B1,<enter>,B2:B8000,<enter>. The cursor could be in any cell when you did this. This method would save time for long ranges. Using Excel 97. Thanks for any help. Ken Ken, here is one way, select B1, then in the name box type B8000, then shift+enter, then Ctrl d -- Paul B Always backup your data before trying something new Using Excel 97 & 2000 Please post any response to the newsgro...

Location & Keyboard Button
I have just installed Outlook 2007, and want to have my email key on my keyboard access it. Am unable to locate Outlook on my computer. Tried under Programs etc, can find Office, but not Outlook. Any suggestions? I have an HP computer with the Email button on the keyboard. HP unable to help, told to go this route. CB Try "C:\Program Files\Microsoft Office\Office12\OUTLOOK.EXE" (taken from the properties page of the Outlook shortcut on start menu, programs or quick launch) -- Diane Poremsky [MVP - Outlook] Outlook Tips: http://www.outlook-tips.net/ Outlook & Exchange ...

vlookup copy down
Is there any way when copying a vlookup formula or similar to have the row of the range change for example suppose you have a formula =VLOOKUP($C$3,summary,3,FALSE) and you want to copy down to cell below so formula is =VLOOKUP($C$3,summary,4,FALSE) and keep on going so when you copy down next the 4 changes to a 5 etc. Thanks Dave Try this: =VLOOKUP($C$3,summary,ROW(A3),FALSE) Now when you copy down, the A3 will change to A4, and cause the ROW function to return a value of 4. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* &...

Same Database two different locations.
Anyone know of a way to say have a brick and mortar store with one database and two POS stations on RMS 2.0 and a third mobile laptop with internet access using the same database as the store with the same inventory and customers. Example: Some calls for a service call and you load up two items for the call. Go fix the problem with the two items, then while out in the field you use the laptop to write up and process the order with either an in house account or you take a credit card, check or cash in the field with internet and using the same database as the store's without using ...