Refer to muliple cells in vba

Resolved · Medium Priority · Version 2007

Thu 6 Feb 2014, 14:44 Delegate Imran said...

Imran has attended:
Excel VBA Intro Intermediate course
Excel VBA Advanced course
Excel Advanced - Formulas & Functions course
Excel Advanced - For Power Users course

Refer to muliple cells in vba

Hi,

I have the following code that I am trying to execute, however it only returns the value of f4 in cells a1:a7, whereas I want them to be listed in order.

Sub CopyCells()

Dim CopyData As Single

CopyData = Range("f4, f7, h7, j8, l6, n7, n10").Value

Range("a1:a7") = CopyData

End Sub

Thu 6 Feb 2014, 15:04 Trainer Gary said...

RE: refer to muliple cells in vba

Hi Imran

Thanks for getting in touch. This type of variable can only hold one value.

To store multiple values, you'll need an array variable. You'll probably need to loop to pull the values in, and loop to push the values out again.

There are some good examples here:

http://stackoverflow.com/questions/18993915/get-values-from-union-of-non-contiguous-ranges-into-array-with-vba-with-a-simple

If you're still stuck, let me know.

Kind regards

Gary Fenn
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

Thu 6 Feb 2014, 15:42 Edited on Thu 6 Feb 2014, 16:27 Delegate Imran said...

RE: refer to muliple cells in vba

Hi Gary,

After reading that, I am totally lost, I did not understand that at all! I have however got this of MrExcel.com

Which seems to work ok

Sub CopyCells2()

Dim CopyData As Range
Dim cell As Range
Dim x As Integer

x = 0
Set CopyData = Range("F4,F7,H7,J8,L6,N7,N10")

For Each cell In CopyData
Range("A1").Offset(x) = cell.Value
x = x + 1
Next cell

End Sub

Regards

Imran