copying formulas in vba

Hey guys.

I was wondering if someone could help me. I am writing a vba script that 
takes in data, analyzes it, and then copies the results to a new file. I am 
having a problem with two things.

1) I am using a template for the new file so there are a lot of formulas 
(sums and std) already defined and ready to use. However, there are some 
instances where there is a random amount of additional data I have to put in. 
So, I have to apply the same formulas to this new data. How do I copy 
formulas from one cell to another (allowing for a change in row) in vba?

Lets say cell(1,4) has the formula "=sum(A1:C1)
If I do: cells(4,4).formula = cells(1,4).formula 
then cell(4,4) has the formula "=sum(A1:C1)"

How do I make it become "=sum(A4:C4)" using vba?

2) For these new cells, I also have to format some columns (currency, date, 
etc) them and also put borders around them. How do I set these properties in 
vba?

I greatly appreciate and assistance you could provide.

Thanks,

-Michael
0
Michael5492 (655)
11/17/2005 7:28:08 PM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
307 Views

Similar Articles

[PageSpeed] 54

1)

    Cells(1, 4).Copy
    Cells(4, 4).PasteSpecial Paste:=xlPasteFormulas

2) There is a BorderARound property for a range that you could use. Check it
out in help.

-- 

HTH

RP
(remove nothere from the email address if mailing direct)


"Michael" <Michael@discussions.microsoft.com> wrote in message
news:4D99CB83-1C89-43EA-8FA7-421E84927013@microsoft.com...
> Hey guys.
>
> I was wondering if someone could help me. I am writing a vba script that
> takes in data, analyzes it, and then copies the results to a new file. I
am
> having a problem with two things.
>
> 1) I am using a template for the new file so there are a lot of formulas
> (sums and std) already defined and ready to use. However, there are some
> instances where there is a random amount of additional data I have to put
in.
> So, I have to apply the same formulas to this new data. How do I copy
> formulas from one cell to another (allowing for a change in row) in vba?
>
> Lets say cell(1,4) has the formula "=sum(A1:C1)
> If I do: cells(4,4).formula = cells(1,4).formula
> then cell(4,4) has the formula "=sum(A1:C1)"
>
> How do I make it become "=sum(A4:C4)" using vba?
>
> 2) For these new cells, I also have to format some columns (currency,
date,
> etc) them and also put borders around them. How do I set these properties
in
> vba?
>
> I greatly appreciate and assistance you could provide.
>
> Thanks,
>
> -Michael


0
bob.phillips1 (6510)
11/17/2005 8:48:50 PM
Reply:

Similar Artilces:

VBA in a workbook
Hello I got 10 identicle sheets in a workbook. I wrote a program to search some data in 1 sheet. I put them in a varriable. Now i want to totalize the same data in a different number of sheets. I want the input by a box. for ex: sheets 1,2 an other time sheets 1,5,8. Can anyone help me??? THX In dutch Hoi Ik heb 10 identieke (van struktuur) tabbladen in een excel werkboek. Ik heb een routine geschreven om in een blad enkele gegevens op te halen in varriabelen. Nu wil ik via een inputbox aangeven welke tabbladen hij moet totaliseren. Bijvoorbeeld: cijfers van 1,2; later weer 3,5,8 enz. Wie ...

macros vba question
is there a way that when I save as a file. If a cell in a worksheet says Week 1 it will copy and paste with vaules a col if if says week 2 is will copy and paste with values a different col in the worksheet You can do this with a macro. I am assuming you mean a particular cell on a particular sheet, not just any cell on any sheet. The following code checks cell A1 on Sheet1 whenever you save the workbook. If A1 contains Week 1 then column D is copied & pasted in place as values. If A1 contains Week 2 then column E is copied & pasted in place as values. Private Sub W...

