I'm hoping this is a simple question someone might help clear up for me.
I've defined a few cell ranges on a worksheet (in my example I've defined their names as site1, site2, site3, site4, site5). All these ranges are identical in # of columns and rows.
On a separate worksheet, I'm trying to display a single named range, depending on the value in a dropdown menu.
In my example, I have a pulldown menu with the following items: site1, site2, site3, site4, site5 (mirroring the named cell ranges). I'd like to display one of these ranges, depending on which name is selected in the dropdown.
In another tab, I have a report where a row containd the project name, and I want to find the maximum value of the dates in the ither tab, where my project name matches the project name on the tab data I am given.
So, if I have "Project-C", I need to know the max of the dates in the row for Project-C; but I dont know what row that will be on in the other tab. For info such as 'data1' I have been simply using vlookup using the project name as a key.
Using VBA, I am importing data from Access (Access2007) into Excel. The code that I use works as required but I need to refine it so that I can filter for the records that I need to import. I can import ALL records. I can import selected records by manually changing my SQL query. I would like to reference an array of values or a range of cells so that I do not need to manually change my query every time I want to run it.
This code gets all records for "Jeff" and "Bob" from the Access database. I need to query about 100 names at a time so manually inputting them is going to take forever. How I can make it work?
By using an array: [Code]....... or by using a range of cells:
I have a loop that executes roughly 7.7 million times when my VBA program runs. Neednless to say it take a long time to run - usually a couple days.
The inner-most loop contains a line of code from way back in my early vba programming days when I knew even less than the small amount I know about programming now (and if you can follow that sentence you might be able to understand some of the spaghetti code I write :-) ).
for k = 1 to n
if Worksheets("Personell").Range("D" & Trim(Str(k))) > dtCompleted then ...
Would it be faster to use this syntax (which I just found out about):
if Worksheets("Personell").Cells(k,4) > dtCompleted then ...
It would cut out 2 functions calls, trim() and str(), so it would be faster, right?
I am almost embarassed to post the Range("D" & Trim(Str(k)) line of code because it looks so convoluted now, but that's how I learned to program, just fumbling through until it ran...
So in short, I just wanted to confirm that the cells() syntax run faster before I spend an hour editing and tested.
I often have problems with the cells range reference method. For some reason I get an error and can't tell why. Other times it works fine. Is this just an unstable method to use or is this completely wrong? I'm assuming I am using the wrong syntax. It is definetly the range reference causing the problem.
Dim cnt1 as integer, cnt2 as integer,cnt3 as integer
cnt1 = 2 cnt2 = 50 cnt3 = 2
dim myrange as range
set myrange = sheets("sheet1").range(cells(2,cnt1), cells(cnt2,cnt3))
I have no idea why it won't work. I'm basically searching a columner range of cells.
I have a data that is split into multiple cells and needs to concatenated. Unfortunately, the number of columns wherein lies the data varies throughout the workbook. As such, I wanted to select a range of cells, define this range as the reference point for the macro, run the macro, then move on (selecting a different number of columns on the next try).
For example, I have this:
A B C D E
No Not Very Far
I want to select cells A1:B1, run a macro concatenated the two columns, then select cells C1:E and run the same macro to get this:
I've been trying to get the sum of a range of cells based on their relation to the active cell. Here is the Pseudo If the SUM of (the cell 2 rows up THROUGH the cell 2 rows up and 2 columns to the left) = 0 then .......
I think that activecell.offset is the best way to do it, but I can't figure out how to work that in a range....
I want to create a macro which asks some information like functions do. For example, I would like the macro to ask for some data to be filled by the user. For that purpose I thought about using the inputBox vba function. I wanna fill the field in the inputBox with a reference to a range from an excel sheet. So, I click on a cell and then I try to hold ctrl + down cursor to get all the range starting from that cell on until the last consecutive filled cell on the sheet. Nonetheless, the problem I have is that when I do ctrl+down cursor, I just do not get the reference I want, because it seems that the ctrl+down cursor combination does not work in the inputbox for filling a reference to a range of cells. Does anyone know some workaround to achieve this?
I am have some code that takes values from a range (sourceAll) defined on my worksheet as:
Set sourceAll = Workbooks(pathnm).Worksheets(m).Range("A1", "S34")
I'd now like to be able for the range to be generated dynamicaly, but my problem is that the far right column (S) does not always have a value for every entry. Is there a way of using a column that does have a value for every entry, say column B, but then extending the range accross to S? If possible I'd like to avaoid having to move one of the columns with values for every entry. Auto Merged Post;I was looking at some of the suggested threads and found some code that I worked into my own
Set countRow = Workbooks(pathnm).Worksheets(m).Range("B1250").End(xlUp) Set sourceAll = Workbooks(pathnm).Worksheets(m).Range("A1", countRow) sourceAll = sourceAll.Resize(sourceAll.Rows.Count, sourceAll.Columns.Count + 17)
now this seems to work mostly, but it has deleted everything that was in column B.
I am using the STDEV function on a range of values: STDEV($I$26:I2545). My starting cell is constant and I would like to make my ending cell variable based on a value I enter in a master cell (A1). The value I enter in the master cell is multiplied by 252 the product would equal the row number I would like to stop at. So if I enter 1 the formula will be STDEV($I$26:I252). I tried: STDEV($I$26"I"&(A1*252))) and other such variations to no avail
I maintain Production data in an Excel Sheet and have the code which will create Chart for each Employee. Sample Sheet Attached. However, I want the Chart Range to be updated in a Single Chart and exported to a .Gif File.
Need assistance with this code provided by SHG. See attached sample. I need to create a range called DaysOff so that this function will work. As it is the function is perfect; though the accuracy if the result is dependent on the exact range, thus I need this named range to be dynamic. I have tried but it does not seem to work correctly. The named range should refer to the list of names between 1-10. The range should dynamically include any names that are listed.
Public DaysOff As Range Function datNext(datLast As Date, DaysOff As Range) As Date Dim datBeg As Date, datEnd As Date Dim iLastPd As Integer Dim iDay As Long, nDay As Long Dim iYr As Integer, iMo As Integer iYr = Year(datLast) iMo = Month(datLast) datBeg = DateSerial(iYr, iMo + 1, 1) datEnd = DateSerial(iYr, iMo + 2, 0) iLastPd = Pd(datLast) nDay = DaysOff.Rows.Count Do.......................................
I am trying to multiply a range of cells by a cell reference. The cells currently have hard coded values in them. I know with past special you can multiply a range of cells by a copied #. I want a similar function to that just instead of a copied cell its a cell reference. No VBA.
See the attached example. I have data listed as shown, but need the chart to automatically update whenever a new sales figure is added to the next month. I know you have to use the OFFSET to create dynamic ranges but I can't get it to work horizontally, only if my data is laid out vertically.
I have a challenge I'm trying to surmount. I have an excel sheet in which I want to be able to select Zone codes from a drop down, click on a link "search" which takes me to a place in the document that has been named according to the Zone code i pick in the drop-down. I want to achieve this using only formulas as i'm distributing the file to a wide audience who are not versed enough with computers to know how to enable macros.
I have 100s of name ranges in my spreadsheet. I am using a series of drop down boxes for the user to select the info that they want to see. Based on the dropdown box, the macro "dynamically generates" a range name from which to pull the info.
For instance, if they Select Manager = Terry, Analyst 1, and Month = Jan, then the range name would be JanTA1
The if statement would dynamically create this name by concatenating the info
If manager = Terry mnger = "TA" if analyst = 1 anlyst = 1 if month = Jan mnth = "Jan"
rangename = mnth & mnger & anlyst
So, I am dynamically creating the range name based on user selections...then to display it, I want to set the display cell = to the value
Setting the XValues property gives an "Unable to set the XValues property of a series class".
Using the chart wizard, trying to set the category labels parameter of the chart wizard to a range doesn't give an error message, but does leave the category labels section of the chart (when I click and view it) blank.
Each of my data sets spans roughly 75 columns by 250 rows at present, but this could expand. The first 7 rows contain metadata. Columns 2-25 or so contain the raw data, from which everything to the right is calculated. The data sets have most columns in common, but not necessarily all.
In order to tease out the most meaningful information from my data, I frequently sort all or part of it based on varying criteria. When I find a useful sorting criterion, I create a new column with a header that describes the criterion and populate it with a formula that returns a 1 if the condition of interest is met for that row, or a 0 if it is not. For example, if I am doing this in column AA, I might enter
and copy it down to the end of the data. The resulting vector of 1s and 0s quickly re-identify data that meets that criterion even after subsequent resorting. It also makes locating data that meets multiple sorting criteria extremely simple. Essentially, I create a truth table.
Cell $AA$4 in the above example contains a "comparator" value I might wish to change at some point, which would change the subset of data the condition selects for.
Here's the first hard part:
For each data set, I need the ability to generate meaningful plots that includes separate series based on the criteria I have described. However, I also need to retain the ability to resort the data or change the comparator value without disrupting these plots. In other words, the plots must NOT change when the order of the data is changed, but MUST change to display the appropriate data when the comparator changes.
Here's the 2nd hard part:
Once I have this working for one data set, I need to be able to port it to other data sets (which are contained in other workbooks), so that I can compare equivalent plots from each. I also need to minimize the number of manual steps involved in doing so, to avoid human errors and excessive time consumption.
The only other possible complication I can think of at the moment is that, to this point, I have been inserting blank rows to isolate subsets that I do not wish to perform further sorting on from each other.
Right now I am angling toward VBA code that loops through the entire data set to generate base dynamic ranges using the column header row (row 1) as the names, and the entire column of data for the rangeloops through the truth table columns to generate "branch" row ranges for each of the sorting conditions,loops through the entire data set one more time to create "branch" ranges for each of the base ranges.
I could generate some code to accomplish a one-off solution for a given configuration of a single data set (provided there is not a list length limit in a chart series that I'd be violating)...but without a dynamic named range, I don't know how to get to something that would update appropriately. So in essence, I am still stuck at the dynamic range part of this.