worksheet function

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...

<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...

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.

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...

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...

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...

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...

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...

<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...

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

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...

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 &...

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...

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")...in case you didn't notice, that date...

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?

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...

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 =...

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...

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...

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...