worksheet function

  1. L

    Getting input from one textbox and displaying output in second textbox after some calculations.

    <tbody> MATERIAL TEMPERATURE ALLOWABLE STEEL 300 423 STEEL 350 427 STEEL 400 428 STEEL 450 425 STEEL 500 419 STEEL 550 410 STEEL 600 401 </tbody> Hi all, I have a table like this where I store my values. Also I have two textboxes inside a user form. One is for inputting the...
  2. K

    Function or Formula to return date last modified?

    Hello, I am wondering if there is a simple Excel Worksheet Function (or less ideally, a custom VBA-defined Function) which can automatically return the date last modified, i.e., when the document was last updated/modified, recalculated and/or saved? Thanks for any tips. K.
  3. T

    Finding Last Saturday in Range

    I am writing a macro to try and sort some payroll information. On the worksheet, column "I" contains the "payroll end dates" for accounting records in the spreadsheet. I have used the following section of code to find the max date column "I" and assign it to a variable: Dim lastEndDate As Long...
  4. M

    Average a row of a range if the letter "i" is typed in - range has to be dynamic.

    This is my first post on here, so I apologize if I leave out details or do not explain something well enough. I have a range, where values between 1 and 5 are entered manually depending on how well someone scored, and a total row that calculates the averages of the column per section. If there...
  5. S

    CountIf Macro for Relative Columns

    Hello! I am relatively new to using VBA and am struggling to generate a macro. What I would like to do is generate a macro to perform a countif function on a column, to see how many individuals selected "1" on a 1-5 rating scale, then do the same countif function for ratings 2-5. I will need...
  6. Gingertrees

    Using VBA to reference a changable Named Range

    To pull notes from an old worksheet into the latest worksheet in a workbook, I'm trying to automate the process of adding in a VLOOKUP into a sheet. (Each day I note things in a worksheet and save the notes as a named range in the workbook.) The challenge is that the named range this VLOOKUP...
  7. P

    Coefficient of Dispersion: Create a UDF to average a range of cells

    I wish to create a UDF that calculates a formula used in Mass Appraisal of Real Estate I use MS Excel 2007, and Windows 7 This is a sample what my Data looks like: <tbody> AssessedValue SalePrice Ratio = AV/SP 90 88 1.02 90 93 0.97 90 87 1.03 90 94 0.96 90 85 1.06 90 96 0.94...
  8. M

    Populating worksheet using the Indirect function and copying that formula across the cells

    <tbody> down vote favorite Hi I am currently creating a budget and am stuck on the formulas i need to get the data on various worksheets pulled into specific sheets. I have created worksheets for Jan to Dec for my household expenditure and have also another...
  9. L

    Power function

    Hi, Anybody knows what is the difference between both codes and why the second one results in an error? Public Sub Cardano1() x = WorksheetFunction.Power(((-1 - Sqr(1)) / 2), 1 / 3) End Sub Public Sub Cardano2() x = ((-1 - Sqr(1)) / 2) ^ (1 / 3) End Sub Thanks in advance, Luiz
  10. P

    using the cell worksheet function

    Hi all, In my worksheet the user will type a column letter or letters (such as 'GD') In another cell I want to calculate teh difference between the column number of the cell letter given by the user and start column (F, whcih will be 6) I tried this: =CELL("col",cell("contents",G23)) but get...
  11. P

    count how many times a word is repeated within a cell and build new simplified name strings

    Hi, I have a list of several thousand items, which consist of several different names together like this: Mr P Thompson & Mrs S Thompson & Mr A Thompson Mr C Guy-Johnson & Mrs A Guye-Johnson & Miss J Guye-Johnson Mrs Fuller & Ms D Fuller & Dr K U Fuller Dr V Patel & Dr OO Patel Mr B Burden &...
  12. A

    sumsq function in VBA

    Hi, I am trying to calculate the sumsq throught VBA for some automation. The formula to be entered is - =SUMSQ(K1025:K1358)/(COUNT(K1025:K1358)-COUNTIF(B1025:B1358,"0")) Another thing is that the row numbers are variables, and not fixed in automation. Let's call them R1 and R2. So I need the...
  13. L

    Formatting Cells in VBA

    Hello All, I'm working on a function that is supposed to compare text. The way that it works is simple: the function takes in a data range as one of its input variables; the range is set up so that the first column is a custom date format ("mm/dd/yyyy") case you didn't notice, that date...
  14. C

    Question about using IF WS function

    Is there any way to use the Excel Worksheet IF function and keep the format of a cell? =IF(RawData!E34="","",RawData!E34) It does what I want but sometimes, the reference cell is filled with a blue color. Any ideas?
  15. J

    Tab or Worksheet Name within Worksheet

    How do I reference a worksheet name within the worksheet itself? The header/footer section will do it with the reference [TAB]. I can find nothing on this site or in Excel Help. Here is what I am looking for: Tab name is Sheet1 I would like a command that will simply put the name of the tab in...
  16. MarkCBB

    Worksheet.Function add another "Do while"

    Hi there, I need to add anther argument to this code: Sub TestRun() Range("A1") = InputBox("How many Winners would you like to pick?") MsgBox ("This is a test run. All test winners will be cleared afterwards!") Range("B2").Select Do ActiveCell.Formula =...
  17. L

    Copy / Paste Values & Formatting of Worksheet with Pivot Table

    Hi there. I generate reports for a client each day. They have gone through many iterations, the most recent of which utilizes a PivotTable. The PivotTable exists on its own worksheet (Tab). When my report is complete, I need to copy/paste values and formatting of the entire tab to a new...
  18. G

    Application.WorksheetFunction(Now) getting error

    Dear All, I am using Excel 2003 with Windows XP on a network. I keep getting errors with some code. In a sheet, I have: Sub GetUser() Range("Created").Value = Application.WorksheetFunction(Now()) Range("Author").Value = Application.UserName End Sub The code for the Author works perfectly...
  19. G

    Trouble with IF statement in VBA

    Dear All, I am having trouble in VBA for an Excel 2003 project. The following works fine: Range("AccountNumber").FormulaR1C1 = "=VLOOKUP(Template,TemplateDetails,4,FALSE)" The following gives me a run time error 1004 Application-definded or object-defined error...

Some videos you may like

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...