method

Dear Team, I have problem in finding expiry stock, I have a file contain two sheets, First sheet shows the batch wise stock with expiry dates and the second sheet shows month wise consumption, for example I have first batch expiry in June-2019 with the qty of 1000, and my stock consumption in...

While working on a spreadsheet at work, I was getting the error "method open of object workbooks failed", but when I got home, the same file was not giving me any errors. Any ideas why?

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

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

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

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

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

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!

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.

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

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

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

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

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

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

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

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

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

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

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