Copy based on match criteria. Code Alternative to looping?

DocAElstein · Sep 13, 2014

Copy based on match criteria. Code Alternative to looping?

Hi, I need help in speeding up one of my simple Sort Codes…
.. I have had some success learning in sorting programs from participating in MrExcel Threads, but as a beginner I think my methods are still somewhat primitive (Mostly simple looping). One problem I find in my own project is that my looping method can take very long for some big files of mine.
. I have organized a much shortened version of one of my projects here with working codes of the type which take very long in my actual Files. These files are stripped down to the very minimum for clarity, so of course, are very fast.
. I would appreciate any code alternatives, mainly in the following directions:
. - I have ideas in the direction of filters , Advannced Filter Copy etc. (direct in VBA code or using the evaluate function within VBA to use some sort of spreadsheet filter function), but have no experience at all in this area and am attempting to learn that now. (Column B is free and available and could be used as a help column). Even if there are no speed alternatives I would also appreciate code alternatives to help me in learning different methods.

. So the example A

. This the first sheet in a Main Workbook, before the running of the macros:

Book1

A

B

C

1

Produnt

2

Name

3

Chocolate-europe aroma

4

Chocolate-Cookies

5

Banana-Chocolate-Split

6

Limette-Ksekuchen

7

Erdbeere-Quark

8

Erdbeere-Mix

9

Jamaica Sun

10

Waldbeeren

11

12

13

Tabelle1

. This is the first sheet of another file with entries to be included in the main file by the macros:

Book1

A

B

C

1

Product

2

Name

3

Haselnu-Walnu-aromatisiert

4

Tiramisu

2

5

Chocolate-colonial blend

6

Chocolate-europe aroma

4

7

Chocolate-Cookies

8

Jamaica Sun

6

9

Himbeere-Joghurt

10

Erdbeere-Quark

8

11

Erdbeere-Mix

12

Banana-Chocolate-Split

10

13

Waldbeeren

14

Kirsche

12

15

Kirsche-grner Apfel

16

Kirsche-Ananas

14

17

Stracciatella

18

Limette-Ksekuchen

16

19

grner Apfel-Quark

20

Blutorange-Quark

21

Pro

. This is how the first sheet in the main workbook looks after running the macros:

Book1

A

B

C

1

Produnt

2

Name

3

Chocolate-europe aroma

4

Chocolate-Cookies

5

Banana-Chocolate-Split

10

6

Limette-Ksekuchen

16

7

Erdbeere-Quark

8

Erdbeere-Mix

9

Jamaica Sun

6

10

Waldbeeren

11

12

Tabelle1

Here are the 2 files:
The main File with macros in it (In Module named “SortingMacros”);
XL 2007
FileSnack | Easy file sharing

XL2003
FileSnack | Easy file sharing

The file for entries to be included in the main File;
XL 2007
FileSnack | Easy file sharing

XL 2003
FileSnack | Easy file sharing

. Here are the codes again (XL 2007 versions) first simplified; . and a fuller version with some comments etc.

Code 1

Code:

Sub ProZuBlancoEingabe2Simplified()
Application.ScreenUpdating = False
Dim Blanco As String, Pro As String
Let Blanco = "MrExcelMainFileSorting2_2007.xlsm"
Let Pro = "MrExcelFileWithEntries2_2007.xlsx"
Dim wkstBlc As Worksheet, wkstPro As Worksheet
Set wkstBlc = Workbooks(Blanco).Worksheets.Item(1)
Set wkstPro = Workbooks(Pro).Worksheets.Item(1)
Dim CClmPPRow As Long, CClmBlcoRow As Long

  For CClmBlcoRow = 3 To 10 Step 1
    For CClmPPRow = 3 To 20 Step 1
    Application.StatusBar = "Blanco Row " & CClmBlcoRow & " Pro Row " & CClmPPRow
      If wkstBlc.Cells(CClmBlcoRow, 1).Value = wkstPro.Cells(CClmPPRow, 1).Value And wkstPro.Cells(CClmPPRow, 3).Value <> "" Then
      Let wkstBlc.Cells(CClmBlcoRow, 3).Value = wkstPro.Cells(CClmPPRow, 3).Value
      Else
      End If
    Next CClmPPRow
  Next CClmBlcoRow
Application.ScreenUpdating = True
Application.StatusBar = False
End Sub

Sub ProZuBlancoEingabe2() 'Look for Name match and Entry in every row ' Application.ScreenUpdating = False Dim Blanco As String, Pro As String 'File names Let Blanco = "MrExcelMainFileSorting2_2007.xlsm" 'Test Main File for MrExcel Let Pro = "MrExcelFileWithEntries2_2007.xlsx" 'Test File with Entries for MrExcel Dim wkstBlc As Worksheet, wkstPro As Worksheet 'Main File Worksheet, File with Entries Worksheet Set wkstBlc = Workbooks(Blanco).Worksheets.Item(1) 'First sheet in Main File Set wkstPro = Workbooks(Pro).Worksheets.Item(1) 'First sheet in File with entries Dim CClmPPRow As Long, CClmBlcoRow As Long 'C Columns in Spreadsheets Row Count 'Main Program 'Strategy: Take each Row in turn in Blanco then look through each row in Pro for a name match in first column and an Entry in Pro column C. Then if a an entry is given in Pro Column C, copy that value to Blanco Column C For CClmBlcoRow = 3 To 10 Step 1 'For each row in Blanco.... For CClmPPRow = 3 To 20 Step 1 '....Go through rows in Pro Application.StatusBar = "Blanco Row " & CClmBlcoRow & " Pro Row " & CClmPPRow 'Can be ommited for speed but in large files is not usually the limiting factor If wkstBlc.Cells(CClmBlcoRow, 1).Value = wkstPro.Cells(CClmPPRow, 1).Value And wkstPro.Cells(CClmPPRow, 3).Value <> "" Then 'Check for name match and an Entry In Pro Column C Let wkstBlc.Cells(CClmBlcoRow, 3).Value = wkstPro.Cells(CClmPPRow, 3).Value 'Match and Entry found so write entry value from Entry File in Main File Else 'Else no match and entry so do nothhing! End If Next CClmPPRow 'Go to next Pro Row.. Next CClmBlcoRow '... when Pro row to end start again witn next row in Blanco Application.ScreenUpdating = True Application.StatusBar = False End Sub 'ProZuBlancoEingabe2()

Code 2

Code:

Sub ProZuBlancoEingabe3Simplified()
Application.ScreenUpdating = False
Dim Blanco As String, Pro As String
Let Blanco = "MrExcelMainFileSorting2_2007.xlsm"
Let Pro = "MrExcelFileWithEntries2_2007.xlsx"
Dim wkstBlc As Worksheet, wkstPro As Worksheet
Set wkstBlc = Workbooks(Blanco).Worksheets.Item(1)
Set wkstPro = Workbooks(Pro).Worksheets.Item(1)
Dim CClmPPRow As Long, CClmBlcoRow As Long
Dim Match As Boolean

  For CClmPPRow = 3 To 20 Step 1
    If wkstPro.Cells(CClmPPRow, 3).Value <> "" Then
      Match = True
      For CClmBlcoRow = 3 To 10 Step 1
      Application.StatusBar = "Blanco Row " & CClmBlcoRow & " Pro Row " & CClmPPRow
        If wkstBlc.Cells(CClmBlcoRow, 1).Value = wkstPro.Cells(CClmPPRow, 1).Value Then
        Let wkstBlc.Cells(CClmBlcoRow, 3).Value = wkstPro.Cells(CClmPPRow, 3).Value
        Let Match = False
        Else
        End If
      Next CClmBlcoRow
      If Match = True Then
      MsgBox "Entry in Pro for " & wkstPro.Cells(CClmPPRow, 1).Value & ".  But no match in Blanco"
      Let Match = False
      Else
      End If
    Else
    End If
  Next CClmPPRow
  
