worksheet function

  1. C

    worksheetfunction Lookup

    Hi Guys, Im trying to use the WorksheetFunction Lookup to store the calculation within the VBA code rather than in a cell within the worksheet. For some reason I cant get the below code working in VBA although in my sheet its working correctly. In my worksheet the formula is...
  2. 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...
  3. 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.
  4. 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...
  5. 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...
  6. 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...
  7. 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...
  8. 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...
  9. 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...
  10. 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
  11. 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...
  12. 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 &...
  13. 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...
  14. 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...
  15. 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?
  16. 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...
  17. 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 =...
  18. 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...
  19. 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...
  20. 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...

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back