Setting a variable to range of cell values
 

Thanks Incidental. That helps a bit, but I have some other questions
regarding the Array solution. I am able to make it work with the code below
(listed under "Code Works") -- is it possible to write this more efficiently
so that I don't need to specify what each cell individually needs? See the
code listed under "Would like to get code to work" below. Also, should I
specify a data type other than variant given that each cell in the array
contains no more than 10 letters, all text?

Code Works:

Sub copyheader()

Dim MyArray As Variant
Dim range1 As Range

MyArray = Sheets(2).[A1:L1]

With Sheets("Sheet3")
Set range1 = .Range("A1:A20")
For Each A In range1
If A.Value = "Need header" Then
A.Value = MyArray(1, 1)
A.Offset(0, 1).Value = MyArray(1, 2)
A.Offset(0, 2).Value = MyArray(1, 3)
A.Offset(0, 3).Value = MyArray(1, 4)
A.Offset(0, 3).Value = MyArray(1, 5)
A.Offset(0, 3).Value = MyArray(1, 6)
A.Offset(0, 3).Value = MyArray(1, 7)
A.Offset(0, 3).Value = MyArray(1, 8)
A.Offset(0, 3).Value = MyArray(1, 9)
A.Offset(0, 3).Value = MyArray(1, 10)
A.Offset(0, 3).Value = MyArray(1, 11)
A.Offset(0, 3).Value = MyArray(1, 12)
Else
End If
Next
End With

End Sub

Would like to get code to work:

Sub copyheader()

Dim MyArray As Variant
Dim range1 As Range

MyArray = Sheets(2).[A1:L1]

With Sheets("Sheet3")
Set range1 = .Range("A1:A20")
For Each A In range1
If A.Value = "Need header" Then
.Range(A, A.Offset(0, 11)) = .Range(MyArray(1, 1), MyArray(1, 12))
Else
End If
Next
End With

End Sub

--
Robert


"Incidental" wrote:

Hi Robert

Not sure if this will be any help to you or not but I thought I would
drop it in here just in case. from your code it looks like you are
just coping a range from one sheet and pasting it in the same format
in another sheet if the value in a specified range is "Need header" if
this is the case something like the code below should do what you
want.

Option Explicit
Dim MyCell, MyRng As Range

Private Sub CommandButton1_Click()

Set MyRng = Sheets(3).[A1:A50] 'Set your range

Sheets(1).[A1:L1].Copy 'Copy the required range

For Each MyCell In MyRng 'Move through each cell in MyRng

If MyCell.Value = "Need header" Then

MyCell.PasteSpecial (xlPasteAll)

End If

Next MyCell 'Iterate 1 cell

End Sub

However if you were planning on putting the values from A1:L1 into un-
contiguous cells then an array would be the way to go for me anyhow.
The code below should give you a better idea of how to do this.

Option Explicit
Dim MyArray As Variant

Private Sub CommandButton1_Click()

MyArray = Sheets(1).[A1:L1] 'Pass your range to an array

Sheets(3).Activate 'Move to the sheet you want

'Pick the cell you want the array value to be
'placed in and indicate where that value is in
'the array, it might help to visually think of
'the array in this case as a spreadsheet with
'values in the first row only indicated by (1,...
'then the column value being indicated by the
'second number here its marked with *'s (1,*1*).

[A1].Value = MyArray(1, 1)
[B2].Value = MyArray(1, 2)
[C3].Value = MyArray(1, 3)
[D4].Value = MyArray(1, 4)
[E5].Value = MyArray(1, 5)
[F6].Value = MyArray(1, 6)
[G7].Value = MyArray(1, 7)
[H8].Value = MyArray(1, 8)
[I9].Value = MyArray(1, 9)
[J10].Value = MyArray(1, 10)
[K11].Value = MyArray(1, 11)
[L12].Value = MyArray(1, 12)

End Sub

I hope that you find something of use in these examples.

Laterz

S

Setting a variable to range of cell values
 

Oops - the code that is working is now listed below (I copied a prior version
previously):
--
Robert


"robs3131" wrote:

Thanks Incidental. That helps a bit, but I have some other questions
regarding the Array solution. I am able to make it work with the code below
(listed under "Code Works") -- is it possible to write this more efficiently
so that I don't need to specify what each cell individually needs? See the
code listed under "Would like to get code to work" below. Also, should I
specify a data type other than variant given that each cell in the array
contains no more than 10 letters, all text?

Code Works:

Sub copyheader()

Dim MyArray As Variant
Dim range1 As Range

