Setting a variable to range of cell values

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi all,

I'm trying to set a variable called "header" to the values that currently
occupy range(A1:L1) on Sheet2. I then want to go to Sheet3 and paste the
values copied into various locations in the sheet. I was thinking I should
be using an array to do this, but honestly afer looking through various help
topics on arrays, it just wasn't clear enough. Below is my "attempted"
code...though this does not work, I'm hoping you see what I'm trying to do
here. Thanks for your help!

Sub copyheader()

Dim header(0, 11) As String 'there will only be text values stored in this
variable
Dim range1 As Range

With Sheets("Sheet2")
Set header = .Range("A1:L1")
End With

With Sheets("Sheet3")
For Each A In range1
If A.Value = "Need header" Then
A.Value = header.PasteSpecial(xlPasteAll)
Else
End If
Next
End With


End Sub
 
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
 
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 said:
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
Click to expand...
 
Oops - the code that is working is now listed below (I copied a prior version
previously):
--
Robert


robs3131 said:
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:
Click to expand...
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 said:
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
Click to expand...
Click to expand...
 
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Object variable or with Block variable not set 9
My Never ending ARRAY code problems 18
ERROR 400 on identical codes different range 8
Populating a range of empty cells with usernames 4
Find string from column A in Range("B2:F7") list the header of that column/s 5
VBA set thick borders around non empty cells in range 0
Array code only works on selected sheet 14
VBA Excel - Select a value from one cell in Excel to update a SQL table 1

Back
Top