Working with Row and Columns inside a named range

Festus Hagen · Aug 16, 2011

Hi All,

VBA, Excel97

I'm sure this has been asked a zillion times yet I am unable to find the answer, I suspect I'm searching for the wrong terms ...
Highly possible I'm going about this the wrong way as well!

Locating an intersecting cell within a range using row and column number in relation to the upper left corner of the named range.

Explaination:
Lets say ya have a named range that covers cells X50:AH60.
X50 = Col1, Row1
AH60 = Col11, Row11

Using Row number and Column number how do you locate the cell in Row3,Column3 (in relation to the upperleft of the named range) ...
This range moves around, so using worksheet rows and columns doesn't work ...

Here is what I am doing, In case I am going about this the wrong way...

I have one table 'Data', with column headers and row headers ... Rows=Names, Columns=Dates
Another table 'NewData' with NEW data, that needs to be placed in the 'Data' table in the appropriate cells.
The 'NewData' table has one Date cell (each 'NewData' table import is a different date), and two columns containing: Name and Value.

Using the Date from table 'NewData' to find the column in table 'Data' ...
Using the Name from table 'NewData' to find the row in table 'Data' ...
Thus the intersection as to where 'NewData!Value' can be placed!

Thanks all

-Enjoy
fh <font color="#FF0000">:</font> )_~

Steve=True · Aug 16, 2011

You can always do index/Match formulas within your named range to return the row/column

you can also use index/match in combination with Offset the area you want./need.

Steve=True
www.exceldashboardtemplates.com

Gary McMaster · Aug 16, 2011

Hi Festus,

The cells in a named range (or any other range specification) are indexed sequentially from top left to bottom right. The cell (you specified) in the 3rd row and 3rd column would be the 25th cell in the group. Two rows of 11 + 3 for the column. You can use "Offset" from the first cell or you can do the math yourself based on the number of rows and columns.

Here's a sample. I hope it helps.

Gary

Code:

Public Sub Test()

'Assumes existing named range "MyRange" from X50:AH60
Debug.Print ActiveSheet.Range("MyRange").Rows.Count & vbTab & ActiveSheet.Range("MyRange").Columns.Count

'Cell index #1, top left
ActiveSheet.Range("MyRange").Cells(1).Interior.ColorIndex = 3

'Cell index cells.count, bottom right
ActiveSheet.Range("MyRange").Cells(ActiveSheet.Range("MyRange").Cells.Count).Interior.ColorIndex = 4

'Using offset
ActiveSheet.Range("MyRange").Cells(1).Offset(2, 2).Interior.ColorIndex = 5 '(2,2) because zero counts as 1st row & col

'or: Doing the math
Dim iRow As Integer
Dim iCol As Integer
Dim iCellIndex As Integer

iRow = 3
iCol = 3

iCellIndex = (iRow - 1) * ActiveSheet.Range("MyRange").Columns.Count + iCol

ActiveSheet.Range("MyRange").Cells(iCellIndex).Value = "Row 3, Col 3"

End Sub

solitude · Aug 16, 2011

Like Steve said you can simply use the NameRange in place of Array/Reference in pretty much all the in built excel functions.

If your name range is say "CData", then to find the 3,3 value you can use

Code:

=Index(CData,3,3)

and this is by default with respect to the top left cell, as you wanted! Similarly for Match, VLookup etc.

Festus Hagen · Aug 16, 2011

Does this make it clearer ...

