Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 14
Default VBA CreateNames (ignores last 5 rows?)

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 ###########################
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,872
Default VBA CreateNames (ignores last 5 rows?)

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.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,514
Default VBA CreateNames (ignores last 5 rows?)

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


  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,514
Default VBA CreateNames (ignores last 5 rows?)

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


  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 14
Default VBA CreateNames (ignores last 5 rows?)

Hi Garry,

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.

What I found was that the defined ranges that I manually created, would get deleted 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 do it 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 functionality built 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.



On Monday, October 7, 2013 11:16:28 PM UTC+8, GS wrote:
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



  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 14
Default VBA CreateNames (ignores last 5 rows?)

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.
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,514
Default VBA CreateNames (ignores last 5 rows?)

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[s] 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[s] 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


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
range.createnames method DaveL Excel Programming 2 November 19th 08 05:16 PM
Sum that ignores text Smurfette18 Excel Programming 5 October 30th 07 06:41 PM
Sum function which ignores hidden rows bucketheaduk Excel Worksheet Functions 2 October 12th 05 01:56 PM
Did I stump everyone? Counter that ignores hidden rows TechMGR Excel Discussion (Misc queries) 2 April 5th 05 08:45 PM
Min Value using array ignores Zero mauddib Excel Discussion (Misc queries) 2 February 16th 05 08:02 PM


All times are GMT +1. The time now is 06:52 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"