Set a Range to a variable

  • Thread starter Thread starter Howard
  • Start date Start date
H

Howard

I am trying to set a group of four cell to three variables.

Dim sec1 As String (tried Value, Range and Variant also)
Dim sec2 As String
Dim sec3 As String

sec1 = Range("B3:E3").String (tried Value, Range and Variant also)
sec2 = Range("G3:J3").String
sec3 = Range("L3:O3").String

MsgBox sec1 & sec2 & sec3

The above produces every type miss match error available in Excel in the process.

When successful with that I then want to Randamize them something like
=Rand(sec1&" "&sec2&" "&sec3) back into three sets of four cells else where on the sheet.

Thanks,
Howard
 
Howard said:
I am trying to set a group of four cell to three variables.

Dim sec1 As String (tried Value, Range and Variant also)
Dim sec2 As String
Dim sec3 As String

sec1 = Range("B3:E3").String (tried Value, Range and Variant also)
sec2 = Range("G3:J3").String
sec3 = Range("L3:O3").String

MsgBox sec1 & sec2 & sec3

The above produces every type miss match error available in Excel in the process.

When successful with that I then want to Randamize them something like
=Rand(sec1&" "&sec2&" "&sec3) back into three sets of four cells else where on the sheet.

Thanks,
Howard
Click to expand...

..String does not exist as property

it must be
Range("B3:E3").Value

Because B3:E3 is a range with more than one cell so .Value returns an array


dim sec1 as variant

or simply

dim sec1
if you are to sure what type it should be.



MsgBox sec1 & sec2 & sec3 will not work at all because sec variables
are arrays


dim msg as string
dim s as string

for each s in sec1
msg = msg + s
next s


MsgBox msg
 
I am trying to set a group of four cell to three variables.



Dim sec1 As String (tried Value, Range and Variant also)

Dim sec2 As String

Dim sec3 As String



sec1 = Range("B3:E3").String (tried Value, Range and Variant also)

sec2 = Range("G3:J3").String

sec3 = Range("L3:O3").String



MsgBox sec1 & sec2 & sec3



The above produces every type miss match error available in Excel in the process.



When successful with that I then want to Randamize them something like

=Rand(sec1&" "&sec2&" "&sec3) back into three sets of four cells else where on the sheet.



Thanks,

Howard
Click to expand...

Thanks Witek,

I'll take that and get to work on it.

Thanks again.

Howard
 
'Declare your variables
Dim sec1, sec2, sec3

Set sec1 = Range("B3:E3")
Set sec2 = Range("G3:J3")
Set sec3 = Range("L3:O3")
'This creates 3 1Row,4Col 2D arrays


'Do something with them
Dim v1, v2, v3, n&

For n = LBound(sec1, 2) To UBound(sec1, 2)
MsgBox sec1(1, n)
Next

For n = LBound(sec2, 2) To UBound(sec2, 2)
MsgBox sec2(1, n)
Next

For n = LBound(sec3, 2) To UBound(sec3, 2)
MsgBox sec3(1, n)
Next

HTH

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
I originally intended to use v1,v2,v3 to demonstrate how to manipulate
the data but opted to go with MsgBox for simplicity. The main idea is
to show how to load a range into a variable (type=Variant), AND how to
access each piece of data.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
GS should have stuck with his original idea...
'Declare your variables
Dim sec1, sec2, sec3
'Load the ranges
Set sec1 = Range("B3:E3") MsgBox sec1.Address
Set sec2 = Range("G3:J3") MsgBox sec2.Address
Set sec3 = Range("L3:O3")
Click to expand...
MsgBox sec3.Address
'This creates 3 1Row,4Col 2D arrays
Click to expand...
Dim v1, v2, v3
v1 = Range("B3:E3")
v2 = Range("G3:J3")
v3 = Range("L3:O3")
'Do something with them
Dim n&
Click to expand...
For n = LBound(v1, 2) To UBound(v1, 2)
MsgBox v1(1, n)
NextFor n = LBound(v2, 2) To UBound(v2, 2)
MsgBox v2(1, n)
NextFor n = LBound(v3, 2) To UBound(v3, 2)
MsgBox v3(1, n)
Next
HTH
Click to expand...

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
'Declare your variables

Dim sec1, sec2, sec3



Set sec1 = Range("B3:E3")

Set sec2 = Range("G3:J3")

Set sec3 = Range("L3:O3")

'This creates 3 1Row,4Col 2D arrays





'Do something with them

Dim v1, v2, v3, n&



For n = LBound(sec1, 2) To UBound(sec1, 2)

MsgBox sec1(1, n)

Next



For n = LBound(sec2, 2) To UBound(sec2, 2)