TheEnd:
Application.ScreenUpdating = True
Application.StatusBar = False
End Sub

Sub ProZuBlancoEingabe3() 'look first only for C Column entry and then a match in Names ' Application.ScreenUpdating = False Dim Blanco As String, Pro As String 'File names Let Blanco = "MrExcelMainFileSorting2_2007.xlsm" 'Test Main File for MrExcel Let Pro = "MrExcelFileWithEntries2_2007.xlsx" 'Test File with Entries for MrExcel Dim wkstBlc As Worksheet, wkstPro As Worksheet 'Main File Worksheet, File with entries Worksheet Set wkstBlc = Workbooks(Blanco).Worksheets.Item(1) 'First sheet in main File Set wkstPro = Workbooks(Pro).Worksheets.Item(1) 'First sheet in File with entries Dim CClmPPRow As Long, CClmBlcoRow As Long 'C Columns in Spreadsheets Row Count Dim Match As Boolean 'Flag (Indicator) for match found = True for match or = False for no match found 'Main Program 'Strategy: look down C Column rows in Pro and when an entry is there loop through name columns in Blanco to find match in Names For CClmPPRow = 3 To 20 Step 1 'look down C Column rows... If wkstPro.Cells(CClmPPRow, 3).Value <> "" Then '....Look for entry in pro Column, If found.... Match = True 'Match Found For CClmBlcoRow = 3 To 10 Step 1 'For each Blanco C cell (or rather down the rows in Blanco) Application.StatusBar = "Blanco Row " & CClmBlcoRow & " Pro Row " & CClmPPRow 'Can be ommited for speed but in large files is not usually the limiting factor If wkstBlc.Cells(CClmBlcoRow, 1).Value = wkstPro.Cells(CClmPPRow, 1).Value Then 'Check for Name match Let wkstBlc.Cells(CClmBlcoRow, 3).Value = wkstPro.Cells(CClmPPRow, 3).Value 'Match found so type in amount in Blanco Let Match = False 'reset Match indicater for next loop Else 'No Name match so do nothing End If Next CClmBlcoRow 'look again for a match in next Blanco C Row. If Match = True Then 'We have not reset Match to false so we had an entry but no match MsgBox "Entry in Pro for " & wkstPro.Cells(CClmPPRow, 1).Value & ". But no match in Blanco" Let Match = False 'The user is warned of entry but no match. So reset Match = False for next loop Else 'Match is reset to false so match was found so do nothing (No warning MsgBox of no match) End If Else 'Else do nothhing as no entry was found in Column c of Pro. End If Next CClmPPRow ' go to next Row in C column of Pro and look for entry ' TheEnd: Application.ScreenUpdating = True Application.StatusBar = False End Sub 'ProZuBlancoEingabe3()

Thanks,
Alan.

Jerry Sullivan · Sep 14, 2014

Hi Alan, This thread compares the relative speed of a few vba approaches...

http://www.mrexcel.com/forum/excel-...p-visual-basic-applications-code-vlookup.html

One note on terminology- you use the term "Sorting" for this process but that typically means reordering the items (in ascending or descending order).
The process you are doing is probably better referred to as a "Lookup".

DocAElstein · Sep 14, 2014

Hi Jerry. Thanks for the reply. Much appreciated.

Jerry Sullivan said:
Hi Alan, …….
…….note on terminology- you use the term "Sorting" for this process but that typically means reordering the items (in ascending or descending order)….
…probably better referred to as a "Lookup".

.

That Tip (or rather memory jog) may help me in the direction I was looking. That small but important distinction was very useful…

. I originally started getting into VBA a few months ago because some large files were getting too big mainly because they were full with thousands of the VLOOKUP Formulas. I think my ideas were sound but having so many formulas was ridiculous. (The penny did not drop to use that reference “Lookup” in this Thread Title as I must use German versions and the equivalent SVERWEIS did not translate in my still mostly English brain to “Lookup” ).
. Thanks to a quick look at a Jon von der Heyden translation link along with the apparent automatic translation to English Of the MrExcel Spreadsheet HTML Maker, I can immediately show an example of the idea direction that I am now currently thinking of by giving such a formula in my example File. Column B has now the appropriate formula in, and Column C has the values after running my current looping macros.

Book1

A

B

C

1

Produnt

2

Name

3

Chocolate-europe aroma

4

Chocolate-Cookies

0

5

Banana-Chocolate-Split

10

6

Limette-Ksekuchen

16

7

Erdbeere-Quark

8

Erdbeere-Mix

0

9

Jamaica Sun

6

10

Waldbeeren

0

11

Tabelle1

Cell Formulas
Range	Formula
B3	=VLOOKUP(A3,[MrExcelFileWithEntries2_2007.xlsx]Pro!$A$1:$C$20,3,FALSE)
B4	=VLOOKUP(A4,[MrExcelFileWithEntries2_2007.xlsx]Pro!$A$1:$C$20,3,FALSE)
B5	=VLOOKUP(A5,[MrExcelFileWithEntries2_2007.xlsx]Pro!$A$1:$C$20,3,FALSE)
B6	=VLOOKUP(A6,[MrExcelFileWithEntries2_2007.xlsx]Pro!$A$1:$C$20,3,FALSE)
B7	=VLOOKUP(A7,[MrExcelFileWithEntries2_2007.xlsx]Pro!$A$1:$C$20,3,FALSE)
B8	=VLOOKUP(A8,[MrExcelFileWithEntries2_2007.xlsx]Pro!$A$1:$C$20,3,FALSE)
B9	=VLOOKUP(A9,[MrExcelFileWithEntries2_2007.xlsx]Pro!$A$1:$C$20,3,FALSE)
B10	=VLOOKUP(A10,[MrExcelFileWithEntries2_2007.xlsx]Pro!$A$1:$C$20,3,FALSE)

( Modified Main File (XL2007) FileSnack | Easy file sharing )

. ( The zeros are a bit of a pain, but that is a minor detail for now. )

. I need now maybe to try, when I can, to put some thought again in the use of the VBA Evaluate Function to avoid a Loop for Range calculations. I tackled this in some replies and participation in a few threads and almost got there, but the actual syntax is a nightmare for a busy beginner’s brain!
( #9 in http://www.mrexcel.com/forum/excel-...-indicate-range-cells-merge.html?#post3908684
From #15 in Multiple Columns Into Single Column Using Data Text To Column - Page 2
And general reference VBA Trick of the Week :: Avoid Loop for Range Calculations – Evaluate | Useful Gyaan )

. But thanks again, your reply may have put me back on track on this one. I will get on to that when I can and if have success drop a reply in giving details.
. In the meantime, if any experts in the use of the Evaluate Function in VBA, (Particularly in Ranges to avoid Looping), can give me some code ideas I would be very grateful.

Thanks
Alan Elston

Jerry Sullivan · Sep 14, 2014

DocAElstein said:
. In the meantime, if any experts in the use of the Evaluate Function in VBA, (Particularly in Ranges to avoid Looping), can give me some code ideas I would be very grateful.

Alan, Have you tried adapting either of the two fastest code examples in the link I provided? They both avoid looping through the range and should be ~100 times faster than your current code. It's not clear why you feel the need to use the Evaluate function.

DocAElstein · Sep 14, 2014

Hi Jerry.

.

Jerry Sullivan said:
….. It's not clear why you feel the need to use the Evaluate function.

. The Evaluate to avoid a Loop for a Range is an idea which I think could be generally useful in my work. I just have the feeling it could be neat idea once one gets the hang of the tricky syntax

