Replace variable row number in formulas

What I'm looking for is a macro that will replace the row number in a
formula with a new number that I designate through an input box.

Below is the code I have thus far.  The problem lies in how I'm
defining the integer portion of the formula that I want to replace -
vbInteger (or vbLong) don't seem to work.  Then finally I need to set
Section 2 in a loop through Column CN.  Any advice would be greatly
appreciated!

---------------------------------------------------------------------------------------------------------------------------
Sub UpdateFormulas_2()

    Dim LRowNumber As Long

    LRowNumber = Application.InputBox _
        (prompt:="Please enter the row number to update the formulas.",
_
        Title:="Update Formulas", Type:=1)
    If LRowNumber = False Then Exit Sub

    Sheets("Review").Select


'Section 1 - Works just fine

    ActiveCell.Select
    If IsEmpty(Range("Input!A" & LRowNumber - 2).Value) Then
        ActiveCell.Value = ""
    Else
        ActiveCell.Formula = "=CELL(""contents"",Input!A" & LRowNumber
- 2 & ")"
    End If


'Section 2 - Needs help

    ActiveCell.Offset(0, 1).Activate
    ActiveCell.Select

         ActiveCell.Formula = Replace(ActiveCell.Formula, vbInteger,
LRowNumber)

    End If

    Range("A1").Select

    MsgBox ("The formulas were successfully updated to row " &
LRowNumber & ".")

End Sub
---------------------------------------------------------------------------------------------------------------------------

0
windhorst (1)
11/3/2005 10:25:49 PM
excel.misc 78881 articles. 5 followers. Follow

0 Replies
731 Views

Similar Articles

[PageSpeed] 15

Reply:

Similar Artilces:

Message numbers?
In Exchange 2003 is there a way to track simply how many email messages have been sent and recieved on a month by month status? All that is really needed is the numbers but not where the went or who sent. Of course if there are more granular ways to do this, please include. Thanks! No easy way although you could turn on journaling and do it manually, write a transport event sink to start and increment a count based on date. Nue "Transam388" <Transam388@discussions.microsoft.com> wrote in message news:FDDA6E49-D467-4C41-B696-FD4E0F2BCAA6@microsoft.com... > In Exchan...

Auto change formula in cells when source is changed
Good day experts; If specific cells have the same formula for example: C1 = Product( A1;B1) C16 = Product(A16;B16) C17= Product(A17;B17) And so on ... What formula should i write in C16, C17, ... so that: when the formula in the source cell, C1, is changed the formulas in C16, C17, ... would automatically change accordingly. I tried "=C1" it does not work Thanks in advance Carlo carlo wrote: > Good day experts; > > If specific cells have the same formula for example: > C1 = Product( A1;B1) > C16 = Product(A16;B16) > C17= Product(A17;B17) > And so on ... ...

Delete Rows #2
I would like to be able to delete a row with a name in it based on a cell with that name from another cell in a different worksheet. Any ideas? Thanks, Bernie That worked great, thank you so much. How do I run the same macro for another name right after this one? Bernie Gary''s Student wrote: > This macro: > > Sub remover() > v = Sheets("Sheet1").Range("A1").Value > For Each r In Sheets("Sheet2").UsedRange > If r.Value = v Then > r.EntireRow.Delete > Exit Sub > End If > Next > End Sub > &g...

Unreconciling Multiple Rows In Money
I would like to know if there is a way to unreconcile multiple rows in Microsoft Money? I can do one at a time, but I need to do a large number of them and don't want to do them one at a time. I've tried to contact Microsoft directly about this but got passed to the company that I bought my machine from, who passed me off to another support number in their company - which tried to charge me for asking this question. Any info on this would be great! Doug In microsoft.public.money, Doug wrote: >I would like to know if there is a way to unreconcile multiple rows in >Microsoft ...

"Replace With" option
Hi all I would like to amend a column with phone numbers and change only th first two characters to a "0" Most of the numbers look like this; 44131 555 8000 It should all read like this; 0131 555 8000 I could use the Ctl H feature but it would affect the number if "44 appeared in the middle or end of the number. Is this possible?? Thanks for any advice Joe -- Message posted from http://www.ExcelForum.com Hi! Try this in another column and copy down: =IF(LEFT(C2,2)="44","0"&MID(C2,3,LEN(C2)-2),C2) where your number is in C2. If you have larg...

Convert time stored as decimalised number to time format
Hello How can I convert 3.5 hours to 3:30:00? -- Emma Emma A1/24 and formate as time Mike Rogers "Emma" wrote: > Hello > How can I convert 3.5 hours to 3:30:00? > -- > Emma ...

