Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
range.createnames method | Excel Programming | |||
Sum that ignores text | Excel Programming | |||
Sum function which ignores hidden rows | Excel Worksheet Functions | |||
Did I stump everyone? Counter that ignores hidden rows | Excel Discussion (Misc queries) | |||
Min Value using array ignores Zero | Excel Discussion (Misc queries) |