HOW TO REFERENCE CELLS WITH VARIABLES IN EXCEL VBA

You're doing it with less double quotes.

Here's an example: DimnAs

integer

nworkbooks("xx table").

There is a virtual variable here that you can change according to your own needs.

sheets(1).activate

range("a2:a"

n).select

When both are variables, you can't use n&m to represent them, because the content after range is not represented by r1c1, and you can't use two numeric variables to delineate a region.

To reference cells that are not in this workbook, the methods are as follows:

1. Open the workbook to reference the data as follows:

e: Standard load test record of power supply management. xls"In parentheses is the path to the workbook.

2. The cited data is:

workbooks("The name of the workbook that references the data").worksheets("The name of the worksheet").cells(cell address) = workbooks("The name of the workbook to reference the data").

worksheets("The name of the worksheet").cells (cell address).

3. Close the workbook that references data.

workbooks("The name of the workbook to reference the data").colose

Method 1: If you use Excel's built-in functions, the only thing you can use is the indirect function, which can turn strings into references.

However, indirect is a volatile function, and cross-workbook references must be open to update data.

In your example, you can use:

vlookup(a6,indirect("'d: Folder path [Dalí.

When you pull down, you will get the effect of Darí 8.

However, to successfully update the data, your relevant file [Dali Dali must be opened (which is actually a bit difficult).

At the moment, there is no better way than to use VBA to customize a function to automatically open and close related files.

Method 2: 1. Add an event.

onkeyup="checklength(this,'256','chleft');

The first parameter: represents the currently entered character; The second parameter: how many characters can be entered; The third parameter is to display the span or div id name of how many characters can be entered.

Verify the text field and also enter more and less words.

function checklength(which,count,name) {var maxchars = count;

if ( maxchars)

maxchars);

var curr = maxchars - = ;

For example, reference data from a1:a2, c2:c4, e5, f6 and output to a9:a15.

1. First define a cell variable rng; ‍

dim rng as range

2. Use the union() function to unite multiple cells and regions, and assign a value to the cell variable rng;

set rng = union([a1:a2], c2:c4], e5], f6])

3. Define an array arr to store the data of the joint cell range RNG;

redim arr( -1)

4. Use the for each loop to store the data in rng into the array arr in turn;

dim xx, i%

for each xx in rng

arr(i) = xx

i = i + 1

Next5, use resize() and transpose() to output the array arr to a9:a15.

Refer to the diagram below.

You can use range, e.g. range("a1:b2"That's a reference to 4 cells.

First method:

Ctrl+G brings up the positioning dialog box, enter A1:E5000 in the Reference Location, and click OK.

The second method:

Enter a1:e5000 in the name box in the upper left corner of the program, and press enter.

The third method:

Select any cell in the cell range and press CTRL+SHIFT+* to select the entire range of the cell.

Fourth method:

Press Ctrl+End to jump to the lower-right cell of the region and select it with the mouse. Then pull the scroll bar back to the first row, hold down shift, and click A1 with the mouse.

Suppose you assign the A1 cell in sheet1 to the x variable, and the statement is as follows:

x = sheets(1).range("a1")

Suppose x is calculated to get a variable value y, then assign y to a1 with the following statement:

sheets(1).range("a1")=y

In this way, the mutual assignment between cells and variables is realized.

1. What is a variable?

A variable is a named memory location. It is a named item that is used to temporarily store data during the running of a program or **, and the data stored in it can be changed as needed.

Variables are specified by the name (variable name) and data type:

Variable Name - Used to refer to the variable.

Data Type – Determines the type of information that the variable can store and how much storage space it occupies.

2. Naming rules for variables.

1) The name is any combination of letters (a-z, a-z) or numbers and underscores "". (Chinese can also be included in Excel 2002 and later versions); The last character can be a type specifier;

2) The length of the variable name cannot exceed 255 characters.

3) The variable name must be unique within a valid range. A valid scope is the scope of the reference variable that can be recognized by the program, such as a process, a form, and so on;

