Creating Variable Range?

May 18, 2013

How would I create a variable range with:

Sheets("output").Cells(I + r, 1):Sheets("output").Cells(I + r, 9)

View 3 Replies


ADVERTISEMENT

Creating Range Object Using Variable Indices

Mar 28, 2008

how do we do something like Range("A1:B2") using an index for the cells. For example Range(R[0]C[0]:R[i]C[j]) which doesn't work. I know about Cells(i,j), but that only returns a range object of a single cell.

View 9 Replies View Related

Creating A Variable Word Art

Jan 23, 2009

I am wondering if anyone as created a what to do word art, using a input variable.I.e in may example I have a person, Suzie Smith who is an Chief Manager, when I box requires her signature, I would like for it eo be imprinted going diagonal across the box. Is this possible?

View 7 Replies View Related

Creating A Formula With A Variable

Jul 14, 2008

Column A will be completely empty.

When an * is inserted into a single cell in column A, that Row number will become the variable in this equation:

=Sheet1!$G$'variable'

i.e. if i put a * in A14, then the formula should read (=Sheet1!$G$14).

View 10 Replies View Related

Creating A Variable Macro

May 24, 2007

I currently have the below macro:
If Cells(1, 3) = 2 Then
For a = 1 To 336
b = Round(a * 2 + 0.99)
Cells(b + 0, 3) = a
Cells(b + 1, 3) = a

Next a
MsgBox " Cell C1 is not equal to 2"
End If

where the intent is that whatever cell A1 is equal to, that is how many times over it will duplicate a number in consecutive cells before moving to the next number, ie:
1
1
2
2
3
3
etc to 336.

How can I tweak the code so that if I change the data in cell A1 to 9, I don't have to go in and change the macro?

View 9 Replies View Related

Creating External Reference With Variable?

Feb 27, 2014

I'm wondering if there's any way to put a variable for a cell address in an external reference formula. Basically, I'm creating an abridged report of customer data that takes a single customer's info from 2 other workbooks on a networked drive and compiles the chosen cell data into one worksheet summary of their info. The base external reference formula looks like this:

='F:Projects[CustomerDB.xls]Sheet1'!VariableCellGoesHere

I want to know if there's a way I can make the formula use a variable cell address that I can just type into another cell to tell it which row I need to grab info from. Generally, all of the customer data I need is spanned across multiple columns in one single row. The formula above appears multiple times in the sheet I'm trying to make. The first is for "Name", the next is for "Address", next is "Phone Number", etc. So for instance, if I want customer data from the main spreadsheet and that customer's info is on row 355, my first few formulas are:

='F:Projects[ProjectDB.xls]Sheet1'!$A$355
='F:Projects[ProjectDB.xls]Sheet1'!$B$355
='F:Projects[ProjectDB.xls]Sheet1'!$C$355

Is there a way I can maybe have a designated cell where I just enter the row number I need (e.g. 481) so the formula sees that number and plugs it into the cell reference above resulting in the formulas adjusting themselves to ='F:Projects[ProjectDB.xls]Sheet1'!$A$481?

View 9 Replies View Related

Creating Matrix With Variable Size

Nov 19, 2012

I 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

View 1 Replies View Related

Creating String Using Variable File Name?

Dec 18, 2012

I'm trying to check to see if a file exists using VBA but the routines I see use a string that has the path and then the file name. I have a fixed path C:VBtesting and the a variable file name. I'm using a file name that consists of a username & date such as cwilliams201212. I'm having problems getting the variable file name into the string.

View 1 Replies View Related

Variable File Name For Creating External Links

Apr 10, 2013

I am working on a research project of which details cannot be disclosed. I basically have many worksheets which each contain 3D positional data and I am plotting overlaid graphs using multiple worksheets. I have gotten them to work where I manually enter the names of the worksheets, but I am trying to use the CELL("filename") command to get the current worksheet's name, then using a substitute command to change certain fields, then I would like to use the string name created by those functions to call a global variable in another worksheet which defines a range. I will try to give an example.

