worksheetfunction

I've looked over some posts here related to my issue, but can't seem to get this to work. I want to use Index/Match using VBA to find a specific cell value in a named range. Dim myDistro As String Dim myID As Integer myDistro = Application.WorksheetFunction.Index(Worksheets("PS Console -...

Hi I have this code which isn't working; For Each c In Sheets("Support").Range("B2:B13") c.Value = (Application.WorksheetFunction.Sum(Application.CountIfs(Worksheets("Data").Range("G:G"), Array("6", "7"), Month(Worksheets("Data").Range("B:B")), Month(c.Offset(, -1).Value))) -...

I have a VBA script error out with "Type mismatch" on the Application.WorksheetFunction MInverse line. How else am I suppose to calculate a matrix inverse? What is the type mismatch? Both arrays are the same size and the matrix defined is invertible. Option Explicit Option Base 1 Function...

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

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

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

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

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

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.

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

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

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

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

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

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

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

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

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

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

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