# function

1. ### function to return unique values to activecell

Good Morning from California, I am trying to turn a subroutine that I have borrowed from @hiker95 into a function but I cannot get the output the dictionary to the activecell. I have tried a number of ways, but I just can't figure it out. I have verified that all of the values are being stored...
2. ### How do i put the last value of a loop from a vba program into a cell in excel

i have a do while loop w a nested if statement, it is for iterations for the bisection method for finding the root of an eq. i woud like to know how to put the last iteration in cell C2 please an thank you
3. ### Excel formula question

Hi, I need a formula that will do the following, =IF(A1≥300,"YES","NO") but cannot seem to get it working. The formula essentially needs to read A1, ignore the first two characters of the cell and output YES if the number is more than or equal to 300. Below is an example of the data set and...
4. ### Using transpose or other way to make a table

Hello, is there any chance to make the table in sheet "PL_FORM" transposed to the table in sheet "AS_FORM" automatically? I've tried everything I know but nothing does the trick :( Please help me Thanks in advance, Cheers.
5. ### Disable and enable one of excel functions in ribbon menu.

Hey, I wanna know how to disable one of excel functions in the ribbon menu? And how to enable it again? For example, I want to disable the pivot table function so that I can't create pivot table in my worksheet. Also I want to know how to enable the pivot table function again. That's all my...
6. ### VBA Iterator

Hi Mrexcelians. I have this formula Pi = Ni^k (power k) Applied to this Odds example: 1.51 3.69 5.30 (in columns D, E, F respectively) Where Ni represents 1.51, 3.69, 5.30 individually - There are 2 competitors, n, & N=Sum of the 3 Ni(s) k is derived as: Log n / Log(n/N) - k is the SUBJECT...
7. ### Searching names in a column and coloring them if they match in another column

Hello. Sorry if my English is bad, I am french. I would like to compare a column of name with another column of names. I used a levenshtein code to have a percentage of similarity with the columns. What i want to do is like if the words are matching with a > 70%, it colors the name in green If...
8. ### Cannot use "Right" Function. Apparently meaning has changed.

I have a large VBA project I'm working on that uses the "Right" function a lot. All of a sudden it gives me an error message of... "Compile Error Wrong number of arguments or invalid property assignments." When I try to compile, with the "Right" Function highlighted. What's really strange is...
9. ### 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...
10. ### 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?
11. ### 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...
12. ### 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...
13. ### 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...
14. ### 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]
15. ### 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...
16. ### 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...
17. ### 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...
18. ### 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...
19. ### 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...
20. ### 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'...