An example of imported data:
"TableNewData"
<html><body><table border="1" cellpadding="4" cellspacing="1"><tr><td>3/1/2011</td></tr><tr><td>Value</td><td>Name</td></tr><tr><td>43</td><td>Name One</td></tr><tr><td>96</td><td>Name Three</td></tr><tr><td>78</td><td>Name Six</td></tr></table></body></html>
The Date is always going to be in cell A1
Dynamic Named Ranges (the number of rows/names varies:

TableImportValueRange = The Value column Data Area
TableImportNameRange = The Name column Data Area

I need to apply the above imported data to the following table ...
This is a frequent process that is currently done by hand, I also have many many years of history I would love to pull in.

Thus in VBA, I would need to loop over TableImportNameRange looking up that name (CurrentName) in TableDataNameRange to find the correct row in 'TableData' (Example:=1,3,6) then lookup the date from 'TableNewData' Cell(A1) in TableDataDateRange to find the correct column in 'TableData' (Example:=Column 3), then apply CurrentName.Value (from 'TableImportData') to the column row intersection just found in 'TableData'!

"TableData" (The upper left of this table does move, thus the named range use)
<html><body><table border="1" cellpadding="4" cellspacing="1"><tr><td> </td><td>2/19/2011</td><td>2/20/2011</td><td>3/1/2011</td><td>3/7/2011</td><td>3/12/2011</td></tr><tr><td>Name One</td><td> </td><td> </td><td>43</td><td> </td><td> </td></tr><tr><td>Name Two</td><td> </td><td> </td><td> </td><td> </td><td> </td></tr><tr><td>Name Three</td><td> </td><td> </td><td>96</td><td> </td><td> </td></tr><tr><td>Name Four</td><td> </td><td> </td><td> </td><td> </td><td> </td></tr><tr><td>Name Five</td><td> </td><td> </td><td> </td><td> </td><td> </td></tr><tr><td>Name Six</td><td> </td><td> </td><td>78</td><td> </td><td> </td></tr></table></body></html>
Dynamic Named Ranges (Names and Dates are added):

TableDataRange = The Data Area
TableDataDateRange = The Date Row Area
TableDataNameRange = The Name Column Area

Didn't want to do this, I thinks it's a hacked mess and didn't want to show it ...
This is something similar I've got going on in another workbook, I'm hoping for a slicker cleaner process for my current project.
My apploligies for the non-wrapped code, I don't wrap, So I don't know the rules, didn't want to post it broke for y'all ...
Maybe a nice mod that understands wrapping will fix it for me ...

Thanks!

Code:

Private Sub CommandButtonParsePicksPaste_Click()
Dim iRowThisWeekPick As Integer
Dim iColCurrentPlayer As Integer
Dim rPlayersLong As Range
Dim rPlayersShort As Range
Dim i As Integer

  If IsEmpty(Range("DataPasteZone")) Then Exit Sub

  CommandButtonParsePicksPaste.BackColor = &HFF00& ' &HFF00& = LtGrn, &H8000000F = BtnFaceGray
  Application.ScreenUpdating = False

  Set rPlayersLong = ActiveWorkbook.Names("PlayersLongNameListRange").RefersToRange
  Set rPlayersShort = ActiveWorkbook.Names("PlayersShortNameListRange").RefersToRange
  iRowThisWeekPick = Range("DataRange").Rows.Count + 1 ' Dynamic range

  For i = 0 To rPlayersShort.Rows.Count - 1
    iColCurrentPlayer = Application.Match(Application.Index(rPlayersShort, Application.Match(Trim(Cells(i + Range("DataPasteParsePlayer").Row, Range("DataPasteParsePlayer").Column)), rPlayersLong, False), 1), rPlayersShort, False)
    Range("DataRange").Cells(iRowThisWeekPick + 1, iColCurrentPlayer).Value = Cells(i + Range("DataPasteParseFinish").Row, Range("DataPasteParseFinish").Column).Value2
    Range("DataRange").Cells(iRowThisWeekPick, iColCurrentPlayer).Value = Cells(i + Range("DataPasteParseDriver").Row, Range("DataPasteParseDriver").Column).Value2
  Next

  Set rPlayersLong = Nothing
  Set rPlayersShort = Nothing
  Application.ScreenUpdating = True
  CommandButtonParsePicksPaste.BackColor = &H8000000F ' &HFF00& = LtGrn, &H8000000F = BtnFaceGray
End Sub

So is that basically the technique, sure seems like there would be a simpler/slicker/cleaner way. ??

Thanks y'all!

-Enjoy
fh <font color="#FF0000">:</font> )_~