Copy and paste in CEdit
Hello! Does anyone know how to support copy and paste with shortcuts in an edit control? In my MFC application you can copy and paste via context menu but not via Ctrl+C and Ctrl+V. Thanks in advance, Robert "Norbert P�rringer" <thalion77@graffiti.net> wrote in message news:a03c6$42b28170$d4badae4$30534@news.chello.at... > Hello! > > Does anyone know how to support copy and paste with shortcuts in an edit > control? In my MFC application you can copy and paste via context menu but > not via Ctrl+C and Ctrl+V. Try this KB Article... http://support.microsoft....

Hyperlinks static but formulas are not. Why?
Hi, If I set up hyperlink from one cell to a cell in another wksheet (same workbook), then the link doesnt get updated if I change the name of the target wksheet. On the contrary if i write a formula in sheet1 of cell A1 -- =Sheet2!D15 -- then when i change the name of the worksheet Sheet2 to NewSheet2, the formula in sheet1 of cell A1 also gets updated to -- =Newssheet!D15 How is it that formulas get updated on changing name of the wksheet, but hyperlinks dont get updated? -- Thanks a lot, Hari India >>How is it that formulas get updated on changing name of the wksheet, but >&...

copying(burning) my emails to a disk
I wish to copy my emails to a disk, so once a month I can free up all the space they occupy. How do I do this with Windows Vista Home Premium using Windows Mail? In Windows Mail go to File, Export, Messages, select "Microsoft=20 Windows Mail". When the export wizard asks for the destination, point it to an empty folder under your profile. When the export is finished, copy that folder to your external media. Windows Mail can't=20 export directly to external media. --=20 Gary VanderMolen, Microsoft MVP (Mail) Microsoft MVP Program: http://mvp.support.microsoft.com ...

Formula
There isa command to allow a formula to refer to a number in a cell to adjust the column that is used in the formula. BUT I CAN"T FIND IT! If I want the formula to be using cell A11, I want to have 11 in a cell and have it refer to whatever to A11. If I have 12 in the cell then I want it to refer to A12 instead. Sorry, I am not explaining the scenario very well. Help. MD -- mdalby ------------------------------------------------------------------------ mdalby's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=7055 View this thread: http://www.excelfor...

Excel 2007: Pivot Table to SSAS why menu item "Options"->"Formulas" is disabled?
I am using Excel 2007 with SQL Server Analysis Services 2005 SP2. I want to create calculated measure based on other measures, but I cannot do this for Pivot table. If I go to menu "Options"->"Formulas" I can see that it is disabled. Same menu is enabled if I create Pivot table based on cells in sheet. How can I create caclulated measure for Pivot Table based on SSAS? Thank you ...

Matching data from one table and copy them to another
Hi, I have two Excel tables. Table 1 contains list of animals and their description in two columns. Table 2 contains list of animals and results of a test. Not all animals from Table 1 mach the animals in Table 2, but most of them do. I have to link those two tables in a way that test result from table 2 appears in Table 1 Column 3 but of course I must use animal ID column as a reference to match data. How can I do this? It's always a good idea to reveal ranges... That said... Table1 = A2:B100 Table2 = F2:G200 In D2 enter & copy down: =MATCH(A2,$F$2:$F$100,0) In C2 enter &am...

Use VBA to update custom field on Collection Main screen
I've modified the Collection Management main screen by adding a field to display the e-mail address linked to the AP address ID. The code to update that field fires after the user tabs away from the customer ID field. However, if the user wants to use the VCR buttons at the bottom of the screen, they have to click twice to go to the next record. When they do that, the field doesn't update itself. I know I am probably being dumb, but am having a huge mental block on how to get that field to update itself when the user advances to another record on the screen. Any assistance is appr...

Embedded image to excel via vba
As we all know, the export to xls from a report loses all formating. I've come up with a workaround for all of the information within a report except for an embedded image. I can place the image into a spreadsheet using code if it has a static path, but this is less than ideal. Here's the broken code I've been trying to use: Dim xlApp as Excel.Application Dim xlBook As Excel.Workbook Dim xlSheet As Excel.Worksheet Dim imgPic as Image Set xlApp = CreateObject("Excel.Application") Set xlBook = xlApp.Workbooks.Add Set xlSheet = xlBook.Worksheets(1) Set imgPic = Repo...

