Defining A Range With Variables And Cells Function

Aug 25, 2009

I am trying to make the "A4:A" portion of this line of code generic so it can work in multiple different files. I have defined a variable FirstGridRow that will take care of the '4' but I need to find a way to splice it all together. Essentially I am stuck trying to tack on the ":A". The first piece shows where I am coming from and the second is where I am trying to go to.

View 3 Replies


Defining Average Range With Variables?

Apr 5, 2012

It's just meant to work through a long list of data blocks defined by column 8 changing. At that point I want to calculate the average of the first 20 entries in columns 12, 13, 14, 15, 16, 17 in that particular block.

I can achieve the sort and calculate the block start rows OK, just can't get the average definition right.

Option Base 1
Dim c(6)
Sub calc_averages()


View 2 Replies View Related

Defining Variable Range In Sumif Function With VBA Within Table

Jul 28, 2014

I sum number of articles that are coming in on one sheet and articles going out on another. The total of In - Out is a separate value. All three values are placed on a third sheet.

The following code I use does the job, but I have to use a set range in the SumIf function. I want to be able to use a variable for the lastrow of a table colum.

Private Sub ArttotKnop_Click()

Dim Klanttel As Integer
Dim Rij, vLastRow, iLastRow As Long
Dim varResult, varMin, varTotal As Variant

iLastRow = Sheets("Ingebracht").ListObjects("Ingebracht").ListRows.Count
[Code] ....

Since my table grows larger every day I want to use variables.

Where I wrote A2:A500, B2:B500, B2:B550 and D2:D550 I want to be able to replace A500 and B500 with A and B + the variable iLastRow. The same for B550 and D550 with the vLastRow variable

Tried using:
.Range("A2:A" & iLastRow)
.Range("A2", Cells(iLastRow, 1))
.Range(Cells(2, 1), Cells(iLastRow, 1))

They don't work.

View 2 Replies View Related

Code: Defining Variables

Sep 15, 2008

I'm trying to develop a work spreadsheet template that is to be summarised every fortnight. Our fortnights here go Thurs-Thurs. So I figured I would start with a base date (thurs - Sept 4th) and then have the spreadsheet display the true fortnight ending upon loading. Ultimately this fortnight ending will be used to copy and paste the template information into a summary spreadsheet but for now I am trying to get this intial part working. It's linked to a command button and my attempt at code is below:

Private Sub CommandButton1_Click()
Dim Base As Date
Dim Now As Date
Dim Delta As Integer
Dim No_of_Fortnights!
Dim Rounded As Integer
Dim Fract!
Dim Fort_week_end As Date

(Note the following 8 lines are not the problem, they just open the main sheet. The problem is the eights lines starting with the word 'set')

Sheets("Technical Inquiry Form").Activate
Sheets("Technical Inquiry Form").Range("b3").Select
ActiveCell.FormulaR1C1 = "=TODAY()"
Sheets("Technical Inquiry Form").Range("b3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Technical Inquiry Form").Activate

View 9 Replies View Related

Defining Rows As Variables

May 25, 2006

What the code does is that it runs in a Master workbook, and searches for XLS files in a defined folder. It opens a file, count, closes the workbook and takes the tallied number of sheets and places it in the master file, column A being File Names and column B as Number of sheets. What I need is for the code to take the file name from column A, run it and then place sheet counts in Column B and then move down the columns until all the counting is done. Currently I am only able to define one row using the range function and i'm at a loss.

'Get file names and enter into Master
Public Sub ShowFolderList()
Dim wb As Workbook
Dim ws As Worksheet
ChDrive "C"
ChDir "C:Documents and SettingsTest Folder"
outrow = 2
Filess = Dir("*.xls")
While Not Filess = ""
Cells(outrow, 1).Value = Filess
outrow = outrow + 1
Filess = Dir()

View 2 Replies View Related

VBA - Defining The Range Of Cells Containing Formulas

May 24, 2007

Using the SpecialCells method with a Type = "xlCellTypeFormulas", I can identify the range contains cells with formulas.

But when there isn't any of these cells in the range you've applied the SpecialCells method to, you get a VBA Error 1004. (Doing this outside VBA gives you a "No cells found" box.) How can I skip over this error?