Say I have 3 worksheets for arbitrary measurements:
1) filename 1mm
2) filename 2mm
3) filename 3mm

I would like to graph all 3 sets of data in worksheet "filename 1mm", using the fact that everything in the filenames are the same except for the 1/2/3mm part. I have a template of which I will be copy/pasting data from numerous data sets and then each worksheet has defined names "X_vals" and "Y_vals" which give me dynamic ranges for the data I wish to plot from that worksheet.

When I type the names manually--i.e. " 'filename 2mm'!X_vals" everything works fine, but when I try to create the exact same string dynamically using indirect/substitute/left/right etc, it does not let me do it. I am able to create a dynamic string with a range, such as " 'filename 2mm'!C10:C100" but then when I change it to " 'filename 2mm!X_vals" it just gives me #REF!.

I guess my question is just is there any way to use the indirect function to create a string name dynamically which references a defined name in another worksheet? If not possible with just simple excel functions, is there a way to do this with a macro? I am not exactly familiar with VBA in excel, though I know how to run macros.

View 3 Replies View Related

Creating A User Form With Variable Number Of Controls Buttons

Sep 4, 2008

Is it at all possible to create a User Form where the number of CheckBoxes will be linked to the number of entries in a cell range?

For example I have a 3 records in the range A:A called "Blue", "Green", "Yellow". I want to have a user form with 3 CheckBoxes with the same caption names.

View 9 Replies View Related

Creating Autofilter That Returns Records Based On Date Variable

Mar 13, 2013

I have a strange problem I cannot solve in that I'm trying to create an auto filter that returns records based on a date variable.

If I set the code to equal the date variable, it returns information. If I set the code to return records AFTER the date, it returns nothing.

Code to return records on the date:

Dim startdate As Date
startdate = Format((Now), "dd/mm/yyyy")
ActiveSheet.Range("A:B").AutoFilter Field:=2, Criteria1:="=" & startdate

This returns data, however if I slightly amend it to get records on or after the date:

Code:
Dim startdate As Date
startdate = Format((Now), "dd/mm/yyyy")
ActiveSheet.Range("A:B").AutoFilter Field:=2, Criteria1:=">=" & startdate

It returns nothing. Is there a syntax problem in my code.

View 1 Replies View Related

String Variable To Call Defined Range Variable

Nov 10, 2006

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.

View 5 Replies View Related

Autofill With Range That Is Column Variable And Row Variable

Apr 4, 2008

I 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 Related

Creating Named Range Taking Avg To Date Of Dynamic Range

Jul 15, 2014

I have a column of data that keeps getting new information in it. what i need is a named range that i can use for a chart, first point in the named range will be the first value in the column, second point will be avg. of point 1 and 2, then 3rd point will be avg. of 1,2,3 etc.

View 1 Replies View Related

Creating ActiveCell Range And Checking That Range For Specific Character?

Mar 14, 2013

I have a custom email creation template I am merging with another version. The problem I am having is wrapping my head around not only selecting a range that is offset from ActiveCell (column 6-9) but seeing if there is an "x" in that range which is normally blank. My previous attempts identify the "x" but adds the text every time it is found. (Each column is a flag for an email bullet and they can have all four bullets in the email where I only want the text included ONCE if they have ANY bullets included). I use the range because I do not want the text included if none of the bullets are used.

Teh StandHTML then gets used in the body of the email like other HTML items I use

The email is generated using the ActiveCell.Offset to insert special text, emails and routing and has weathered alot of changes over time.

Dim Myrange As Range
Myrange = Range(ActiveCell.Offset(0, 6), ActiveCell.Offset(0, 9)).Select
If Myrange = "x" Then
StandHTML = StandHTML & "Important Text"
End If

View 5 Replies View Related

Creating A Range After Creating A Different One

Dec 22, 2008

I am trying to build a selection for a procedure to use.

