Post my named ranges in column A

  • Thread starter Thread starter L. Howard
  • Start date Start date
L

L. Howard

GEN, COMM, MAJ etc. are dynamic named ranges on sheet 1, Workbook in scope.
Refers To formula looks like this:

=OFFSET(Sheet1!$T$2,0,0,COUNTA(Sheet1!$T$2:$T$50),1)

Each range has a header and four entries for testing but when working each will be a different number of rows.

I need them to list each range in column A starting at A2 and a one row space between each range.

The code here lists only the header of each named range in column A and no space between them.

I tried to put a Resize in the posting line but that did not work either, I just used 5 rows by 1 column but it too just posted the headers.

The commented out line did nothing.

I cannot remember the step I am missing to post the entire named range instead of just the header.

Thanks.
Howard



Sub RankArray()

Dim Rank_array(9)
Dim i As Long

Rank_array(0) = Range("GEN")
Rank_array(1) = Range("COMM")
Rank_array(2) = Range("MAJ")
Rank_array(3) = Range("CPT")
Rank_array(4) = Range("LT")
Rank_array(5) = Range("MSGT")
Rank_array(6) = Range("SGT")
Rank_array(7) = Range("CPL")
Rank_array(8) = Range("PVT")

For i = LBound(Rank_array) To UBound(Rank_array)
Range("A" & Rows.Count).End(xlUp)(2) = Rank_array(i)

'Range("A" & Cells(Rows.Count, "A").End(xlUp).Row) _
.End(xlUp)(2).Value = Rank_array(i)

Next 'i

End Sub
 
Hi Howard,

Am Fri, 25 Jul 2014 04:14:29 -0700 (PDT) schrieb L. Howard:
GEN, COMM, MAJ etc. are dynamic named ranges on sheet 1, Workbook in scope.
Refers To formula looks like this:

=OFFSET(Sheet1!$T$2,0,0,COUNTA(Sheet1!$T$2:$T$50),1)
Click to expand...

you have to add your other range names. I only defined 3 of them:

Sub RankArray()

Dim Rank_array As Variant, arrOut As Variant
Dim Rank_Str As String
Dim i As Long
Dim dest As Range

Rank_Str = "GEN, COMM, MAJ"
Rank_array = Split(Rank_Str, ", ")

For i = LBound(Rank_array) To UBound(Rank_array)
Set dest = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Offset(2, 0)
arrOut = Range(Rank_array(i))
dest.Resize(rowsize:=UBound(arrOut)) = arrOut
Next 'i

End Sub


Regards
Claus B.
 
Hi Howard,



Am Fri, 25 Jul 2014 04:14:29 -0700 (PDT) schrieb L. Howard:







you have to add your other range names. I only defined 3 of them:



Sub RankArray()



Dim Rank_array As Variant, arrOut As Variant

Dim Rank_Str As String

Dim i As Long

Dim dest As Range



Rank_Str = "GEN, COMM, MAJ"

Rank_array = Split(Rank_Str, ", ")



For i = LBound(Rank_array) To UBound(Rank_array)

Set dest = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Offset(2, 0)

arrOut = Range(Rank_array(i))

dest.Resize(rowsize:=UBound(arrOut)) = arrOut

Next 'i



End Sub





Regards

Claus B.

--

Vista Ultimate / Windows7

Office 2007 Ultimate / 2010 Professional
Click to expand...

Thanks Claus, that now looks familiar.

Works good.

Appreciate it.

Regards,
Howard
 
I don't understand why you're listing the dynamic range names in colA!

Starting off, delete the global scope names unless they're absolutely
necessary!

With the headers, put them in row 1 as fieldnames for the underlying
data and give them each defined names with local scope...

name: sheet1!Gen_Hdr
refersto: =$A$1

...for example of the 1st header. You can create a dynamic range based
on this as follows...

name: sheet1!GenData
refersto: =offset(gen_hdr,1,0,counta($A:$A)-1,1)

...where this will result in a named list of contiguous entries in colA
that does not include the header.

Assuming the next header is colB...

name: sheet1!Comm_Hdr
refersto: =$B$1

...and define a dynamic range based on this as was done for colA...

name: sheet1!CommData
refersto: =offset(comm_hdr,1,0,counta($B:$B)-1,1)

Note that it's now imperative that the data in every row in each
dynamic named col be contiguous to include all entered data. Use a
placeholder for fields that are empty, such as a single space
character.

You can also create a horizontal dynamic named range for the headers as
follows...

name: sheet1!Hdrs
refersto: =offset(Gen_Hdr,0,0,1,counta(1:1))

...so it updates same as the fields when you add/remove items. You can
also create a dynamic range name that includes all the data...

To include headers:
name: sheet1!DataTable
refersto: =offset(hdrs,0,0,counta(GenData)+1)

To exclude headers:
name: sheet1!TableData
refersto: =offset(GenData,0,0,,counta(hdrs))

...where it assumes colA contains 'primary key' values so the scope of
the table is defined as to its record count. If 'GEN' is not a 'primary
key' field then rearrange the order of the headers -OR- use the defined
name of any field that will 'NEVER' be blank.


Now you can 'dump' any range into a 2D array and loop it in a normal
manner to access the data...

Dim vHdrs, vItem, n&
vHdrs = Range("Hdrs")

...which results a 1 row by 9 col array...

For n = 1 To UBound(vHdrs, 2)
Debug.Print vHdrs(1, n) '//print the fieldname
Range(vHdrs(1, n)).Select '//select the range of data -OR-...
For Each vItem In Range(vHdrs(1, n)) '//do something with the data
Debug.Print vItem.Address
Next 'vItem
Next 'n

