function

  1. S

    Syntax errors

    Good Morning, Trying to compile a workbook, I keep getting a few errors. Here are the first two: Syntax Error: Function TotalAdder(RCell As Range) 'Begins Error Handling Code On Error GoTo Helper Dim xIndex As Long Application.Volatile xIndex = RCell.Worksheet.Index nsheet = Left(Tname...
  2. Z

    find first letters in a string of alphanumerics

    I have a string ex: ".13 - LAR" I would like to get the first letters, or non alphanumeric and non numeric characters of the string. They are not always the same distance from the beginning so I can't use left or right function. However they always begin in the 6th or 7th position. Any thoughts?
  3. R

    Combine mutliple file in one PDF through VBA

    Hi All, I want to combine multiple files into one PDF. I found one code which is working fine if my all files in PDF format. Here my concern is, I have PNG files as well and the code is not able to combine PNG file however if i am combining manually then it combines all the PDF and PNG files...
  4. T

    User Function returning #Value

    Hi All I am currently using the below function to show if a row is hidden or not, when i first use the function it works perfectly but as soon as i unhide/hide a row all the values change to #VALUE . What am i doing wrong? Public Function isvisible(rng As Range) isvisible = Not...
  5. B

    Referencing information on a hidden sheet?

    I have a number of functions that reference sheets which are only used for lookup, a typical example of this code is; Public Function HaeLaitteet(BoxAr, prdFam) Application.ScreenUpdating = False On Error Resume Next srcSheet = ActiveSheet.Name 'Sheets("My_Sheet_Name").Select For j = 2 To...
  6. L

    Indirect function on a Table Column name

    I have a drop down list in cell A1 with years (2018, 2019, 2020). I want to make a formula dynamic based on cell A1, how do I use INDIRECT function on the column part of a table nomenclature where it says 2019? FiscalYear[2019 Actual]
  7. E

    Yearfrac problem

    Hi all, I'm using the YEARFRAC function to calculate the fraction of the year worked. Based on 13/5/2019 - 31/07/2019 the function returns <colgroup><col width="93" style="width: 70pt; mso-width-source: userset; mso-width-alt: 3401;"> <tbody> 0.2166666667. </tbody> However based on...
  8. W

    Pulling info from 2 worksheets

    Hi All, I have a workbook with 3 different sheets - (Sheets1 & 2) are data sheets where I enter in various data which both start on A1 and only go as far as row I, depending on criteria will depend on which sheet I update. On sheet 3 I would like an overall master sheet which pulls the data...
  9. W

    copying data from 2 different sheets

    Hi All, I have a workbook with 3 different sheets - (Sheets1 & 2) are data sheets where I enter in various data which both start on A1 and only go as far as row I, depending on criteria will depend on which sheet I update. On sheet 3 I would like an overall master sheet which pulls the data...
  10. C

    VLOOKUP a custum function based on category

    Hello all, I'm trying to figure out how to VLOOPUP a custum number rounding function based on the type of pollutant. For example...(these emission rates are made up for the purpose of the example) Avg (lb/hr) Max (lb/hr) Annual (tpy) Pollutant A 0.11115...
  11. davewatson86

    VBA/SAP help

    Hi all i am trying to automate the creating and saving of invoices as a pdf. so far i have everything working up until it brings up the pdf preview and then i cant get it to work from there. i have done heaps of searching and tried lots of different code but to be honest its a bit out of my...
  12. R

    Run the function when the first 5 cells have at least one figure

    Hello there, I am currently using this function =INDEX(('1'!$A2:$A262),MATCH(MAX('1'!B2:B262),'1'!B2:B262,0)) where B2:B262 includes figures and NAs (text). What I would like to do is to run this function when the first 5 cells (B2:B6) in B2:B262 have at least one figure. Otherwise, return 'NA'...
  13. S

    Missing power query functions

    Hi, I have a Excelfile with a power query model in it. It works fine on one computer, but when I run it in on another computer there are errors. I use excel 2016 on both computers but the one with errors crashed a couple of weeks ago and got office reinstalled just the other day. The error...
  14. A

    Translate function VBA

    Hi all, After trying various VBA scripts I am stuck as none of them worked. I am looking for a VBA function which will allow me to translate a cell and output translation to another cell. So for example if cell A1 had some text in Dutch, then if the function was used in B1 to translate to...
  15. R

    Conditional XIRR with multiple columns for dates / cash flows

    I need a XIRR function that would only compute the XIRR for specific ISINs. This is a picture of a sample file with unnecessary info deleted: <tbody> ISIN DATE OF INVESTMENT DATE OF SALE BUY VOLUME SELL VOLUME US7475251036 18.11. 2015 2421 -385,86 US3546131018 3.12. 2015...
  16. S

    Formula not calculating

    Hello All, Lately I have been having certain formulas fail to calculate (yes, I AM on automatic calculation ;)). On November 2, it was a simple =Today() function that hadn't updated since October 31. Hitting calculate or refresh did nothing. Only when I entered the formula for editing, then...
  17. S

    military time function error

    Good Morning, A user made this (and I just contributed) probably a year ago and I've been fighting a bug in it. I have a userform that inputs data (in a halfway pretty GUI) in appropriate cells on a sheet. The user is supposed to be able to input, say, 0900, 9:00, or any "theoretical" method...
  18. U

    Excel formula error (not working )

    Hi sir , =IF(L8>O8,"BUY",IF(B8>O8,"BUY-F","")) in the above formula the data in cell no L8 & B8 are Real time data and in O8 Predefined data , if when L8 B8 & O8 cells are Real time data then above function is correctly working but when we want to use O8 CELL as predefined value then above...
  19. M

    Lookup Function

    will someone help me to convert the following formula in access. LOOKUP(2,1/((SEARCH($D$1&"*",BOM!$I$88:$I$2617))*(BOM!$C$88:$C$2617=Sheet6!A6)),BOM!$D$88:$D$2617),"") Thanks in advnce
  20. P

    Mimicking ROW() function using VBA

    Hi, I'm trying to broaden my horizons. Is there a way to mimic Excel's ROW() function in VBA? The following code is close:Function MYROW() MYROW = Selection.Row End FunctionBut if you have a range that spans more than two rows and hit Ctrl + Enter, it will only output the top row of the...

Some videos you may like

This Week's Hot Topics

  • Importing multiple excel files into one spreadsheet
    Hi, I'm trying to import multiple excel files (with the same format into a single spreadsheet) so that each day's file is listed underneath the...
  • find many based on a certain criteria
    good evening, I hope someone can help me? I have a workbook sheet 2 contains lots of data.... I would like to be able to find anything on sheet...
  • How to copy multiple rows using If
    Hi all, I'm very new to VBA and have written this simple code to copy certain cells if a certain cell within that row contains any data. I need...
  • VBA If statement
    Dear All, I have two dates, where I'd like a message box to pop, if the dates are between this criteria. [CODE] sDate1 = #10/1/2019#...
  • Text Format
    I have a sheet for user to keyin the data. The format of the data can be 451 / 1903, 0012 / 9908 or 00287 / 0099. The number after the "/" is...
  • Syntax errors
    Good Morning, Trying to compile a workbook, I keep getting a few errors. Here are the first two: [code=rich]Syntax Error: Function...
Top