View 2 Replies View Related

Set Range Variable Using Variable Row Number

Mar 11, 2008

I am using a variable named " Totals" as a range type to refference the range in a formula. It works the way I have it.

Dim Totals As Range
Set Totals = [U37: AE37]

Now instead of the absolute refference, I would like to change the row refference by an offset of my current row, using a formula with a varriable. The columns stay the same.

View 3 Replies View Related

Autofill In Macro Range Is Constant How Can I Code To Be A Variable Range?

Feb 19, 2010

I am trying to write a macro which will autofill specific columns. The macro will set the range from the start of my autofill to the end of my autofill as a constant range.

The problem I need to get around is the end of my range can always change each time I run the macro. For instance, the first time I run the macro I may only need to autofill from row 4 to row 15. The next time, I may only need to autofill from row 4 to 23 (because of user updates). How can I make the end of my range not be a constant address but variable?

View 6 Replies View Related

Select Range In Worksheet Where Last Cell In Range Is Variable?

Jan 27, 2012

I am trying to write code to select a range in a worksheet where the last cell in the range is variable.

Sub DataTest()
Dim LastColumn As Integer
Dim LastRow As Long
Dim LastCell As range

[Code].....

View 8 Replies View Related

Creating A Range In VBA

Jun 30, 2009

I'm trying to create a range between two text values; for example:

Equity
Stock1
Stock2
Stock3
Equity Total

Fixed
Stock1
Stock2
Stock3
Fixed Total

How can I name a range from "Equity" to "Equity Total" and from "Fixed" to "Fixed Total" in VBA?

View 9 Replies View Related

Assign Named Range To A Vba Range Variable?

Jan 25, 2013

I have a named range, called SubjectNamesPastoral on a worksheet called Worksheets("Group to Teacher")

I can't assign the named range to the rngSubjectFamilyRangeOnSubjectUsedSheet variable in vba.

the first two lines of code work fine, the msgbox shows "E100:E105", happy days!

However when I try to assign the same range to the rngSubjectFamilyRangeOnSubjectUsedSheet variable, the debugger runs past the 'Set' line without error, but throws 'error 91' at the second msgbox.

VB:
thisString = "SubjectNames" & strSubjectFamilyOfGroup

MsgBox Range(thisString).Address

Set rngSubjectFamilyRangeOnSubjectUsedSheet = Worksheets("Group to Teacher").Range(thisString)

View 1 Replies View Related

Copy A Range And Paste It Over A Variable Range

Jul 16, 2009

I am using the code below to copy a range and paste it over a variable range.

View 4 Replies View Related

Set Range Variable To Growing Dynamic Range

Mar 6, 2008

I have been working on part of the code for my spreadsheet and it works fine in the spreadsheet “Databaseform” however when I copied the code to my master spreadsheet “Paul_PartLocDBCombo” it does not work, I get the error:

Method ‘ range’ of object ‘_worksheet’ failed
The code is then highlighted in yellow, the code is:
Set rng = wksPartsData.Range("a1", Range("a65536").End(xlUp))

Meaning this part is incorrect but I don’t know why? To work it: go to Databaseform and press start. Enter 7mm in the product field and press find all. It will then return all the matching results in the userform. Its this I want to try and achieve on the other spreadsheet when the button find label is pressed.

View 3 Replies View Related

Creating Named Range?

Aug 19, 2014

I'm trying to create 3 different named ranges which are of alternate cells in columns. Each column runs from row 3 to row 106 and each named range covers 5 columns. Therefore each named range needs to contain 260 cells (52 cells per column x 5 columns).

Apart from the fact that you can't deselect a sell by Ctrl+Right Clicking on it, making a selection of the relevant cells is proving impossible.

For example I carefully selected all 260 cells for my 1st named range and entered a name. However when I then go into Name Manager I see that loads of cells haven't been selected at all and instead some cells in some columns have been but with huge gaps between them. If I try to then add cells my Ctrl+Clicking on them I find that I can add some but then one of two things will happen. I'll either click on one cell, the same cell every time, that deselects everything and I have to start all over again or when I try to save the new range I get a message saying the formula is wrong.