MyArray = Sheets(2).[A1:L1]

With Sheets("Sheet3")
Set range1 = .Range("A1:A20")
For Each A In range1
If A.Value = "Need header" Then
A.Value = MyArray(1, 1)
A.Offset(0, 1).Value = MyArray(1, 2)
A.Offset(0, 2).Value = MyArray(1, 3)
A.Offset(0, 3).Value = MyArray(1, 4)
A.Offset(0, 4).Value = MyArray(1, 5)
A.Offset(0, 5).Value = MyArray(1, 6)
A.Offset(0, 6).Value = MyArray(1, 7)
A.Offset(0, 7).Value = MyArray(1, 8)
A.Offset(0, 8).Value = MyArray(1, 9)
A.Offset(0, 9).Value = MyArray(1, 10)
A.Offset(0, 10).Value = MyArray(1, 11)
A.Offset(0, 11).Value = MyArray(1, 12)
Else
End If
Next
End With

End Sub


Would like to get code to work:

Sub copyheader()

Dim MyArray As Variant
Dim range1 As Range

MyArray = Sheets(2).[A1:L1]

With Sheets("Sheet3")
Set range1 = .Range("A1:A20")
For Each A In range1
If A.Value = "Need header" Then
.Range(A, A.Offset(0, 11)) = .Range(MyArray(1, 1), MyArray(1, 12))
Else
End If
Next
End With

End Sub

--
Robert


"Incidental" wrote:

Hi Robert

Not sure if this will be any help to you or not but I thought I would
drop it in here just in case. from your code it looks like you are
just coping a range from one sheet and pasting it in the same format
in another sheet if the value in a specified range is "Need header" if
this is the case something like the code below should do what you
want.

Option Explicit
Dim MyCell, MyRng As Range

Private Sub CommandButton1_Click()

Set MyRng = Sheets(3).[A1:A50] 'Set your range

Sheets(1).[A1:L1].Copy 'Copy the required range

For Each MyCell In MyRng 'Move through each cell in MyRng

If MyCell.Value = "Need header" Then

MyCell.PasteSpecial (xlPasteAll)

End If

Next MyCell 'Iterate 1 cell

End Sub

However if you were planning on putting the values from A1:L1 into un-
contiguous cells then an array would be the way to go for me anyhow.
The code below should give you a better idea of how to do this.

Option Explicit
Dim MyArray As Variant

Private Sub CommandButton1_Click()

MyArray = Sheets(1).[A1:L1] 'Pass your range to an array

Sheets(3).Activate 'Move to the sheet you want

'Pick the cell you want the array value to be
'placed in and indicate where that value is in
'the array, it might help to visually think of
'the array in this case as a spreadsheet with
'values in the first row only indicated by (1,...
'then the column value being indicated by the
'second number here its marked with *'s (1,*1*).

[A1].Value = MyArray(1, 1)
[B2].Value = MyArray(1, 2)
[C3].Value = MyArray(1, 3)
[D4].Value = MyArray(1, 4)
[E5].Value = MyArray(1, 5)
[F6].Value = MyArray(1, 6)
[G7].Value = MyArray(1, 7)
[H8].Value = MyArray(1, 8)
[I9].Value = MyArray(1, 9)
[J10].Value = MyArray(1, 10)
[K11].Value = MyArray(1, 11)
[L12].Value = MyArray(1, 12)

End Sub

I hope that you find something of use in these examples.

Laterz

S

Setting a variable to range of cell values
 

Hi Robert

The code below should do what you want all I did was set another range
from the cell containing "Need header" to the offset column (0,11)
then I used an integer to iterate through the elements of the array
placing them in the cells.

Just a note of interest it is good practice to always declare your
variables as it leads to easier to read code and will help out with
any nasty spelling mistakes in you code that can take an age to find
manually.

Option Explicit
Dim MyArray As Variant
Dim range1 As Range
Dim A As Range
Dim MyCell, MyRng As Range
Dim i As Integer
Private Sub CommandButton1_Click()

MyArray = Sheets(2).[A1:L1]

With Sheets("Sheet3")

Set range1 = .Range("A1:A20")

For Each A In range1

i = 1

If A.Value = "Need header" Then

Set MyRng = Range(A, A.Offset(0, 11))'Set new range

For Each MyCell In MyRng

MyCell.Value = MyArray(1, i) 'Place array value in cell

i = i + 1 'Iterate Integer

Next MyCell 'Iterate Cell

End If

Next A

End With


End Sub


Hope this helps

S