I'm fairly new to VBA and am trying to add a range to an array which
stores various information (so that I can have a simple image
associated with each piece of data). I can do this without problem,
however, when I then call upon this range through the array the output
I get is not formatted as the initial range is.

Currently my code looks like this:

Sub Load()
Dim i As Integer
Dim array(13, 4)

For i = 1 To 13
'Various inputs into array

array(i, 1) = Worksheets("Sheet1").Cells(37 + i, 2).Value
array(i, 2) = Worksheets("Sheet1").Cells(37 + i, 4).Value
array(i, 3) = Worksheets("Sheet1").Cells(37 + i, 5).Value

Next i

For i = 1 To 13
'The range which I hope will be the simple "image"

array(i, 4) = Worksheets("Sheet1").Range(Cells(2, 8*i -6), Cells(8,
8*i)).Value

Next i

'This range when outputed is not in the format I'd like
Worksheets("Deck").Range("N38:T44").Value = Cards(2, 4)

End Sub

I'd appreciate it if anyone could give me a hand with this.

Thanks for your time,

Mike

Mike,

Try using .Text instead of .Value.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Mike" wrote in message
m...
I'm fairly new to VBA and am trying to add a range to an array which
stores various information (so that I can have a simple image
associated with each piece of data). I can do this without problem,
however, when I then call upon this range through the array the output
I get is not formatted as the initial range is.

Currently my code looks like this:

Sub Load()
Dim i As Integer
Dim array(13, 4)

For i = 1 To 13
'Various inputs into array

array(i, 1) = Worksheets("Sheet1").Cells(37 + i, 2).Value
array(i, 2) = Worksheets("Sheet1").Cells(37 + i, 4).Value
array(i, 3) = Worksheets("Sheet1").Cells(37 + i, 5).Value

Next i

For i = 1 To 13
'The range which I hope will be the simple "image"

array(i, 4) = Worksheets("Sheet1").Range(Cells(2, 8*i -6), Cells(8,
8*i)).Value

Next i

'This range when outputed is not in the format I'd like
Worksheets("Deck").Range("N38:T44").Value = Cards(2, 4)

End Sub

I'd appreciate it if anyone could give me a hand with this.

Thanks for your time,

Mike

This construct is flawed:

array(i, 4) = Worksheets("Sheet1").Range(Cells(2, 8*i -6), Cells(8,
8*i)).Value

the unqualified cells refers to the active sheet. If you are not getting an
error, then the activesheet is Sheet1, but it makes no sense to do it half
way. Either qualify all or qualify none.

Worksheets("Sheet1")
array(i, 4) = .Range(.Cells(2, 8*i -6), _
.Cells(8,8*i)).Value
End With

Also, Bob's advice to use Text instead of Value will work for your first
loop since you are putting a single cell's value in an single array element.
It will not work with the above, however, where you are assigning an array
to an array element. You will need to create an additional element to hold
the formatting

Dim array(13, 5)


Worksheets("Sheet1")
array(i, 4) = .Range(.Cells(2, 8*i -6), _
.Cells(8,8*i)).Value
Array(i,5) = .Range(.Cells(2,8*i-6), _
.Cells(8,8*i)).Format
End With


Worksheets("Deck").Range("N38:T44").Value = Array(2, 4)
Worksheets("Deck").Range("N38:T44").Format = Array(2, 5)


--
Regards,
Tom Ogilvy


"Mike" wrote in message
m...
I'm fairly new to VBA and am trying to add a range to an array which
stores various information (so that I can have a simple image
associated with each piece of data). I can do this without problem,
however, when I then call upon this range through the array the output
I get is not formatted as the initial range is.

Currently my code looks like this:

Sub Load()
Dim i As Integer
Dim array(13, 4)

For i = 1 To 13
'Various inputs into array

array(i, 1) = Worksheets("Sheet1").Cells(37 + i, 2).Value
array(i, 2) = Worksheets("Sheet1").Cells(37 + i, 4).Value
array(i, 3) = Worksheets("Sheet1").Cells(37 + i, 5).Value

Next i

For i = 1 To 13
'The range which I hope will be the simple "image"

array(i, 4) = Worksheets("Sheet1").Range(Cells(2, 8*i -6), Cells(8,
8*i)).Value

Next i

'This range when outputed is not in the format I'd like
Worksheets("Deck").Range("N38:T44").Value = Cards(2, 4)

End Sub

I'd appreciate it if anyone could give me a hand with this.

Thanks for your time,

Mike

Tom,

Thanks for the help with qualifying the cells.

When I try and run the recommended code I get an "Object doesn't support
this property or method" error at the line:

Array(i,5) = .Range(.Cells(2,8*i-6), .Cells(8,8*i)).Format

Can you suggest why this would be?

Regards,

Mike

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

My bad (got myself confused)

You can pick up formulas that way, but not formats. You will need to loop
for the formats

With Worksheets("Sheet1")
set rng = .Range(.Cells(2, 8*i -6), _
.Cells(8,8*i))
End With
Array(i, 4) = rng.Value
' use value just to establish the array
Array(i,5) = rng.value
' now replace with Numberformats
m = 1 : n = 1
for k = lbound(array(i,5),1) to ubound(array(i,5),1)
for l = lbound(array(i,5),2) to ubound(array(i,5),2)
array(i,5)(k,l) = rng(m,n).NumberFormat
n = n + 1
Next
m = m + 1
Next
End With


Worksheets("Deck").Range("N38:T44").Value = Array(2, 4)
for i = 1 to 7
for j = 1 to 7
Worksheets("Deck").Range("N38:T44")(i,j).NumberFor mat = Array(2, 5)(i,j)
Next
Next

--
Regards,
Tom Ogilvy


"Mike Stout" wrote in message
...
Tom,

Thanks for the help with qualifying the cells.

When I try and run the recommended code I get an "Object doesn't support
this property or method" error at the line:

Array(i,5) = .Range(.Cells(2,8*i-6), .Cells(8,8*i)).Format

Can you suggest why this would be?

Regards,

Mike

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!