Jerry Sullivan said:
….. Have you tried adapting either of the two fastest code examples in the link I provided? …...

. As a beginner that all seemed a bit complicated at first glance for me. I have no experience at all with VBA and VLOOKUP or FormulaR1C1. So I was just trying to stay initially with the things I know. But I respect your recommendations and will try to understand it and try the ideas out sometime.

Thanks again
Alan Elston

Jerry Sullivan · Sep 14, 2014

DocAElstein said:
. As a beginner that all seemed a bit complicated at first glance for me. I have no experience at all with VBA and VLOOKUP or FormulaR1C1. So I was just trying to stay initially with the things I know.

Okay, I understand. Here are adaptations of those two examples...

Code:

Sub UsingR1C1()
'enters R1C1 formula into results range then
'  converts formulas to values

 Dim wkstBlc As Worksheet, wkstPro As Worksheet
 Dim lLastRow As Long

 Set wkstBlc = Workbooks("MrExcelMainFileSorting2_2007.xlsm").Worksheets(1)
 Set wkstPro = Workbooks("MrExcelFileWithEntries2_2007.xlsx").Worksheets(1)

 lLastRow = wkstBlc.Cells(Rows.Count, "A").End(xlUp).Row

 With wkstBlc.Range("B3:B" & lLastRow)
   .FormulaR1C1 = "=VLOOKUP(RC[-1]," & _
      wkstPro.Range("A:C").Address(ReferenceStyle:=xlR1C1, External:=True) _
      & ",3,0)"
   .Value = .Value
   .Replace What:=0, Replacement:="", LookAt:=xlWhole, SearchFormat:=False
 End With

End Sub

