method

  1. S

    Formula to find last row in a range

    Hi, I am using the formula below to determine if all entries in a column range are the same (if they are than it inserts that item). The range starts at G41 and the indirect is picking up the last row number based on a value in A1. I use this type of formula many times in my workbook and...
  2. W

    Extract Values from Dependent Lists based on Twin Criteria

    I am looking at a way to extract values based on twin criteria, preferrably using vlookup or any other method. I have 2 constraints - One, I can't have any VBA code in my spreadsheet Two, I may not be able to add any additional columns in my sheet 1 Requirement: I would like to get values...
  3. S

    VBA Copy files

    Hi friends, I'm using excel VBA for copying list of files from column, I prepared program to copy files to a specific destination (Below the program) I need help to create program for copying files to a list of destination listed in column respective to list of files Option Explicit Sub...
  4. G

    Looking for closest value without duplicates

    Hi guys, I am looking for a way to match the closest value in a column without any duplicates. I used the following method : https://www.extendoffice.com/documents/excel/4279-excel-find-closest-nearest-value-greater-than-less-than.html This method worked fine : however I am looking for one...
  5. R

    Thisworkbook property

    In the below lines the first 6 and the 11th work, but lines 7, 8, 9 and 10 don't work Do you have to use Sheets when using ThisWorkbook [myrange] = "Yes" [myrange].Offset(0, 1) = "Yes" Sheets("Sheet1").[myrange].Offset(0, 2) = "Yes" Range("myrange").Offset(1, 1) = "Yes"...
  6. C

    range.replace or other method based on <> string value

    Looks like the range.replace method has a findvalue and a replacevalue, is there a way to replace all values in a range which are not equal to 'alpha' for example ? already have code to iterate each cell. hoping for something more awe-inspiring :) thank you!
  7. L

    is Workbook.SaveAs a method or sub?

    Hi In this article https://docs.microsoft.com/en-us/office/vba/api/excel.workbook.saveas MS define SaveAs as a method!! While object browser in VBA editor define it as Sub? Which one is the correct one? or it does not matter? Thank you very much.
  8. L

    Workbook.add

    Hi Why the code below is acceptable? Workbooks.Add.SaveAs Filename:="c:\testing.xlsx" The part I do not understand, is that Add is a method so why excel accept Add.SaveAs? The above code, means Add is an object. So I checked Add and it is method that return Workbook as a type. A function...
  9. E

    Is there a file saver Macro generator?

    Hello I have created a sheet with lots of formulas and text after a lot of effort and time. I want to make a Macro that will just create that sheet and type everything that I have typed. The suggested method to go through every cell and click F2 and then Enter, while recording a Macro, is not...
  10. L

    Please can you help me debug this code?

    Hi All I'm trying to get a button to apply the contents of A1 to the tab name on every sheet, (if that makes sense?) I was using this code successfully on some other similar spreadsheets but now it simply won't work. I am getting this error "Run-time error '1004': Method 'Name' of object'...
  11. L

    Date stamp iterative method failing

    I'm using the formula: =IF(E3<>"",IF(K3<>"",K3,NOW()),"") to date stamp in K3 when an entry in E3 is made. I have itterative calculation enabled to 1000 iterations. I have formated K column to date. However, whatever I input the date 00/01/1900 is returned. What am I doing wrong to get...
  12. K

    Efficiency of macro Execution

    In a spreadsheet, I have 5 columns of data (numbers or text) that I wish to copy to 5 other columns by using a macro. There are at least 2 ways to do this: 1. use a loop, changing the column names/numbers by using a variable to identify the columns. Then carry out the copy/paste functions...
  13. L

    Fastest method to move duplicates from to a new sheet

    I have looked and tried several ways to identify duplicates and copy them to a new sheet. I have about 20,000 lines in columns A:J. If there are duplicates in column E, then I want move the entire row (both original and duplicate) to the new sheet. I've used VBA to add a countif formula to...
  14. H

    Confidence Intervals using Solver

    Hello Everybody I'm doing the parameters estimation of a model using the Excel solver with the GNG Nonlinear method. My question is, how can I get the confidence intervals of the optimized values? Thanks
  15. auto.pilot

    Seeking method to list all combinations of six numbers, with a twist

    I've seen other threads on this topic but can't find anything to fit my needs. I am seeking a method to list all combinations of numbers 1, 2, 3, 4, 5 & 6. However, I also need all of the combinations of fewer numbers. Examples: 1,3,5 and 2,3,6 and 1,3,4,5,6 However, I don't need repeats...
  16. C

    Method Intersect of Object _Global Failed

    I don't have a lot of experience with VBA and there's one error that keeps bugging me. The following code does exactly what I want, with the exception that I get the "Method Intersect of Object _Global Failed" error every time I update cell B3. What am I doing wrong? I thought I was being...
  17. S

    Method Range of Object Worksheet Failed error

    I am receiving the method range of object worksheet failed error on the line below: G() = PBK.Range(Cells(2, 1), Cells(N, 1)) G is a variant, PBK is "Set PBK = Workbooks("A_LA_CARTE.xls").Sheets("Sheet1")", and N is an integer variable I am not sure why the range method used to select the...
  18. D

    Milestone Message Boxes that timeout and show the progress through steps in a Excel Macro

    I have an Excel 2016 VBA macro for processing a large volume of data. The macro can take up to 14 minutes to run due to the volume of data. I have used the usual methods for speeding up the macro which means screen refresh is switched off. So when the macro is running I do not know if all is...
  19. T

    With statement

    Can someone please tell me why this works: Sheet1.Shapes("Button").Select With Selection .ShapeRange.Fill.ForeColor.RGB = 12345 End With but this doesn't: With Sheet1.Shapes("Button") .ShapeRange.Fill.ForeColor.RGB = 12345...
  20. Eric W

    Magic Squares

    This is not a typical question thread, although I hope it may be of interest to those who play around with Excel for fun. It's somewhat in line with the "Tough Problem" series that ran a few years ago in this forum. People with spare time and interest might read on. With the advent of the new...

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top