I've also tried typing in the names of the cells manually but the same thing happens. Annoyingly I've manually created a formula with all the cells I need in Notepad but even though the option to Paste is there, it won't let me paste into the field!

Is there a limit to the number of cells you can have in a named range?

Is there way of editing the name range accurately?

Is there a better, easier way of selecting all the cells for a named range?

[Code]....

View 4 Replies View Related

What Are The Limits When Creating A Range With VBA

Dec 27, 2009

I have a text string concatenated from an array of about 3,500 elements. The concatenation looks something like the following:

sRange = "$A$3,$A$7,$A$8,$A$13, ..."

When I try to set a range with the said string: MyRange = Range(sRange), the system throws an error.

I've seen a few methods in help ("Union" for one) that accept up to 30 arguments. I'm unable to find anything in help that states a limit for MyRange = Range(). It appears that 30 may be the magic number for everything.

I have tried using "Union" and building the range one element at a time from the array. That works, but it's incredibly slow.

Is there any way I can pass my array as the argument directly? Something like: MyRange = Range (MyArray).

View 9 Replies View Related

Creating Range Of Bar Codes

Oct 29, 2006

I am generating Barcodes in Excel with the Barcode Symbol Code 39.

When i enter the desired number in the cell, the Barcode for the entered number gets automatically generated on the previous cell.

eg: Barcode (A3)
Number (A4)
Empty cell
Barcode (A6)
Number (A7)

Now i want to generate a sequence of barcodes starting from so and so number and ends in so and so number.

Now the way i do is manually enter each number one by one which is a tedious work.

View 9 Replies View Related

Creating Border For A Range Using R1C1

Mar 6, 2013

I'm trying to create a border for a range using R1C1 and get an error.

VB: Sheet11.Range(Cells(y, 1), Cells(y, 5)).BorderAround Weight:=xlThin

If I do not use R1C1 it works...

VB: Sheet11.Range("A18:E18").BorderAround Weight:=xlThin

View 2 Replies View Related

Creating Bar Chart With Acceptable Range

Dec 29, 2009

For work, I'm doing a project for Quality Control. I'm looking to create a Bar Chart (horizontal bars) with 2 "vertical" lines to indicate the range of acceptable values

Attached to this Post is a *.pdf that (hopefully) further explains what I'm looking for.

View 4 Replies View Related

Creating A Date Range Trigger

Jun 5, 2007

hi i am currently trying to construct a date based maintainance sheet

i will try to explain what im trying to do

in one cell date is inputted
the next cell automatically adds +30 to generate next test date

what i am trying to do is get the sheet to
there are 4 testers so its a 3 month cycle for each ie
tester 1 jan
tester 2 feb
tester 3 march
tester 4 april
tester 1 may
tester 2 june

you get the picture? i hope
so if


cell A1 todays date

a2...................................b2......................................c2

21 jan.............................21 feb................................tester 2


inputed ...................... =A1+30
and date
formated
cell


i am trying to get the formula to tell me after the inputed date has been changed who the next tester is

i have tried using the IF command but it wont let me specify a date range ie 1 jan to 31 jan (or in the specified date codes) date range only one specific date with >= or <= which doesnt quite work properly

View 9 Replies View Related

Creating Date Range From Two Dates

Mar 20, 2012

I have a start date, and an end date for a promotion. However, I am trying to create some unique keys for a VLOOKUP, so I can combine the data I have by day, with the data I have for promotional start/end dates.

So, simple question, if I have a start date and an end date, is there was way (either using excel or vba) to pull out all the dates within that range and display them on a spreadsheet?

For example, I have 21/01/2012 and 28/01/2012 as the start and end dates. I would want to display 21/01/2012, 22/01/2012, 23/01/2012 etc.

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved