Solved

Range of Cells to have value from another cell

Posted on 2011-03-11
4
282 Views
Last Modified: 2012-05-11
I would like a formula to use in a Macro where a range of cells is given the value from another cell.  Not a copy and paste formula but rather the range of cells is selected in the macro and then every cell in that range is given the value either from a dimensioned variable or from another cell in the workbook.
0
Comment
Question by:DavidH7470
  • 4
4 Comments
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 35111337
To select a range use the Application.InputBox function. The returned type is a Range. Since the range type is returned the result must be assigned using the Set operator. This means that if Cancel is clicked the assignment will generate an error because a Boolean cannot be assigned using the Set operator. Therefore to handle the input of a range error handling must be used as illustrated with the example code below.

   Dim Result As Range
   On Error Resume Next
   Set Result = Application.InputBox("Select a range:", Type:=8)
   On Error GoTo 0
   If Not Result Is Nothing Then
      ' Result contains a range
   End If

Kevin
0
 
LVL 81

Accepted Solution

by:
zorvek (Kevin Jones) earned 500 total points
ID: 35111352
To reference a range of cells and copy values from one cell to another from VBA you will find some of the following useful.

There are many ways to specify a range in Visual Basic. Below are some examples.

   [A1]
   [A1:B2]
   [A:A]
   [1:1]
   [RangeName]
   Range("A1")
   Range("A1:B2")
   Range("A:B")
   Range("1:2")
   Range(Cells(Row1, Column1), Cells(Row2, Column2))
   Range(RangeVariable, RangeVariable)
   Range("RangeName")
   Cells(Row, Column)
   Cells(Row, "ColumnLetter") (Important: see note at the end of this article about a bug in Excel 2003 and earlier)
   RangeVariable.Range("C1:E1")
   RangeVariable.EntireRow.Cells(1, "Z") (Important: see note at the end of this article about a bug in Excel 2003 and earlier)
   RangeVariable.EntireColumn.Cells(10, 1)
   Columns(1)
   Columns("A") (Important: see note at the end of this article about a bug in Excel 2003 and earlier)
   Columns("A:B") (Important: see note at the end of this article about a bug in Excel 2003 and earlier)
   Rows(1)
   Rows("1:2")

When using a "ColumnLetter" parameter with the Cells or Columns property, the letter is translated into an absolute column number.

All of the above referencing methods except for the bracketed method ([A1]) can be used on any previously defined range object. When used in this manner, the row and column are treated as offsets into the range. They are also not limited to the parent range and thus can reference cells beyond the range's boundary. Below are some examples.

   [C1:E1].Cells(1, "D") equals [C1:E1].Cells(1, 4) or [F1]
   [C5].Columns("B") equals [C5].Columns(2) or [D5]
   [C5].Rows(4).Columns(2) equals [D8]

Note that the Cells method must be used when indexing into a range derived from the Rows or Columns collections, the EntireRow and EntireColumn objects, or when indexing into a range specified with square brackets:

   Columns("B")(23) will fail so use Columns("B").Cells(23)
   Rows(2)(1, 3) will fail so use Rows(2).Cells(1, 3)
   [B2].EntireRow(1, "C") will fail so use [B2].EntireRow.Cells(1, "C")
   [B10:B20](23) will fail so use [B10:B20].Cells(5)

If starting with a range that does not start in row 1 or column A and an absolute row or column is desired, use the EntireColumn or EntireRow method:

   [C5].EntireRow.Cells(1, "D") equals [D5]
   [C5].EntireRow.Columns("D") equals [D5]
   [C5].EntireColumn.Cells(10, 1) equals [C10]
   [C5].EntireColumn.Rows(10) equals [C10]

When passing range variables to the Range method the top left cell of the first parameter and the bottom right cell of the second parameter are used and the result is the smallest rectangular range encompassing both range parameters:

   Range([B2:C3], [D4:E5)] equates to B2:E5

Use commas to create unions:

   [A1,B1,C1:C5]
   Range("A1,B1,C1:C5")
   [RangeName1, RangeName2]

Note that the text passed to Range and in square brackets cannot be longer than 255 characters.

Use spaces to create intersections:

   [1:2 A:B] equates to A1:B2
   
To offset from a particular cell:

   [B2].Cells(2, 3) equates to [C4] and is the same as [B2].Offset(1, 2)

To double offset from a particular cell:

   [B2].Cells(2, 3)(4, 1) translates to [D6] and is the same as [B2].Offset(1, 2).Offset(3, 0)