4) The variable name cannot be a reserved word (keyword) in VBA, nor can it be a reserved word with a type specifier at the end, but the reserved word can be embedded in the variable name.

Note: (1) Variable names are case-insensitive. Such as ABC, ABC, ABC, etc. are all the same;

2) When defining and using variables, it is common to define the variable name as one that is easy to read and can describe the usefulness of the data it contains;

3) Mix uppercase and lowercase letters and numbers as needed. If you need to use a multi-word group, the first letter of each word in the variable is capitalized, for example: datatemp represents temporary data, or it can be separated by two underscores in two words, for example: last num;

4) There are also better suggestions for variable names, where each variable name starts with two or three character abbreviations that correspond to the type of data in which the variable is going to store data. For example, the strworksheet as string table is used to indicate the name of the worksheet, and the prefix str indicates the type of the current variable.

For example, if you want to copy cell A1 in sheet1 to the x variable, then x = sheets("sheet1").range("a1"For example, if you want to copy the value y of the variable of a certain operation result to a1 of sheet1, then:

sheets("sheet1").range("a1")=y

What you want to do, be straightforward, don't be smug, and be ashamed to talk ......

excel

Variable declarations. 1. Declare a variable and assign a value to it.

2. Define a macro function to show whether a global variable has a value or not.

4. Then change the position of the global variable declaration.

Like what. You want to.

Handful. sheet1

A1 cell.

Copied to. x variable. So. x

sheets("sheet1").range("a1"For example, you have to put it again.

A. The result of the calculation.

The value of the variable. y

Copied to. sheet1. A1 So:

sheets("sheet1").range("a1")=y

Variables: Changed during the running of the program, and disappeared automatically after the program was run.

For example, the following paragraph is a custom function that calculates factorial. The answer code is as follows:

function jc(a as integer)if a > 50 then

jc = 0

exit function

end if

jc = 1

for i = 1 to a

jc = jc * i

next i

end function

JC here can also be understood as a variable of uppermutation, the default value is set to =1, and the required factorial value is obtained by multiplication.

Changes in the JC can be seen by stepping into.

Set to a=10 to see the function run.

Method steps.

Create a new blank workbook, press the shortcut key ALT+F11 or right-click any worksheet tab in the worksheet interface, and click "View" in the pop-up right-click shortcut menu to enter the VBA environment, and the next ** will be demonstrated on this interface, as shown in the following figure:

The easiest way to define a variable is to use a dim statement. As shown in the following **, a variable is defined, the name of the variable is i, and a value is assigned to the variable.

sub xyf()

dim ii = 1

end sub

This is shown in the figure below.

Variables have different types of variables, and different types of variables can store different data and occupy different memory space. The specific variable types are as follows:

boolean, byte, integer, long, currency, single, double, date, string (variable-length string), string * length (fixed-length string), object, or variant

As shown in the following **, an integer variable i and a character variable j are declared.

sub xyf()

dim i as integer

dim j as string

i = 1j = abc"

end sub

When a variable is defined without specifying a specific variable type, its type belongs to variant.

As shown in the following **, a variant k of type variant is declared.

sub xyf()

dim kk = 1

end sub

Variant variables can be used to store arbitrary data. However, it also takes up the largest amount of memory space.

In fact, it is okay not to declare the variable first, such as the following ** can also work normally.

sub xyf()

i = 1end sub

However, with the increase of **, not first waxing or Zen declaring variables will lead to variable names that cannot be found after writing errors. Wheel dust.

You can force all variable branches to be declared first, and enter a mandatory variable declaration statement in the declaration section of the ** window.

option explicit, you can require all variables to be defined first, if the variables are not defined, running ** will pop up an error message as shown in the following figure.

You can click the option explicit statement to be automatically added by clicking the Options command under the Tools menu, selecting Require Variable Declaration in the pop-up Options dialog box, and then clicking OK. With this setup, all VBAs will require mandatory variable declarations. This is shown in the figure below.