# Assign Variable To Worksheet Formulas

Aug 23, 2008

Split from Copy, Cut, Fill Relative Or Absolute Formulas

Originally Posted by AAE
Welcome to Ozgrid.

Assume the following:
Cell A1: is the input cell
Cell B1: contains a number
Cell C1: formula = A1*B1 or \$A\$1*\$B\$1

Your formulas must contains absolute cell references, other wise Excel will attempt to update them to the new cell references when copying/pasting.
If you "cut", rather than copy, Excel will maintain the original cell references in the formula.

Both of these questions are well within the experience level of a user with above average skill level as your profile shows. Please lower it (before one of the Admins does it for you) to a more realistic level.

See this training page on how to toggle between relative/absolute cell references: [url]

Well cutting is a nifty trick, thank you for that, I don't believe you quite understood my first question.

Lets use my calculator for instance. Using something it has designated K (for no apparent reason) I can enter a formula, such as K=*2-4, and after that is entered every number I then type and press equals to it will run that formula and spit out an answer. (after entering that function, if I press "5" "equals" it will give me "6") Now what I'm wondering is if excel has a "K" function that I can assign to certain cells, namely a column, so that whenever a figure is typed in any of the cells in that column, the required operation is done, namely *.9685.

## Assign Variable For Next Available Row?

Jan 15, 2010

This code finds the second avaliable row from the top of the sheet.

## Assign Array To Variable

May 19, 2009

My question is about assigning an entire array to a single variable. In this case, I want to assign an entire array to one element of another array.

## Assign Range To Variable?

Jul 11, 2014

Why the variable DataRange won't set as below. I get Run-time error 9: subscript out of range.

Code:
Sub CreatePivotTable()
Dim DataRowCount As Integer

[Code].....

## How To Assign An Array To A Variable

May 15, 2008

how to do is the assignment of GoodArray1 to CurrentArrayToUse, i.e. "CurrentArrayToUse = GoodArray1" below:

Global NextArrayToUse()
Global CurrentArrayToUse()
Global PreviousArrayToErase()
Global GoodArray1(), GoodArray2(), [etc]
Global CurrentGuessNumber As Integer

[bunch of code, part of which assigns a number to CurrentGuessNumber, then the following...]

Select Case CurrentGuessNumber
Case 1
CurrentArrayToUse = GoodArray1
NextArrayToUse = GoodArray2
Case 2
CurrentArrayToUse = GoodArray2
NextArrayToUse = GoodArray3
PreviousArrayToErase = GoodArray1
ReDim PreviousArrayToErase(0, 0)

## Assign Range To Variable

Dec 24, 2007

I am trying to assign a range of cells to a variable but it isn't working. My code is as follows:

Dim VarRateTable As Range

Private Sub CalcBudgets(rng As String)
' Dim VarRateTable As Range
Dim CurrentRng As Range
Set CurrentRng = Selection
Set VarRateTable = Range(rng)

Private Sub NEWCALC(datarow)
Dim vt As Double

vt = Application.WorksheetFunction.VLookup(Cells(datarow, 4).Value, VarRateTable, 3, 0)

but the Set statement gives me an error: <method 'range'of object '_global' failed>

## Assign Value From Userorm Textbox To Variable?

Aug 18, 2008

I have created a userform with 3 textboxes and I want to return the values entered to variables for use in a procedure. I cannot figure out how to return the values.

Behind the command button I am unloading the form so that the calling procedure continues executing but my vars cannot see the controls.

I have tried making them public outside of the calling sub, but nothing. Can anyone point out to me what I'm doing wrong?

## Assign Variable To Selected Range

Dec 21, 2006

What I need is a macro that assigns manually selected cells/ranges (which may or may not be continious) to a Variables.

## Open Workbook And Assign To Variable

Jul 16, 2007

Back to coding in VBA after long months of Matlab, and banging my head around this simple code snippet:

Sub openfile(FilePath)
Dim myWBk As New Workbook
'Now open Form
myWbk=Workbooks.Open(FilePath)
End Sub

This returns a nasty error "Activex Component couldn't create object". It opens the file given by FilePath though. What I would like is to assign the open workbook to the myWbk variable, such that it is easier to work with.

## Assign Variable To Different Data Types

Oct 12, 2007

Putting the final touches on a project I have and the last element doesn't work 100%. the scenario: I have a function that I wrote based on the user's input of the desired column to perform the operation. As a method of error-trapping and ease of use, I set instructions for the code to activate:

1) if the user highlights multiple cells of the desired column
2) enters the letter of the column (up to "z", not case sensitive)
3) enters the number of the column

Sub Button5_Click()
Dim ColumnUsed As Variant
' find number of columns selected
For MyColNum = 1 To Selection.Columns.Count
Cols = Cols + 1
Next MyColNum
'find number of rows selected
For MyRowNum = 1 To Selection.Rows.Count
Rows = Rows + 1
Next MyRowNum..............................

