VBA CreateNames (ignores last 5 rows?)

[Steven North]

I've got this Macro from Contexures to create names which I've incorporated into a macro that uses definedname ranges when it imports data from Access.

It works rather brilliantly except I've got a slight glitch...

All the ranges are short 5 rows of data.

The lastrow is 802, headings start at row 9. Data starts at row 10.
All ranges are from rows 10 to 797 rather than 10 to 802.

Am I missing something??

' ################# VBA CODE ###########################

Option Explicit
' Downloaded from www.contextures.com
Sub CreateNames()
' written by Roger Govier, Technology4U

Dim wb As Workbook, ws As Worksheet
Dim lrow As Long, lcol As Long, i As Long
Dim myName As String, Start As String

' set the row number where headings are held as a constant
' change this to the row number required if not row 1
Const Rowno = 9

' set the Offset as the number of rows below Rowno, where the
' data begins
Const ROffset = 1

' set the starting column for the data, in this case 1
' change if the data does not start in column A
Const Colno = 1

' Set an Offset from the starting column, for the column number that
' will always have data entered, and will therefore be used in calculating lrow

Const COffset = 0 ' in this case, the first column will always contain data.

On Error GoTo CreateNames_Error

Set wb = ActiveWorkbook
Set ws = ActiveSheet

' count the number of columns used in the row designated to
' have the header names

lcol = Cells(Rowno, Columns.Count).End(xlToLeft).Column
lrow = ws.Cells(Rows.Count, Colno).End(xlUp).Row
Start = Cells(Rowno, Colno).Address

wb.Names.Add Name:="lcol", RefersTo:="=COUNTA($" & Rowno & ":$" & Rowno & ")"
wb.Names.Add Name:="lrow", RefersToR1C1:="=COUNTA(C" & Colno + COffset & ")"
wb.Names.Add Name:="myData", RefersTo:= _
"=" & Start & ":INDEX($1:$65536," & "lrow," & "Lcol)"

For i = Colno To lcol
' if a column header contains spaces, replace the space with an underscore
' spaces are not allowed in range names.
myName = Replace(Cells(Rowno, i).Value, " ", "_")
If myName = "" Then
' if column header is blank, warn the user and stop the macro at that point
' names will only be created for those cells with text in them.
MsgBox "Missing Name in column " & i & vbCrLf _
& "Please Enter a Name and run macro again"
Exit Sub
End If
wb.Names.Add Name:=myName, RefersToR1C1:= _
"=R" & Rowno + ROffset & "C" & i & ":INDEX(C" & i & ",lrow)"
nexti:
Next i

On Error GoTo 0
' MsgBox "All dynamic Named ranges have been created"
Exit Sub

Exit Sub

CreateNames_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure CreateNames of Module Technology4U"

End Sub

' ################# END VBA CODE ###########################

 

[Claus Busch]

Hi Steven,

Am Sun, 6 Oct 2013 19:59:03 -0700 (PDT) schrieb Steven North:

All the ranges are short 5 rows of data.

The lastrow is 802, headings start at row 9. Data starts at row 10.
All ranges are from rows 10 to 797 rather than 10 to 802.

if all ranges are from 10 to 797 you can use (Names are always the
headers):

Sub Test1()
Dim LCol As Integer
Dim Lrow As Long
Dim i As Integer

LCol = Cells(9, Columns.Count).End(xlToLeft).Column
Lrow = Cells(Rows.Count, 1).End(xlUp).Row

For i = 1 To LCol
ActiveWorkbook.Names.Add Cells(9, i), _
RefersTo:=Range(Cells(10, i), Cells(Lrow - 5, i))
Next
End Sub

If last row can change and you want 5 rows offset from the end then try:

Sub Test2()
Dim LCol As Integer
Dim Lrow As Long
Dim i As Integer

LCol = Cells(9, Columns.Count).End(xlToLeft).Column
Lrow = Cells(Rows.Count, 1).End(xlUp).Row

ActiveWorkbook.Names.Add Cells(9, 1), _
RefersTo:="=Offset($a$1,9,," & Lrow - 14 & ")"
For i = 2 To LCol
ActiveWorkbook.Names.Add Cells(9, i), _
RefersTo:="=Offset(" & Cells(9, 1) & ",," & i - 1 & ")"
Next
End Sub


Regards
Claus B.

 

[GS]

Food for Thought...

It appears that the code is creating dynamic ranges, and so the rows
above each header (plus the header) must be *correctly* factored in if
they contain values so the size of the range is accurate.

I'm not clear on why you're even doing it this way when it would be way
easier to load your Access data into a recordset and work with that to
import into your worksheet. A recordset already has fieldnames and
their positions defined and so ADODB can access accordingly via
standard SQL.