Dragging cell's formula changes BOTH referenced cells
Sorry for the long post! Please see the attached Word doc - it has a table that I copied from a spreadsheet I'm working on. I included each cell's formula to help explain what I'm trying to do. As you can tell, I'm creating a kind of fuel calculator/comparison chart. I want to be able to compare the amount spent over a year for a couple of vehicles with a certain mpg rating. There's more to the chart, but this information is sufficient for my question/problem. I'm including fuel prices from $1.50 to $4.00 (man, can you believe prices these days?! :eek: ). That's...

Using API to copy files
I wrote a file syncronization program to compare and copy multiple projects between my PC and the server (some 24 projects with almost 1000 files today, but the number grows every day). It works great, and as far as I'm concerned, the speed is fine, but I was told by a co- worker that using API can copy files faster than the FileCopy function in VBA. Is this true? How much faster can it work? Does anyone have sample code to do this? And while I'm here, is there and API routine that works faster than the Kill function? My knowledge of API can fit on the head of a pin, so ...

Variable-Length dependent formula
Hi all- I have a specific (and I believe, difficult problem) I have an Excel program that does several calculations on a data set that is an input from another program. Background: The basic gist is that I have a program that varies voltage sequentially and measures the corresponding current. The program then outputs these points in a text file seperated by commas. The user then copies this information into the Excel sheet and runs a macro in order to do all the calculations on the function. Problem: Previously, all files were in the same format: for example, it swept the voltage from 0V...

How can i protect a formula and still allow copy and move data?
i have 12 columns with data on days and each new day i want to add a new day to the set of 12 columns without adding a 13th one. So the oldest day are removed and the view is still 12 days. In these columns some values are calculated with formulas from data residing in the column (not from other columns) and i want to protect the formula but allow the column to be moved one step to the left. As of now i havent been able to use the built in functions of protection of cells and protection of formulas. Any ideas? You could just move (copy-paste) the direct entry data i.e. the d...

rank formula
I'm having some trouble with using the rank(x,x:x) formula. The cells I'm referencing have a sum formula in them, and the values are < 0. When I enter my rank formula it returns #VALUE! instead of the rank. Is this because the values are less than 0? Is this because I'm trying to referance a cell that has a formula in it? Is there a workaround? Will Batman make it to Gotham in time to stop the Joker? Help!! On Oct 16, 4:17=A0pm, Matthew Dyer <matthew.e.d...@gmail.com> wrote: > I'm having some trouble with using the rank(x,x:x) formula. The cells > I'm refer...

What is VBA code for new message?
Hi In Word if I create a macro called FileNew which pops up a message box then it will override the default file new code. What is the equivalent of FileNew in Outlook 2007 so that I can override the default functionality? and which template should I store it in so that it works evey time. Christine Outlook programming questions are best posted to = microsoft.public.outlook.program_vba.. --=20 Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. All unsolicited mail sent to my personal account will be deleted without reading. =20 "xtine" ...

How do I restrict a user to copy text from Office documents ?
Can anyone let me know - how do we set the restrictions to prevent any user copying any text from the Word or Excel or Powerpoint or PDF ( generated from Microsoft Office softwares ) files ? As I would not like the content of my documents to be copied and re-used by anyone else. I would only like the users to read the contents and not copy-paste it. Can anyone please help me ? You can't, really. You can copyright it so it's legally yours. -- Susan Ramlet -- please reply to the newsgroup so all may benefit. "Vikrant" <Vikrant@discussions.microsoft.com>...

Copy Shape text to Custom properties using VBA
I want to copy the shape text to custom properties using VBA. Any help will be appreciated. Hello VB, Have a look at this post, which deals with something similar and let me know if you have any questions: http://groups.google.co.uk/group/microsoft.public.visio.general/browse_thread/thread/3712a3eb1daebc0e?hl=en&ie=UTF-8 Best regards John John Goldsmith (Visio MVP) www.visualSignals.typepad.co.uk www.visualSignals.co.uk "VB" <VB@discussions.microsoft.com> wrote in message news:D95EA556-60D3-413C-8C18-03854C74DEFC@microsoft.com... >I want to copy the shape text...