Before using this example, add a reference to Microsoft Scripting Runtime to your VBA Project
(in the VBA Editor: Tools > References... > scroll down and check the box next to Microsoft Scripting Runtime ") > OK)"

Code:

Sub UsingDictionary()
'--Reads lookup table into dictionary
'  Reads lookup values into array then
'  stores results of dictionary lookups in array
'  then transfers array values to cells in one write

'--requires library reference to MS Scripting Runtime
 Dim dicLookupTable As Scripting.Dictionary
 Dim i As Long
 Dim sKey As String
 Dim vLookupValues As Variant
 Dim vLookupTable As Variant
 Dim wkstBlc As Worksheet, wkstPro As Worksheet
 Dim lLastRow As Long

 Set wkstBlc = Workbooks("MrExcelMainFileSorting2_2007.xlsm").Worksheets(1)
 Set wkstPro = Workbooks("MrExcelFileWithEntries2_2007.xlsx").Worksheets(1)

 Set dicLookupTable = New Scripting.Dictionary
 dicLookupTable.CompareMode = vbTextCompare

 With wkstPro
   vLookupTable = .Range("A2:C" & .Cells(Rows.Count, "A").End(xlUp).Row).Value
 End With
 
 For i = LBound(vLookupTable) To UBound(vLookupTable)
   sKey = vLookupTable(i, 1)
   If Not dicLookupTable.Exists(sKey) Then _
      dicLookupTable(sKey) = vLookupTable(i, 3)
 Next i
  
 With wkstBlc
   lLastRow = wkstBlc.Cells(Rows.Count, "A").End(xlUp).Row
   vLookupValues = .Range("A2:A" & lLastRow).Value
 End With
   
 For i = LBound(vLookupValues) To UBound(vLookupValues)
   sKey = vLookupValues(i, 1)

   If dicLookupTable.Exists(sKey) Then
      vLookupValues(i, 1) = dicLookupTable(sKey)
   Else
      vLookupValues(i, 1) = CVErr(xlErrNA)
   End If
 Next i
 With wkstBlc
   .Range("B2").Resize(UBound(vLookupValues) - _
      LBound(vLookupValues) + 1, 1) = vLookupValues
 End With

End Sub

As far as I know, a single Evaluate statement can't be used with Vlookup function for multiple lookup values.

DocAElstein · Sep 15, 2014

Jerry.
. Thank you so much. I do try to do as much alone as I can but this sort of help is invaluable just now. I am extremely grateful. By taking the time to adapt to my exact examples you have saved me a depressing brain straining!. I see now exactly wot you were saying ..

Jerry Sullivan said:
Alan, Have you tried adapting either of the two fastest code examples in the link I provided?.

. Sorry I missed the point that you were referring to your tests 3 and 4. . But I may still not have got there without your help:
..
……………..

Jerry Sullivan said:

………. Here are adaptations of those two examples...

Code:

Sub UsingR1C1()
'enters R1C1 formula into results range then
'  converts formulas to values

 Dim wkstBlc As Worksheet, wkstPro As Worksheet
 Dim lLastRow As Long

 Set wkstBlc = Workbooks("MrExcelMainFileSorting2_2007.xlsm").Worksheets(1)
 Set wkstPro = Workbooks("MrExcelFileWithEntries2_2007.xlsx").Worksheets(1)
……….etc.
…………………………………………………
.                                                           ………………
End Sub

. This has put exactly my formulas (and removed the zero!!) , as well of translating it automatically into German.
. I see almost immediately exactly wot is going on and when I get the time later to study in detail I think I will very quickly have a good understanding of the whole RC Formula stuff. That is an extremely helpful time saver in my learning. Thanks.

Jerry Sullivan said:
……
Before using this example, add a reference to Microsoft Scripting Runtime to your VBA Project
(in the VBA Editor: Tools > References... > scroll down and check the box next to Microsoft Scripting Runtime ") > OK)"

…..

.
. I am right at the beginning so precise instructions are invaluable. Very interesting again to see Microsoft Scripting Runtime cropping up. – despite just starting to get to grips with the whole Object Orienteering and Object Library idea, this Library I was using in Threads I am involved in to do with text file writing and reading. So it may be a standard / default setting for me now!! (I was using this “Early Binding” thing as well as the “Create object Late Binding” thing. Sort of a “belt and braces”)

………………………………………………………………………………

Jerry Sullivan said:

………….

Code:

Sub UsingDictionary()
'--Reads lookup table into dictionary
'  Reads lookup values into array then
'  stores results of dictionary lookups in array
'  then transfers array values to cells in one write

……….etc!!
…………..
.     ………..

End Sub

….

. Wow! Once more, by taking the time to adapt to my exact example you have helped me immensely!.
..
.. I almost see immediately wot is going on, but will do justice later to your efforts and “rip it apart’ with green comment graffiti until I understand exactly wot is going on. Again it is invaluable for my learning. And also my actual project. - I have had ideas to put my tables out into Arrays (or text files initially) and use other methods along the lines of the Microsoft Scripting stuff, to “look up” but had always been advise against it (and advised as alternative for example to learn / buy ACCESS). This gives me further insight into a possible realization of the ideas I was having.

Jerry Sullivan said:
……..

As far as I know, a single Evaluate statement can't be used with Vlookup function for multiple lookup values.

. I was thinking along the lines of taking the formula from the B Column and putting that in VBA in a simple =Evaluate(“ Here the Formula “), but taking very carefully into account wot I learnt from participation in those other Threads with the tricky Address / Range syntax. But I will bear in mind your comments.

…………………

Many thanks again for your efforts.

Alan Elston.

DocAElstein · Sep 28, 2014

Jerry Sullivan said:
Alan, Have youtried adapting either of the two fastest code examples in the link I provided?They both avoid looping through the range and should be ~100 times faster thanyour current code. ……………………<o></o>

Jerry Sullivan said:
. ……………Here are adaptations of those two examples..............

<o

></o

>
<o

> </o

>
<o

> </o

>
<o

> </o

>
. Just a quick intermediate bit of feedback onthis one…<o

></o

>
. .. I did some tests on an actual file. It is still an intermediate Solutionfile for me and I hope to get on later to some much bigger files, and will thenpossibly post a reply again…<o

></o

>
. The File(s) are a bit too big to screen shothere but I give a link below as well as the relevant codes below<o

></o

>
……….<o

></o

>
<o

> </o

>
<o

> </o

>
[TABLE="class: MsoNormalTable, width: 375"]
<tbody>[TR]
[TD="bgcolor: transparent"] Test #<o

></o

>
[/TD]
[TD="bgcolor: transparent"] Method Description<o

></o

>
[/TD]
[TD="bgcolor: transparent"] Approx Processing time in seconds<o

></o

>
[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"] Test_1
[/TD]
[TD="bgcolor: transparent"] Full Simple (Double) Looping<o

></o

>
[/TD]
[TD="bgcolor: transparent"] 570<o

></o

>
[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"] Test_2
[/TD]
[TD="bgcolor: transparent"] Improved Simple looping<o

></o

>
[/TD]
[TD="bgcolor: transparent"] 5<o

></o

>
[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"] Tests_3
[/TD]
[TD="bgcolor: transparent"] enters Vlookup formula into results range (with Range)<o

></o

>
[/TD]
[TD="bgcolor: transparent"] <1<o

></o

>
[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"] Test_3c
[/TD]
[TD="bgcolor: transparent"] VBA App.Vlookup<o

></o

>
[/TD]
[TD="bgcolor: transparent"] <1<o

></o

>
[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"] Test_4
[/TD]
[TD="bgcolor: transparent"] enters Vlookup formula into results range (with loop)<o

></o

>
[/TD]
[TD="bgcolor: transparent"] 4.5<o

></o

>
[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"] Test_5
[/TD]
[TD="bgcolor: transparent"] Scripting Dictionary<o

></o

>
[/TD]
[TD="bgcolor: transparent"] <o

> </o

>
<1<o

></o

>
[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"] Test_6<o

></o

>
[/TD]
[TD="bgcolor: transparent"] single Evaluate statement with Vlookup function<o

></o

>
[/TD]
[TD="bgcolor: transparent"] Still thinking about that one!?!?<o

></o

>
[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]
<o

> </o

>
<o

> </o

>
….. I timed them with aStop Watch so the results are not too accurate. But clearly I need to pullmyself away from using primitive loops for my final files unless I have a goodreason for using them (For now, as a beginner it helps sometimes to see betterwhat is going on with debugging , F8, setting watches etc.).<o

></o

>
. My gut feeling is that the ScriptingDictionary is Mega quick. It also has an advantage for me over the VLookUpmethod in that the Product Name (“Key” in Scripting Dictionary or “lookup_value”in VLOOKUP Function) is not limited to what appears to be 255 characters….( http://www.mrexcel.com/forum/excel-questions/808351-vlookup-lookup-value-255-character-limit.html)<o

></o

>
<o

> </o

>
. I will get back again if I do some more accurate/ extensive tests….<o

></o

>
<o

> </o

>
Alan<o

></o

>
<o

> </o

>
Files (XL 2007):<o

></o

>
Main File with Macros(Effectively the Look up values) <o

></o

>
https://app.box.com/s/00jpr3oh9awrbdgig7d8<o

></o

>
<o

> </o

>
File with Entries(Effectively the Look Up Table)<o

></o

>
https://app.box.com/s/4yc7w1e1fpdcab3vkbl7<o

></o

>
<o

> </o

>
<o

> </o

>
Relevant Codes:<o

></o

>
<o

> </o

>

Code:

[color=darkblue]Sub[/color]ProZuBlancoEingabeAlan_1() [color=green]'Check every Pro Row for match inProduct Name in Blanco, then copy entry in columc C if exists.[/color]<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]<o:p> </o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]Application.ScreenUpdating= [color=darkblue]False[/color]<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"][color=darkblue]On[/color][color=darkblue]Error[/color] [color=darkblue]GoTo[/color] TheEnd<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"][color=darkblue]Dim[/color]Blanco [color=darkblue]As[/color] [color=darkblue]String[/color], FullProName[color=darkblue]As[/color] [color=darkblue]String[/color]<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"][color=darkblue]Dim[/color]wkstBlc [color=darkblue]As[/color] Worksheet, wkstPro[color=darkblue]As[/color] Worksheet [color=green]'Main File Worksheet, Filewith entries Worksheet[/color]<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"][color=darkblue]Let[/color]Blanco = "xx.09.2014.xlsm"<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"][color=darkblue]Set[/color]wkstBlc = Workbooks(Blanco).Worksheets.Item(1) [color=green]'First sheet inmain File[/color]<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"][color=darkblue]Let[/color]FullProName = "26.09.2014.ods"<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"][color=darkblue]Set[/color]wkstPro = Workbooks(FullProName).Worksheets.Item(1) [color=green]'First sheetin File with entries[/color]<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]<o:p> </o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"][color=darkblue]Dim[/color]CClmPPRow [color=darkblue]As[/color] [color=darkblue]Long[/color], CClmBlcoRow[color=darkblue]As[/color] [color=darkblue]Long[/color] [color=green]'C Columnsin Spreadsheets Row Count[/color]<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"][color=green]'MainProgram[/color]<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]'Strategy: Take each Ccell in turn in Balnco then look for C cell entry in Pro then copy that valueto Blanco<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]  [color=darkblue]For[/color] CClmBlcoRow = 3[color=darkblue]To[/color] 636 [color=darkblue]Step[/color] 1 [color=green]'Foreach Blanco C cell (or rather down the rows in Pro)[/color]<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]    [color=darkblue]For[/color] CClmPPRow = 3[color=darkblue]To[/color] 630 [color=darkblue]Step[/color] 1 [color=green]'408Step 1[/color]<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]    Application.StatusBar = "Blanco "& CClmBlcoRow & " Pro " & CClmPPRow<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]      [color=darkblue]If[/color]wkstBlc.Cells(CClmBlcoRow, 1).Value = wkstPro.Cells(CClmPPRow, 1).Value AndwkstPro.Cells(CClmPPRow, 3).Value <> "" [color=darkblue]Then[/color][color=green]'Check for match other than an empty cell[/color]<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]      [color=darkblue]Let[/color]wkstBlc.Cells(CClmBlcoRow, 3).Value = wkstPro.Cells(CClmPPRow, 3).Value[color=green]'Match found so type in amount in Blanco[/color]<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]      [color=darkblue]Else[/color][color=green]'Else do nothhing![/color]<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]      [color=darkblue]End[/color][color=darkblue]If[/color]<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]    [color=darkblue]Next[/color] CClmPPRow<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]  [color=darkblue]Next[/color] CClmBlcoRow<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]TheEnd:<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]Application.ScreenUpdating= [color=darkblue]True[/color]<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]Application.StatusBar =[color=darkblue]False[/color]<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"][color=darkblue]End[/color][color=darkblue]Sub[/color] [color=green]'ProZuBlancoAlan1()[/color]<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"][color=green]'[/color]<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]'<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"][color=darkblue]Sub[/color]ProZuBlancoEingabeAlan_2() 'wkst version strategy look first for Pro Column Centry then a match row names and then copy entry to Blanco.<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]<o:p> </o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]Application.ScreenUpdating= [color=darkblue]False[/color]<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"][color=darkblue]On[/color][color=darkblue]Error[/color] [color=darkblue]GoTo[/color] TheEnd<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"][color=darkblue]Dim[/color]wkstBlc [color=darkblue]As[/color] Worksheet, wkstPro[color=darkblue]As[/color] Worksheet [color=green]'Main File Worksheet, Filewith entries Worksheet[/color]<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"][color=darkblue]Dim[/color]Blanco [color=darkblue]As[/color] [color=darkblue]String[/color], FullProName[color=darkblue]As[/color] [color=darkblue]String[/color]<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"][color=darkblue]Let[/color]Blanco = "xx.09.2014.xlsm"<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"][color=darkblue]Set[/color]wkstBlc = Workbooks(Blanco).Worksheets.Item(1) [color=green]'First sheet inmain File[/color]<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"][color=darkblue]Let[/color]FullProName = "26.09.2014.ods"<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"][color=darkblue]Set[/color]wkstPro = Workbooks(FullProName).Worksheets.Item(1) [color=green]'First sheetin File with entries[/color]<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]<o:p> </o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"][color=darkblue]Dim[/color]CClmPPRow [color=darkblue]As[/color] [color=darkblue]Long[/color], CClmBlcoRow[color=darkblue]As[/color] [color=darkblue]Long[/color] [color=green]'C Columnsin Spreadsheets Row Count[/color]<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"][color=darkblue]Dim[/color]Match [color=darkblue]As[/color] [color=darkblue]Boolean[/color][color=green]'Flag for match found = True or False[/color]<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]<o:p> </o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"][color=green]'MainProgram[/color]<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]'Strategy: look down CColumn Rows and when an entry is there loop through Product Name Rows to findmatc<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]  [color=darkblue]For[/color] CClmPPRow = 3[color=darkblue]To[/color] 630 [color=darkblue]Step[/color] 1 [color=green]'408Step 1 'look down C Column rows...[/color]<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]    [color=darkblue]If[/color]wkstPro.Cells(CClmPPRow, 3).Value <> ""[color=darkblue]Then[/color] [color=green]'....Look for entry in pro Column, Iffound....[/color]<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]      Match = [color=darkblue]True[/color][color=green]'Match Found[/color]<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]      [color=darkblue]For[/color] CClmBlcoRow =3 [color=darkblue]To[/color] 638 [color=darkblue]Step[/color] 1 'For eachBlanco C cell (or rather down the rows in Pro)<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]      Application.StatusBar = "Blanco" & CClmBlcoRow & " Pro " & CClmPPRow<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]        [color=darkblue]If[/color]wkstBlc.Cells(CClmBlcoRow, 1).Value = wkstPro.Cells(CClmPPRow, 1).Value AndwkstPro.Cells(CClmPPRow, 3).Value <> ""[color=darkblue]Then[/color] [color=green]'Check for match other than an emptycell[/color]<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]        [color=darkblue]Let[/color]wkstBlc.Cells(CClmBlcoRow, 3).Value = wkstPro.Cells(CClmPPRow, 3).Value[color=green]'Match found so type in amount in Blanco[/color]<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]        [color=darkblue]Let[/color] Match =[color=darkblue]False[/color] [color=green]'reset for next loop[/color]<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]        [color=darkblue]Else[/color]<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]        <o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]        [color=darkblue]End[/color][color=darkblue]If[/color]<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]      [color=darkblue]Next[/color] CClmBlcoRow<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]      [color=darkblue]If[/color] Match =[color=darkblue]True[/color] [color=darkblue]Then[/color] [color=green]'We havenot reset so we had an entry but no match[/color]<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]      MsgBox "Entry in Pro for "& wkstPro.Cells(CClmPPRow, 1).Value & " But no match inBlanco"<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]      [color=darkblue]Let[/color] Match =[color=darkblue]False[/color] [color=green]'The user is warned of entry but nomatch so reset match false for next loop[/color]<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]      [color=darkblue]Else[/color]<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]      [color=darkblue]End[/color][color=darkblue]If[/color]<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]    [color=darkblue]Else[/color][color=green]'Else do nothhing! No match[/color]<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]    [color=darkblue]End[/color][color=darkblue]If[/color]<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]  [color=darkblue]Next[/color] CClmPPRow<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]  [color=green]'[/color]<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]TheEnd:<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]Application.ScreenUpdating= [color=darkblue]True[/color]<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]Application.StatusBar =[color=darkblue]False[/color]<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"][color=darkblue]End[/color][color=darkblue]Sub[/color] [color=green]'ProZuBlancoEingabeAlan2()[/color]<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"][color=green]'[/color]<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]'<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]<o:p> </o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"][color=darkblue]Sub[/color]ProZuBlancoEingabepgcJerry_3VLOOKUPFormula() 'Test3UsingR1C1JerrySullivan()'enters R1C1 formula into results range then converts formulas to values<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]<o:p> </o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]Application.ScreenUpdating= [color=darkblue]False[/color]<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"][color=darkblue]On[/color][color=darkblue]Error[/color] [color=darkblue]GoTo[/color] TheEnd<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"][color=darkblue]Dim[/color]wkstBlc [color=darkblue]As[/color] Worksheet, wkstPro[color=darkblue]As[/color] Worksheet [color=green]'Main File Worksheet, Filewith entries Worksheet[/color]<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"][color=darkblue]Dim[/color]Blanco [color=darkblue]As[/color] [color=darkblue]String[/color], FullProName[color=darkblue]As[/color] [color=darkblue]String[/color]<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"][color=darkblue]Let[/color]Blanco = "xx.09.2014.xlsm"<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"][color=darkblue]Set[/color]wkstBlc = Workbooks(Blanco).Worksheets.Item(1) [color=green]'First sheet inmain File[/color]<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"][color=darkblue]Let[/color]FullProName = "26.09.2014.ods"<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"][color=darkblue]Set[/color]wkstPro = Workbooks(FullProName).Worksheets.Item(1) [color=green]'First sheetin File with entries[/color]<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"][color=darkblue]Dim[/color]rngName [color=darkblue]As[/color] Range<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"][color=darkblue]Set[/color]rngName = wkstBlc.Range("A3:A638")<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]  [color=darkblue]Dim[/color] lLastRow[color=darkblue]As[/color] [color=darkblue]Long[/color] [color=green]'[/color]<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]  lLastRow = 638<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]  [color=darkblue]Dim[/color] rngCC[color=darkblue]As[/color] Range<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]  [color=darkblue]Set[/color] rngCC =wkstBlc.Range("C3:C" & lLastRow)<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]<o:p> </o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"][color=green]'MainProgram[/color]<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]<o:p> </o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]  [color=darkblue]With[/color] rngCC<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]   Application.StatusBar = "At "& rngCC.Address<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]   .FormulaR1C1 ="=VLOOKUP(R[0]C[-2]," &wkstPro.Range("A:C").Address(ReferenceStyle:=xlR1C1, External:=True)& ",3,0)"<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]   [color=green]'Jerry put my exact SVERWEISSformula in! German: =SVERWEIS(A3;[26.09.2014.ods]Tabelle1!$A:$C;3;0)[/color]<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]   [color=green]'. Syntax: FormulaR1C1="here the formula ". The [] makes it relative referrencing.[/color]<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]   <o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]                        [color=green]'SubTest3b_pgc()[/color]<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]   .Formula = "=VLOOKUP(" &rngName(1, 1).Address(0, 0) & "," &wkstPro.Range("A3:C638").Address(External:=True) &",3,FALSE)"<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]   [color=green]'pgc alternative puts my exactSVERWEISS formula in! :=SVERWEIS(A3;[26.09.2014.ods]Tabelle1!$A$3:$C$638;3;FALSCH)[/color]<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]                        [color=green]'End Sub'Test3b_pgc() Note:- Anything other than 0,0 in Address(0, 0) gives fixed $Address based on Cell in range given by ...rngName(Row, Column). So (Row,Column) here is relative referencing from rngCC, in our case 1,1 nooffset[/color]<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]   <o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]   <o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]   [color=green]'.Value = .Value 'Removes Formula(Putsvalue in)[/color]<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]   [color=green]'.Replace What:=0,Replacement:="", LookAt:=xlWhole, SearchFormat:=False 'Get rid ofzeros[/color]<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"] [color=darkblue]End[/color][color=darkblue]With[/color]<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]<o:p> </o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]  [color=green]'[/color]<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]TheEnd:<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]Application.ScreenUpdating= [color=darkblue]True[/color]<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]Application.StatusBar =[color=darkblue]False[/color]<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"][color=darkblue]End[/color][color=darkblue]Sub[/color][color=green]'ProZuBlancoEingabepgcJerry3VLOOKUPFormula()[/color]<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"][color=darkblue]Sub[/color]ProZuBlancoEingabepgcJerry_4VLOOKUPFormula() [color=green]'enters R1C1 formula into results range then convertsformulas to values[/color]<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]<o:p> </o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]Application.ScreenUpdating= [color=darkblue]False[/color]<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"][color=darkblue]On[/color][color=darkblue]Error[/color] [color=darkblue]GoTo[/color] TheEnd<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"][color=darkblue]Dim[/color]wkstBlc [color=darkblue]As[/color] Worksheet, wkstPro[color=darkblue]As[/color] Worksheet [color=green]'Main File Worksheet, Filewith entries Worksheet[/color]<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"][color=darkblue]Dim[/color]Blanco [color=darkblue]As[/color] [color=darkblue]String[/color], FullProName[color=darkblue]As[/color] [color=darkblue]String[/color]<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"][color=darkblue]Let[/color]Blanco = "xx.09.2014.xlsm"<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"][color=darkblue]Set[/color]wkstBlc = Workbooks(Blanco).Worksheets.Item(1) [color=green]'First sheet inmain File[/color]<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"][color=darkblue]Let[/color]FullProName = "26.09.2014.ods"<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"][color=darkblue]Set[/color]wkstPro = Workbooks(FullProName).Worksheets.Item(1) [color=green]'First sheetin File with entries[/color]<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"][color=darkblue]Dim[/color]rngName [color=darkblue]As[/color] Range<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"][color=darkblue]Set[/color]rngName = wkstBlc.Range("A3:A638")<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]  [color=darkblue]Dim[/color] lLastRow[color=darkblue]As[/color] [color=darkblue]Long[/color] [color=green]'[/color]<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]  lLastRow = 638<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]  [color=darkblue]Dim[/color] rngCCRow[color=darkblue]As[/color] [color=darkblue]Long[/color]<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]  <o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]<o:p> </o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"][color=green]'MainProgram[/color]<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]<o:p> </o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]  [color=darkblue]For[/color] rngCCRow = 3[color=darkblue]To[/color] lLastRow [color=darkblue]Step[/color] 1<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]   Application.StatusBar = "Processing Row" & rngCCRow & " of   " & lLastRow<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]   [color=green]'Let wkstBlc.Cells(rngCCRow,3).FormulaR1C1 = "=VLOOKUP(R[0]C[-2]," &wkstPro.Range("A:C").Address(ReferenceStyle:=xlR1C1, External:=True)& ",3,0)"[/color]<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]   [color=green]'Jerry put my exact SVERWEISSformula in! German: =SVERWEIS(A3;[26.09.2014.ods]Tabelle1!$A:$C;3;0)[/color]<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]   [color=green]'. Syntax: FormulaR1C1="here the formula ". The [] makes it relative referrencing.[/color]<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]   <o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]                        [color=green]'SubTest3b_pgc()[/color]<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]   [color=darkblue]Let[/color]wkstBlc.Cells(rngCCRow, 3).Formula = "=VLOOKUP(" & rngName(1,1).Address(0, 0) & "," &wkstPro.Range("A3:C638").Address(External:=True) &",3,FALSE)"<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]   [color=green]'pgc alternative puts my exactSVERWEISS formula in! :=SVERWEIS(A3;[26.09.2014.ods]Tabelle1!$A$3:$C$638;3;FALSCH)[/color]<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]                        [color=green]'End Sub'Test3b_pgc() Note:- Anything other than 0,0 in Address(0, 0) gives fixed $Address based on Cell in range given by ...rngName(Row, Column). So (Row,Column) here is relative referencing from rngCC, in our case 1,1 nooffset[/color]<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]   <o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]   <o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]   [color=green]'.Value = .Value 'RemovesFormula(Puts value in)[/color]<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]   [color=green]'.Replace What:=0,Replacement:="", LookAt:=xlWhole, SearchFormat:=False 'Get rid ofzeros[/color]<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"] [color=darkblue]Next[/color] rngCCRow<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]<o:p> </o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]  [color=green]'[/color]<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]TheEnd:<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]Application.ScreenUpdating= [color=darkblue]True[/color]<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]Application.StatusBar =[color=darkblue]False[/color]<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"][color=darkblue]End[/color][color=darkblue]Sub[/color][color=green]'ProZuBlancoEingabepgcJerry4VLOOKUPFormula()[/color]<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"][color=green]'[/color]<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]'<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]'<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]'<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]'<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]<o:p> </o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"][color=darkblue]Sub[/color]ProZuBlancoEingabe_3cVBA_pgc()<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]<o:p> </o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]Application.ScreenUpdating= [color=darkblue]False[/color]<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"][color=darkblue]On[/color][color=darkblue]Error[/color] [color=darkblue]GoTo[/color] TheEnd<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"][color=darkblue]Dim[/color]wkstBlc [color=darkblue]As[/color] Worksheet, wkstPro [color=darkblue]As[/color]Worksheet [color=green]'Main File Worksheet, File with entriesWorksheet[/color]<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"][color=darkblue]Dim[/color]Blanco [color=darkblue]As[/color] [color=darkblue]String[/color], FullProName[color=darkblue]As[/color] [color=darkblue]String[/color]<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"][color=darkblue]Let[/color]Blanco = "xx.09.2014.xlsm"<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"][color=darkblue]Set[/color]wkstBlc = Workbooks(Blanco).Worksheets.Item(1) [color=green]'First sheet inmain File[/color]<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"][color=darkblue]Let[/color]FullProName = "26.09.2014.ods"<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"][color=darkblue]Set[/color]wkstPro = Workbooks(FullProName).Worksheets.Item(1) [color=green]'First sheetin File with entries[/color]<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"][color=darkblue]Dim[/color]rngName [color=darkblue]As[/color] Range<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"][color=darkblue]Set[/color]rngName = wkstBlc.Range("A3:A638")<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]  [color=darkblue]Dim[/color] lLastRow[color=darkblue]As[/color] [color=darkblue]Long[/color] [color=green]'[/color]<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]  lLastRow = 638<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]  [color=darkblue]Dim[/color] rngCC[color=darkblue]As[/color] Range<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]  [color=darkblue]Set[/color] rngCC =wkstBlc.Range("C3:C" & lLastRow)<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"][color=darkblue]Let[/color]rngCC = Application.WorksheetFunction.VLookup(rngName,wkstPro.Range("A3:C638"), 3, [color=darkblue]False[/color])<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]<o:p> </o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]TheEnd:<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]Application.ScreenUpdating= [color=darkblue]True[/color]<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]Application.StatusBar =[color=darkblue]False[/color]<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"][color=darkblue]End[/color][color=darkblue]Sub[/color] [color=green]'ProZuBlancoEingabe3cVBA_pgc()[/color]<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"][color=green]'[/color]<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]'<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"][color=darkblue]Sub[/color]Test_5JerryScriptedRuntimeDictionary()<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]<o:p> </o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]Application.ScreenUpdating= [color=darkblue]False[/color]<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"][color=darkblue]On[/color][color=darkblue]Error[/color] [color=darkblue]GoTo[/color] TheEnd<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"][color=green]' Code 4Jerry Sullivan Scripted a Runtime "Dictionary" of my File withEntries and used it to "Look up" wot to be put in the mainFile.[/color]<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"][color=green]'--Readslookup table into dictionary[/color]<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"][color=green]'  Reads lookup values into array then[/color]<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"][color=green]'  stores results of dictionary lookups in array[/color]<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"][color=green]'  then transfers array values to cells in onewrite[/color]<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]<o:p> </o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]<o:p> </o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"][color=green]'--requireslibrary reference to MS Scripting Runtime (Early Binding)-"Belt"[/color]<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"][color=green]'        Tools>>References>>scrolldown and check the box next to Microsoft Scripting Runtime[/color]<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"][color=green]'  ..Or crashes at next line.....[/color]<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"][color=green]' DimdicLookupTable As Scripting.Dictionary 'Data held with a unique "Key"or Part Number.[/color]<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"][color=green]' SetdicLookupTable = New Scripting.Dictionary[/color]<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"][color=green]' The nexttwo lines are an alternative called Late binding. Braces[/color]<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"][color=darkblue]Dim[/color]dicLookupTable [color=darkblue]As[/color] [color=darkblue]Object[/color]<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"][color=darkblue]Set[/color]dicLookupTable = CreateObject("Scripting.Dictionary")<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]  dicLookupTable.CompareMode = vbTextCompare<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"] <o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]<o:p> </o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]<o:p> </o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"] <o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"] [color=darkblue]Dim[/color] sKey[color=darkblue]As[/color] [color=darkblue]String[/color] [color=green]'Temporystring for part number or "key" - In my case the product name[/color]<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"] [color=green]'.  Very naively the key can be"pictured" as the first Name column in a master Array.[/color]<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"] [color=green]'.  But this contradicts the basic principal andadvantage of the "Scripting Dictionary method". -[/color]<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"][color=green]'.  A Dictionary in VBA is a collectionobject:you can store all kinds of things in it.[/color]<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"][color=green]'.  Every item in a Dictionary gets its ownunique key, a very important characteristic.[/color]<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"][color=green]'.  Instead of manipulating data in anExcel-worksheet etc, you can do that in memory, easy and quick since they aretemporarily stored there.[/color]<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"][color=green]'.  So it should speed up codeconsiderably.[/color]<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"] [color=darkblue]Dim[/color] i[color=darkblue]As[/color] [color=darkblue]Long[/color]<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"] [color=darkblue]Dim[/color] vLookupTable()[color=darkblue]As[/color] [color=darkblue]Variant[/color][color=green]'Effectively Spreadsheet Pro as Array[/color]<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"] [color=darkblue]Dim[/color] wkstBlc[color=darkblue]As[/color] Worksheet, wkstPro [color=darkblue]As[/color]Worksheet<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"][color=darkblue]Dim[/color]Blanco [color=darkblue]As[/color] [color=darkblue]String[/color], FullProName[color=darkblue]As[/color] [color=darkblue]String[/color]<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"][color=darkblue]Let[/color]Blanco = "xx.09.2014.xlsm"<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"][color=darkblue]Set[/color]wkstBlc = Workbooks(Blanco).Worksheets.Item(1) [color=green]'First sheet inmain File[/color]<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"][color=darkblue]Let[/color]FullProName = "26.09.2014.ods"<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"][color=darkblue]Set[/color]wkstPro = Workbooks(FullProName).Worksheets.Item(1) [color=green]'First sheetin File with entries[/color]<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"] [color=darkblue]Dim[/color] lLastRow[color=darkblue]As[/color] [color=darkblue]Long[/color]<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"] [color=darkblue]Let[/color] lLastRow = 638<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"] [color=darkblue]Dim[/color] vLookupValues()[color=darkblue]As[/color] [color=darkblue]Variant[/color][color=green]'Effectively Column C in Blanco as 1 dimensioinal Array. Not surewhy must be variant?? we know size from[/color]<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"] [color=darkblue]ReDim[/color]vLookupValues(lLastRow - 2) [color=green]' Must use ReDim as Dim only takesnumbers, not variables[/color]<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"] <o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"] [color=darkblue]With[/color] wkstPro<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]   vLookupTable = .Range("A2:U638")[color=green]'A*s* over Tit Array[/color]<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"] [color=darkblue]End[/color][color=darkblue]With[/color]<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"] <o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"] [color=darkblue]For[/color] i =[color=darkblue]LBound[/color](vLookupTable) [color=darkblue]To[/color] [color=darkblue]UBound[/color](vLookupTable)[color=green]'First Row to last Row in Pro[/color]<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]   sKey = vLookupTable(i, 1) [color=green]'Givea unique key to each product in Pro first row[/color]<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]   [color=darkblue]If[/color][color=darkblue]Not[/color] dicLookupTable.Exists(sKey)[color=darkblue]Then[/color] [color=green]' check that part number isfree??[/color]<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]      dicLookupTable(sKey) = vLookupTable(i, 3)[color=green]'Assigns the value in column C to that part Number (Key)[/color]<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]   [color=darkblue]Else[/color]<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]   [color=darkblue]End[/color][color=darkblue]If[/color]<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"] [color=darkblue]Next[/color] i<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]  <o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"] [color=darkblue]With[/color] wkstBlc<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]   [color=green]'lLastRow = .Cells(Rows.Count,"A").End(xlUp).Row' Probbably better to do this here And notdimension Array above explicitly in size to be sure of getting (Automtically)dimensioning right[/color]<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]   vLookupValues = .Range("A2:A"& lLastRow).Value<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"] [color=darkblue]End[/color] [color=darkblue]With[/color]<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]   <o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"] [color=darkblue]For[/color] i =[color=darkblue]LBound[/color](vLookupValues) [color=darkblue]To[/color][color=darkblue]UBound[/color](vLookupValues)<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]   sKey = vLookupValues(i, 1)[color=green]'give tempory string part number (Product name) in first column ofBlanco[/color]<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]<o:p> </o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]<o:p> </o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]   [color=darkblue]If[/color]dicLookupTable.Exists(sKey) [color=darkblue]Then[/color] [color=green]'Theimportent bit. look for that part number in the dictionary[/color]<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]      vLookupValues(i, 1) = dicLookupTable(sKey)[color=green]'If it is there vLookupValues array the value corresponding tothat key[/color]<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]   [color=darkblue]Else[/color]<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]      vLookupValues(i, 1) = ""[color=green]' or as JS wrote =CVErr(xlErrNA)'gives the error 'NV[/color]<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]   [color=darkblue]End[/color][color=darkblue]If[/color]<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"] [color=darkblue]Next[/color] i<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"] [color=darkblue]With[/color] wkstBlc<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]  .Range("C2").Resize(UBound(vLookupValues) - _<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]     [color=darkblue]LBound[/color](vLookupValues) + 1, 1) = vLookupValues[color=green]'Use column B2 for the final Array[/color]<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"] [color=darkblue]End[/color][color=darkblue]With[/color]<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"][color=green]'And thenfinally good practice to close / shut off these sort of things! At least Ithink I read that somewhere[/color]<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]TheEnd:<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]Application.ScreenUpdating= [color=darkblue]True[/color]<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]Application.StatusBar =[color=darkblue]False[/color]<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"][color=darkblue]Set[/color]dicLookupTable = [color=darkblue]Nothing[/color]<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"][color=darkblue]End[/color][color=darkblue]Sub[/color][color=green]'Test5JerryScriptedRuntimeDictionary()[/color]