## Assign External Cell Reference To Variable In VBA?

Jun 29, 2006

I'd like to assign a value located in a cell in a workbook to a variable in a VBA program in another workbook. I do NOT want to open the workbook that contains the cell with the value. I can't get the hang of referencing an external cell reference. Let's say there's a value in a cell named "nbr" in a workbook named "tst.xls" located in a path "c: est" that I want to assign to a variable "tstnbr" in a VBA program, what is the best way to code that?

## Assign Multiple Cell's Values To A Variable

Jul 23, 2006

Need exact VBA code syntax to assign a workbook (to be closed) sheet's cells J4 to J72 values to a variable called "ColJValues" to be assigned to another sheet (to be opened later in the macro). The values are all dates. Once the other workbook is opened later in the macro, need the exact syntax to assign the value in the above variable, "ColJValues", to it's cells J4 to J72.

## Assign To A Variable, The Total Number Of Text Entries In Col

Feb 28, 2009

I need to assign to a variable, the total number of text entries in col. A (or alternatively in a named range).

## 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).

## VBA Macro To Assign HTML Content Of Wikipedia Search To String Variable

Feb 16, 2014

I am doing a Regular Expression search on a string variable assigned to the HTML content of a Wikipedia search. However I am currently manually going to Wikipedia, searching for the term, saving the html page, opening the saved page with Notepad and then copying the content into a cell.

Can the above process be automated with VBA, how to assign the html content of a Wikipedia search to a string variable.

## Copy Worksheet In Workbook With All Formulas On New Worksheet Referencing Previous Worksheet

Apr 21, 2012

I have a workbook that contains 50 worksheets named 1-50. I need to add more worksheets. all the formulas in the worksheets always refers to the previous worksheet.

How can i make a copy of the worksheet named 50, name it 51 and have all the formulas in worksheet 51 refer back to worksheet 50?

## Assign Values To Textboxes On Worksheet

Jul 21, 2014

I am using a ComboBox on a worksheet where the items in that box are pulled from Sheet1A:A. When the selection of the item in the comboBox is made, I would like several textboxes on that same page to be auto-populated based upon info derived from cells adjoining whatever row in Column A was chosen on Sheet 1.

For simplicity sake, let's say the range on Sheet 1 is A:H, and TextBox1 = "B", TextBox2= "C", etc.

so if,
A B C D
JOHN 15 DOGS BLUE
SUE 18 CATS RED
BOB 20 TURTLES YELLOW

When Combobox selection is JOHN, I want TextBox1= "15" TextBox2= "DOGS" TextBox3= "BLUE"

## Assign A Number Value To Text Within Worksheet

Sep 10, 2009

I need to count the number of times a value appears in a row and assign a number that tells which occurence of the text it is.
For example

Jim 123 Smith Street
Jane 123 Smith Street
Bob 543 Apple Street
Mary 543 Apple Street

I would like a way to insert a column that puts a 1 next to Jim and a 2 next to Jane, a 1 next to Bob and a 2 next to Mary and so on.
Ultimately, I want to sort the list and delete all the twos, thus deleting the duplicate address entry.

## Assign Specific Cell Value Of Worksheet To A Label

Aug 1, 2006

I am struggling with working with specific cells from specific sheets. e.g.

I want to make a label display teh value of a specific cell.

