There are LOTS of ways to determine how many rows
are in any given worksheet.
Which is "best" is different for any given situation.
I'll try to describe how these two techniques work.
UsedRange
----------------
As you add data to a worksheet, this "Range" increases in size.
In earlier versions of Excel, when you delete rows, this range didn't
decrease until you exit the worksheet and re-open it.
(actually, I think it reserved the rows until the "redo" option was no longer
available)
However, it looks like in Excel2010, it seems to update appropriately.
WorksheetFunction.Counta
----------
for this technique to work, your data has to be without blanks in the column
you're counting.
If you have header rows that do not have data, then this technique must be used
with caution.
I think for your situation, either technique may be used.
Paul
-----------------------------------------
"Do all the good you can,
By all the means you can,
In all the ways you can,
In all the places you can,
At all the times you can,
To all the people you can,
As long as ever you can." - John Wesley
-----------------------------------------
________________________________
From: danielrose02 <dan.rose@parker.com>
To: ExcelVBA@yahoogroups.com
Sent: Thu, July 12, 2012 1:48:43 PM
Subject: [ExcelVBA] Re: Need help on a Macro to compare two spreadsheets
Paul,
Thank you for your quick reply. I am looking over what you sent and be doing
some learning (and experimenting) with this material. So, it might be a little
while before I ask more questions. There is one item I do have a question about:
I use this line of code to determine how many rows of data present:
totalrows = ActiveSheet.UsedRange.Rows.Count
However, in your example you have:
nRows =
Application.WorksheetFunction.CountA(Sheets(MasterSheetName).Range("A1:A1000000"\))
What is the difference and which is better to use?
--- In ExcelVBA@yahoogroups.com, Paul Schreiner <schreiner_paul@...> wrote:
>
> Perhaps the problem was with the previous message..
> I'll try it without and see how it goes:
>
>
> OK...
> I think I'm going to answer your questions in reverse order....
> --------------------
> Option Explicit
> --------------------
> Using this AS THE FIRST LINE OF EACH MODULE tells the compiler that all
> variables have to be "declared" (using Dim, Public, Global, etc.)
>
> Without this, a variable doesn't HAVE to appear in a Dim statement.
> The compiler will "automatically" declare the variable the first time it is
> used.
> This can be dangerous because if you're using a variable called "YearValue"
> and in one place, you accidentally call it "YearValeu", the compiler will not
> recognize that as a problem. It will simply create a new variable.
> "Option Explicit" tells the compiler that all variables have to be "Explicitly"
>
> declared.
> ----------------------------------------------------------
>
> "The DIM statements do not have DATA TYPES "
> ----------------------------------------------------------
>
> Data Types are not REQUIRED.
> If not specified, the compiler defaults to type "Variant".
> This only becomes a problem if the value being stored in the variable
> is sometimes a string, and other times numeric.
> Once a value is stored in the variable, the data type is set to the type of
> data.
> That is: YearValue = 2012 will define it as an Integer,
> while YearValue = "2012" will define it as a String.
> again, this isn't a problem unless you're NEEDING it as a number in
> order to add/subtract from it.
> ----------------------------------------------------------
>
> "The variable LOAD_DICT_DATA is not in any DIM statements"
> ----------------------------------------------------------
>
> Load_Dict_Data is not a variable, it is a FUNCTION.
> ----------------------------------------------------------
>
> "I see that there is a FUNCTION used. "
> "Is that new MACRO?"
> "Is that coded inside a MACRO?"
> ----------------------------------------------------------
>
> The term "Macro" is actually inappropriate for VBA programming (mostly)
> It refers to:
> 1. A single, user-defined command that is part of an application and executes a
>
> series of commands.
>
> 2. A shorthand representation for a number of lines of code.
>
> Yet, you'll notice that the work "Macro" doesn't actually appear in the VBA
> Editor.
> If you choose to "record a macro", VBA calls the it a "Sub" (short for
> "Subroutine")
> Another type of "macro" is called a "Function".
> The main difference between the two is that you cannot "pass" a value to a
> Subroutine,
> nor can you "return" a value from a Sub. But you can from a Function.
> For instance... throughout a lengthy subroutine, you periodically have
> to modify a variable to ensure that it always is (4) characters long (prefixed
> with "0").
> Rather than repeat this code whenever you need to use it, you can create a
> Function:
>
> Function Pad4(tValue as string)
> while len(tValue) < 4
> tValue ="0"&tValue
> wend
> Pad4 = tValue
> End Function
>
> you can then use something like:
> WeekValue = Pad4(WeekValue)
>
> your result is that WeekValue will now be 4 characters long (i.e. "0025")
>
> When writing Macros (subroutines) of significant length, it is common practice
> to place separate "activities" in their own subs of functions.
> Like: one function for reading existing data from a spreadsheet, a separate
> function for reading new data from a CSV file.
>
> Another benefit is that some of these "subs" cannot be run independently.
> They MUST be called from within another sub.
> (it doesn't make sense to read the NEW data until after you're read the
>EXISTING
>
> data)
> When you select the list of "Macros" to run from the Excel icon,
> functions do not appear in the list.
> so, in one of my applications that have 50 FUNCTIONS, there is only ONE that is
>
> available to be executed!
> ----------------------------------------------------------
>
> Can these macros be stored anywhere?
> ----------------------------------------------------------
>
> Where macros are stored depends on how they are to be used.
> macros stored in a Sheet module only apply to the specific sheet.
> These should be stored in a "Standard" module.
> ----------------------------------------------------------
>
> "I receive the new data as an CSV file. "
> "How do I set "yearvalue" and "weekvalue"?"
> ----------------------------------------------------------
>
> Since a CSV file is a Comma Separated file,
> then I would open the file,
> read each line,
> "split" the line using the "," delimiter
> Set the YearValue and WeekValue based on the position in the string
> Like:
> ----------------------------------------------------------
>
> "What happens if the search does not find any thing? "
> "How do I check for that?"
> ----------------------------------------------------------
>
> I went ahead and wrote the "Add/Update" function (see below)
> The only things you should have to change is:
> MasterSheetName should be set to the sheet name that contains your "Master
>Data"
> NewDataFile should be set to the name of the CSV file.
> ----------------------------------------------------------
>
> ----------------------------------------------------------
>
> Option Explicit
> Public Dict_Data
> Public MasterSheetName
> Sub Auto_Open()
> Dim stat, nRow
> Dim NewDataFile
> '--------------------------------------
> MasterSheetName = "Master_Data"
> NewDataFile = "C:\temp\NewData.csv"
> '--------------------------------------
> stat = Load_Dict_Data
> stat = Read_New(NewDataFile)
> End Sub
>
> Function Load_Dict_Data()
> Dim nRows, R, wRecord, stat
> Set Dict_Data = CreateObject("Scripting.Dictionary")
> stat = Dict_Data.RemoveAll
> nRows =
>Application.WorksheetFunction.CountA(Sheets(MasterSheetName).Range("A1:A1000000"))
>)
>
> For R = 2 To nRows
> wRecord = Sheets(MasterSheetName).Cells(R, "A").Value _
> & "_" & Sheets(MasterSheetName).Cells(R, "B").Value
> If (Not Dict_Data.exists(wRecord)) Then
> Dict_Data.Add wRecord, R
> End If
> Next R
> Load_Dict_Data = Dict_Data.Count
> End Function
> Function Read_New(tDataFile)
> Dim nRows, TextLine, TxtArray
> Dim nRecord, DataRow
> nRows =
>Application.WorksheetFunction.CountA(Sheets(MasterSheetName).Range("A1:A1000000"))
>)
>
> '---- Open file -------------
> Open tDataFile For Input As #1
> '---- Loop until end of file ----------
> Do While Not EOF(1)
> '---- Read line into variable----
> Line Input #1, TextLine
> '---split line using "," delimeter------
> TxtArray = Split(TextLine, ",")
> '---Check to ensure there are at least (3) fields (Array index begins
> with "0")
> If (UBound(TxtArray) >= 2) Then
> ' --- Check to see if value already exists ----
> If (Not Dict_Data.exists(TxtArray(0) & "_" & TxtArray(1))) Then
> '-- Add Record ---
> nRows = nRows + 1
> Sheets(MasterSheetName).Cells(nRows, "A").Value = TxtArray(0)
> Sheets(MasterSheetName).Cells(nRows, "B").Value = TxtArray(1)
> Sheets(MasterSheetName).Cells(nRows, "C").Value = TxtArray(2)
> '----- Add to Dictionary Object --------------
> Dict_Data.Add TxtArray(0) & "_" & TxtArray(1), TxtArray(2)
> '----------------------------------------------
> Else ' Update Record
> DataRow = Dict_Data.Item(TxtArray(0) & "_" & TxtArray(1))
> Sheets(MasterSheetName).Cells(DataRow, "C").Value =
TxtArray(2)
> End If
> End If
> Loop
> Close #1 ' Close file.
> End Function
> ----------------------------------------------------------
>
> ----------------------------------------------------------
>
> ----------------------------------------------------------
>
>
> ==============================================================
>
>
> Paul
> -----------------------------------------
> "Do all the good you can,
> By all the means you can,
> In all the ways you can,
> In all the places you can,
> At all the times you can,
> To all the people you can,
> As long as ever you can." - John Wesley
> -----------------------------------------
>
> [Non-text portions of this message have been removed]
>
[Non-text portions of this message have been removed]
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.
----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA
----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com
----------------------------------
Tidak ada komentar:
Posting Komentar