Excel 2010 :: VBA Repeating Formula(s) In Specified Cells Based On Defined Variables?
Dec 28, 2012
In my worksheet, I know the last line of my report AND I know how many "reporting segments" (all equidistant and identical) there are in the report based on the last row using the below (which I tested in several reports in messages):
Dim vListEnd As Variant
Dim vTaskCount As Double
vListEnd = ActiveSheet.Range("A1").Offset(Sheet1.Rows.Count - 1, 0).End(xlUp).Row
vTaskCount = ((vListEnd - 5) / 22)
My "reporting segments" are ALWAYS 22 rows each and the first one always starts in row 5. Example: A document where the last row (containing an End of Report special character) is 247 would have 11 reporting segments.
Within each reporting segment, there are two different formulas needed.
For the first reporting segment located in rows A5 thru J27:
I need FORMULA1 in all cells of the range (D5:D15 dragged out to H5:H10(not 15) and D18) and FORMULA2 in cells (F20:F22,J20:J22).
Then I need to repeat that vTaskCount number of times (number of segments) and at intervals of 22 (segment repeater offset). Some reports may have 2 segments, some might have 50, but the structure is always identical.
My formulas are all pretty simple if it makes a difference (originally using FormulaR1C1 = "=RC[-1]+RC[-3]" --- but because I now know solid cell locations I suppose I don't need to make them relative, too.)
I have tracked down an overflow Error 6 to the following line:
dMax = Int(s * DPoints / SPoints)
Where all variables are defined as Integers. The error goes away when I define s as Long, but I don't understand why this should be required since none of the individual variables ever exceed the scope of an Integer (-32768 to 32767).
Strangely, I get the same overflow problem when I type either of the following calculations in the immediate window:
Note that no variables are defined, so why the overflow problem? Surely Excel/VBA should be able to work with this very basic calculation without any problem, by assigning the appropriate dimension, no?
Is this a known problem with Excel/VBA?
I am using Windows 7, with Excel 2010 (updated from 2009).
I have a list that I need to move to another column without spaces. I have it moving without spaces but it is only one item over and over again. I am using Excel 2010 and that may be my issue. I have attached the exact worksheet and formula.
I am using Microsoft Excel 2010 and Microsoft VBA 7.0 on my system. I would like to eventually create a PowerPoint and insert charts generated in the Excel workbook. In the meantime, I cannot get the basic PowerPoint created.
The line in red is highlighted blue when the compile error "User-defined type not defined" message box appears.
Public Sub TryAgain() Dim myPowerPoint As PowerPoint.Application ' ' do nothing for now 'End Sub
I have set the references such that Microsoft Project 14.0 Object Library is indeed checked. The Excel file only contains this code in a module. All sheets are blank. Nothing else is written yet.
how to apply conditional formatting via VBA to a range of cells based on input from another range of cells. Obviously this would be easy in Excel 2010, but I'm still using 2003 at the office and it needs to stay in this format to be readable by other users:
For cells M8:EK8, my conditional formatting condition 2: Formula Is =AND($E$8>=M2,(($E$8-$D$8)>=(N2-$M$2))), color index is 40 condition 3: Formula Is =AND($F$8<=M2,$G$8>=M2), color index is 39 I want to add: condition 4: Formula Is =AND($H$8<=M2,$I$8>=M2), color index is 40 condition 5: Formula Is =AND($J$8<=M2,$K$8>=M2), color index is 39 and so on
The cells in the range M8:EK8 are blank, they only get colored based on input added to D8 to K8. If there is no input, then the cells should be uncolored.
I'm using Excel 2010. I have three columns. Column A contains the date, Column B contains the time, and Column C contains the date and time. Column C is the result of a formula, which adds Columns A and B (ie. =A2+B2). So I have the following...
Date Time Date and Time
1/15/13 9:00:00 AM 1/15/13 9:00:00 AM
1/15/13 9:00:05 AM 1/15/13 9:00:05 AM
I'm using a defined name to store a date and time, which I'm going to use as a lookup value in a Match function. I've defined MyVal as follows...
1/15/13 9:00:20 AM
Excel automatically converts this to the following decimal number...
When I first run either of the following two macros, they correctly return 5...
Code: Sub test1() Dim x As Double x = [MyVal]
Debug.Print Application.Match(x, Range("C2:C10"), 0) End Sub
Sub test2() Dim x As Date
x = [MyVal] Debug.Print Application.Match(CDbl(x), Range("C2:C10"), 0) End Sub
However, when I save and close the workbook, and then re-open it, these macros both return an error. Is this some sort of bug in Excel?
There is a lot going on, including a check box, which I dont have much experience with and data on two different sheets. I need to sumif the box is checked (true cell) and the item matches the item on another sheet then I need to sum the qty in the adjacent column.
I will have a similar formula in both C and D, but this will be for what I need in D, and I should be able to figure C out from that. What I have to get my starting number is "=SUM('Proc-Pack SUMMARY'!B16-'Proc-Pack SUMMARY'!C16)". Now I need to subtract what is sold from that number to give me a current on hand number.
Type date qty packaged qty bulk
Inventory sold sheet:
The type column is a drop down box with that matches the type column on the inv sheet. The "cyl" and "lbs" columns have check boxes in them. So if C (cyl) is checked and A matches A on the inventory sheet I need to subtract D.
Type Cyl Lbs Qty
Ive tried various variations of this "=SUM('Proc-Pack SUMMARY'!B16-'Proc-Pack SUMMARY'!C16)-sumif(Sold!F:F,"true",Sold!E:E=Inventory!A:A,Sold!H)"
Also is it possible to link so you can only one of the two check boxes in a row? I know the option button does this, but I didnt think that would work with the formula.
I have some VBA that dumps various sheets data into an SQL Database.
Part of that requires me to sanitize all of the fields before they make it to the DB, (at least to prevent the code from breaking itself w/ errant ' characters.
right now my code is as follows
Code: If InStr(aa, "'") > 0 Then aa = Replace(aa, "'", "''") End If If InStr(bb, "'") > 0 Then bb = Replace(bb, "'", "''")
I was hoping to condense it to something like the following, however it is not working how I hoped / want it to. .. I had found somewhere out there that this Eval() function possibly could be used to 'reference' dynamic variables, however it does not appear to work at all anymore, and even then it may only have worked to 'read' and not 'write' to the variable. (Excel 2010, on Windows 7 64-bit).
I have a text box that is set to "=B1". B1 is a cell from an Access table import with memo format. The text memo is long, probably on average between 1000-2000 characters. I have set up the text box to automatically resize for the text, but the text stops at what I assume is 255 characters, even though cell B1 displays the entire memo.
How can I set it up so that this text box (or any other similar shape) displays the text and retains its resize/wrap property?
I'd like to apply multiple formula to a set of cells on a summary page. My summary page also contains 3 variable dropdowns, and I'd like to display data based on the text selected in those dropdowns (pulling data from 2nd tab "Variables")
The following formula works in the first instance:
=IF(AND(H4="Product Type A1", H6="External", H8="Existing"), Variables!C4, 0)
What I'm struggling to do is add additional formula to the same cell in order to deal with the remaining eventualities of the drop down variables:
Variable 1: Product Type A1 Product Type A2 Product Type B1 Product Type B2
I am trying to use the IF formula or a similar VBA/Macro to color certain cells. We deal with 16 different sand types that come in on rail cars. I want to put in the sand type in a cell; EX: C11 has text "30/50BH" if this statement is true to color cells A11-D11 yellow. If it is not a true statement to check for the next sand type, "20/40BH" and go on from there. If I can get an example of what to do I can build it for the 16 sands we have.
The formula I have in mind would be something like
The situation is that I have 3 interest income from 3 different companies. Column B acts as a helper column to determine if a partner receives just one interest income or receives multiple interest income. If they receive multiple interest income, I want column B to display a 1 and if not make it blank. If you notice partners 5, 9, and 16 contain two or more interest incomes (Columns C - E). All the cells are have either an amount (positive or negative) or a zero. There are no blank cells. Columns (F - H) contains the interest income amount if only that partner receives just one interest income. So for those partners they should not display any of their amounts. The reason is because I am using a word template and currently have to use multiple templates ( 3 in total) to display correctly for each partner. I got this to work with a word macro that will collapse all non blank columns in a particular table. This might be on a ongoing project for me as I do have more logic to solve.
I am trying to set public variables from an event handling procedure based in a worksheet so I can use that variable in a userform. Nothing I have tried works no matter where I declare the variable. I am using a msgbox to display the variable (a range) but it shows as blank regardless of whether I place the variable in a module, this workbook object or in the sheet object where the event code is placed.
I am sure there is a simple way to transfer variables from the sheet's code (where it must remain as the variable depends on the target cell's position that triggers the event).
I'm new to VBA and macros, using Excel 2010, and am trying to figure out how to delete all duplicate rows in a sheet where 2 or less of their values in column A is "1". I'd like have a script that is flexible enough to change to 3 or less if need be. I also have a header row that needs to be offset in the process.
A---B- 0--123 <-delete 0--123 <-delete 0--123 <-delete 1--123 <-delete based on this the value of column A 0--123 <-delete 0--123 <-delete 1--321 1--321 1--321 1--321 1--321
A---B- 0--123 <-delete 0--123 <-delete 1--123 <-delete 1--123 <-delete based on this the value of column A 0--123 <-delete 0--123 <-delete 1--321 1--321 1--321 1--321 1--321
I am trying to create a UserForm in Excel 2010 that will look for a part from our database (or on a specific Sheet, possibly a hidden sheet), then add specific data from that part's row into a new line.
I want to be able to input the Part #, and have it automatically add specified information to sheets 1 and 2 and those two differ. 1 is our Quote Sheet, and 2 is our bill of materials.
Also, I need each piece of information to go to a specific column (ex. Cost always goes to Column K)
What I have now is a broken UserForm that references a DOC file with a Table, but I need something a good but more complex.
Ideally, what I want is to have 4 ListBoxes where you choose Category --> SubCategory --> Make --> Model then have several CheckBoxes below that would determine whether to add a part (Cost, Labor, Weight).
The reason for this is because we have no need for the Cost or Labor Hours in our Bill of Materials, but need that information for our Quotes. And we generally don't need Specific Dimensions in our Quotes, but DO need them in the Bill of Materials...
Can I make it so that it will insert Pre-Specified information into both sheets at the same time?
I have been using a macro to search and highlight customer addresses for me, however I have changed my worksheet and now have the addresses in two columns instead of one (D for the numbers, E for street names) for sorting purposes.
What I would like to do is either; find a way to search using data from 2 cells to get a result, or have multiple results highlight and popup.
Using: Excel 2010
Code: Sub SEARCH_FUNCTION() Set FoundCell = Sheets("Route").Cells.find(What:=Sheets("Intro").Range("G15"), LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False) If Not FoundCell Is Nothing Then With FoundCell
Based on the current month (which I planned on typing in) on the MainList worksheet I need to count how many blank cells are in the table above. The information above is on a separate worksheet but I can change that in the formula. Every month gets a new column.
This is what I have but its not working correctly: =SUMPRODUCT(--(BTES!AD1:BQ18=F2),--(BTES!AD1:BQ18" "))
I have set up a formula to sum data from specific columns in multiple sheets in the same file. The simple formula had worked perfectly until I needed the data from the last sheet. In all of the other sheets the data that I need to pull is all in consecutive rows but in the last sheet the data that I need pulled and added is in every 4th row. The sheet looks like this:
All of the columns I've filled in work fine. The ones blank are the ones I am running into issues with. When I drag down the formulas excel pulls the next consecutive rows, which is perfect for the first four sheets I am summing, but for the fifth sheet (I'll ref the Test1 column) I need it to pull 'Wayne 2014'!C6 and then 'Wayne 2014'!C10 in the next row and so forth.
Date All Test Test1 Test2 Test3 Test4 $ Amt.
The first four sheets look like this, which is why they work fine:
Date All Test Test1 Test2 Test3 $ Amt.
Jan. 16 =SUM(C2:E2)
The fifth sheet looks like this: I only want the formulas from the first sheet to pull the data from the dated rows in this sheet.
Date All Test Test1 Test2 Test3 $ Amt.
Jan. 16 =SUM(C2:E2) =SUM(C3:C5)
Is there any way in Excel 2010 to get it do what I want? (I am not sure how that one cell became outlined and I do not know how to fix it.)
Is there a way to create a macro to color code a cell based on the value in a cell, and then look up a value in a table, then color code it based on where it fits into the table?
I have a table of values for about 30 projects. In column g - there is a CPI value (see bold column)
Example: Project ID Name Program PMT SI ID AC Milestone TCP Level [Code] ......
Here is the table:
I have to color code a cell, base on the CPI and how it fits into the table below. So if the current Milestone is M2 or M3 and the CPI calculated is .14 the cell would be colored RED, if the CPI number is 2.01 for M2-M3 I would want cell to be colored Turquiose. If we were at Milestone M6 and the CPI was 2.01, it would be colored blue. If the CPI was .75 at Milestone M5, it would be colored Green
1 workbook, 2 worksheets (or tabs). On tab 1, I want a formula/alert that tells the user if any duplicate values exist in Column A of tab 2
Tab 2, Column A, has Unique ID's (6 digit numeric values)
The user manually inputs the ID's on new rows in Column A
Row 1 is reserved and in use for something else Row 2 is my header, so cell A2 says "ID" Row 3-623 currently contain unique ID's
When the user inputs a new ID into cell A624, then they return to Tab 1, I want my formula/alert on Tab 1 to tell the user that they have duplicates in Column A of tab 2. I know the Conditional Formatting, but if the user copies in 100 new values, they won't necessarily see the highlighted cells. My tab 1 is my "checks and balances" and the last place the user is suppposed to look to ensure that they haven't created any duplicate ID's. If the user sees a warning message that says duplicates exist, then I'll tell them that they need to look at column A (for cells that have been conditionally highlighted).
One issue that I'm running into with the conditional highlighting is that I want cells A3:A1048576 to already have the conditional formatting - this way when the user inserts a value into Cell A624, then A625, etc they conditional formatting is already there. Right now with data in cells A3:A623, cells A624:A1048576 are all highlighted with the Red/Bold Red Font (which is okay I guess), but ideally it would be nice to not count 2+ empty cells as duplicates and I'll have to have my formula on Tab 1 not include the blank cells.
I DO NOT want to use the Remove Duplicates feature of Excel 2010. If I remove them I could be removing data in columns B, C, D, etc that belong to the Unique ID. I just need the user to be told in Tab 1 that they DO have duplicates and I'll train the user how to research this and fix it.
The reason I want to look for duplicates in the entire Column A is because the list of Unique ID's will grow over time.
I wasn't sure if I should put this question under "Excel Programing" or "Word Programing"... I am writing this program in excel, but the program references the word object library.
WHAT I AM TRYING TO DO: This program I'm making defines variables based on the values of a couple specfic cells. I want to save a newly created word document that this program generates, using the variables as its name.
I am trying something out the ordinary, instead of hardcoding the Range, I want o find the last used row of the Range and append, then filldown. I think my idea is good, but either syntax is off or not going to be allowed to do this.