(i is the row number of the cell i'm interested in)

'now make labellaptime.caption = the cell (i-1, 3) of sheet1(jules)

'LabelLaptime.Caption = i - 1 'works no problem
'LabelLaptime.Caption = Application.Worksheets(Sheet1).Cells(i, 3) 'laptime
'LabelLaptime.Caption = Application.Worksheets(1).Range("i-1,3").Value
'LabelLaptime.Caption = Application.WorksheetFunction.VLookup(
'LabelLaptime.Caption = Range("c3") 'works no problem
'LabelLaptime.Caption = ThisWorkbook.Worksheets.("sheet1").range("c1").value

I've based all this on the book i'm trying to learn from but it doesn't work and (obviously) i don't know why...

(plus any links to info on how to work with specific cells on specific sheets e.g making a variable = to the value of a cell(x,x) of sheet 3)

## Using Vba Variable In Formulas

Mar 18, 2007

I am trying to write a macro that will cut the text in a given cell to 16 characters. My code works as long as the text is always in the same cell, but I eventially want to ask the user what cell to use. So I want to replace the “RC[-25]” with my WorkCell variable. I can’t seem to figure out how to put it in there.

Sub test()
Dim SizeOfRange As Integer
Dim WorkCell As String
SizeOfRange = 10
WorkCell = A1
For x = 1 To SizeOfRange
Range("z" & x).Select
ActiveCell.FormulaR1C1 = "=LEFT(RC[-25],16)"
Next x
End Sub

## Code To Assign Predefined Number In Separate Worksheet

Apr 27, 2009

Excel 2003: I need code that, when an "x" is entered in a cell in the "Activity" worksheet to assign a temporary unit #, it will look for the next available Temporary Unit # in the "Assign" worksheet. Then mark that unit # as "assigned" (by placing an "X" in the column next to it) and copy it to a cell in the "Activity" sheet.

I will be doing the same thing with assigning different types of PO numbers. I figure if I have the code for the Unit #, I can use the same logic for the other assignments, with some modifications, of course.

I've attached a sample workbook.

If I am not considering the most effective way to accomplish what I am trying to do here, I have no ego at all about someone suggesting a better solution.

## Automatically Assign Predefined Unit # In Separate Worksheet

Apr 5, 2009

I have a worksheet that logs equipment activities. Equipment may already have a standard unit # assigned to it or it may need a temporary unit # assigned for tracking. In a separate worksheet is a column of predefined temp unit #'s that can be assigned and next it is a column to place an "X" once a unit # has been assigned.

I need to write logic that, when an "X" is entered in Column F (Assign a Unit #) of the Activities worksheet, the Unit # (Column G) will automatically select the next available Temp Unit # in the Assign worksheet...and mark that Unit # as no longer available. Quite frankly, this may be more easily done with VBA, but I am not experienced in that at all. I have attached a sample worksheet showing the format of the unit # to be assigned, etc.

## Create Button & Assign Macro When Worksheet Added

Aug 6, 2007

Is there a VBA code that will enable me to create a button and assign a certain macro to it everytime I insert a new worksheet?

how to insert a new worksheet with VBA, what I want is that when I insert that worksheet, there is already a button there with a specific macro(already made) assigned to it.

## Count & Summing Formulas Containing More Than One Variable

Sep 17, 2009

I am trying to create a formula that will count the number of entries that contain either a name of 'A', 'B' or 'C' and fall within a set date parameter.

I am currently using this formula (which works perfectly well for 1 variable but not for multiple), where column B is my date and column R is my name field.

=COUNTIFS(Extract!B:B,"

## Variable Usage Inside Formulas

Aug 10, 2007

In a macro I am writing, I am trying to use the data stored in a variable inside a formula. Specifically, my variable holds a string that represents the title of a specific sheet in my workbook. I am trying to put this variable into the count formula to be used as the sheet location.

## Variable Cell Ranges In Formulas

Feb 23, 2008

I need to run a formula each month which calculates the ‘product’ for a range of cells in a column, where the cell range will change each month. The starting cell for the range is static, but the range end will always change - additionally, the last valid cell will always be followed by a null value cell. The attached example shows the basic format of my data – a range of values, by row, which will always end with a null value cell. The formula I’m using (to match my example) is: =(PRODUCT(A10:A13) –-> and I want to display the result in cell C10.

While I’ve been able to identify the ‘ending good row’ several ways, I haven’t been able to figure out what to do with this information – I seem be be lost in the translation of OFFSET values to CELL REFERENCE values. Has anyone encountered this specific scenario and found a solution?

## Variable Amount For Global Functions/Formulas

Jun 22, 2008

How can one change the cell values of a worksheet by creating a setup page in another worksheet. Example: the worksheet value is =average(E7, F7, G7, AQ7)*0.6 -- which this formula makes 60% of the average. On the setup page or worksheet I want o change value of *0.6 to say *0.5 for all the cell that has this value. In other words form the setup all the values will change on the related worksheet from the setup page.

## 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

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

## Sum Variable Range Of Numbers From Worksheet Without Activating Worksheet

Apr 29, 2013

I care about is the line starting wks4.Cells(Di, 2) = I am trying to sum data from a variable length column in worksheet 3 and place the result in column 4 However, when I hit enter after entering the formula into =SUM() I get

Compile Error:

Expected: list separator or )

[code]
Sub Macro1()
Dim wks3 As Worksheet Dim wks4 As Worksheet
Set wks3 = Worksheets("Sheet3")

[Code].....

## Assign Userform Name To Variable For Use On Another Userform

Dec 17, 2008

In order to deal with the multitude of screen resolutions that can be found at our work, I have got a module to check the current screen resolution on loading the workbook. I then have set up 3 different sized userforms, all containing the same coding, and based on the displayed screen resolution one of these is open (the userforms are named Customer1, Customer2, Customer3).

The displayed userform then has a button to show another userform (Customer_Details), and this form requires some referencing back to the previous form for some of it's information eg,

customer_details.textbox1.text = customer(1, 2 or 3).textbox1.text
customerdetails.combobox1.list = customer(1, 2 or 3).combobox.list

way for me reference the specific "Customer" form that has been opened, without calling it by name?