To index into a range of cells starting from the top left cell and moving across each row from left to right and then down each row from top to bottom:

   [B2:D4].Cells(Index)
   [B2:D4].Cells(1) equates to [B2]
   [B2:D4].Cells(5) equates to [C3]

This technique works for any shape of range. Note that indexing continues down past the end of the specified range:

   [B2:D4].Cells(10) equates to [B5]

Top left cell of a range:

   Target(1, 1)

Bottom right cell of a range:

   Target(Target.Rows.Count, Target.Columns.Count)

Nth row or column of a range:

   Target.Rows(N)
   Target.Columns(N)

Working with all rows or columns in a range:

   For Each Row In Target.Rows
      Row(1, 1)
      Row(1, 2)
   Next Row

   For Each Column In Target.Columns
      Column(1, 1)
      Column(2, 1)
   Next Column

Important: Note that the Columns and Cells collections fail when passed a column letter and the active sheet is a chart or there is no visible workbook. This bug was resolved in Excel 2007. The workaround is to use the Range method. The problem occurs even if a specific parent expression is used which fully qualifies a worksheet. The following examples will fail when a chart sheet is active or there is no visible workbook:

   Set MyRange = ThisWorkbook.Worksheets(1).Columns("A")
   Set MyRange = ThisWorkbook.Worksheets(1).Cells(1, "A")
   Set MyRange = ThisWorkbook.Worksheets(1).Range("A1:C10").Columns("A")
   Set MyRange = ThisWorkbook.Worksheets(1).Range("A1:C10").Cells(1, "A")

These, however, will work:

   Set MyRange = ThisWorkbook.Worksheets(1).[A1:IV65536].Columns("A")
   Set MyRange = ThisWorkbook.Worksheets(1).[A1:IV65536].Cells(1, "A")
   Set MyRange = ThisWorkbook.Worksheets(1).[A1:IV65536].Range("A1:C10").Columns("A")
   Set MyRange = ThisWorkbook.Worksheets(1).[A1:IV65536].Range("A1:C10").Cells(1, "A")

As will these:

   Set MyRange = ThisWorkbook.Worksheets(1).Columns(1)
   Set MyRange = ThisWorkbook.Worksheets(1).Cells(1, 1)
   Set MyRange = ThisWorkbook.Worksheets(1).Range("A1:C10").Columns(1)
   Set MyRange = ThisWorkbook.Worksheets(1).Range("A1:C10").Cells(1, 1)

Kevin
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 35111378
Regarding copying values from cell to cell, below are various examples describing the most commonly occuring scenarios and how to copy the desired cell attributes.

To copy values only while converting formulas to values:

   TargetRange.Value = SourceRange.Value

To copy formulas only:

   TargetRange.Formula = SourceRange.Formula

To copy values, formulas, and formatting:

   SourceRange.Copy TargetRange

To copy formatting only:

   SourceRange.Copy
   TargetRange.PasteSpecial xlPasteFormats

To copy values and number formats:

   SourceRange.Copy
   TargetRange.PasteSpecial xlPasteValuesAndNumberFormats

To copy values and formats while converting formulas to values:

   SourceRange.Copy TargetRange
   SourceRange.Value = SourceRange.Value

To copy formulas and formatting but not values:

   SourceRange.Copy TargetRange
   TargetRange.SpecialCells(xlCellTypeConstants).Value = ""

Any single or multiple area range can be copied to another location using the assignment method as long as there are no overlapping cells.

The Copy method does not work when the range comprises multiple areas. In that case each area must be copied independently.

Kevin
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 35111407
And finally, how to copy cells to arrays and back again.

Normally, when a range is copied to a variant array, the result is a two-dimensional array even if a single row or column is copied. The Transpose worksheet function can be used to create a single dimension array as illustrated in the examples below.

To create a single dimension array from a column of values:

   Dim Values As Variant
   Values = Application.Transpose([A1:A100])

To create a single dimension array from a row of values:

   Dim Values As Variant
   Values = Application.Transpose(Application.Transpose([A1:J1]))

To load a column of cells which can contain a variable number of non-blank values then the above column-based function with the CountA function:

   Dim Values As Variant
   Values = Application.Transpose([A1].Resize(Application.CountA([A1:A1000])))

To copy a single dimension to a row of cells:

   [A1:J1] = Values

To copy a single dimension array to a column of cells:

   [A1:A10] = Application.Transpose(Values)

Kevin
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now