Norie · Aug 16, 2011

Are you perhaps overcomplicating things here?

For example:

Code:

Set rPlayersLong = ActiveWorkbook.Names("PlayersLongNameListRange").RefersToRange
  Set rPlayersShort = ActiveWorkbook.Names("PlayersShortNameListRange").RefersToRange

Why not just something like this?

Code:

Set rPlayersLong = ActiveWorkbook.Range("PlayersLongNameListRange")
Set rPlayersShort = ActiveWorkbook.Range("PlayersShortNameListRange")

[/code]

Also do you really need to do all this stuff with named ranges?

Can't you just loop through the imported data and search the named ranges to get the column/row for the cell the data is to go in?

Something like this:

Code:

Set rngData = Range("A3") 

While rngData.Value<>""
   ImportValue  = rngDate.Value
   NameValue = rngDate.Offset(,1)
  
   Set rw = rShortName
  ' code to put ImportValue in correct cell
 
   Set rngData = rngData.Offset(1)
 
Wend

I'm not sure about the code for searching/finding - there's so many names I've lost track.

Festus Hagen · Aug 18, 2011

Norie said:
Are you perhaps overcomplicating things here?

Highly likely ... It would be dead on typical for me to do so!
And that is what I was hoping to find out. with the posting of that code, I don't want to go through that again in this new project.

Norie said:
Why not just something like this?

Code:

Set rPlayersLong = ActiveWorkbook.Range("PlayersLongNameListRange") Set rPlayersShort = ActiveWorkbook.Range("PlayersShortNameListRange")

Error: Object doesn't support this property or method

Norie said:
Also do you really need to do all this stuff with named ranges?

Dynamics ... I know of no other way to work with dynamic data in Excel.
Is there a better method ???

Norie said:
Can't you just loop through the imported data and search the named ranges to get the column/row for the cell the data is to go in?

Something like this:

Code:

Set rngData = Range("A3") While rngData.Value<>"" ImportValue = rngDate.Value NameValue = rngDate.Offset(,1) Set rw = rShortName ' code to put ImportValue in correct cell Set rngData = rngData.Offset(1) Wend

Need to play with this, not sure I understand how it increments.

For my current project the imported data will always land in (manipulated to) cell A1 on the same worksheet, thus something like this would work, for the project of the code I posted, it will not that I know of, the imported data is a dynamic moving target.

Norie said:
I'm not sure about the code for searching/finding - there's so many names I've lost track.

That's scary ... Me too!

And your Excel experience/knowledge far outweighs mine, I have very very little Excel knowledge/experience. A month maybe ... And my old shriveled pea brain is just a smoking!

-Enjoy
fh <font color="#FF0000">:</font> )_~

Festus Hagen · Aug 18, 2011

Norie said:

Code:

Set rngData = Range("A3") 

While rngData.Value<>""
   ImportValue  = rngDate.Value
   NameValue = rngDate.Offset(,1)
  
   Set rw = rShortName
  ' code to put ImportValue in correct cell
 
   Set rngData = rngData.Offset(1)
 
Wend

Cool, I like!

Thanks

-Enjoy
fh <font color="#FF0000">:</font> )_~

Working with Row and Columns inside a named range

Festus Hagen

New Member

Excel Facts

Steve=True

Well-known Member

Gary McMaster

Well-known Member

solitude

New Member

Festus Hagen

New Member

Norie

Well-known Member

Festus Hagen

New Member

Festus Hagen

New Member

Similar threads

Forum statistics

Share this page

Working with Row and Columns inside a named range

New Member

Excel Facts

Well-known Member

Well-known Member

New Member

New Member

Well-known Member

New Member

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock