I am using a script that I found on this site (see below). The problem I am having is that I recieve an error on one of my sheets. The used range streatches from Column A to Column J and from row 1 to past 32767. I have found that if I delet the information below row 32767 that I do not get an error. Is there anything I can do out side of splitting the information up on different sheets?
Dim FirstRow As Integer, FirstCol As Integer, _
LastRow As Integer, LastCol As Integer
FirstRow = Cells. Find(What:="*", _
SearchDirection:=xlNext, _
SearchOrder:=xlByRows).Row
FirstCol = Cells.Find(What:="*", _
SearchDirection:=xlNext, _
SearchOrder:=xlByColumns).Column
LastRow = Cells.Find(What:="*", _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByRows).Row
LastCol = Cells.Find(What:="*", _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByColumns).Column
Set theRng = Range(Cells(FirstRow, FirstCol), _
Cells(LastRow, LastCol))
theRng.Select
Selection.Copy
I have a function which i am using as part of a macro. The macro itself works fine and locates a search string i type into an input box across ALL worksheets in an excel doc. However, i have a function which takes the results and prints it to the front page, but when i try to use the worksheet name which has been passed to the function, i get the error: Run-time error '42': object require. this happens when i get to a line of code which says
I am having a problem with a run time 13 error on a variable assignment. The variable is being assigned a value from a cell that contains a formula, and I am suspicous that perhaps the mismatch is coming thru this. The de-bugger stops on:
Area = ActiveSheet.Cells(count + 41, 8). Here is the complete code (with all my poor coding skills!)
Sub Reservoir() Dim Inflow As Single Dim Withdrawal As Single Dim Evap As Single Dim Area As Single Dim Spill As Single Dim Stor_1 As Single Dim Stor_2 As Single Dim count As Integer Dim max As Integer max = ActiveSheet.Cells(9, 4) For count = 1 To max * 12 Stor_1 = ActiveSheet.Cells(count + 40, 11) Inflow = ActiveSheet.Cells(count + 41, 4) Withdrawal = ActiveSheet.Cells(count + 41, 6)..........................
I currently have a userform which inputs data into two cells, the third then multiplies these two cells together, which is all honky dory. It works, 2 x 2 = 4. But when i put 0.6 for example it really doesnt work. 0.6 x 25 = 25. WRONG. My code in the macro is fairly long winded but will paste anyway.
If ComboBox2.Text = "Pounds (£)" Then Sheets("Claims").Range("G16") = TextBox2.Text Else Sheets("Claims").Range("F16") = TextBox2.Text End If
i have a small problem with a text box, its working fine, its on a seperate sheet to the one it passes the value to, its basically a quantity box, so the cell it passes the value to has a simple =I12*J12 formula in it, to multiply the quantity by the unit price, thus equalling the total priec of course. My problem comes if you change your mind, you put in the item, and the quantity, all works good, but if you want to delete the item you enter 0 or delete the value right? When i delete the value, the total cell brings back a #VALUE error, instead of 0, its almost like the text box is passing an invalid string to the quantity box, instead of nothing, if i manually delete the the value (even tho the cell is empty) from the linked cell, the #VALUE error goes away, any clues as to what the text box is passing that is causing hte error?
I have a row which shows date by month and then a row which shows cash flow. I would like a formula to calculate the break even point in the cash flow row and return the month which this occurs. I would also like to show the number of units required to break even.
Is it at all possible to have a hyperlink that when clicked will take the user to detailed information on another page? I have a summary information with account numbers on one page and would like to click on a hyerlink related to the account number and take the user to the detailed information on another page.
I am looking for a discounted cash flow worksheet where I can set the period of time to a certain number of years, set the terminal value at the end of that period of time, as well as having the ability to set the discount rate. When all three things are set, I would like to see the annual cash flows as well as the present value amount.
I am trying to model the distribution of cash flows from a real estate investment. This is a bit complicated, but basically A1 through A37 are a series of cash flows after payment of debt service from month 0 (closing) to month 36. The real estate project has two investors, the Institutional Investor and the Developer. In this case, the Institutional Investor contributes 90% of the required project equity and the developer contributes the remaining 10%. The cash flow is distributed according to a set of parameters specifying cash flow percentage allocations and IRR targets, collectively known as a "cash flow waterfall"." As with all for-sale residential development deals, the early cash flows are negative and then turn positive as unit sales occur. The order of distribution of the cash flow remaining after debt service is paid is as follows (the waterfall):
1. Both investors receive the return of their invested capital from cash flow distributions. 1. Hurdle 1 (after the return of equity to both investors), the Institutional Investor receives 90% of the monthly cash flows and the Developer 10%, proportional to their respective overall equity contributions, until both investors achieve a 12% IRR (all IRR's to be calculated from deal inception/first dollar invested). 2. Hurdle 2, (after Hurdle 1 is satisfied), the Institutional Investor receives 60% of the monthly cash flows until the Institutional Investor achieves an 18% IRR. 3. The Final Split (after satisfying Hurdle 2) allocates monthly cash flow 50% to each investor.
On an overall project basis, the Developer should realize a disproportionate share of the cash flow after the second hurdle is hit. This is what is known as the "promote." The most recent 36 month (37 including Closing) projected cash flows are as follows:
I realize this is complicated, but this structure is quite common to real estate, private equity, venture capital and hedge fund investments (along with most any joint venture deals with multiple classes of equity participants). The canned software that is out there that performs this type of anlysis is both expensive and rigid, and doesn't typically enable the required sensitivity analysis in structuring a deal.
i have a river cross section data. for different flow levels in the river i need to to calculate the flow area. i have a very large series of flow level data for 10 years at 1 hr interval.can you give a solution of that.
I am trying to create an interpolation for water flow in a given area. Tables below for better understanding:
Inputs Required
Area 3 km2
L 5 km
L2/A (Calculated) 8.3
[Code] ......
The idea is that when I increase the input area, I would like to interpolate between the RFFP frequency for the flow Q50/Q20 that best fits the area and provide me with a peak flow rate.
I am trying to put together a projection spread sheet for a client for cash flow purposes. I have it laid out but I cannot wrap my head around the formula and I've tried a few. Here it is.
A1: Client Name B1: Invoice Date C1: Projected Due Date ( I have 45 days added to B1) D1: Invoice Amount E1: Jan F1: Feb G1: March H1: April I1: May J1: Juneetc...
I am trying to forcast out the invoice collection date (C1). So basically in the month of June column I am trying to say if C1 falls between June 1 & June 30th then it should equal D1 (the amount due in that month). So if the invoice has a due date (C1) that falls in that particular month than I am forecasting the cash flow in the month it's collected.
I need to flow data from the Download tab to the Disb08 tab based on the date, that column F of the Download tab = "Outgoing Money Transfer" and that the 4 digit number in row 2 of the Disb08 tab is matched against Column G in the Download tab. When all those match, then the value from column J of the download tab would flow over.
I have some text in a cell, which is longer than the width that I am going to make that cell. When I don't wrap the text, it cuts it off at the end of the cell.
Since each column represents a day in a calendar, it would be incorrect to merge the two cells, but I don't want the text hidden, since I need to print the final product.
It is non-numeric text, and i've pasted --> values to remove formula issues.
Macro Goal - collect specfic raw data on sheet 1 and paste relevant data as Values on sheet 2
Error - I get the ERROR 400 pop up once I include the instructions to paste the raw data to a new sheet. (The code to paste works on its own, just not when I add it to the end of my existing code)
I looked in the FAQ for this forum and I found something similar to what I am looking for. I tried entering the formulas to sum the nth number, the problem I am running into now is that I would like to have the sum at the end of the column I am summing. I know this formula will not allow this, is there anything else I could try?
I have various sections in my spreadsheet, each section has a macro that would enable the user to add additional lines to each section as needed. The problem I am running into is that once the new lines are inserted, the formula to add every 3rd lines omits the second last cell it needs to add in the formula. I want to add G13+G16+G19+G22+G25+G28+G31+G34
Then I hit the macro button to add 3 more lines and now I want the formula to add G37 ito the list of cells in the previous the formula, instead I get
G13+G16+G19+G22+G25+G28+G31+G37
and if you add 3 more lines you get G13+G16+G19+G22+G25+G28+G31+G40. Why is this happening and how can I correct it?
I get spreadsheets sent to me with hours and minutes in decimal format. Frequently a quarter of an hour or three quarters of an hour are posted as 0.15 or 0.45 instead of 0.25 and 0.75. Other odd fractions can also appear.
I want an error message if the value when divisible by 0.25 is not a whole number.
I work a lot with PCOMM and I have a macro that I can't get to work for the life of me. I have tried error handling, but unless the code passes the test it just gives me the 1004 error.
HTML Sub Testvlook() Dim Calc1 As String Dim Res As Variant Dim scalc1 As String 'Find the TOPS window If Loops = 0 Then Set TOPS = New AutSess TOPS.SetConnectionByName "A" End If Loops = Loops + 1
scalc1 = TOPS.autECLPS.GetText(13, 27, 6) Calc1 = Application.WorksheetFunction.VLookup("scalc1", Range("p:q"), 2, False) If Err.Number = 0 Then MsgBox "Works" Else MsgBox "Didn't Work" End If
The sub i'm currently using does an instr search for a unique string in an xml file. This works fine but i'd like to speed up the search if possible because I have a large number of xml files to search. My problem is that i have very little experience in parsing xml.
I notice that if i load the xml file and look at the namespaces i see item(1) through about item(20) and the unique string is always contained in one of those items. Note that the number of items can be different for each xml file i load.
The xml file is loaded into an object i define as oInstance and i'd like to do something like:
Do counter = counter+1
if oInstance.namespaces.item(counter) = uniquestring then[code]....
This code works fine, the problem is if I put a number in, and then backspace in the textbox to change the number, I get a runtime 13, type mismatch. This error only occurs when I delete all the numbers in the cell. For example, say the number in the cell is 5025. If i change it to 5123, that is I did not have to delete the first 5, it will work. If I want to change to something like 4167, i must delete the first 5 and it will give the error. I have only been using VBA for a couple weeks, I assume it's something simple.
Private Sub TextBox1_Change() Dim StaffCost As Single Dim AvgDriveTime As Single Dim AddlDrives As Single StaffCost = OLEObjects("TextBox1").Object.Value AvgDriveTime = Range("E5").Value AddlDrives = Range("F5").Value With Range("G5") .Font.Bold = False .Value = StaffCost * AvgDriveTime * AddlDrives End With End Sub
I used the formatting wizard to make all dates the format of dd/mm/yyyy but, now, when I put a date in the column it throws up a number '41313' instead of a date, even if I manually input the date in the same format as above.
Also, I have tried to turn cells back into general number fields, but this doesn't appear to be changing it back to a date format..
There are HUNDREDS of dates I might have lost if I can't get them to start showing as dates again!
I am getting this error and where th If not starts its is in red showing that is where the issue is:
HTML Code:Â
Sub RemoveRows() Dim LR As Long, i As Long Dim ws As Worksheet Set ws = Worksheets("100 Airports") LR = Range("B" & Rows.Count).End(xlUp).Row For i = LR To 10 Step -1
"Type Mismatch error" in the macro below. I am trying to implement logic, where if value in column F, equals "1ma+89", then extract 89 only in column G. I also have other values in column F, where I have set specific rules for them, to extract numbers.
I have attached a worksheet with example data and highlighted red cells, represent incorrect output from the macro.