why do i get #### in my formula cell?
I don't really know much about Excel, and my formula (it's just simple arithmetic formulas) returns with ##### displayed on the cell, with a tooltip showing the cell's correct value when I hover over it. airn, make the column wider and see if that helps -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 2003 "airn" <airn@discussions.microsoft.com> wrote in message news:C6F4DD7C-3D7D-475C-894D-0F7745735666@microso...

How to paste a cells row from Excel to a PowerPoint 2003 Table row?
Hello! How to paste a row of cells from Excel to a PowerPoint 2003 Table row? PowerPoint 2003 pastes all the row cells values in every cell in the row in the PowerPoint table if I select the row in the PP table before the pasting. PP pastes the row as an overlapping column if I place cursor in the first cell of the target row before pasting. Best regards, Dima +7 9163876746 +7 9035093892 ...

how i can make the numbers
hi i am fatih fistly sorry my english is not good my trouble is haw can i will write the number of the contribution for example 1.000.000.000 =yaziyla(b42) (one milyar) and tahan my office version is turkish version thank you so your company will interesting my problem Fatih, If I understand you correctly, you want a formula to translate numbers to words (in Turkish). Laurent Longre has a language version of an function which offers this capability. It is at http://longre.free.fr/english/ I don't know if it supports Turkish, but if not, send me the Turkish words for all...

Formula #16
I need to have a formula that can add a row of cells with a variable number of colmns. Columns will be added and deleted everyday, so the formula needs to have a variable range. I hope this is clear. Thanks, Bernie ...

simple? pasting simple formula, error on copy
I have some store buying spreadsheets I made up. Very very basic, one of the columns has a total at the bottom or end as you may call it for total $ amount bought. that final number is in c33. c1, c2, c3 etc is a amount from each vendor. I want to have a column telling me the percentage being spent on each, simple enough so I make a column with the following formula =c33/c5. I get a number, change it to percentage and BAM I have exactly what I want. I pasted that formula and pasted it into all the other rows of that certain column, but I keep getting the #div/0 error, cause the new formulas...

polynomial formula for a line graph...
Hi. I haven't looked at this feature in a couple of years. Excel can give you the formula for a charted function... what I mean is, given a few data points, it can create a best fit curve, and display the function that it used to do that... Would someone remind me where, please? Thanks. Hi, Right click the data series and pick Add Trendline. From the dialog you can choose the type of trend line and whether to display the formula. Cheers Andy mark wrote: > Hi. > > I haven't looked at this feature in a couple of years. > > Excel can give you the formula for ...

Excel formula copied down
Is there a way to copy down a column this formula =F3*A2 and have the A2 stay the same all the way down? The F3 would change on each row, F4, F5 etc. but the A2 would stay 'constant'. Thanks in advance for any help! =F3*A$2 -- Ian -- "Pat" <Pat@discussions.microsoft.com> wrote in message news:73505B29-538A-48C7-8F2F-F6126EF2126B@microsoft.com... > Is there a way to copy down a column this formula =F3*A2 and have the A2 > stay > the same all the way down? The F3 would change on each row, F4, F5 etc. > but > the A2 would stay 'constant'...

Partial replace
Hello, I'm trying to search and replace but I do not want to replace the whole cell, only a few characters in them. Namely, I have list of names, some of which have two stars ** after it and I want to get rid of those 2 stars after the name. How do I do that? Thanks a lot Sandra One way .. Assuming data in A1 down, Put in B1: =TRIM(SUBSTITUTE(A1,"**"," ")) Copy down -- Rgds Max xl 97 --- GMT+8, 1� 22' N 103� 45' E xdemechanik <at>yahoo<dot>com ---- "Sept" <sep@sept-europe.com> wrote in message news:BEB9F138.137%sep@sept-europe.c...

Help with creating a formula
Hello! I am trying to create a formula that will sum up certain criteria from one column as it relates to criteria in the same row in another column. For example, I want to sum up all the entries that begin with the letter "H" in column A as it relates to an entry of "2" from column B that is in the same row as the "H" entry from column A. COLUMN A COLUMN B H10 2 G01 2 H09 3 H15 2 H12 2 If there is such a formula...

Excel does not calculate my formulas
This is very strange...Excel does not calculate any formula. I can do a simple =A1+A2, and it just return a zero value. I made sure of the following: - Cell is set to General (tried it with different cell settings too) - Autocalc is on in settings (even a manual F9 calc doesn't work) -- Martin Verville We might need a few more clues: What is in A1? What is in A2? -- David Biddulph "Martin" <Martin@discussions.microsoft.com> wrote in message news:A9A25385-5BE8-4D38-A7A7-15E523C69DD9@microsoft.com... > This is very strange...Excel does not calculat...

coloring in rows
Hi Could somebody please help me with the following problem? How do I set a conditional format that if L5,M5, or N5 = �BAD� the highlight the row yellow, otherwise leave it the standard non fil color (clear) Thanks Andre -- kob ----------------------------------------------------------------------- koba's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2863 View this thread: http://www.excelforum.com/showthread.php?threadid=48371 Select Row 5. Format> Conditional Formatting. Formula is: =OR($L5="Bad",$N5="Bad",$M5="Bad") Set ...

Importing five row list from word 2003 to excel
I have a Word five row list I would like to import into Excel in a five column list. Every hundred items or so their is a new heading. What do I do? If it is in a word table you can cut and paste it. But you will have to reformat it. Once you get it into excel you can sort it and delete the duplicate headers. --- Message posted from http://www.ExcelForum.com/ ...

Excel CSV leaving out empty columns from row 17 onwards
Excel omitting commas in random ways !! Anyone come across this ? When I save this file in csv using excel 2003 A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z,AA A,,C,D,,21-Nov-06,27-Nov-06,S,1,2,,3.5,BP,N,O,,,,,,,,,,,, A,,C,D,,21-Nov-06,27-Nov-06,S,1,2,,3.5,BP,N,O,,,,,,,,,,,, A,,C,D,,21-Nov-06,27-Nov-06,B,1,2,,3.5,BP,N,O,,,,,,,,,,,, A,,C,D,,21-Nov-06,27-Nov-06,B,1,2,,3.5,BP,N,O,,,,,,,,,,,, A,,C,D,,21-Nov-06,27-Nov-06,B,1,2,,3.5,BP,N,O,,,,,,,,,,,, A,,C,D,,21-Nov-06,27-Nov-06,B,1,2,,3.5,BP,N,O,,,,,,,,,,,, A,,C,D,,21-Nov-06,27-Nov-06,B,1,2,,3.5,BP,N,O,,,,,,,,,,,, A,,C,D,,21-Nov-06,27-Nov-...

Solution: Error 2306: There are too many rows to output (Access to Excel using OutputTo)
I've joined the legions who've encountered this problem with the OutputTo method in Access: Run-time error 2306 There are too many rows to output, based on the limitation specified by the output format or by Microsoft Access A lot of people seem to have encountered it. The reason is because OutputTo defaults to an old version of Excel which only allows 16K rows A lot of people also suggest using TransferSpreadsheet instead, but that caused me a lot more problems (putting a single quote in front of my text data) Intellisense for the command does not list what your options are for th...

Excel Question: How to Delete All Blank Rows from a worksheet?
Hi, I'm brand new, and that is my question. I just spent 2 hour laboriously mousing along deleting row by row, every other row thinkin there's gotta be a better way. (I could've done it of course if the were adjacent, but they were interspersed with my good data). I'm little behind the times: agile with keystrokes but less so with mous [think I was quicker in Lotus DOS! (actually "TWIN")]. But I do play mean piano -- Message posted from http://www.ExcelForum.com Check out: http://www.cpearson.com/excel/deleting.htm#DeleteBlankRows "DaveThePianoGuy >...

Move data from column to rows HELP!!!
Hi thanks for taking the time to look at my problem, currently i have column that has thousands of rows of information in it, it looks lik this A 40432 432654 3432 532543 32432 523 53425 532532 532 523 532 111 222 333 666 numbers that go on into mabye the 5000-6000 range what i need to do is have that data moved So it looks like this A | B | C 40432 | 32432 | 532 432654 | 523 | 523 3432 | 52432 | 111 532543 | 532532 | 222 So on and so on, so instead of 1 column with 6000 lines it ...

adding a zero in front of number
how do you add a zero in front of other numbers, I am using item numbers and most start with zero, just shows whole numbers when I enter. example 095421 when I enter shows 95421. help. Hi When the number must remain numeric data, then format the cell as Custom "00000" (the number of 0's determines to which length is the entry padded). When you want the number to be converted to string, then use the formula (in my example the original number resides in cell A1) =TEXT(A1,"00000") (again, the number of 0's in format string determines the length of padding) Arvi ...

How do I make this formula?
I have an Excel worksheet to track my 401k performance. I have a row for Date, the Balance corresponding to each date, Contributions, a formula to calculate my Total Contributions, and a formula to calculate my Total Gain/Loss. My question is, how to I get my Gain/Loss formula to update corresponding to a new Date/Balance? Is there a formula function to make the formula ignore Column data in the previous Rows? Thanks! There will be, just add/subtract items from that row. Can you show us an example with data. -- HTH RP (remove nothere from the email address if mailing direct) &...

Date Formula #3
I am having difficulty with the "NOW" formula. What I would like for it to do is when I enter a value in one cell have it update in another cell the date when the information was entered and that date stay permanenty. What I am having is the date changes to the current date everyday. Is this possible to do? Any help would be appreciated. Thanks. Todd You can do it with the change event of the worksheet This example will place the date/time in the B column if you change a cell in the range A1:A20. Place the code in the Sheet module Right click on a sheet tab and choose ...