<o

></o

>

Jerry Sullivan · Sep 28, 2014

Hi Alan,

Well done on your continued learning. Thank you for sharing your findings.

To do more accurate time testing than a stop watch or the VBA Timer() function, try using the MicroTimer code that Charles Williams shows in this article....

Excel 2010 Performance: Improving Calculation Performance

But clearly I need to pullmyself away from using primitive loops for my final files unless I have a goodreason for using them

Just to clarify, looping through data stored in memory can be a very fast approach. Your Test_5 (dictionary approach) uses loops and is fast. The thing to avoid is solutions with loops that have a large number of iterations and a relatively high amount of overhead time associated with each iteration (like reading and writing to worksheets).

DocAElstein · Sep 29, 2014

[QUOTE=Jerry Sullivan;3951577]…….. try using the MicroTimer code that Charles Williamsshows in this article....

Excel 2010 Performance: Improving Calculation Performance

Justto clarify, looping through data stored in memory can be a very fast approach.Your Test_5 (dictionary approach) uses loops and is fast. The thing to avoid issolutions with loops that have a large number of iterations and a relativelyhigh amount of overhead time associated with each iteration (like reading andwriting to worksheets).[/QUOTE]<o

></o

>
<o

> </o

>
Hi Jerry. <o

></o

>
.. Thanks for that. I caught that link fromyour other threads. Got a bit bogged down at first glance but now I see the bit about CharlesWilliams Codes stuff I shall takeanother look with a mind to testing out my SIXcodes…***<o

></o

>
<o

> </o

>

JerrySullivan said:
…. Well done on your continued learning. Thank you for sharingyour findings.

JerrySullivan said:
………..QUOTE]<o></o>
<o> </o>
. My biggest frustration is having to takelong breaks when I am keen to learn and so I “lose the Thread” as it were (I donot mean literally – I have them all saved and all codes backed up etc.) andmiss the point then sometimes. The help from you and other regulars (both inthe threads I am in and previous ones that I try hard to find in the massivewealth of info here) is proving invaluable just now……*** - I have now finally aone line Evaluate Range VLOOKUP code from RoryA. It was a great surprise (But Ithink I may go a bit trying to pull itapart with green code comments until I understand it!!!). I hope I get so far asto come up with something like that one day!! <o></o>
. And thanks to the codes you and others adaptedfor my examples I was able to convert the code from RoryA quite quickly to getit in the right syntax for my specific examples….<o></o>
<o> </o>
. Here is the 6^th code <o></o>
<o> </o>

Code:

[color=darkblue]Sub[/color]RoryA_6JerryS_Evaluate()<o:p></o:p>[/FONT][/COLOR] [FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]<o:p> </o:p>[/FONT][/COLOR] [FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]Application.ScreenUpdating= [color=darkblue]False[/color]<o:p></o:p>[/FONT][/COLOR] [FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"][color=darkblue]On[/color][color=darkblue]Error[/color] [color=darkblue]GoTo[/color] TheEnd<o:p></o:p>[/FONT][/COLOR] [FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"][color=darkblue]Dim[/color]wkstBlc [color=darkblue]As[/color] Worksheet, wkstPro[color=darkblue]As[/color] Worksheet [color=green]'Main File Worksheet, Filewith entries Worksheet[/color]<o:p></o:p>[/FONT][/COLOR] [FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"][color=darkblue]Dim[/color]Blanco [color=darkblue]As[/color] [color=darkblue]String[/color], FullProName[color=darkblue]As[/color] [color=darkblue]String[/color]<o:p></o:p>[/FONT][/COLOR] [FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"][color=darkblue]Let[/color]Blanco = "xx.09.2014.xlsm"<o:p></o:p>[/FONT][/COLOR] [FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"][color=darkblue]Set[/color]wkstBlc = Workbooks(Blanco).Worksheets.Item(1) [color=green]'First sheet inmain File[/color]<o:p></o:p>[/FONT][/COLOR] [FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"][color=darkblue]Let[/color]FullProName = "26.09.2014.ods"<o:p></o:p>[/FONT][/COLOR] [FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"][color=darkblue]Set[/color]wkstPro = Workbooks(FullProName).Worksheets.Item(1) [color=green]'First sheetin File with entries[/color]<o:p></o:p>[/FONT][/COLOR] [FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"][color=darkblue]Dim[/color]rngName [color=darkblue]As[/color] Range<o:p></o:p>[/FONT][/COLOR] [FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"][color=darkblue]Set[/color]rngName = wkstBlc.Range("A3:A638")<o:p></o:p>[/FONT][/COLOR] [FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"] [color=darkblue]Dim[/color] lLastRow[color=darkblue]As[/color] [color=darkblue]Long[/color] [color=green]'[/color]<o:p></o:p>[/FONT][/COLOR] [FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"] lLastRow = 638<o:p></o:p>[/FONT][/COLOR] [FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"] [color=darkblue]Dim[/color] rngCC[color=darkblue]As[/color] Range<o:p></o:p>[/FONT][/COLOR] [FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"] [color=darkblue]Set[/color] rngCC =wkstBlc.Range("C3:C" & lLastRow)<o:p></o:p>[/FONT][/COLOR] [FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"] [color=darkblue]Let[/color] rngCC.Value =Evaluate("transpose(INDEX(VLOOKUP(T(IF(1,TRANSPOSE(" &rngName.Address & ")))," &wkstPro.Range("A3:C638").Address(External:=True) &",3,FALSE),))")<o:p></o:p>[/FONT][/COLOR] [FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]TheEnd:<o:p></o:p>[/FONT][/COLOR] [FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]Application.ScreenUpdating= [color=darkblue]True[/color]<o:p></o:p>[/FONT][/COLOR] [FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]Application.StatusBar =[color=darkblue]False[/color]<o:p></o:p>[/FONT][/COLOR] [FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"][color=darkblue]End[/color][color=darkblue]Sub[/color] [color=green]'RoryA6JerryS_Evaluate()[/color]

<o></o>
<o> </o>
<o> </o>
And the updated XL2007File <o></o>
Main File with Macros(Effectively the Look up values)<o></o>
https://app.box.com/s/he5vb6xp2vmwzwck2lbd<o></o>
<o> </o>
<o> </o>
And here is aspreadsheet version I have worked out (Arbitrarily for lookup Value in cellA564 (Birne: (Pear in English!)): <o></o>
In German:<o></o>
=MTRANS(INDEX(SVERWEIS(T(WENN(1;MTRANS(A564)));[26.09.2014.ods]Tabelle1!$A$3:$D$638;3;0))<o></o>
And English:<o></o>
=TRANSPOSE(INDEX(VLOOKUP(T(IF(1,TRANSPOSE(A564))),[26.09.2014.ods]Tabelle1!$A$3:$D$638,3,0),))<o></o>
<o> </o>
. I hope I can get my head around them sometimeto understand them!!.<o></o>
<o> </o>
. In the meantime I will try to find time toget those Speed codes up and running for my codes. And I will report back myresults.<o></o>
<o> </o>
<o> </o>
Many thanks again.<o></o>
Alan<o></o>
<o> </o>
<o> </o>
<o> </o>

Copy based on match criteria. Code Alternative to looping?

DocAElstein

Banned user

Excel Facts

Jerry Sullivan

MrExcel MVP

DocAElstein

Banned user

Jerry Sullivan

MrExcel MVP

DocAElstein

Banned user

Jerry Sullivan

MrExcel MVP

DocAElstein

Banned user

DocAElstein

Banned user

Jerry Sullivan

MrExcel MVP

DocAElstein

Banned user

Similar threads

Forum statistics

Share this page

Copy based on match criteria. Code Alternative to looping?

Banned user

Excel Facts

MrExcel MVP

Banned user

MrExcel MVP

Banned user

MrExcel MVP

Banned user

Banned user

MrExcel MVP

Banned user

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock