Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Set a Range to a variable
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Set a Range to a variable
Howard wrote:
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 ..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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Set a Range to a variable
On Tuesday, February 12, 2013 5:32:21 PM UTC-8, Howard wrote:
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 Thanks Witek, I'll take that and get to work on it. Thanks again. Howard |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Set a Range to a variable
'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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Set a Range to a variable
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Set a Range to a variable
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") MsgBox sec3.Address 'This creates 3 1Row,4Col 2D arrays Dim v1, v2, v3 v1 = Range("B3:E3") v2 = Range("G3:J3") v3 = Range("L3:O3") 'Do something with them Dim n& For n = LBound(v1, 2) To UBound(v1, 2) MsgBox v1(1, n) Next For n = LBound(v2, 2) To UBound(v2, 2) MsgBox v2(1, n) Next For n = LBound(v3, 2) To UBound(v3, 2) MsgBox v3(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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Set a Range to a variable
On Tuesday, February 12, 2013 6:44:48 PM UTC-8, GS wrote:
'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 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Set a Range to a variable
On Tuesday, February 12, 2013 5:32:21 PM UTC-8, Howard wrote:
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 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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Set a Range to a variable
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Set a Range to a variable
On Tuesday, February 12, 2013 5:32:21 PM UTC-8, Howard wrote:
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 Hi GS, Beautiful, gorgeous, brilliant & GOLD PLATED!!! Smack on. Can't tell you how much I appreciate it!!! Regards, Howard |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Set a Range to a variable
Okay!!<hehe I appreciate that exuberant feedback very much!
Best wishes in your endeavors... -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Set a Range to a variable
On Tuesday, February 12, 2013 5:32:21 PM UTC-8, Howard wrote:
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 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 |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Set a Range to a variable
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 |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Set a Range to a variable
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 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 |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
Set a Range to a variable
On Tuesday, February 12, 2013 5:32:21 PM UTC-8, Howard wrote:
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 Have to go with PLATIMUN PLATED & DIAMOND STUDDED ON THIS ONE! Thanks much. Howard |
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
Set a Range to a variable
You're very welcome!
-- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Range to VLOOKUP as a Variable (range in another file) | Excel Programming | |||
select range and put range address in variable | Excel Programming | |||
Macro to copy a specified range to a variable range | Excel Programming | |||
setting a range variable equal to the value of a string variable | Excel Programming | |||
Problem trying to us a range variable as an array variable | Excel Programming |