Another thing I'm not clear about is why your code *deliberately*
assigns *global scope* to the range names when the fieldnames (headers)
may be identical if/when your file contains more than 1 sheet of data
from the same source (data table). No problem as long as nobody copies
the sheet into a workbook with existing sheets, whereby a name conflict
will occur that (depending on the answer chosen about which version of
the name to use in the target workbook) *may* result in your data (or
existing data) having incorrect values return if there are formulas
that use those names.

On the other hand, if you don't mind using this rather rigid structure
and there's no possibility any flexibility will be needed down the road
then by all means carry on with it. It's not the way I'd go because it
leaves way too much opportunity for disaster to creep in later on, and
it doesn't offer much flexibility in terms of working with the data in
the ways I'm familiar with people wanting to work with their 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]

Food for Thought...

It appears that the code is creating dynamic ranges, and so the rows
above each header (plus the header) must be *correctly* factored in
if they contain values so the size of the range is accurate.

I forgot to mention that this requires contiguous data and so won't
return an accurate range if there's any records with blank data.

I'm not clear on why you're even doing it this way when it would be
way easier to load your Access data into a recordset and work with
that to import into your worksheet. A recordset already has
fieldnames and their positions defined and so ADODB can access
accordingly via standard SQL.

Another thing I'm not clear about is why your code *deliberately*
assigns *global scope* to the range names when the fieldnames
(headers) may be identical if/when your file contains more than 1
sheet of data from the same source (data table). No problem as long
as nobody copies the sheet into a workbook with existing sheets,
whereby a name conflict will occur that (depending on the answer
chosen about which version of the name to use in the target workbook)
*may* result in your data (or existing data) having incorrect values
return if there are formulas that use those names.

On the other hand, if you don't mind using this rather rigid
structure and there's no possibility any flexibility will be needed
down the road then by all means carry on with it. It's not the way
I'd go because it leaves way too much opportunity for disaster to
creep in later on, and it doesn't offer much flexibility in terms of
working with the data in the ways I'm familiar with people wanting to
work with their 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

 

[Steven North]

Hi Garry,

Thank you for your response. The idea is for the financial transactions forthe month to be imported into one sheet (this is done via the SQL with defined ranges being used as criteria) and then another sheet would to the calculations (financial report template). On completion, the two worksheets (data & report) would be exported to a new workbook as values only, save, close and then continue through.

What I found was that the defined ranges that I manually created, would getdeleted when any existing transaction data was deleted. Thus why I discovered this macro to 'define' the names via VBA. It worked but for some reason, the named ranges excluded 5 rows of transactions.

I've not had much experience working with recordsets and would prefer to doit this way if I can get it work.


I use the formula of... {SUM((A=A)*(B=B)*(C=C)*(D<>D)*(AMOUNTS))} in the excel sheets. However, I couldn't figure out how to do this using SQL to report on various headings etc. etc....

This is only a temporary measure until the acquisition and implementation of a new finance system has been done. The system has all this functionalitybuilt in and this would become obsolete.

I also agree with everything you've said. There's a lot of room for error doing something like this.

Thank you again.

 

[Steven North]

Thanks,

That has helped me figure out what was happening.
I had 5 'blank' rows above the row headers. Thus LROW removed those 5 rows.

 

[GS]

Thank you for your response. The idea is for the financial

transactions for the month to be imported into one sheet (this is
done via the SQL with defined ranges being used as criteria) and then
another sheet would to the calculations (financial report template).
On completion, the two worksheets (data & report) would be exported
to a new workbook as values only, save, close and then continue
through.

This is typical period reporting/analysis activity which I have some
deal of experience with and so is what I suspected you were doing.

What I found was that the defined ranges that I manually created,
would get deleted when any existing transaction data was deleted.

Correct! This is because most accounting systems 'Clear' the sheet
before updating with new data. (Assumes the data is written directly to
a specific sheet in a specified workbook) *OR* the import process will
'Clear' the sheet for new data. I don't think 'Refresh' has the same
effect on defined name ranges but can't say for sure!

Here's how I handle this for my clients! I design their
'report/analysis' sheet as desired for layout/formatting. I use
lookup formulas to pull data from the 'Data' sheet as required for the
fields on these sheets. This is based on the default layout of the
imported data so field info locations are reliably consistent. Now my
client can reuse this workbook every period by refreshing the 'Data'
sheet appropriately. The report/analysis sheets update automatically
when the new data is available. Same as you do, the results get copied
to a fiscal consolidation workbook as 'values'.

The 'Data' sheet is a plain worksheet without formatting, and has no
defined names.

Any defined names on the report/analysis sheet have local scope so
there's no 'name conflicts' when period sheets are copied to the
consolidation workbook.

--
Garry

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