# worksheetfunction

1. ### 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. ### 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. ### 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. ### 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. ### WorksheetFunction

Hi I can find some excel functions in WorksheetFunction object. Where can I find the other one? Thank you.
6. ### 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. ### 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. ### 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. ### 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. ### 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. ### 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. ### 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. ### 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. ### 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. ### 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. ### 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. ### 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. ### 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. ### 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. ### 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...

### 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 MrExcel.com.

### Which adblocker are you using?

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

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
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