worksheetfunction

  1. X

    Macro VBA INDEX MATCH LOOKUP

    Sub FindBeginEnd() Dim m As Long Dim Lastrow As Long Dim ws As Worksheet Set ws = Workbooks("TYPE A").Worksheets("Aimei Cheah Output") Lastrow = Sheets(1).Cells(Rows.count, "A").End(xlUp).Row Range("AT4").Value = "Shortage Begin" Range("AU4").Value =...
  2. W

    Whole circle bearing calculation with vba

    Hi, I'm trying to use this expression in a macro to obtain the whole circle bearing between 2 points of known latitude and longitude, the LatA Lonb etc (all in radians) 360-(MOD(ATAN2((COS(LatA)*SIN(LatB))-(SIN(LatA)*COS(LatB)*COS(LonB-LonA)), SIN(LonB-LonA)*COS(LatB)),2*PI())*180/PI()) I...
  3. P

    can't find the function I created under the WorksheetFunction

    As the title states, I haven't dealt with the worksheetfunction alot and I've never dealt with it when it came to a function that I created. So I am not sure why my function doesn't show up but it shows up on the spreadsheet. Any help would be greatly appreciated. Thank You
  4. L

    functions available for vba programmer

    Hi I just want to clear the confusion I have about vba functions vs WorksheetFunction. Please correct me if I am wrong: All VBA functions are listed here: https://www.techonthenet.com/excel/formulas/index_vba.php All excel functions (without vba, like open sheet and type = then function name)...
  5. L

    WorksheetFunction

    Hi I can find some excel functions in WorksheetFunction object. Where can I find the other one? Thank you.
  6. L

    Worksheetfunction does not have concatenate()

    Hi I thought class WorksheetFunction has all excel functions inside but I just checked and noticed that Concatenate() function is not there. Where to find it? Is there any object in vba which All excel functions? Thank you for all your help.
  7. W

    Unable to get the VLookup property

    Within a longer macro I've included the following Dim readingdate As String Dim yrstart As Date yrstart = Application.WorksheetFunction.VLookup(readingdate, Sheets("Lookups").Range("B7:E19"), 4, False) but it returns an error "Unable to get the VLookup propertry of the Worksheetfunction...
  8. S

    Unable to get the Pearson Property of the worksheetfunction class

    Greetings, I am receiving the error "Unable to get the Pearson Property of the WorksheetFunction Class" Runtime Error on the line below. Arg1 and Arg2 are both Range variables. J, and i are integer variables. I would like the value of Cells(j,i) to be the value of the Pearson...
  9. R

    Error while finding duplicate with Application.WorksheetFunction.Match

    Hi All, I am trying to find the duplicates in a source sheet "Data" and print the duplicate values in another sheet "Data Errors". I am using the below code : Sub Macro3() ' ' Macro3 Macro ' ' Dim i, j As Long Dim LastRow, LastCol As Long Dim DataCell, DataRange As Range...
  10. D

    Index-match with multiple criteria in worksheetfunction

    Hi all, Well, I`m stuck, I couldn`t find adequate answer in forums. Here is the code: matchRECORD0 = Application.WorksheetFunction.Index(Sheets("TEMPDB").Range("D" & lrSJIMPORT2 + 1 & ":D" & lrCHIMPORT2), _ Application.WorksheetFunction.Match(Sheets("STATIONS").Cells(RARSTAT2, 2) &...
  11. T

    VBA WorksheetFunction.Match problem

    Hi I have a dynamic range of cells that contain information belonging in another table. I'm trying to use the WorksheetFunction.Match to find the row position in the other table but I keep getting returned the same value. For i = 2 To NumOfRows Worksheets(2).Range("R" & i).Value = _...
  12. S

    Coding best practices for calling worksheet functions which may return an error?

    Hey all, So I just stumbled across an issue which I hadn't expected when processing certain datafiles with my VBA using the WorksheetFunction.AverageIf(). Evidently it's possible for me to process an array with this function call which may actually contain ONLY zeros, and hence throw an error...
  13. JimJohnson

    WorksheetFunction.MAX + tables

    I am trying to get the maximum value in a range, which is part of a table (named tbl_range). I'm wondering if, instead of doing this (which works): nch_y_axis = Application.WorksheetFunction.max(Sheets("TrendRaw").Range("F2:F10")) I could something along the lines of (which does not work)...
  14. M

    Updating claim number - WorksheetFunction?

    Long time lurker - first time post. I've been trying to figure this out on and off for a couple weeks. It took a bit for me to come crawling out of the shadows. I import claim data on a daily basis at work and one process I still can't figure out how to automate. Range("J14:M14") is named...
  15. T

    Unable to Search using Application.WorksheetFunction.Match and Range with Variables

    Using 2013 VBA. I am trying to search a Range, i.e. D2:D100 for a string. String to match is a Sting Variable that changes, i.e. TktToFind = "100001-". Object is to return the Row to a Long Variable RowCount. The Range changes so the beginning and ending are contained in string variables...
  16. J

    VBA / Userform Expert Needed!

    Hi guys, I know someone out there can help, and most likely the solution is right under my nose! Here's my problem: <gs id="599a80ab-3dc0-4be6-9580-6cb172c5fdca" ginger_software_uiphraseguid="b4acff07-67dd-4d48-8ca8-8220b278e8cc" class="GINGER_SOFTWARE_mark">I</gs> have a <gs...
  17. C

    Having an Issue Using WorksheetFunction in a UDF

    Hi I have what I thought was a simple task but I keep getting unexpected results... I googled around but could not find an answer so hopefully this question is not redundant... I am creating a UDF which is going to wrap some regular excel functions. This UDF will reference ranges in one of the...
  18. M

    calling colums in another sheet for vlookup

    I've tried these three call formats, none work (runtime error)... what am I doing wrong? Thanks in advance Application.WorksheetFunction.VLookup(Cells("A1").Value, Range("airport list!B:C"), 2, False) Application.WorksheetFunction.VLookup(Cells("A1").Value, Sheets("airport list").Range("B:C")...
  19. S

    How to nest long formula in VBA?

    Hello Everyone! How to nest the long excel formula in vba using WorksheetFunction? Here is my goal... I need to convert the following into Minutes Column A - Column B(mins) 0Day(s), 5Hour(s), 25Min(s) - 325 4Day(s), 7Hour(s), 11Min(s) - 6191 7Day(s)...
  20. B

    VBA finding duplicates and taking lowest value

    Having tried for hours to work out the solution myself I am throwing in the towel, can anyone please help me out? I will be doing this many times so would like to avoid using pivot table and copy paste from that. So I am trying to use VBA to go through a document and delete all duplicate rows...

Some videos you may like

This Week's Hot Topics

Top