variables

I have VBA variables of types range, long, and string that I would like to save before the workbook is closed so their values will be preserved for the next time the workbook is opened. I have done some searching and so far the only method I can find for doing this is to assign the variables to...

Hi folks, I'm back again for more genius advice on how would I achieve the following... I would like to run a macro that will search for a number of variables and assign them for later calculation. For instance, as simplified version: I have a sheet with a number of pivot tables on it. I...

I'm having trouble with dimensioning variables to be used across multiple subs. I'm trying to keep the length of the individual subs as short as possible, and currently the only working version requires me to run the dims in every sub. In its simplest form... Sub Master RunSub1...

I am hoping to be able to calculate a date range when an initial scan would be required for a patient treatment by working backwards from the date of a patient treatment and factoring in different variables. Variables would include varied timeframes based on differing complexity and staff leave...

Guys, I'm trying to select all the cells on my worksheet. In order to do so, I was thinking about trying to use range and the variables lr and lc which stand for last row and last column. However, currently it's not working. This is the code I have: Sub Graphs() Dim lr As Integer, lc As...

I am making a duplicate entry notification. But the values will not equal for the notification msgbox to kick in. I have made this work before with another line of code by dim the value as double. But this will not work at all. Please take a look and tell me what you think. Dim Line1, Line2...

How many possible outcomes are there in 8 soccer matches

I have an addin that has range names that are used in code that is called from another open workbook. Most of the range names refer to a single cell and I successfully use the value of those cells with the following code: the first three variables are public variables declared as strings...

i'm genuinely curious....does eliminating "selecting" and declaring variables really make a difference with performance nowadays? sometimes i get this feel people that just say selecting is the root of all evil cause it screams recorded code ....but from my experience (but maybe the macros I...

Hello :) I am using excel to demonstrate supply Vs demand, with regards to project resource capacity. What I am trying to achieve (using conditional formatting), as a look up between two sheets, that identifies IF (name) in Sheet 1, has a capacity less than usual in Sheet 2, then highlight the...

I have several variables declared at the top of my UserForm (outside of any procedure), some of them are ranges, ListObjects, and PivotTables. Is it necessary to use Set variable = nothing before closing the UserForm to remove them from memory, or will they automatically go out of scope when...

I am trying to insert an Index-Match function into a table. I get an error message when I try to use string variables in the formula. The below works: Dim T1 As String Dim T2 As String T1 = "Table1" T2 = "Table2" Sheets("Orig").ListObjects(T1).ListColumns("Revised").DataBodyRange = _...

https://www.dropbox.com/s/0ulhnl1wd60qvgq/N%20Plain%20Disappearance_5JUL18.xlsx?dl=0Trying to match 4 variables, but receiving #NA . I've incrementally built formula, and three variables pulled in expected value. Adding of the 4th variable is apparently the issue, however it looks correct from...

I want to store the SUB below in a module then to call it from anywhere so that I don't have to write the code over and over again... My problem is how/what to declare of the variables (in the SUB line) THEN when this sub is called, it will be as below: SUB SortSubtotal("client", "C2:C",3) SUB...

I have several locations in my spreadsheet that have merged cell location that has the colorindex of 16. I want to save the address of the different locations in my sheet that have colorindex of 16 to several variables or an array.. How can i do this? thanks

Hi everyone, I currently write my Master thesis in the field of renewable energy. I want to implement the famous mean-variance analysis by Markowitz to show how all possible combinations of 10 wind parks are different in terms of return/risk ratio. There must be 2 specific formulas...

Hi all, So I'm working on improving my ability to write and understand macros (I'm entirely self-taught in excel, so I figure why change that now?). Basically writing Macros, then looking for ways to make them better/faster. Anyway, when I'm designing subs and functions, I put watches on the...

Good day, Excel Guru's! Right to the point: After closing a userform and opening a userform from the same project (addin file) referencing global object variables, I get the RTE 91 "Object variable or With block variable not set". I normally don't use global variables, but this project is...

I need to visualize train paths on a single track railway to suggest optimization of train schedule slots and increased service patterns. The distances the train are traveling are obviously well known and should ideally be displayed on the Y-axis with the fixed points being the (accumulated)...

Hi, I've just started dipping my toe into the world of PowerQuery and M and I can see the potential of what it can do ... however my skills aren't quite there yet ! I have a folder called Weekly Reports that will contain folders full of xlsx files saved by weeks eg : 2018-05-21 2018-05-28...