RE: [ExcelVBA] Re: Need help on a Macro to compare two spreadsheets

 

Guys, I hope you don't mind my chipping in to add my 2c worth.

There is an issue with using
totalrows = ActiveSheet.UsedRange.Rows.Count

The used range does not necessarily start at row 1 so totalrows will not give the row number of the last used row.

To get that you need to use

lastRow= ActiveSheet.UsedRange.Rows.Count + ActiveSheet.UsedRange.Row -1

Best Regards
David Grugeon
Excel VBA Group Moderator

-----Original Message-----
From: ExcelVBA@yahoogroups.com [mailto:ExcelVBA@yahoogroups.com] On Behalf Of danielrose02
Sent: Friday, 13 July 2012 3:43 AM
To: ExcelVBA@yahoogroups.com
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]
>

------------------------------------

----------------------------------
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

----------------------------------Yahoo! Groups Links

__._,_.___

Reply to sender | Reply to group | Reply via web post | Start a New Topic

Messages in this topic (11)

Recent Activity:

• New Members 3

Visit Your Group

----------------------------------
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

----------------------------------

Switch to: Text-Only, Daily Digest • Unsubscribe • Terms of Use

.

__,_._,___