In the case of not wanting to access individual fieds of data by name,
you can avoid needing to use placeholders as described above (so long
as at least 1 field will never contain blank data) by accessing
individual fields from an array of 'TableData' via Application.Index...

Dim vData, vField, n&, k&
vData = Range("TableData") '//excludes hdrs
'vData = Range("DataTable") '//includes hdrs
For n = 1 To UBound(vData, 2)
'Put each field into an array
vField = Application.Index(vData, 0, n)
For k = 1 To UBound(vField)
Debug.Print vField(k, 1)
Next 'k
Next 'n

If you use code to access individual fields specifically, you can use
an enum so you always get the correct field of data...

Enum Fieldnames
Gen = 1: Comm: Maj: Cpt: Lt: Msgt: Sgt: Cpl: Pvt
End Enum
Public vTableData '//give this global scope so it can be accessed
'from anywhere in code

...where the enum order follows the headers order as listed in the 1st
row of the worksheet. Note that specifying the start number is needed
so the enum matches 1-based array indexing!

Load vTableData at startup and access it like this...

Sub List_CommData()
Dim vData, n&
vData = Application.Index(vTableData, 0, Fieldnames.Comm)
For n = 1 To UBound(vData)
Debug.Print vData(n, 1)
Next 'n
End Sub 'List_CommData

I prefer to use dynamic names for headers ("Hdrs"), all table data
("DataTable"), and just table data ("TableData") and use an enum for
the fieldnames. This reduces the number of defined names needed for
processing via code. For example, the data table might use defined
names in formulas to ref specific col values. These would be defined as
col-absolute/row-relative (local scope), with names that reflect the
headings so user know which fields are being used in the formula. Thus
there's really no need to duplicate that with dynamic range names for
each field (though you could use those in formulas if they are local
scope).

--
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 don't understand why you're listing the dynamic range names in colA!
Click to expand...

Hi Garry,

What is going to column A is a copy of the named ranges data after the usermakes changes to those ranges.

The actual named ranges are off screen. By using two DV drop downs and some code, data is move FROM one range TO another, then reposted into column Aand sheet 2 column A. So far it is working mostly as it needs to. Not tosay that improvements could be made, but for me to throw all the weight ofyour suggestions into this would be pointless as it is far above my pay grade.

Some error checking would most likely make good sense.

Here is a link if you care to take a look, plus there is a link on the sheet to the original thread, I suppose it will work from a linked workbook.


https://www.dropbox.com/s/fe2w5jouk05b5e4/Xbox Game Drop Box.xlsm


Howard
 
Hi Garry,

What is going to column A is a copy of the named ranges data after
the user makes changes to those ranges.

The actual named ranges are off screen. By using two DV drop downs
and some code, data is move FROM one range TO another, then reposted
into column A and sheet 2 column A. So far it is working mostly as
it needs to. Not to say that improvements could be made, but for me
to throw all the weight of your suggestions into this would be
pointless as it is far above my pay grade.

Some error checking would most likely make good sense.

Here is a link if you care to take a look, plus there is a link on
the sheet to the original thread, I suppose it will work from a
linked workbook.


https://www.dropbox.com/s/fe2w5jouk05b5e4/Xbox Game Drop Box.xlsm


Howard
Click to expand...

Ok.., I'll have a look at it over the weekend!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
Ok.., I'll have a look at it over the weekend!
Click to expand...

Hi Garry,

FYI, was able twist OP's arm and get a link to the workbook.

Looks like a VLOOKUP and some pretty simple Input Box code will solve the issue.

Time permitting I would be glad to see an example of the code attributes you described.

Knowing it is not a solution to a project, but rather "Here are the techniques and naming procedures"

Maybe stay with the premise of making changes to the dynamic ranges and then posting all the ranges to column A.

Where the good info would be seeing it on the sheet and in the Name Manager etc.

Again, if time permits

Regards,
Howard
 
Time permitting I would be glad to see an example of the code
attributes you described.

Knowing it is not a solution to a project, but rather "Here are the
techniques and naming procedures"

Maybe stay with the premise of making changes to the dynamic ranges
and then posting all the ranges to column A.

Where the good info would be seeing it on the sheet and in the Name
Manager etc.
Click to expand...

After looking at the file you posted a link to on Friday, I realized it
was nothing even remotely like what I read you were trying to do! Where
can I access the link to your OP's file?

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
After looking at the file you posted a link to on Friday, I realized it

was nothing even remotely like what I read you were trying to do! Where

can I access the link to your OP's file?
Click to expand...

Hi Gary,

The OP's file as I got it.

https://www.dropbox.com/s/jxebj44f7iyiwam/Book1-1 Xbox Game.xlsm



The file using VLOOKUP I sent back to the OP.

https://www.dropbox.com/s/k3r5sly8efstba9/Xbox Game Vlookup-1.xlsm


On Member Info sheet ctrl + shift + Q for an input box.

Select a Gametag name in column B to move to another Rand > OK.

Select a cell in column B adjacent to the new Rank > OK.

Name is moved from old cell to new cell AND on sheet Clan Tag the name is moved also in column B and the "Call To Duty App Name" in column C is moved with it using VLOOKUP.

If you read the entire thread you can see the information is scant and quite unclear about what the final out come needed to be.

This is the thread title in Mr Excel.

"Sharing information between Sheets"

I'm thinking it is not worthy of you time.

Howard
 
Back
Top