MsgBox sec2(1, n)

Next



For n = LBound(sec3, 2) To UBound(sec3, 2)

MsgBox sec3(1, n)

Next



HTH



--

Garry



Free usenet access at http://www.eternal-september.org

Classic VB Users Regroup!

comp.lang.basic.visual.misc

microsoft.public.vb.general.discussion
Click to expand...

Thanks Garry,

More good info for me to work with. I'll gather this up with what Witek posted and get with it.

Appreciate it.

Howard
 
I am trying to set a group of four cell to three variables.



Dim sec1 As String (tried Value, Range and Variant also)

Dim sec2 As String

Dim sec3 As String



sec1 = Range("B3:E3").String (tried Value, Range and Variant also)

sec2 = Range("G3:J3").String

sec3 = Range("L3:O3").String



MsgBox sec1 & sec2 & sec3



The above produces every type miss match error available in Excel in the process.



When successful with that I then want to Randamize them something like

=Rand(sec1&" "&sec2&" "&sec3) back into three sets of four cells else where on the sheet.



Thanks,

Howard
Click to expand...

This code by Witek works just great to take the 'verbage' from the three 4-cell arrays and display it in a Msgbox.

I thought I could do the next task once I got this far but I am falling short.

I want to take the arrays that are displayed perfectly in the msgbox and have the ability to put them into cells of my choice on the sheet, lets just say in
B10:E10, G10:J10, L10:O10.

So with code/loop put them in the the cells just mentioned and follow in each row below the next with the scheme as listed here. Where I would end up with all six possible combinations in consecutive rows.

I would appreciate your help.

Howard

123
132
213
231
312
321


Option Explicit
Sub ThreeSecs()
'BY: Witek

Dim sec1 As Variant
Dim sec2 As Variant
Dim sec3 As Variant

Dim msg As String
Dim s As Variant

sec1 = Range("B3:E3").Value
sec2 = Range("G3:J3").Value
sec3 = Range("L3:O3").Value

For Each s In sec1 ' 2 and 3
msg = msg + s
Next s
MsgBox msg

End Sub
 
If I understand correctly, you want a 6row by 12col table containing
the results. If so then try...

Sub CombineData()
Dim sec1, sec2, sec3, n&, lRows&, lCols&, vaDataOut()

'Load ranges into arrays
sec1 = Range("$B$3:$E$3")
sec2 = Range("$G$3:$J$3")
sec3 = Range("$L$3:$O$3")

'Get the number of rows/cols for the output
lRows = (UBound(sec1, 1) + UBound(sec2, 1) + UBound(sec3, 1)) * 2
lCols = UBound(sec1, 2) + UBound(sec2, 2) + UBound(sec3, 2)

'Resize the output array
ReDim vaDataOut(1 To lRows, 1 To lCols)

'Load the output array
For n = 1 To UBound(sec1, 2) '//fortunately they're all the same
'sec1
vaDataOut(1, n) = sec1(1, n): vaDataOut(2, n) = sec1(1, n)
vaDataOut(3, n + 4) = sec1(1, n): vaDataOut(4, n + 8) = sec1(1, n)
vaDataOut(5, n + 4) = sec1(1, n): vaDataOut(6, n + 8) = sec1(1, n)

'sec2
vaDataOut(1, n + 4) = sec2(1, n): vaDataOut(2, n + 8) = sec2(1, n)
vaDataOut(3, n) = sec2(1, n): vaDataOut(4, n) = sec2(1, n)
vaDataOut(5, n + 8) = sec2(1, n): vaDataOut(6, n + 4) = sec2(1, n)

'sec3
vaDataOut(1, n + 8) = sec3(1, n): vaDataOut(2, n + 4) = sec3(1, n)
vaDataOut(3, n + 8) = sec3(1, n): vaDataOut(4, n + 4) = sec3(1, n)
vaDataOut(5, n) = sec3(1, n): vaDataOut(6, n) = sec3(1, n)
Next

'Dump the data back into the wks
Range("$B$10").Resize(lRows, lCols) = vaDataOut
End Sub

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
I am trying to set a group of four cell to three variables.



Dim sec1 As String (tried Value, Range and Variant also)

Dim sec2 As String

Dim sec3 As String



sec1 = Range("B3:E3").String (tried Value, Range and Variant also)

sec2 = Range("G3:J3").String

sec3 = Range("L3:O3").String



MsgBox sec1 & sec2 & sec3



The above produces every type miss match error available in Excel in the process.



When successful with that I then want to Randamize them something like

=Rand(sec1&" "&sec2&" "&sec3) back into three sets of four cells else where on the sheet.



Thanks,

Howard
Click to expand...

