Range Names Created From String Variables

Aug 30, 2006

I work with data that varies in row numbers but is consisten in column width. I am trying to write code that will create a named range for the data but be flexible to expand or contract based on the amount of data that is pulled in. Below is the

Const lngLastPossRow As Long = 65536
Dim strDataRng As String
strDataRng = ActiveSheet.Name & "!R4C1:R" & Range("a" & lngLastPossRow).End(xlUp).Row & "C17"
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
ActiveWorkbook.Names.Add Name:=("Data"), RefersToR1C1:= _

While the result creates a named range called "Data" , it does not allow me to reference and data in any formulas ( sumif's, etc). Can someone tell me what I am missing. It appears to be returning the wrong data type (string as opposed to range values).

View 6 Replies


Dynamically Created Range Names In A Macro

Apr 1, 2008

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

Range("display cell").Value = Range(rangename).Value

The problem is, I am getting an object error every time I try to refer to a range this way.

If I type:
Range("display cell").Value = Range("JanTA1").Value, it works

View 9 Replies View Related

Can Create Data Validation List Of Names Created In Name Box Or Sheet Tab Names

May 7, 2012

Can I create data validation list of the names created in the name box or of the sheet tab names?

View 5 Replies View Related

Newly Created Tab Names Placed As Hyperlink On Content Sheet

May 11, 2006

I attach an example worksheet with the code I have thus far.

In my workbook I have other sheets, one of which changes and updates a specific one each time a new client's data is entered on said other sheet. Because I want to save the specific client's data and not lose it when another client's stuff is entered on this other sheet, I copy the sheet where the data is summarised (I called this sheet "Sheet to Copy From") to a newly inserted sheet and use Paste Special, Values Only to change all functions /f ormulae / Links ect to values.

I then change the name of the sheet to the name of the client.

I then use this sheet name / cell value to polulate a range on another sheet (Next Empty Cell) as a Hyperlink to the newly created sheet above. This sheet I called "Table of Contents".

how to code the hyperlink. Using Macro Recorder uses the specific case's names, but the Tab name to be used as Hyperlink value will always be the name of a new client,

View 8 Replies View Related

Excel 2007 :: Picture Created With Unique Names - Adding Comments To Cell

Oct 23, 2012

Excel 2007

I have a few dozen pictures created when a macro runs. They all have unique names. I'd like to add comments to cells, where the cell.value decides which picture to pull. All the examples I've found online show how to do this if you have pictures saved on your hard drive by referencing the file path "c://mydocs/...blahblah/"

Is there a way to reference the pictures I've created/named with my macro?

Here's the snippet of code that creates the pictures and names them:

For i = 2 To Application.CountA(Sheets("Allocation").Rows(1))
Set rInput = Sheets("Allocation").Range(Cells(1, i), Cells(10, i))
sPicName = "_" & Sheets("Allocation").Cells(1, i) & "_"
sSheet = Sheets("Allocation").Cells(3, i)
dDate = Sheets("Allocation").Cells(5, i)

[Code] ......

Here are some examples that are close to what I'm looking for.

VBA Popup Pictures - 1108 - Learn Excel from MrExcel Podcast - YouTube
VBA Express : Excel - Add pictures that float like comments.

View 4 Replies View Related

Pulling Out Single Names From A String Of Names

Sep 1, 2009

I have a list of names in a single cell. They are all seperated by a comma, then a space. Example would be: John Smith, Steve Wilson, Wallace O Malley, etc. What formula could I use to pull out the names individually, starting from the farthest right?

View 2 Replies View Related

Assigning Variables To Worksheet Names VBA

Jun 6, 2014

How to assign a "WS(i)" worksheet name and have the assigned variable (i) in the name equal the consecutive steps in a total page count.

Here's the code that does not work:

Dim WB as Workbook
Dim WS as Worksheet

Set WB = ThisWorkbook
For i = 1 To WB.Sheets.Count
Set WS(i) = WB.Sheets(i)

I can see why it does not work. I can't figure out what to declare in my Dim or how to word the statement using the (i) variable in order to capture the value to attach to "WS."

Details: The issue is that the number of pages created will vary day to day depending on staff needs. There's an array of staff names to be assigned to page tabs and there will be skips and deletions along the way depending on daily attendance so "WS(i)" worksheet names can't be assigned at creation. What happens if they are is that the succession of worksheet names winds up being (e.g.) "WS1, WS2, WS5, WS12," etc. instead of consecutive.

There's a very complex data-sort-and-assign system that depends upon how many staff-assigned sheets there are, and as such each sheet has to have a "WS" worksheet name for the process to stay smooth. If the names are not consecutive, though, the sort-and-assign process becomes confused. This step takes place at the end of the initial workbook set up process, immediately before the data are addressed, so there really isn't an earlier opportunity. It can't be later since from this point on the system depends upon the "WS(i)" names being there.

It started fine when there were 5 people. Now there are 37.

View 2 Replies View Related

For Statements For Multiple Variables With Similar Names

Feb 17, 2013

Basically I have a list that user can select up to 6 values from. I have set them as individual strings.

Public IH1 As String
Public IH2 As String
Public IH3 As String
Public IH4 As String
Public IH5 As String
Public IH6 As String

What I want to be able to do is loop through them and do certain actions. How can I get a for statement to do that.

I tried something like that but it doesnt work. How to get it working I need to do tests on the variable strings and I want to be able to reference them and I'm not sure sure how

For i = 1 To 6
If "IH" & i = vbNullString Then
MsgBox "IH" & i
End If

View 3 Replies View Related

Switching Between Workbooks Whose Names Are Stored As Variables

Jul 20, 2006

I'm having trouble finding a way to switch between two workbooks that I have open whose names are stored as variables. If the variable name were variable, for example, I have tried the following:

workbook.activate variable
workbook.select variable

none of these work and I've tried a ton of other ways to get it to work but I just can't figure it out!

View 8 Replies View Related

Can Use Variable Names To Identify / Assign To Other Variables Or Arrays?

Apr 10, 2014

I have set up a number of arrays e.g. DataSetA (1 to 100), DataSetB (1 to 100) etc.

Is it possible to use a string variable (e.g. DataIndentifier) to identify and assign values to certain arrays.

For example, under certain conditions

DataIdentifier would be set to "DataSetA" and under other conditions
DataIdentifier would be set to "DataSetB".

Having done this, I want to assign values such as DataIdentifier(1) = 3.4 or DataIdentifier (10) = 6.2 etc.

This obviously won't work because DataIdentifier is defined as a string not an array. How do I enable the string DataIdentifier to be used as an identifier for the array I want to assign values to? Therefore if DataIdentifier is set to "DataSetA" then effectively I want something like "DataIdentifier(1) = 3.4" to assign 3.4 to the array DataSetA(1).

View 5 Replies View Related

How To Compare Two String Variables

Mar 27, 2014

I am trying to to work out how to compare two string variables that I can see with my eyes are clearly a match but getting the right syntax to convince VBA they are the same is my issue.The first example is:

Direct Credit 158824 BRANDON DONNA C/ Lak30

the second example is:

Direct Credit 158824 BRANDON DONNA C/ 30 Lake Vue Parade

but that still would not get a match that I can find code for.

i am chasing the "holy grail" in VBA and I will leave the records that do not exactly match to manual processing although i hate to give up that easily

View 2 Replies View Related

Limitation On String Variables

Aug 2, 2006

I have recently come across a VBA macro, which reads from a text box within an excel input sheet, copy the text content, and send it via email. However, when the text content started to go beyond a certain number of characters, the text that comes out of the email, got truncated. I presumed this is something to do with the limitation to the string variables where the string " messagebody$ " which is used to read the text can only take up a certain bytes.

I was looking for VBA help, and somehow, it mentioned something called a variable-length string. Would that be a solution to my problem, or how is that used ?

ActiveSheet.Shapes("Rectangle 1").Select
messagebody = Selection.Characters.Text

email.Body = messagebody

View 6 Replies View Related

Splitting A Cell String Depending On Variables

Mar 1, 2009

On to what brought me here; I have a spreadsheet with a column containing an address, eg '12 SMITH ST SOMESUBURB NSW 2001'. I'm looking at splitting this cell in half after the street and moving the second half (suburb state postcode) into the next cell, which i've managed to do with a series on InStr statements.

Now the fun part is that not all these addresses are streets, some are avenues (AV or AVE), roads (RD), place (PL), drive (DR or DRV) etc. Initially i just did this:

View 7 Replies View Related

VBA How To Read Cells Into Variables/array And Use In String

Apr 15, 2009

I made the formula work great, however I want to cut it down and simplify it and make it easier to manipulate... as opposed to having the filename written every so many times.. replace with a loop instead.... so:

Private Sub Weeklytestt_Click()
Dim varCurDate As String
If DateYesterday.Value = True Then Answer = "1"
If DateToday.Value = True Then Answer = "2"
If DateTyped.Value = True Then Answer = "3"

Select Case Answer
Case Is = "1"
varCurDate = Format(Date, "yyyymmdd")
varCurDate = varCurDate - 1
Case Is = "2"
varCurDate = Format(Date, "yyyymmdd")..........

View 9 Replies View Related

Long Text String With Embedded Variables

Apr 17, 2007

I am trying to automate a report writing spreadsheet in which the text never changes in the cell (up to and over 50 words long).

Is there any way in which the figures within that text could be linked to another cell and change accordingly.

For example: a particular cell might contain the following which never changes apart from the figures:

"the performance of the fund was (B5) followed by a change in the index of (B6)...........the performance of the irish stocks was (A19) etc etc."

I know something can be done in formatting but for very long text would this be impracticle?

View 5 Replies View Related

Use Variables In The FormulaText Argement Other Than String Values As Integers

Feb 9, 2009

I've a big problem getting the Constraints in excel solver to use variables.
It seems that the only accepted variable type in the contraints are STRING variables. THe following simple example works AS LONG AS the variable apa is a STRING value and as long as it doesn't contain decimals....

So the question is. How to use varialbes in the FormulaText argement other than string values as integers?

View 14 Replies View Related

Return VBA Created Range

Nov 24, 2008

I am trying to create a function in VBA that will return a range, but I don't want the range to point to cells on a spreadsheet. I want the range to return a vba created range. Is this possible or impossible? Is a variable of type range required to point to cells on a spreadsheet?

View 14 Replies View Related

String Names In Different Subs

Jan 12, 2007

How can it be that this works?

Sub AA()
Dim ReportFileName As String
Cells(1, 19).Select
ReportFileName = ActiveCell.Value
ChDir "C:"
Workbooks.Open Filename:=ReportFileName _
End Sub

And this doesn't work?

Sub BB()
Dim ReportFileName As String
Cells(1, 19).Select
ReportFileName = ActiveCell.Value
End Sub
Sub CC()
ChDir "C:"
Workbooks.Open Filename:=ReportFileName _
End Sub

Somehow it seems that the value of the string "ReportFileName" is forgotten when running the subroutine CC. How do I make it not "forget" the file name?

View 4 Replies View Related

Replace End Of A String Only In Strings Containing Names

Apr 23, 2014

I have a VBA find and replace question

I have a spreadsheet with cognitive tests (approx 300) that have been given names like


What I need is a VBA macro that replaces the _e only at the end of the string to _ss for those strings that begins with w3_, w4_ and DKEFS_ (e.g. from w3_th_e to w3_th_ss)

Likewise I want to replace only the _e at the end of the string on all other names (e.g. pegboard_eh_e) with _t (e.g. from pegboard_eh_e to pegboard_eh_t) Is this possible?

View 4 Replies View Related

VBA For Inserting Input String Alphabetically Into Column Of Names?

Oct 2, 2013

I have a list of names in the A column of the sheet. I wish to use the input string, a new name, of a vba box to insert into the column of existing names in alphabetical order by inserting an entire row, which I suspect will be the case anyway. Also 'Return' key doesn't seem to be inserting a CR on the page that I typed this request upon.

View 5 Replies View Related

Use Variables In A Range?

Aug 23, 2009

I used a macro to get the following code, but would like to do this with VBA code where I use variables and numbers instead of the
macro's ("I568:J568") notation. Thus I would have something like (lRow, 9) : (lRow, 10) or whatever the correct notation is. Basically I'm trying to copy and paste formulas from one row to the next.

View 4 Replies View Related

Using Variables To Set Sum Range

Dec 30, 2013

I am trying to sum the range of column B (Being the found match in column A offset 1) to the relevant column set by the variable intcolumn (integer value of the column matching the criteria (in this case column 25)

rngMember.Offset(0, 13) = Application.Sum(rngTradeMember.Offset(0, 1) & ":" & rngTradeMember.Offset(0, intColumn))

The sum does not fail but returns an incorrect figure.

What I am hoping to see is a sum of the following

result sheet offset (0,13) = sum of (column B to the column Y)

View 7 Replies View Related

Sum Of A Range Based On Two Variables

Jun 18, 2009

I'm trying to create a simple checkbook application for a friend. I've gotten pretty much everything I want figured out except for two functions, which are basically the same thing. I'm trying to find the sum of all deposits and of all withdrawals based on two entered dates.

Basically, they enter the dates, and the formula finds the dates, then sums all the deposits/withdrawals between them. I attached the workbook with some sample data in case I wasn't really clear.

View 2 Replies View Related

Using Variables In Range References

Jun 5, 2009

I am doing some work in which I need a very flexible tool. I will be doing regression analysis on thousands of funds at a time. I need to define the range for each fund by a count function that counts the number of monthly returns that are posted. The code I have so far is below (I underlined where the references use variables):

View 3 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

Selecting A Range Using Variables

Jun 23, 2008

How would I select a range using variables? For example, say I wanted to say

myRange = Sheets(1).Range("A1:A100"),

but the column was variable. How would I do this if the column number was stored in a variable?

View 9 Replies View Related

Determine If Two Range Variables Are Same

Jun 20, 2007

How to decide the two range object variant represent the same range? Plz check the following code, How to decide Rng4 and Rng5 is or not the same range?

Sub IsTheSameRange()
With ActiveSheet
LastRow = .Cells(65536, "B").End(xlUp).Row
Set Rng1 = .Range(.Cells(3, "K"), .Cells(LastRow, "K"))
Set Rng2 = .Range(.Cells(3, "AE"), .Cells(LastRow, "AE"))
Set Rng3 = .Range(.Cells(3, "BQ"), .Cells(LastRow, "BQ"))
Set Rng = Application.Union(Rng1, Rng2, Rng3)
Set Rng4 = Rng.SpecialCells(xlCellTypeFormulas, 23)
Set WhlRng = .Range("A3:DR" & LastRow)
Set Rng5 = WhlRng.SpecialCells(xlCellTypeFormulas, 23)
End With
End Sub

View 2 Replies View Related

Variables To Make Up Range

Jul 25, 2007

I'm trying to edit the predefined range in my macro that changes formulas to values.
My current macro looks like this:

Dim vCol As Variant
vCol = Application.InputBox("Select Column", Type:=2)
If vCol = False Or vCol = "" Then Exit Sub
Set UserRange = Range(vCol & "9:" & vCol & "35")
UserRange.Value = UserRange.Value
End Sub

I tried extending the range to include 48:53, but it doesn't work. I tried with this: Set UserRange = Range(vCol & "9:" & vCol & "35" & vCol & "48:" & vCol & "53")

View 6 Replies View Related

Use Variables To Delete Range

May 23, 2008

I am trying to select rows using constants I have set-up so that I can then delete those rows. However I don't know how to set-up the row formula to accept the constants. So far I have something like

Dim a As Integer
Dim b As Integer

View 3 Replies View Related

Replace Range Names In Formulas With Range References

Jul 7, 2007

I have inherited an Excel workbook in which the formlas all contain cell names (and there are thousands of names in this book). I need to find a way to change from using cell names in a formula back to a standard absolute cell reference but have no idea how to do this?

View 9 Replies View Related

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