I have a workbook that will have a different number of sheets every time. All of the sheets have the same format. I have a summary sheet in which I would like to sum the total of Sheet1 thru the Last sheet in every cell from B12 to B24, then I am going to move on to other columns.
I am getting the same error everytime "Object doesn't support this property or method" and the error is #438.
I have tried different formulas, but none of them are working. Here is my code......
Sub FillSumSht()
Dim wb As Workbook
Dim sSheet As Worksheet
Dim LstShtNm As String
Dim LstSheet As Worksheet
Dim TotalSheets As Long
Dim s As Integer
Dim n As Integer
Map1: contains a mapping table from old values to new values ( Name) Map1: contains a mapping table from old values to new values (Location) Input: contains a table with 3 columns, Amount/Old Name/Old Location Output: contains a table with 3 columns, Amount/New Name/New Location
I need to create the sheet called Output, which will have the same Amount column as Input sheet but will do a vlookup to get New Name and Location values using the Map1 and Map2 sheet.
Can use a highlighted range in a macro or a formula.
Just really want to be able to highlight a range of cells in column B and maybe click a button to launch a userform with a label on. The label would have the average of the highlighted cells.
I’m wondering if it possible to have a variable in a Formula. I was looking around, but non of the other examples I found was working. The code I was trying is:
Sub All() Dim lngDataRows As Long Dim lngDataRowsSum As Long ' add sum lngDataRowsSum = Range("A5").End(xlDown).Row Range("A" & lngDataRowsSum).Offset(1, 0).FormulaR1C1 = "Sum" Range("M" & lngDataRowsSum).Offset(1, 0).FormulaR1C1 = "=sum(M6:M & lngdatarowssum)" End Sub
Another code I tried was from this thread
Sub All() Dim lngRowsBottom As Long Dim lngRowsTop As Long ' add sum lngDataRowsSum = Range("A5").End(xlDown).Row Range("C10").formula = "=sum(R["&lngrowstop&"]C:R["&lngrowsbottom&"]C)" End Sub
The error message is: “Compile error: Expected: enf of statement” and the part “[C:R[“ is highlighted. Tried various ways to solve it but nothing was working. Does anyone of you know how to get it up and running?
My macro inserts the formulas correctly up until the last loop where I get an Application Defined or Object Defined error on the .FormulaR1C1 line. The msgbox returns the correct values.
iCtr = 0 'data starts on 1 For c = 6 To 14 'columns F to N iCtr = iCtr + 1 MsgBox ("Column " & c & " Index " & iCtr) With . Cells(6, c) .HorizontalAlignment = xlCenter .NumberFormat = "#,##0;;" .FormulaR1C1 = "=IF(ISBLANK(INDEX(MyNamedRange," & iCtr & ",1)),"""",'" & Replace(MainPage.Name, "'", "''") & "'!R" & NextRow & "C6)" End With Next
What I would like to do is add to this code. I would like to add the following formula to the cell at the end of that pasted row. =VLOOKUP(Range,LeaveIndicator,6,FALSE). In this case the Range needs to be Row D and the last row of sheet3.
I have a spread sheet with large number of data, problem is all are in various currency so rather than typing =#####.##/a1 in every cell to get the GBP amount (a1 where my exchange rate is linked) I thought if there is macro can do this job for rme.
So what i need is macro which once run enter the formula after the numbers already in the cell in selected or given range.
=AVERAGEIFS(A1:A1000;A1:C1000;"0";A1:A1000;"A1001") into range of cells so the changes relatively to its position e.g. in column B it will look like this
=AVERAGEIFS(B1:B1000;B1:C1000;"0";B1:B1000;"B1001") The macro I recorded will paste formula where I need, but all references remain the same. What should I do to make them change?
I have recorded a macro code for which is as follows. This Macro goes into a worksheet and gives percentile value for a range of data. But this is becoming cubersome as this sheet is 65531 rows and can span multiple spread sheets. Can this be put in a loop of some sort to go through the whole worksheet? ....
If I have a formula that uses cells that are currently blank - and then run a macro that puts data into those cells - the original formula cell ranges change - even if I put $ within the ranges.
i.e.
Cell A1 has formula = sumproduct(e1:e10>10) Currently cells E1:E10 are empty
Run the macro
E1:E10 now has data in.
But formula in cell A1 now reads something like =sumproduct(E13:E23>10) or could read =sumproduct(Ref#>10)
using a macro im trying to paste in a sum formula, however this will be pasted onto different sheets and the number of records will be different for each so i cannot use absolute locations. the only constant is that all ranges will start in row I9. when it runs the following code
it fills in =SUM('I8':I13) how can i make it insert it without the ' ' around the I9? and how can i select the two cells? (again, cannot be absolute..)
I have a formula that works fine in the cell of an Excel spreadsheet but I'm struggling to translate it into VBA (your help please).
The cell formula is:
=If(B2="", "", B2 & " (version: " & F2 & ")")
I want to iterate through all rows in my spreadsheet (about 2000) incrementing the relevant row numbers in the formula @ each pass - eg changing B2 -> B3 -> B4 etc and F2 -> F3 -> F4 etc where column 'B' contains the name of the product and column 'F' contains the version number, resulting in "Product Name (version: 123)" per row.
The closest I've got to this is:
Sub LookupNameInColumnA() Range("A2").Select Dim i As Integer For i = 1 To Selection. CurrentRegion.Rows.Count - 1 ActiveCell.Formula = "=IF(B2="""", """", B2 & "" Version: 999"")" ' problem line? ActiveCell.Offset(1, 0).Select Next i End Sub
This works OK'ish but I want each line to reflect the different data per row. I'm struggling to increment row 'B' and row 'F' in the formula, having tried "B & i + 1" and "F & i + 1" but am getting confused with quotes and concatenating strings within formulas.
I have a spreadsheet that contains the 5 digit numbers in the rows and the columns respecstively. I'd like a formular or macro to change font color for each cell. If the combined value of the 5 rows are greater or less than the combined range 87030 and 87200, the 5 cells will be changed to Red. If:.........
I need a macro, or a formula that can identify if the words in the Words Column (Column A) is contained in Title Column (Column B). If it is, It displays as "Yes". If not, display as "No".
I need a formula that will return the minimum value from a column range that flexs without having to manually go in an change row references. There is a blank row between each section of data in order to separate info. As an example:....
Need to formula to flex to include rows 5 thru 7 in the first section but expand to include rows 9 thru 13 in the second section. =MIN(A5.A7) works but how do I get next section to flex to =MIN(A9.A13) without manually changing the cell references? There is always a blank row in between the sections to separate.
I like to create "templates" for files that I work with on a reoccuring basis, just to make things simpler. The current template I am working on tracks items processed by day and is used for two reporting purposes; 1 totals the pay ending items processed so I have the dates laid out based on the 2 week period with formulas for that. The 2nd way it is reported is by the month. Since this is a template, only the first day of the first pay period for a calendar year needs to be entered and the rest of the dates populate based on that date. What I would now like to do is add columns for January - December monthly totals using a formula that will read something like "if column A (where the date is) contains 1/1/2014 - 1/31/2014, then sum up column L" and than copy that formula for the remaining months.
I have a named range that expands and contracts based upon the amount of data that is in some column. Call it AllData_UsedRange.
I have another named range that actually refers to a range. Call it AllData.
Column A Row2 56 Row3 44 Row4 65
AllData is a named range that refers to the range A2:A65536 AllData_UsedRange refers to A2:A4 by way of this formula. =OFFSET(AllData,0,0,COUNTA(AllData))
How to I obtain an address of AllData_UsedRange in VBA code?
These do not work... ThisWorkbook.Names("AllData_UsedRange").RefersToRange.Address Evaluate(ThisWorkbook.Names("AllData_UsedRange"))
I am trying to create a graph for a range of data that updates monthly (adding an extra month each time). I wanted the graph source data to update automatically each time the data is refreshed so used an OFFSET formula to identify a named range. I then point the graph to the named range as the source data.
When I enter the range as the source data the graph picks it up. However, when I re-enter the source data option on the graph it has converted the named range into a cell written range (ie. replaces "=QUALITY" with "='Front page'!$B$7:$J$10" - which therefore will not update when the range increases.
Currently my Macro should: Turn off any filtersNumber column A from 1 to 1000 (starting in A14)Drags formula from K14-O14 down to last row of data shown in column Athen puts cursor in last empty cell in column B ready for user to enter data
On point 3 - I want the range to be K14-O14 if active worksheet equals "EXCHANGES" but if its on the "VALUATIONS" tab the autofill range should be L14-P14
I have found bits and pieces of macros on the internet and put them together so if my macro below is not the most effective for my needs but here it is in it's current state:
Here is my macro:
Sub AddNewEntry() 'TURNS OFF FILTER IF FINDS ONE ON Dim wks As Worksheet
I am trying to write a macro which will autofill specific columns. The macro will set the range from the start of my autofill to the end of my autofill as a constant range.
The problem I need to get around is the end of my range can always change each time I run the macro. For instance, the first time I run the macro I may only need to autofill from row 4 to row 15. The next time, I may only need to autofill from row 4 to 23 (because of user updates). How can I make the end of my range not be a constant address but variable?
I have a range of values that are not a constant length. I want a macro that will calculate the range and fill with a formula using the new range. In this example, I need a formula to look at the values in range f464:f471 and calculate the number of values greater than $1000. Column B is a salesman identifier. I can do this manually, but it takes me about 1/2 hour to complete. At every change in "B", I need to place this formula in the open, blank cell in "E" such as highlighted below.
week39
ABCDEF45939SCHTERR-WIEDEMANNDCCDIST-MUSCHANY046039SCHTERR-WIEDEMANNDCCDIST-MUSCHANY046139SCHTERR-WIEDEMANNDCCDIST-MUSCHANY046239SCHTERR-WIEDEMANNDCCDIST-MUSCHANY0463 SCH Total 14042.2146439SCITERR-SULLIVANDCCDIST-MUSCHANY15587.1546539SCITERR-SULLIVANDCCDIST-MUSCHANY7217.2246639SCITERR-SULLIVANDCCDIST-MUSCHANY5316.6246739SCITERR-SULLIVANDCCDIST-MUSCHANY2720.6946839SCITERR-SULLIVANDCCDIST-MUSCHANY520.446939SCITERR-SULLIVANDCCDIST-MUSCHANY195.7147039SCITERR-SULLIVANDCCDIST-MUSCHANY047139SCITERR-SULLIVANDCCDIST-MUSCHANY0472 SCI Total 31557.7947339SCJTERR-VANBUSKIRKDCCDIST-MUSCHANY4190.7147439SCJTERR-VANBUSKIRKDCCDIST-MUSCHANY3219.7647539SCJTERR-VANBUSKIRKDCCDIST-MUSCHANY3187.147639SCJTERR-VANBUSKIRKDCCDIST-MUSCHANY2209.3747739SCJTERR-VANBUSKIRKDCCDIST-MUSCHANY2132.7547839SCJTERR-VANBUSKIRKDCCDIST-MUSCHANY2034.56 Spreadsheet FormulasCellFormulaF463=SUBTOTAL(9,F435:F462)F472=SUBTOTAL(9,F464:F471)
Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4
1st - Need a macro to change a range of cells colours based on a single cell having a value greater than 0.001. ie. cells A1 - G1 need to change to grey based on cell F1 having a value greater than 0.001 entered in it?
2nd - Also a macro for deleting the text contents of cell C1 based on cell F1 having a value greater than 0.001. Therefor if cell F1 has a number greater than 0.001 it changes the colour of celss A1 - G1 and also deletes the text in cell C1?
I'm trying to have a macro write down an array formula, but when I hit ctrl+shift+enter, the recorder says it can't record. If I write in the macro ...FormulaR1C1 = {=...} then I get the formula as a text. Is there a way to tell the macro that a formula should be entered as an array formula?
I am trying to find out how many weeks our current inventory will support our sales. I am trying to research formulas that will do this, and coming back with things like OFFSET, MATCH, INDEX but am not certain the best way to put them together to get what I need. I use excel daily, but this is a bit mroe advanced than I am used to
Mini.xlsx
Starting in cell B4, I would like to count how many weeks of Demand can be covered by the specific Available On Hands in row 3 for that week, put the number of "Weeks Covered" into cell B2, and then fill over to the right in row 2. Right now, the values in row 2 are from my own manual calculations, but I would like a dynamic formula that will sum up the values in row 4 up to (but not greater than) the value in B3, give the count of cells that reached that sum (or even better with decimals to show the percentage covered), which I will copy over into B3:B13. Not sure if that makes sense, or if I can explain in a better way. The yellow cells are what I am trying to create a formula for and am currently stuck.
I have a row data corresponding to the measuring of load cell per min and I need to average the values per hour. So I have a column B for the date (from 1-01-2013 to 31-01-2013, column C for the time (0:00:00 to 23:59:00), and D de values per min I want to average. I have to do the same for the rest of the month of 2013 (February, ...., December).
I would like to know if there is a way to create a formula to calculate the average of the first 60 values (to get the average of the first hour of 1-01-2013), and then copy it to get the average of the following 60 (average of the second hour in 1-01-2013) and so on.
If there is no way to do it, I would like to know if I can do it using functions like average, match, index, offset, what would be the best match of those functions.
I also tried it by doing the analysis in another tab and using the function "averageifs" with two criterias: one for the date (example 01-01-2013) and another one for the hour (example 0:00:00), but it didnt work, it show error: #value. I inserted an extra column in the data tab with just the hour (example 0:00:00) in front of the corresponding column with (example 0:01:00, 00:02:00, etc) Equation I used for this:
=AVERAGEIFS('Data (min)'!D$6:D$43206,'Data (min)'!$A$6:$A$44646,A6,'Data (min)'!$B$6:$B$44646,B6) =AVERAGEIFS(TAB AND COLUMN WHERE THE RAW DATA IS,RANGE OF CRITERIA 1,CRITERIA 1,RANGE CRITERIA 2,CRITERIA 2)
I wanted to modify the below macro but don't know how. Could someone help me please? Please see the bold text below.
Dim shortmonth As String Dim todaysdate As String Dim enterdate As String 'GET DATE AND CREATE VARIABLES enterdate = InputBox("Please enter the date in the following format: MMDD") shortmonth = Left(enterdate, 2) todaysdate = Mid(enterdate, 3, 2)
I created two macros. One for refreshing the data I pull from a database, and another one adding some formulas I need. On the formulas macro, I don't know how to have it automatically fill until the last row of data to the left of the column with the formula. (Just like if I double clicked it and it filled down automatically). I also tried to combine these two macros, but I get errors and they don't execute.
Needed a formula in sheet1 in column D where if in sheet2 in column J when the cell value is in the range from 50 to 100 then the cell value in sheet1 column D becomes 0.
The range from 50 to 100 means starting from 50 it can be any one number till 100.
The formula will be pulled down in D column upto the required length.
I am using the above formula. I am trying to use range names where its bolded in place of repeating different cells. However my the result of the formula is N/A.
The range name of "Funds_SS" is a group of 12 funds. What I want to do is take the total of anything that is a Loan in column B, with a given client in column A, if it occurs in any of the funds in range name FUNDS_SS, and the value of the loan is in column F. Does anyone know what I am talking about? Can it not be done? I don't know if I can explain it any better.