Time formula, complicated?
I'm trying to make a simple spreadsheat, in which i type in my work start and end time, then I have some cells that have time hours (07:00-21:00) at each cell it calculates whether it's time IE: 07:00 falls between the time entered, if it does it puts "XX" if not "--" the forula I use: Code: -------------------- =IF(G$2>=$D3,IF(G$2<=$F3, "XX", "--"), "--") -------------------- And it works, so far but I want each "X" or "-" to represent thirty minutes, I just can't figure out the formula to tell if...

Readability statistics
Dear experts: below macro that I copied from some Online VBA tutorial works in Word 2007 but inexplicably not in 2003. Any idea why? Help is much appreciated. Thank you very much in advance. Regards, Andreas Sub readabilitystatistics() Dim StatText As String Dim rs As Variant StatText = "Document Statistics:" & vbCr For Each rs In ActiveDocument.readabilitystatistics StatText = StatText & rs.Name & " - " & rs.Value & vbCr Next rs MsgBox StatText End Sub It works fine in Word 2003 here, returning Document Statistics: Words ...

Impoting photos7 in Excel VBA
Hello, I almost completed my application, and I needto import some photos in the document. It is about 20 photos, and I mamaged routine for selecting and placing phots in the documnet. Problem is now that excel file is too big, when photos are of 2-3 MB each, excel file becom 20 MB an more. That case all starts to work very slowly, and colaps. If there is solution, to have some VB command in to the code to reduce size of the photo during importnitng. It is posible to compress photo in the Exce, but don't know how to make same thing automated in the VB code. Much appreciate if any...

Change button right before the formula field
Hello everyone, Right before where you enter values/formulas you have a button. In some Excel versions it is a "=" button that you can push this if you want to. Now I have a FUNCTION button there instead and that is somewhat irretating. Does someone know how I can change the button right before the formulafield? The = icon disappeared in newer versions of excel (xl2002???). You can add an = icon Tools|customize|commands tab|Insert category (Scroll down the list until you find the = icon) And drag it to your favorite toolbar (nicely positioned). It's not quite the same, ...

Formula needed #3
How do I make a formula that will say this for me? If D18 is greater than 0 then multiply D18 by $5.00 and subtract from L35. Annette Hi Annette =IF(D18>0,L35-D18*5,0) you didn't say what you wanted to do if D18 was less than or equal to zero, so i added the false parameter (the third one above), i.e. the 0 in. Cheers JulieD "Auntie Annette House MO" <anh650t@swbell.net> wrote in message news:O5fnOsg2EHA.1144@TK2MSFTNGP09.phx.gbl... > How do I make a formula that will say this for me? > If D18 is greater than 0 then multiply D18 by $5.00 and subtr...

Need a formula "if c2>c3 and c4"
I wish to set if C2 >C3 and C4 , then the cell's background will change in color. I dont know how to set the formula correctly, while the latter is known to me. Please help. Thanks in advance. Eduardo D -- Message posted from http://www.ExcelForum.com Eduardo, AND(C2>C3,C2>C4). DDM "DDM's Microsoft Office Tips and Tricks" www.ddmcomputing.com "EduardoDon >" <<EduardoDon.14z74y@excelforum-nospam.com> wrote in message news:EduardoDon.14z74y@excelforum-nospam.com... > I wish to set > if C2 >C3 and C4 , > then the cell's ba...

Formulas In Cell Comments
Greetings, Is it possible to run formulas or vba code inside of a cell comment box? If so, how? -Minitman Not sure about VBA code but something like this might - Sub test() Dim sFml As String Dim cm As Comment sFml = "A1*4+A2" Set cm = Range("A1").Comment If cm Is Nothing Then Set cm = Range("A1").AddComment End If cm.Text sFml Range("A1").Value = 20 Range("A2").Value = 10 Range("C3").Formula = "=foo()" Application.CalculateFull End Sub Func...