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

 

Thank you. More questions:

1. The "Master_Data" workbook actually is made up of 3 worksheets:
1.a Chart of Summary Pivot Table.
1.b Summary Pivot Table
1.c LTD Weekly KPI
2. I want to perform all of this updating on the last worksheet (LTD Weekly KPI). So, for the line of code:

nRows = Application.WorksheetFunction.CountA(Sheets(MasterSheetName).Range("A1:A1000000"\))

How do I stipulate to use that particular worksheet?

3. In the line of code:

Set Dict_Data = CreateObject("Scripting.Dictionary")

Is the text "Scripting.Dictionary" a reserved word or phrase?

--- In ExcelVBA@yahoogroups.com, Paul Schreiner <schreiner_paul@...> wrote:
>
> 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@...>
> 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]
>

__._,_.___

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

Messages in this topic (8)

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

.

__,_._,___