I am trying to write a function in excel to use the worksheet function "small" In the vba immediate window it doesn't return anything and in the spreadshhet I get a # NAME? error when called in a cell the code is below.
Sub UseFunction() Dim myRange As Range Dim answer As Integer
Set myRange = Worksheets("Sheet1").Range("A1:F6") answer = Application.WorksheetFunction.Small((myRange), 1) MsgBox answer End Sub
How does one pick up the data contained in a worksheet cell (say B5) and use it in the vba code of a custom function without passing the cell as an arguement
I am copying data from worksheet "Microsoft" to another workbook and paste in sheet1, i want the cell G1 to auto input the worksheet name "Microsoft" where i copy the data from,
How to have G1 show the worksheet name after i copy and paste the data from worksheet name "Microsoft".
Aggre ABC1Brand Sales 2A5 3B 2 4C7 5A1 6D1 7 8Above are my data 9 10Brand Sales 11A6 12B 2 13C7 14 15Above is the result I want. 16 17 Excel tables to the web >> Excel Jeanie HTML 4
The code instead of Vlookup that I'm using at the moment. It takes too long if you have 40,000 to 50,000 rows of data.
The data that I have is something like the sample bebow:
Col A from A1 to A15 with numbers like: 20000000 20000001 20000002 20000003 20000004 20000005 20000006 20000007 20000008 20000009 20000010 20000011 20000012 20000013 20000014
Col C from C1 to C10 with numbers like: 20000000 20000001..............................
I am trying to do is look at the first two digits of the number in column c (Range c5:c5000) if the number starts with 01 then I need the formula in column g = f5*12 if the number is 03 then I want the formula to be g=f5*24 I have several of these "cases" or "if" to put in can someone get me going a little further
Here is what someone gave me to work with but I'm having troubles getting it to work...I get a name error in the cell and I cant use this they way it is because the code needs to go into the sheet mod with a bunch of other code.Also when I use the Option Explicit the rest of my code wont work.
Option Explicit Function re(Cl As Range, Src As Range) Dim i As Long Select Case Left(Cl, 2) Case "01": i = 12 Case "03": i = 24 End Select re = Src * i End Function =re(C5,F5)
I have an Excel sheet with a few thousand rows which I would like to filter by a column with the name TrackingID. This column contains values like:
12AA1 23452BA2 234AA1 345635CA2 ...
I would like to filter this column by the third character from the end (which is always a letter from the range [A-Z]). In the example above, this would be:
I am writing some code where one column is selected and a value is searched for in that column. If found the code continues on its way manipulating the data. The issue is that the value being searched for will not always be there which results in an error. Is there a way that I can just tell the macro to continue running if the value is not found.
On a second question is there some code I could write that would select all the rows with the number 1 in column V and delete them rather than doing the way I am here?
I've been trying to put together a function in excel which will make it simpler and a bit clearer to produce the coefficients of trend lines in cells. I want to produce an excel function like SLOPE and INTERCEPT but for exponential, power and logarithmic trendlines. For example, I can produce the coefficients for an exponential trendline like this:
Ideally, I want to be able to do so without the need to convert the terms with LN function, and be able to replace it with a new function that deals directly with an X Range and a Y range
I've produced the following code:
Code: Function PowerSlope(YRange As Range, XRange As Range)
For Loops = 1 To YRange.Count YRange.Value2(Loops, 1) = Log(YRange.Value2(Loops, 1)) Next Loops
PowerSlope = Application.WorksheetFunction.Slope(YRange, XRange) End Function
However, this causes Excel to crash and shut down (not the macro itself, but the entire program). I can't even get the function to run to a break point in the first line before the crash happens. I'm able to remove the FOR ... NEXT loop and use the function to directly return the result of the linear slope, so I'm confident the issue is not in that part of the code.
I am having problems fixing my function code so that instead of appending myDate with (1) (2) (3) if 3 occurences of the date already exist it just appends it with (3). I know it has to be something simple but I know I am doing something wrong. Thanks for helping a beginner out.
Sub ImportSheets() Dim fName As String Dim WB As Workbook Dim myDate As Date fName = Dir("N:ENGBRANDON*.csv") Do While fName "" Workbooks.Open fName Set WB = ActiveWorkbook 'Copy first sheet to this workbook With ThisWorkbook
I have values that I want to color code in my table. Originally I was tagging them as numbers, but instead I need them as colors.
Function conversion(pVal As Double)
If pVal > 0.05 Then conversion = "1" ElseIf pVal > 0.001 Then conversion = "2" ElseIf pVal > 0.0001 Then conversion = "3" ElseIf pVal > 0.00001 Then conversion = "4"
End If
End Function
Now I want instead of conversion = "1" etc to be
Function conversion(pVal As Double)
If pVal > 0.05 Then (make white) ElseIf pVal > 0.001 Then (make brighter blue) ElseIf pVal > 0.0001 Then (make green) ElseIf pVal > 0.00001 Then (make navy blue)
End If
End Function
I want the text and cell the same color (number and cell so the number disappears so that I just have a colored cell but the value is still there if I want to look at it).
Whilst stepping through code in any new sub/ function that I create in VBA, VBA Editor (seemingly arbitrarily) jumps to a line in a completely different function.
Is it possible to Call the inbuilt Excel Functions (like VLOOKUP) in Macros. we have to pouplate cells with formula which uses VLOOKUP. one way was to update each cell with required Formula like: [code] Range("A1").value = " = 6*F7*(1-Vlookup(lookupvalue........))"[code] but this was stupid as no of cell to be populated was large and variable and moreover it will return formulae to cells rather than fixed value. What we tried was to defile an table_array as Array(x y) and then use this array to return required VLOOKUP value. However I was thinking is there any way to call function like VLOOKUP in Macros so that we can return a value to cell rather than formula.
I'm having a hard time figuring out how to use the SumProduct worksheet function in VBA. The below worksheet formula works: =SUMPRODUCT((accProv=D2)*(accFYE=E2)*(accAcc))
I've written some code to try to count the number of cells that are not empty and save the result as a variable but sometimes the code seems to think that the cells are empty even though they are not.
The code goes through rows 1 at a time and counts the quantity of cells that are not blank and compares them with a couple other values.
The strange thing is if I execute the actual function =COUNTA(with the range that I am selecting below) the COUNTA function works properly by itself in certain problematic sections of the worksheet.
This code below seems to work correctly most of the time but I've found a few instances where it is not properly counting non-blank cells:
why my QtyTests variable sometimes is saved as 0 even though there may be some cells that contain values?
Here is the expanded code:
Sub Macro() 'Global Variables Dim QtyTests As Integer, ReqTests As Integer, InitQty As Integer, Cork As String, Corktype As String Dim result As Long, x As Integer
I have found an addin (http://www.quantdec.com/Excel/smoothing.htm) that adds a worksheet function smoothing data. It operates with 2 arguments: - a range for the input data to smooth - a parameter detailing how to do the smoothing.
It outputs a matrix of smoothed results, and therefore requires the Ctrl-Shift-Enter on a spreadsheet. Until there no problem, the function works properly. However I need to repeat the process about 100 times. Therefore I extended my ranges to duplicate the calculations, and that also worked fine. But because I don't need the intermediate data (100 columns by 1000+ rows) and I don't like to wait 2 minutes for the calculations to finish each time I open the saved file again, I thought I could just use VBA and only output the final results.
This is how the working syntax is in a cell: =SMOOTH(B3:B1032, "5RSSH,5RSSH,>,5RSSH")
And there the nightmare starts : - I cannot apply it even once: I get a type mismatch whatever I do to calculate the range and put it as an argument of the function. I used a string to store the range name and use it as is (B3:B1032 or even "B3:B1032") or with the Range function Range("B3:B1032"). I even tried to put manually "B3:B1032" with the same result. First major issue
- Because the worksheet function requires a range, I cannot use an array, or at least I tried without success. Therefore I need to output the result on the worksheet each time it is processed, and reread it through the formula, for n times the loop is running for... Kind of inconvenient... So I tried to modify the original smooth() function to create a new one that would accept an array as argument instead of range, but with the same consequence: Type mismatch when I feed the new function with an array (which I checked contains the data to process).
I have tried an example I found somewhere using a built-in Excel Worksheet function MMult and it worked with ranges:
Sub SomeSub()
Dim xArray As Variant, yArray As Variant, zArray As Variant Dim Fn As Object Set Fn = Application.WorksheetFunction
What I am hoping to do is find the row associated with column C and row value from LU. I have a stop on the line and LU is properly valued but it won't do the match. Is my syntax wrong. I am leaving the match type blank as it defaults to 1 which is the value I want anyway.
I am trying to use VBA to find a value on a trendline. I have been unable to call the TREND function from Excel, and I can't see what I'm doing wrong. I'm using the following line of point25 = WorksheetFunction.TREND( Range("d2:d25"), Range("c2:c25"), 25)
I keep getting "Runtime error 1004 - Unable to get the Trend property of the WorksheetFunction class".
VBA will not allow the worksheet function MMult to be called during the subroutine. The error that flashes is: "Run-time error 1004. Unable to get the MMult property of the WorksheetFunction property"
I am having problems getting the code right for the WorksheetFunction. I have put a snip of the procedure below.
I have data that is sequentially entered into columns, always in rows 4 to 18 with the column ref increasing by 1 each time new data is entered.
After each instance of data entry into a new column I need to loop through that data and, if the a cell value matches a value in range A1:A200, to place x in column B in the same row as the match in column A. All in Sheet2.
Set cStartcell = Sheet2.Range("IV4").End(xlToLeft) ' goes to last entered column For Each cell In Sheet2.Range("B2:B200") Application.WorksheetFunction.If(Match(Offset.(0, -1), Sheet2.Range.("cStartCell:cStartCell.Offset(15, 0)"),""x"") Next
I have put the problem in bold. whatever I try seems to come up as 'end of statement expected' or 'identifier or bracketed expression expected' errors. When I have managed to get rid of errors the fromula does nothing.
Can you have a function create a new worksheet? i know this is possible with a Sub but i haven't been able to do this with a function. Example is below. I'm planning to call the function from an Excel cell (i.e. =CreateNewSheet())