I'm looping through each sheet in the workbook, and defining the UsedRange. I'm doing another loop that uses the HasFormula property on each cell and then performing an action if HasFormula = True, but this results in a lot of extra work processing cells that don't have formulas.

I'd like to further refine the process by defining the range of cells on each sheet that have formulas. But if a sheet contains no cells with formulas, I'd like to skip past it and move on.

I think this is just a simple syntax / code construction question.

View 9 Replies View Related

Defining Variables In Separate Module

Jun 18, 2008

I started plugging away at VB a little over a month a go and i feel i've learnt alot but can see just how limited my knowledge is. how to define all my variables in one module that then will feed into the code on other modules and forms in the workbook where required. For example, my spreadsheet has approx 30 different variables that are called upon by a variety of subs. How do i define all these in one place and then just call on the relevant ones when required rather then having to redefine them for every new sub.

View 2 Replies View Related

Defining Variables Using Variables

Apr 27, 2006

I'm trying to loop through a range in excel from access, checking where the titles (in Excel row 1) match with the fields (in a recordset in Access that is passed to the function) - and where they do, I want to dimension a variable to hold the column number - I'm not sure it's possible, but I'd be interested to know either way. The line I'm asking about is at the bottom of the code - the rest of the code is just to give context...

Sub ImportGeneric(rsImported As ADODB.Recordset, rsConfirmed As ADODB.Recordset)
Dim fd As FileDialog
Dim xl As New Excel.Application
Dim wb As Excel.Workbook
Dim ws As Worksheet
Dim iFilePicked As Integer
Dim strFilePath As String
fd.Filters.Add "Excel files", "*.xls"
fd.ButtonName = "Select"
iFilePicked = fd.Show
If iFilePicked = -1 Then
strFilePath = fd.SelectedItems(1)
Else ..................

View 3 Replies View Related

Range Variables In Customized Function

Aug 30, 2006

I wrote my function as it is shown below:

Function MyFunction1(r, n, xrange, yrange)
If r <= n Then
For i = 1 To 5
MyFunction1 = MyFunction1 + xrange(i)
For i = 1 To 5
MyFunction1 = MyFunction1 + xrange(i) * yrange(i)
End If
End Function

This is just a very basic idea of what I need to do, so it might not make sense why I am doing it, but I am just trying to test that the function works. Anyhow, the thing is that it works with something like this when I input it in a cell in a worksheet: =MyFunction1(RAND(),0.5,{5,6,7,8,9},{10,20,40,50,100})

However, if I try to define the range or set {5,6,7,8,9} with other cells, it does not work. so for example if I try the following:............

View 3 Replies View Related

Function Back Variables: Function Give Two Or More Output Variables

Jul 27, 2006

Can a Function give two or more output variables. e.g.

Sub a()
x = 5
result = Y(x)
End Sub

Function Y (x As Integer) As Integer
Dim B
B = ... * x
Y = ... * B

this will give back Y as a result. But if I want to get 2 or more output variables (let's say I need to get also B into sub) from one function, how should I do that?
I need this because function works with large matrix and I want to extract some values appeared in between.

View 2 Replies View Related

Range Using Cells Variables

Aug 3, 2012

I'm trying to use a range(Cells1),(Cells2) all working fine, but now I'm trying to use variables for the Row selection. selection.row as one of the variables, but keep on getting a error.

Sub Clear_Time()
Dim Rng As Range
Dim pointer As Integer
pointer = Worksheets("Summary").Range("Z1").Value
Worksheets("SubPanel").Range((Cells(10, pointer)), (Cells(52, pointer))).Find(What:="").Activate


View 3 Replies View Related

Countif Formula For Multiple Variables In The Same Range Of Cells

Jun 4, 2014

I have a drop down list in a column called Report Type (example below).

Report Type - Drop-Down Menu in Column F
Business/Operational/Work Plan
Budget Report
Performance Report
Program Quarterly Report
Program Mid-Year Report
Program Annual/Year-End/Final Report
Service Quarterly Report
Service Mid-Year Report
Service Annual/Year-End/Final Report
Financial Quarterly Report
Financial Mid-Year Report
Financial Annual/Year-End Report
Auditied Financial Statements
In-Year Reallocation
Annual Reconciliation Report
SRI Report
Other Report

I need to count all the cells that have: Budget Report, Financial Quarterly Report, Financial Mid-Year Report, Financial Annual/Year-End Report, Audited Financial Statements, In-year Reallocation, and Annual Reconciliation Report

Is this possible with a countif formula?

View 4 Replies View Related

IF Formula - Defining And Range

Jan 22, 2010

I would appreciate knowing how to fix this formula to do more than less than/more than answers. How do I have the following answers return for these birth year ranges? I have attached a sample file, with the formula in place. Thank you so much for any help!

1974-1991 = amateur 18-35
1900-1973 = amateur 36 and up
1992-1994 = junior 15-17
1995-2005 = junior 14 and under

blanks or zeros = unknown

View 8 Replies View Related

Defining Cell Or Range

Jun 11, 2009

I'm having an issue trying to locate a certain value in a cell.
If it is a number, say 50, then my code works, if its a word though
then it doesnt work. How can I get this to search with a list of words rather then numbers?

Sub alpha()
Dim cell As Range
For Each cell In Range("A2", ["N27"])
If cell.Value = Beff Then cell.Interior.ColorIndex = 6 'doesnt work
If cell.Value = 50 Then cell.Interior.ColorIndex = 6 'does work
Next cell
Range("A2", ["N27"]).Font.Bold = True
End Sub

View 9 Replies View Related

User Defining A Cell Range

Mar 31, 2014

I'm having some issues getting a tool I'm working on to function correctly. I have some cells, the number of which can vary based upon user requirement, that contain cell addresses. What I need from VBA is to translate these addresses into usable row, column values for the user defined range. Example:

Cell D4 contains F14, which was input by the user.

What is the best way to pass this data through VBA. Right now I am currently using:


Dim strRange as String 'left out loop parameters
dim TempRange as Range
strRange(i) = Cells(4, 4) 'using TypeName on StrRange at this point yields a string result as expected
set TempRange = Range(strRange(i))

From my understanding that should work but I am getting type mismatch.

I do not have any reason for actually using a string, but would like the user input to only be 1 cell. If there are cleaner ways around this, I am open to them.

Then from the range, I would have two other variables as type long for storing the row and column data using:


Pathing_Rows = TempRange.Row
Pathing_Cols = TempRange.Column

View 6 Replies View Related

Defining Named Range, Error Criteria

Apr 13, 2007

I am trying to define a dynamic range based upon error criteria. After the first error, all the subsequent cells are filled with the error and I would like to limit the named range to the rows with no errors (one column wide). I am thinking something similar to (realizing this is probably very wrong):

=OFFSET( 'Basin Routing'!$X$5,0,0, COUNTIF('Basin Routing'!$X:$X,AND(NOT(ISBLANK()),NOT(ISERROR()))),1)

View 4 Replies View Related

Merge Cells: Select A Range Based On Two Variables Which Store The Column Numbers

Jul 19, 2009

I am trying to select a range based on two variables which store the column numbers. what I have is:

View 4 Replies View Related

Defining Cell Ranges According To Cells With Certain Value?

May 20, 2013

So I have a spreadsheet with a list of companies, list of users within those companies, and the status of those users(Active/Inactive/Deleted). I'm trying to determine the company-level status based on how the overall status of all the users in the company.


Company A User 1 Active
Company A User 2 Active
Company A User 3 Inactive
Company A User 4 Deleted
Company B User 1 Inactive
Company B User 2 Active
Company B User 3 Inactive
In the above example, cells D1 to D4 would list "Active", since Active users form the bulk of the company. D4 to D6 would list "Inactive" for Company B. I'm trying to use a formula to automate this for the whole spreadsheet (5,000+ rows)

I've figured out the first half of what I need to do:


My problem is in getting Excel to automatically define the cell ranges according to the cells that contain "Company A", "Company B", etc. I have over 5,000 rows on the spreadsheet so having to manually change the cell ranges for each company would take forever.

View 2 Replies View Related

Function To A Range Of Cells Over A Range Of Worksheets

Mar 31, 2009

I have a workbook with several worksheets in the same format. I would like to have a function to output the sheets that have rows in which collumn A = X AND collumn B = Y.

I've been working on this one for a couple days now and I'm not making much progress.

View 9 Replies View Related

IF Function With Three Variables

Feb 14, 2013

I'm having trouble with my logic again :/

Tp std


So I want the Tp in the 3rd column to show the result:

IF AREA is less than or equal to 20, Tp=Tp std / 2

IF AREA is between 20 and 40, Tp=(((AREA-20)/(40-20))*(Tp std/2))+(Tp std/2)

IF AREA is 40 or greater Tp = Tp std

But as one equation

I have just been struggling with the range part.

View 2 Replies View Related

SUM Function To Be Used For A Range Of Cells

Mar 30, 2009

I ahve some range of cells in one Sheet and I have one cel in anonther sheet which will hold the sum values in the range.

View 9 Replies View Related


Feb 14, 2007

I have and Indirect function that works.... I need to modify it to include a cell address reference, but this requires the use of a Vlookup function to find the address ....

I have this formula: but it does not work

I'm not sure how to include the VLOOKUP function in my argument to

View 9 Replies View Related

ISERROR Function With Range Of Cells

Jun 25, 2008

How do I use the ISERROR function on a range. I have tried =ISERROR(A1:A10) but this doesn't work, it always gives the "true" output even if there is no error in that range.

View 4 Replies View Related

VLookup Function That Searches For Two Variables?

Dec 9, 2013

I need to figure out a vlookup function that searches for two variables and return an answer. such as the example attached;

vlookup -- 12253 & 605.34 (if these are matching then) 131001

View 5 Replies View Related

Nested If Formula/Function With Other Variables

May 14, 2008

I am creating a worksheet and I have a formlua where the value is text. I can make it work standing alone but not with other variables. =IF(E11=S1,0)+(IF(E12=S1,0)+(IF(E13=S1,0.0025))+IF(E14=S1,"Call for Prciing"))

View 2 Replies View Related

Insert Relative Function That Uses Variables Into Cell

Mar 4, 2008

I am using VB to insert a function into a cell on an excell worksheet. The relative references work fine. For the absolute reference, I want to use a named range (LowTotal). If I simply put the name into the function, then I just get the name as text in the function. The second insert formula is were the named range is intended to go. Here is the code so far...

I need to know how to use the varriable name in the .formulaR1C1, so that VB knows it is a variable and not just text....

View 4 Replies View Related

Using IF Function To Show Highest Value Achieved Across Range Of Cells

Feb 17, 2014

Basically, I have a worksheet that shows test results for 6 separate tests. If an objective is met in a test then it is marked with an "X". If an objective is partially met then it is marked with a "/" and if it is not met then it is marked with a "." . I have now created a combined column where I want to collate whether an objective has been met at all across the 6 tests.

For e.g. if in the range H14:M14 there is an "X" then N14 should show "X". If there is no "X" but there is a "/" then N14 should show "/" and it there are neither values in the range then it will show "." .

I tried

[Code] ....

but it just came back with a formula error.

View 3 Replies View Related

Code That Inserts A SUMIF Function To A Given Range Of Cells

Dec 10, 2008

I'm trying to write some code that inserts a SUMIF function to a given range of cells:

In simplified form, the code runs as follows: ...

View 9 Replies View Related

SUMIF Function: Sum A Range Of Cells Greater Than Whatever Is In Cell D8

Jul 18, 2006

I am trying to use the SUMIF function in excel where I want to sum a range of cells greater than whatever is in cell D8. Here is what I tried to use, but it doesn't work. SUMIF(A2:A10,>D8,B2:B10)

The content in D8 can change because the user makes a choice from the drop-down list and a number pops up in D8 that is referenced to their choice.

View 2 Replies View Related

VBA Match Function With Variables / Number And Alternating Ranges

Jun 2, 2014

I am having a hard time getting my match functions to work with letters and numbers in the same range. I am also trying to figure out a way to have the ranges switch if there is an error. because the data is in multiple columns. It is hard to explain so I have attached a sample workbook.


View 13 Replies View Related

Copyrights 2005-15, All rights reserved