Input calculation in specified rows and columns

Sub Inspectthis()

Dim F As String
Dim I As Integer
Dim PrevRow As Long
Dim R As Long
Dim Rng As Range
Dim RngEnd As Range
Dim SumArray As Variant
Dim Wks As Worksheet

Set Wks = Worksheets("Sheet1")

SumArray = Array("O", "R", "U", "X", "AA", "AD", "AG")

Set Rng = Wks.Range("A2")
Set RngEnd = Wks.Cells(Rows.Count, Rng.Column).End(xlUp)
Set Rng = IIf(RngEnd.Row < Rng.Row, Rng, Wks.Range(Rng, RngEnd))

PrevRow = 2

For R = 2 To Rng.Rows.Count
If Rng.Item(R) = "Renovation" Then
For I = 0 To UBound(SumArray)
F = "=SUM(" & SumArray(I) & PrevRow & ":" & SumArray(I) & R & ")"
Wks.Cells(R + 1, SumArray(I)).Formula = F
Next I
PrevRow = R
End If
Next R

End Sub


The macro above will inspect every row in Column A for the text Renovation.  
When the text Renovation is found the average formula, =Average(Range:Range) 
will be inserted in the same row in Columns O, R, U, X, AA, AD, AG so that 
all the rows above the formula with numbers will be included in the 
calculation just as if I clicked the AutoSum icon and set it to average.  The 
problem I am having with macro above is that the formula range seems to be 
grabbing into the calculation 1 extra row above what it should be including 
in the formula which is messing up the computation.  Can you help me fix this 
macro so that it stops grabbing the 1 extra row above so the calculation is 
correct?
0
Utf
2/19/2010 6:41:02 PM
excel.worksheet.functions 4936 articles. 2 followers. Follow

1 Replies
868 Views

Similar Articles

[PageSpeed] 13

Please ignore this post.  I mistakenly posted in the wrong forum.  I will 
repost in correct forum.  Please ignore this post.  Thank you.

"Buddy" wrote:

> Sub Inspectthis()
> 
> Dim F As String
> Dim I As Integer
> Dim PrevRow As Long
> Dim R As Long
> Dim Rng As Range
> Dim RngEnd As Range
> Dim SumArray As Variant
> Dim Wks As Worksheet
> 
> Set Wks = Worksheets("Sheet1")
> 
> SumArray = Array("O", "R", "U", "X", "AA", "AD", "AG")
> 
> Set Rng = Wks.Range("A2")
> Set RngEnd = Wks.Cells(Rows.Count, Rng.Column).End(xlUp)
> Set Rng = IIf(RngEnd.Row < Rng.Row, Rng, Wks.Range(Rng, RngEnd))
> 
> PrevRow = 2
> 
> For R = 2 To Rng.Rows.Count
> If Rng.Item(R) = "Renovation" Then
> For I = 0 To UBound(SumArray)
> F = "=SUM(" & SumArray(I) & PrevRow & ":" & SumArray(I) & R & ")"
> Wks.Cells(R + 1, SumArray(I)).Formula = F
> Next I
> PrevRow = R
> End If
> Next R
> 
> End Sub
> 
> 
> The macro above will inspect every row in Column A for the text Renovation.  
> When the text Renovation is found the average formula, =Average(Range:Range) 
> will be inserted in the same row in Columns O, R, U, X, AA, AD, AG so that 
> all the rows above the formula with numbers will be included in the 
> calculation just as if I clicked the AutoSum icon and set it to average.  The 
> problem I am having with macro above is that the formula range seems to be 
> grabbing into the calculation 1 extra row above what it should be including 
> in the formula which is messing up the computation.  Can you help me fix this 
> macro so that it stops grabbing the 1 extra row above so the calculation is 
> correct?
0
Utf
2/19/2010 6:56:01 PM
Reply:

Similar Artilces:

How do I merge the contents of two columns?
I have two columns of text (thousands of rows) that I would like to be in one column. Ex.: 4568-15-651 and FR to 4568-15-651FR. I have done this before i just can't remember how. Help. You need to use a third column =A1&B1 copy down and the copy and paste special as values in place If you want a space between the values =A1&" "&B1 a comma and a space =A1&", "&B1 Regards, Peo Sjoblom "adamnabors" wrote: > I have two columns of text (thousands of rows) that I would like to be in one > column. Ex.: 4568-15-651 and FR...

Separate first and last name in two columns
Greetings all. I need to create a last name column and a first name column from an existing column that contains lastname,firstname m, where the "m" denotes a middle initial. For example, the first row might me "Smith,Joe" and the second row might be "Doe,Jane M". The problem is that some of the names do not contain a middle initial. I got a solution to work, but it has a bunch of loops, and takes about 15 seconds to run on 3500 rows. I'm looking for a more efficient way of pulling out the last and first name, ignoring the first initial. I ...

Excel file automatically entering string in cell for each row
I have a really simple Excel file that is being used for a Credit Card Log. The user enters data for each purchase order, the data that is kept is, PONum PODate POVendor PODescription POAmount POObjectCode POComplete (True/False) POCarryover (True/False) Each cardholder has their own file, and this file is processed into a SQL Server database. I also need to store the a unique identifier for each cardholder with each record. How can I have Excel automatically fill in a cell with predetermined data (can be on a different sheet) for each row that the user enters data on? I hope I am cle...

Conditional formating an entire row?
Hi there I am looking to shade an entire row within a worksheet based upon the value in the first column. The first column contains days of the week, I want to shade the rows for all Mondays, I can shade the first box (Monday ) using conditional formatting, but I can not think how to shade the rest of the row. Is this possible? Thanks Chris Ps I'm still using MS office 97 Hi Chris, Change the formula to reference the letter portion of the address to absolute reference of the column. The formula would be tested by each cell in the row so it must check a specific column. The cells t...

check box column
Is there a clean easy way to get checkbox functionality on an Excel spreadsheet? I need to add a column to my spreadsheet that has a checkbox (or something comparable) in each cell of that column. However, I looked at adding checkboxes using either a form control or an activex control and both seem to sit on top of the spreadsheet, rather than being associated with a cell itself. Any ideas? I'd use something simple--like an X. But if you want something prettier: (saved from a previous post) Select the range that would have held the checkboxes. Format|cells|number tab|...

Breaking out rows of data, sequentially, into headered columns
Merry Christmas everyone, I highly respect the great minds that solve the many Excel riddles that are posted on this newsgroup. I hope you are all having a great holiday. If anyone is so inclined or feels like solving one on their return, here is my issue... I have existing code below that is quite useful for its former purpose, but I wonder if it could be modified to suit a new application. It probably just needs a little tinkering, but who knows. 1) My raw data consists of sequential (already sorted) numbers in column A, running for unspecified (varying, that is) lengths of...

select next empty row
Im a novice and cant seem to find my answer. Sheet1 in my workbook is a data buffer to i simply paste text, sheet 2 organizes the info into a readable format, but only does this for the pasted data, what i want to do is hit a button to have the data stored and go onto the next row to store the next set of data when i hit the button(command) again thanks in advance for your assistance If you want to avoid pasting empty rows, try the following: Say you data is in columns A thru Z, in column AA enter =COUNTA(A1:Z1) and copy down Then switch on AutoFIlter and for column AA pick Custom / i...

How do I calculate "x+x=2x" using Excel?
How do I calculate "x+x=2x" using Excel? I mean, how would I calculate x+x without having to assign a number to x? If I put: A 1 x 2 x 3 =a1+a2 ( I get the error #VALUE!) ...I want it to show "2x". How do i do that? Hi Mel what do you want to see if A2 has a "y" in it? here's one option that might meet your needs ... =IF(A1=A2,"2"&A1,A1&"+"&A2) Cheers JulieD "mel" <mel@discussions.microsoft.com> wrote in message news:598FB434-CF9F-4E96-896B-C92B26039C18@microsoft.com... >I mean, how wou...

Find and Replace against set of rules in 2nd column
Looking for a tool, or code that does the following. Replace Special Character in Sheet 1, Column A. [using "^" as my special character, remove the quotes. Could change if using something else would be better.] Replace ^ with contents of Sheet 1, Column B. For example I have: Column A Column B 12^798 xyz 0^5131 abc 5296^4 efg Column A represents item #'s out of our catalogue. Column B represents Supplier So in the end I would get: Column A Column B 12xyz798 xyz 12abc798 abc 12efg798 efg 0zyx5131 xyz 0abc5131 abc 0efg5131 efg 5296xyz4 ...

How do I determine if a value in one column exists in another column?
This is a multi-part message in MIME format. ------=_NextPart_000_007C_01C505F3.D10143C0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable I tried =3DIF(MATCH(A2,'Equipment Data'!A:A,0),"T","F"), but it produces "T" for all values - even if they do not exist. Thanks in advance,=20 Jim ------=_NextPart_000_007C_01C505F3.D10143C0 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"...

How do I create a formula that calculates shipping costs? #2
I would kike to calculate shipping costs before I receive the invoice from the supplier. The costs are based on destination and weight of the shipment. For example, I may have one shipment to Austria weighing 2.5 kilos, and another weighing 25 kilos. The rates are calculated in increments of half kilos up to 5 kilos, then after that I have a basic charge of 5 kilos plus a reduced 0.5 kilo rate. Can anyone help? Thanks Mark, I think I have an answer for you. Now if I can explain it to you... A B C Weight Shipping ...

Row and column numbering
Hi all, I downloaded some useful templates from MS website. I found some of them don't have the row and column numbers shown. How can I show them again? Thanks, Derek Click Tools / Options / View and check "Row & column headers". /i. "Derek" <derekchang@sina.com> wrote in message news:#ZZuTKSUDHA.1712@tk2msftngp13.phx.gbl... > Hi all, > > I downloaded some useful templates from MS website. I found some of them > don't have the row and column numbers shown. How can I show them again? > > Thanks, > Derek > > ...

Data from one column set up into ten columns
I have some data in one column: 1. ABC 2. 3. xyz 4. 5. 123 6. aaa 7. 8. 9. 10. 11. DEF 12. 13. rst 14. 15. 456 16. bbb 17. 18. 19. 20. 21. GHI .... and so on... I'd like to set this data in one row but in ten columns, like this: ---A--- ---B--- ---C--- ---D--- ---E--- ---F--- ---G--- ---H--- ... 1. ABC xyz 123 aaa .... How it can be done? -- Tobi tobbi@poczta.onet.pl www.aukcje24.pl - najlepsze aukcje internetowe! Hi Tobi copy it, click in A2, choose edit / paste special -values then delete row 1 Cheers JulieD "Tobi" <tobbi@poczta....

change the column labels from A to Name
I am trying to change the column labels from "A" to "Name" and "B" to Address and so forth. How do I do this? I tryed to use the help and that wasn't too helpful. Please give me some pointers and a direction. Thanks! Deputy, You can't. Normally, you use a row at the top and put in your own headings. -- Earl Kiosterud www.smokeylake.com "Confused Deputy" <Confused Deputy@discussions.microsoft.com> wrote in message news:2797D6AB-36CF-4F2F-8593-28FDC6E24F47@microsoft.com... >I am trying to change the column labels from "A&quo...

Hiding Columns and Rows
I use a shared Excell worksheet and im trying to hide rows and colums but when another person opens the sheet they can see the hidden rows that i hid. How can i hide rows and colums and still have other users edit the sheet but not see the hidden rows. Hi Dan, You could try the following: Under the Worksheet Activate event-procedure put something like: Columns("E:E").Select Selection.EntireColumn.Hidden = True Rows("11:11").Select Selection.EntireRow.Hidden = True the above code will hide Column "E" and Row "11" each time the sheet is activate...

VBA macro to hide certain columns even when new columns have been added
I have created a spreadsheet that contains several buttons for different views. Each of these buttons runs its own vba macro that hides certain columns in the spreadsheet. I am trying to figure out how I can set this up so that if a user adds a new column to the spreadsheet, it still hides the same columns. For example, I have columns A,B,C,D, and E. I have a macro that hides column C. If a user adds a new column to the beginning of the spreadsheet, I want that button to still hide the old column C (now column D). Any thoughts on how I could do this? One way is to give for example the ...

no row numbers or column letters DISPLAYED
Help! I have one document where I can't see my row numbers or column letters on my spreadsheet. This makes it difficult to select rows or columns to cut and paste elsewhere. Is there a way to display the column letters and row numbers (not on printed output, but on monitor)? This document was created by a former co-worker and I have to modify it every year. There are numerous formulas and details to start from scratch. <Tools> <Options> <View> tab, And make sure to *CHECK* "Row & Column Headers". -- HTH, RD ====================================...

Hiding Columns #7
Hi, I am new to this forum. I hope someone can help me. I have a workbook to which I add a sheet every pay period. Actually, I copy the sheet from the previous week and make a new, additional sheet at the end of the workbook. I use these sheets for batching the current payroll information and link past columns to new. I do not remove any old info from the new copied sheet, but I add 10 new columns to each sheet each time I copy. I tell you all of this in case it helps with my problem. My new sheet will not allow me to hide the columns I don't need to see. The Error message is "CANN...

Creating a hyperlink in column
I am wanting to have Excel link to a website based on values in a column. For example, 12345678 is the first value in column R:R, or R2. I want Excel to convert these values to a link to "http://www.somewhere.com/abscd.asp?addrs=12345678", but I only want to see 12345678 in that column. Is this possible? Any help you provide is appreciated. I am sure there is a solution in VB but if you want to use a formula.... You could do it so that Column S contains the Hyperlink. In S2 =IF(R2=12345678,HYPERLINK("http://www.exceltip.com",R2),"") In the HYPERLINK for...

Incorrect MRD Calculation In Lifetime Planner
In MS Case # 1065381525ID I received a reply that the Minimum Required Distribution (MRD) calculation from an IRA account in MS Money Plus Lifetime Planner is not being performed correctly and is a known issue with the program. For my particular case (turned 70 this year), I noticed that this MRD calculation is wildly incorrect and distorts all of the future lifetime planner forecasts making this feature of the program essentially unuseable. The above referenced case did not give any indication of when this problem might be fixed. Can you give me some idea of when a fix for this bug wo...

Excel 2008 hanges when deleting cells or rows
Anyone know of a problem with Excel 2008 hanging when deleting? I have a large spreadsheet with all my banking history in it Today after doing a statement download to a .csv and then cutting and pasting into the spreadsheet I tried to delete a few repeated lines and got an hourglass. Eventually I had to kill off Excel. I repeated the cut and paste then did the same delete and had exactly the same problem. deleting just a single cell does the same. Next time I saved after the paste, shut down Excel and restarted again, same problem. I can navigate around the sheet OK, edit cells OK, but as soon...

Array formula does not calculate correctly when run from macro
Hi folks, hope someone can help with this little tear-your-hair-out number. I have an array formula that I use to calculate row-by-row totals from a table range. It looks like this: =SUM(OFFSET(TableWks,ROW(Wks_Total)-10,0,1,COLUMNS(TableWks))) where TableWks is a multi-column, multi-row table, and Wks_Total is the calculating column itself. There is no problem with the array formula; it does exactly what I want it to do. Except when I run a VBA procedure (what it does is not important, I don't think); the result of the array formula comes up the same in every row, correspond...

Excel 2003 calculation set to manual after an update was run
Hi I have an user which uses alot of formulas. Updates ran about 2 weeks ago and since then any spreadsheets she worked on she needs to set the calculations from manual to automatic. When she does start a new spreadsheet calculations is set to automatic, but I need fix the problem with the previous spreadsheets. This setting is saved with the workbook. Just open each of the manual workbooks, set the mode to Automatic and save the workbook. -- Gary''s Student - gsnu200735 "AntonZA2" wrote: > Hi > > I have an user which uses alot of formulas. Updates ran a...

column type charts
does anybody know how to put one data on the top of another in the column type chart? --- Message posted from http://www.ExcelForum.com/ You can use a stacked column chart. Jon Peltier has information on chart types: http://www.peltiertech.com/Excel/ChartsHowTo/ChartType.html maury314 < wrote: > does anybody know how to put one data on the top of another in the > column type chart? -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html thanks for links. Problem is that I dont have option stacked colum chart in my chart wizards (Excel 2...

Change ListView row background
I have a listview in an asp.net web page that I want to change the BackColor when a condition is met. I am checking for and finding the condition in the ItemDatabound event but I don't know what method I need to set the BackColor on a single row. I have done it in a GridView using the GridViewRow but I cannot find anything similar in a ListView control. Thanks. -- David "DavidC" <dlchase@lifetimeinc.com> wrote in message news:1EA4F98C-4325-4726-9A8B-5C5242F9383A@microsoft.com... > I have a listview in an asp.net web page that I want to change the ...