It is useful to use lookup function to find the student name. However, if the table array size is variable
(say, a new student enrolled and the table array will be $A$2:$B$7), the table array size will be changed
and we need to change the vlookup function. How to avoid it? Can we use some kind of variable for the
See attached file( book1)
I am attempting to create a rather complex macro to format periodic raw data being made available to me; this data centers around a main column. The column of interest in these raw data reports is of variable length; I wish to apply a name to this column in order to make it easier to perform calculations. I know how to write VBA code to select every cell in a column down to the point at which the column becomes empty space, regardless of how much data is in the column, and I wish a include naming of the range in the macro. However, all my resources imply you must give the exact definitions of the range when writing the name application in VBA. Does anyone know if there is a way to include naming of a range in a macro without knowing the range's exact size? Put another way, is there any way to apply a name to an already-selected group of cells without giving the exact dimensions?
View 2 Replies View RelatedI need to create an array with a variable as it size For instance:
View 2 Replies View RelatedI am trying to create a matrix in VBA with variable matrix size. The matrix I need will be anywhere from 3 to 9 columns and 15 to 30 rows. I want to set the size as variables and then create the Matrix using these variables, similar to:
rows = 4
columns = 25
Dim XMatrix(rows, columns) As Variant
I am trying to figure out how to make a userform to display the contents of a 2-D array which has a variable number of rows. I want the userform to height of the userform to correspond with the number of rows of data to display.
I don't have much experience with userforms, but here's what I was thinking:
VBA code which would find the # of rows of data and then adjust the height of the userform and the length of the lable (which the data would go in).
I am trying place the "sum" formula into multiple cells, changing with "i" (itteration process).
Something like:
.Range(17,counter).formula = "=Sum(B2:B20)"
when the couter itterates, the same line of code should give:
.Range(17,counter).formula = "=Sum(F2:F20)"
My actual code looks like: ... which doesnt work.
.Offset(17, " & ColumnHeadCout & " + 1).FormulaR18C" & ColumnHeadCout + 1 & " = ""=SUM(R[20]C[" & ColumnHeadCout + 1 & "]:R[" & NumberStreamElem + 20 & "]C[" & ColumnHeadCout + 1 & "])"
I have data from 100 trials. Each trial has a variable number of data points (rows), but each row is labeled in the first column with the trial number. I would like a macro to identify the first row of each trial, calculate the difference of the value of the cell in column G of that row from a constant (718), and then add that difference to that cell and all other cells in that column of that trial.
So based on the attached sample, I would like all values in column G for trial 2 to be +1, for trial 3 to be -5, and for trial 4 to be +2.
In the file, I've cleared all the data from the impertinent columns. Eventually, I will also want to perform a similar process on column H but with a different constant.
I am trying to use a combination of dependent named ranges and variable length ranges, so one can select in eg Col A truck name from a drop down list, and then in Col B, the engine variant only for that make of truck. The indirect(substitute) function works well if I define the dependent range name (the engine variant) using actual cell refs. However if I use the variable length name definition as described in Ozgrid (ie with offset function), I get an error message and the dropdown menu freezes. Is there anyway to overcome this without resorting to VBA?
View 4 Replies View RelatedI have a spreadsheet with a couple of hundred rows on each tab that looks like this: A1: #1 PENROSE TIJUANA MAMA PCH 2620039190 665794 12.00 CT 1 Front 1 1 1. The only constant is the 10 digit number followed by the 6 digit number in the middle and I want to use it to parse the text string. I want the output to be everything prior to the 10 digit number in A1, the 10 digit number in B1, the 6 digit number in c1 and everything else in D1 and so on through the rows.
View 4 Replies View RelatedI have below code (option 1) I was hoping would do what I need except instead of copying formula from A1 it copies values... (option 2) copies formula but does not preserve references...any ideas how to copy exact formula so that references are not changed?
Assumptions:
A1 formula = SUM(B2:E2)
in A5 I would like to copy exact formula to keep references to row 2...
I agree I could use $ in original formula but that would complicate other requirements.
OPTION1:________________
Sub test_var_object()
Dim vRange1 As Range
Set vRange1 = Range("A1")
Range("A5") = vRange1
End Sub
OPTION2:________________
Sub test_var_object()
Dim vRange1 As Range
Set vRange1 = Range("A1")
vRange1.Copy
Range("A5").PasteSpecial
End Sub
I've developed a code to load csv files in excel and do some formatting on it. In addition I want to add a few cells which calculate making use of the data in the file. The data tabel (resulting after the csv import) always has the same number of columns, but the number of rows varies depending on the imported file.
My problem now is that I need to change the formula for my calculation depending on this number of rows.
This is what I got so far (not including the csv import):
Option Explicit
Dim varRow As Double
Function fctCountNrRows() As Double
Range("A1").End(xlDown).Select
varRow = Selection.Row
End Function
Sub subAddTotals()
fctCountNrRows
Cells(varRow + 2, 1).Select ..............
I have an excel based report where I drop data into a blank spreadsheet each month and my formula (in a second worksheet) finds the relevant information. I do this by using the Match function on the first row of my source data. This finds the column numbers to the data I am interested in. (This is handy as additional columns of data appear almost every month). Most of my formula (in the second worksheet) works on vlookups using the column number from the match function.
However, I need to do a sumif. Sumif requires cell references rather than a column number. How can I get around this? Due to the nature of the report, VBA isn't appropriate in this instance. I'm only producing the initial report; someone else will be updating it each month.
How do I pass MATCH or COUNTIF the current dynamic row instead of hardcoding $3? The column is fine hardcoded. Column F has data validation allow List with source equal to dynamic range "userlocation". Column H has data validation allow List with source =OFFSET(userlocation,MATCH($F$3,userlocation,0)-1,1,COUNTIF(userlocation,$F$3),1)
In Column H, this will in effect provide me with a list of values based upon the value of cell F3. Problem is I can't seem to figure out how to reference ROW as a dynamic reference in my MATCH and COUNTIF function.
EX
Column F Column H
Screen1 (if Screen1 = value in my dynamic range pass me back value in a list)
Screen2 (if Screen2 etc.
Screen3
The problem lies in the following line
Range(Cells(1, a), Cells(b, a)).Formula = _
"=(rc[-1]-" & min & ")/(" & max & "-" & min & ") "
When it is executed i get the 1004 error ("application-defined or object-defined error)...
min and max are defined (as worksheetfunction.min and max of a selection), and their values are correct. The problem seems to lie in the max variable, namely if i simplify the formula to just
Range(Cells(1, a), Cells(b, a)).Formula = "=(rc[-1]-" & min & ")"
it works ok, but if it is
Range(Cells(1, a), Cells(b, a)).Formula = "=(rc[-1]-" & max & ")"
I get the error.
I am completely baffled considering both variables are defined in the same way i.e.
min = Application.WorksheetFunction.min(Selection)
max = Application.WorksheetFunction.max(Selection)
Here is what I would like to do: Have a formula like Max(B12:B14) change to Max(B12:B18) when I change the value in different cell from 3 to 7. I would like to stay away from the macro world and keep in formula world if that is possible.
View 6 Replies View RelatedCode:
=SUMPRODUCT(--(_NamedRng1=NamedRng2),$B$49:$F$49)
I am using the above formula in my code with two Named Ranges
Code:
Set Rng3 = Range("_NamedRng1").Offset(1, 0)
=SUMPRODUCT(--(_NamedRng1=NamedRng2),rng3)
[/CODE]
I want to set the range $B$49:$F$49 in my code and I have tried the above, but it does not work.
I want to allow for the fact my end user may insert rows so do not want to use $B$49:$F$49
code to take jobs that are done on one spreadsheet and send them to the next available row of a specified range on another worksheet. I am also looking to incorporate a way to delete the empty rows that are left behind.
I have already made code to select the completed jobs, but am still unsure how to approach the next part. I can't even paste the selected range to another sheet without errors from range sizes.
The following code is done up to the point where I cut my selection.
Sub Update()
Worksheets("OpenGen").Activate
Dim c As Object
Dim rngA As Range
[Code]....
I have the following code that let's the user choose and " import" data to an existing sheet. It works well up until now. The problem is that the three ranges that I am trying to copy the data from on workbook to another has changed size. In previous version of my workbooks the range was two columns by 10 rows. Now, it is 1 column by 10 rows. So, when I run this macro it doesn't work because the two ranges are different. Is there any way to:
1) Only copy over one of the rows of a range thus making the macro run?
2) Do not run that part of the macro if there is an error?
Thanks so much for reading this long-winded description but the error is a big problem
Private Sub CommandButton1_Click()
Import_Data_Form.Hide
Run "UnProtectAll"
Set b = Selection
ad = b.Address
' Local Variables
Dim wkbDataFile As Workbook
The idea is to center an image in the middle of a cell where the cell's size is variable. This shall be done for a column of images if a certain cell in the same row contains content different from 0. If not the image shall be invisible.
Sub Center()Dim Position As Integer
Dim Picture As Integer
Picture = 6
For Position = 7 To 320If Sheets("List of Measures").Cells(Position, 2).Value
[Code] ......
Run-time error 1004: Application-defined or object-defined error?
Say you define a public range variable called Inputworksheet and you set it to refer to the worksheet called Inputworksheet. You have a separate string variable with the value Inputworksheet. How do you get this string variable value to call/control the range variable Inputworksheet?
I am getting an excel worksheet value from a lookup function that corresponds to the name of a VBA range variable. Once I have this worksheet value, I would like to use the range variable that has the same name as the worksheet value.
I am try to Set a range to a variable. The problem is the range differs in size. The good thing is there is a blank cell before each group that I am attempting to set the range to. I know it should be possible to set the range to stop at the blank row. I am locating the correct row by the date and want to set the range to include the next three cells to the right and all the cells below them up to the blank row.
View 5 Replies View RelatedI am trying to autofill dynamic ranges that have column variables (d) and row variables (x)... I am having a hard time with the syntax on this
View 9 Replies View RelatedNeed 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'd like to work out the average of a set of data which changes length and position. It's probably easier if I attach a workbook.
The different sets of data are split by blank cells so they can be distinguished.
I have created a speadsheet for a tournament that lists the team names down column A (leaving a heading row). I have a formula in the top row which copies each row heading to a column heading 2 columns wide (to be able to record a result)
Formula: =INDIRECT(ADDRESS(ROUNDUP((COLUMN()+1)/2,0),1))
This way it will be possible to keep the results of the tournament on a grid. I would like to put in conditional formatting that meets 3 criteria...
1, it must fill every second row but only fill cells that have column and row headings (ie. rows that will have content in them which will be determined by the number of entrants). eg. if i have 10 teams then every odd row from row 1 to 11 will be filled. If 40 teams register then every odd row from 1 to 41 will be filled. 2, the fill needs to only fill cells that have column and row headings eg. if 10 teams register then every odd row from column 1 to 21 will be filled. Then 3, If the row heading and column heading are equal then it need to gray out or fill a different colour (or lock cell contents to empty if possible) but again, only fill cells that have column and row headings.
I have 2 rules.
Rule 1: =(ADDRESS(1,EVEN(COLUMN()-1)))=INDIRECT(ADDRESS(ROW(),1)) PROBLEM:
It needs to only fill cells that have a column and row heading which it doesn't, it fills the correct cells untill there is no column heading then fills every other cell in columns beyond the last column with a heading.
Rule 2: so far this works:
=AND(MOD(ROW(),2)>0)
but when i try to restrict it to cells with row and column headings it doesn't work. This is what I had:
=AND(MOD(ROW(),2)>0,IsEmpty(INDIRECT(ADDRESS(ROW(1),COLUMN()))"")=FALSE) OR =AND(MOD(ROW(),2)>0,CELL("contents""",ADDRESS(ROW(1),COLUMN())))
I want to load one with data from a worksheet query table that will vary in size each time it's executed. The length will vary, but the range is continuous, and five columns wide. I'd prefer a do while loop, looping until a blank cell is encountered. Can someone get me started with general syntax and setup of the array?
View 9 Replies View RelatedNeed to get tile size from product description.
Find attached , within file have specified expected output.
codes cleaner.xls
I am computing the average of anywhere from 5-10 numbers. When the numbers are not inserted there is nothing left in the cell. The problem is I get a value that is close to the average but not the real average. (Also the cells I am using are N37:Q41 for the first 5 samples which is constant the next 5 are not always constant W37:Z41) Here is the formula I am using:
=IF((W37="")+(W38="")+(W39="")+(W40="")+(W41=""),AVERAGE(N37:Q41),IF((W38="")+(W39="")+(W40="")+(W41=""),AVERAGE(N37:Q41,W37),IF((W39="")+(W40="")+(W41=""),AVERAGE(N37:Q41,W37:Z38),IF((W40="")+(W41=""),AVERAGE(N37:Q41,W37:Z39),IF(W41="",AVERAGE(N37:Q41,W37:Z40),AVERAGE(N37:Q41,W37:Z41))))))
I have the same problem with a standard deviation formula and the same group of numbers (I get something close but a few points off).
=IF((W37="")+(W38="")+(W39="")+(W40="")+(W41=""),STDEV(N37:Q41),IF((W38="")+(W39="")+(W40="")+(W41=""),STDEV(N37:Q41,W37),IF((W39="")+(W40="")+(W41=""),STDEV(N37:Q41,W37:Z38),IF((W40="")+(W41=""),STDEV(N37:Q41,W37:Z39),IF(W41="",STDEV(N37:Q41,W37:Z40),STDEV(N37:Q41,W37:Z41))))))
how to reduce formula size/improve excel efficiency?
I have a really complicated formula with lots of COUNTAs, VLOOKUPs, IFs, etc etc etc which returns a variety of values, ZEROs (for blank cells being referenced), and #NAs.
I would like to turn all ZEROs and #NAs into ""s without repeating this super long formula over and over.
I am curently using:
=IF(or(ISBLANK([superlongformula]),ISNA([superlongformula])),"",[superlongformula])
I was looking for a worksheet with vba to do FFT beyond the Excel internal 4096 limit. I wanted to try my hand at writing code to do it faster. The attached does just that.
Everything worked fine until I went beyond 32k samples. The next higher 2^n value for data size to feed the FFT is 65556. When I tried this quantity of samples I got Runtime Error (13) type mismatch when attempting to write the array back to the worksheet using the "transpose" method.If I remember correctly, 32k worked.
Is there some limit to the size of the array that can be transposed and placed into a range? If so, is there another way to do this?
I do not need to re-write the data, but I use this same method elsewhere to write the FFT output. It it fails writing the input data back, it will probably fail when writing the FFT results.
Attached File : FFT.xlsm