Hi GS,

Beautiful, gorgeous, brilliant & GOLD PLATED!!!

Smack on. Can't tell you how much I appreciate it!!!

Regards,
Howard
 
I am trying to set a group of four cell to three variables.



Dim sec1 As String (tried Value, Range and Variant also)

Dim sec2 As String

Dim sec3 As String



sec1 = Range("B3:E3").String (tried Value, Range and Variant also)

sec2 = Range("G3:J3").String

sec3 = Range("L3:O3").String



MsgBox sec1 & sec2 & sec3



The above produces every type miss match error available in Excel in the process.



When successful with that I then want to Randamize them something like

=Rand(sec1&" "&sec2&" "&sec3) back into three sets of four cells else where on the sheet.



Thanks,

Howard
Click to expand...

GS,

My wife came out of the back room wondering what I was whooping about...

One more quick question if I may.

sec1 = Range("$B$3:$E$3") Column F blank
sec2 = Range("$G$3:$J$3") Column K blank
sec3 = Range("$L$3:$O$3")

Are there a few characters in the code I could change to leave column F and K blank in the 6 by 12 table to do a 6 by 14 table. Did not occur to me in my original post. Sorry.

If it requires a full re-write I'd say forget it, I can live with this an be a happy camper.

Howard
 
Well.., that's what I did the first time I tried it because I used
"B3:O3" as the range address. This didn't work out so well in terms of
a 6 x 12 table so I revised it as posted, putting each range into
separate arrays.

I think using separate arrays for the sections is the best approach,
IMO, and so here's a revision that includes your empty columns...

Sub CombineData2()
Dim sec1, sec2, sec3, n&, lRows&, lCols&, vaDataOut()
Const iStep1% = 5: Const iStep2% = 10

'Load ranges into arrays
sec1 = Range("$B$3:$E$3")
sec2 = Range("$G$3:$J$3")
sec3 = Range("$L$3:$O$3")

'Get the number of rows/cols for the output
lRows = (UBound(sec1, 1) + UBound(sec2, 1) + UBound(sec3, 1)) * 2
lCols = UBound(sec1, 2) + UBound(sec2, 2) + UBound(sec3, 2) + 2

'Resize the output array
ReDim vaDataOut(1 To lRows, 1 To lCols)

'Load the output array
For n = 1 To UBound(sec1, 2) '//fortunately they're all the same
'sec1
vaDataOut(1, n) = sec1(1, n): vaDataOut(2, n) = sec1(1, n)
vaDataOut(3, n + iStep1) = sec1(1, n): vaDataOut(4, n + iStep2) =
sec1(1, n)
vaDataOut(5, n + iStep1) = sec1(1, n): vaDataOut(6, n + iStep2) =
sec1(1, n)

'sec2
vaDataOut(1, n + iStep1) = sec2(1, n): vaDataOut(2, n + iStep2) =
sec2(1, n)
vaDataOut(3, n) = sec2(1, n): vaDataOut(4, n) = sec2(1, n)
vaDataOut(5, n + iStep2) = sec2(1, n): vaDataOut(6, n + iStep1) =
sec2(1, n)

'sec3
vaDataOut(1, n + iStep2) = sec3(1, n): vaDataOut(2, n + iStep1) =
sec3(1, n)
vaDataOut(3, n + iStep2) = sec3(1, n): vaDataOut(4, n + iStep1) =
sec3(1, n)
vaDataOut(5, n) = sec3(1, n): vaDataOut(6, n) = sec3(1, n)
Next

'Dump the data back into the wks
Range("$B$10").Resize(lRows, lCols) = vaDataOut
End Sub

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
If you want to be able to have the resulting table inserted at any
'selected' location other than "B10" then revise the following line
Range("$B$10").Resize(lRows, lCols) = vaDataOut
Click to expand...

to this...

ActiveCell.Resize(lRows, lCols) = vaDataOut

...and make sure you have it selected before running the macro.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
I am trying to set a group of four cell to three variables.



Dim sec1 As String (tried Value, Range and Variant also)

Dim sec2 As String

Dim sec3 As String



sec1 = Range("B3:E3").String (tried Value, Range and Variant also)

sec2 = Range("G3:J3").String

sec3 = Range("L3:O3").String



MsgBox sec1 & sec2 & sec3



The above produces every type miss match error available in Excel in the process.



When successful with that I then want to Randamize them something like

=Rand(sec1&" "&sec2&" "&sec3) back into three sets of four cells else where on the sheet.



Thanks,

Howard
Click to expand...

Have to go with PLATIMUN PLATED & DIAMOND STUDDED ON THIS ONE!

Thanks much.

Howard
 
Back
Top