Advertisement
Advertisement
| 07.14.2008 at 08:18AM PDT, ID: 23562887 |
|
[x]
Attachment Details
|
||
|
[x]
The Solution Rating System
|
||
With so many solutions, how can you tell which solutions are most likely to help you and which ones are not? To provide you with a tool to use, we rate our solutions based on various elements that most accurately determine if a solution is a quality solution. To explain what factors affect the solution rating, here are the elements we take into consideration when formulating our solution rating.
Your Input Matters If you have any suggestions that you would like to make for our rating system, please ask a question in the Suggestions Zone of Community Support. Thank you! |
||
1: 2: 3: 4: 5: 6: 7: 8: 9: 10: 11: 12: 13: 14: 15: 16: 17: 18: 19: 20: 21: 22: 23: 24: 25: 26: 27: 28: 29: 30: 31: 32: 33: 34: 35: 36: 37: 38: 39: 40: 41: 42: 43: 44: 45: 46: 47: 48: 49: 50: 51: |
Option Explicit
Dim refWorksheet As Worksheet, srcWorksheet As Worksheet, trgWorksheet As Worksheet
Dim intRefTotalBooks As Integer, intRefLctOffColumn As Integer, intRefTotalColumns As Integer
Dim intSrcTotalColumns As Integer, intSrcBookColumn As Integer, intSrcTotalBooks As Integer
Dim rngRefLctOffShore As Range, rngSrcBooks As Range
Option Base 1
Sub BookEliminateTest()
Set refWorksheet = Sheet1
Set srcWorksheet = Sheet2
Set trgWorksheet = Sheet3
'Find the columns and hidden selecting them
intRefTotalColumns = refWorksheet.Columns.Rows(1).SpecialCells(xlCellTypeConstants, xlTextValues).Count
intRefLctOffColumn = refWorksheet.Cells(1, 1).Resize(1, intRefTotalColumns).Find("OFF_Shore_Books", LookIn:=xlValues, lookat:=xlWhole).Cells.Column
intRefTotalBooks = refWorksheet.Columns(intRefLctOffColumn).Rows.SpecialCells(xlCellTypeConstants, xlTextValues).Count
intSrcTotalColumns = srcWorksheet.Columns.Rows(1).SpecialCells(xlCellTypeConstants, xlTextValues).Count
intSrcBookColumn = srcWorksheet.Cells(1, 1).Resize(1, intSrcTotalColumns).Find("Book", LookIn:=xlValues, lookat:=xlWhole).Cells.Column
intSrcTotalBooks = srcWorksheet.Columns(intSrcBookColumn).Rows.SpecialCells(xlCellTypeConstants, xlTextValues).Count
'Dictionary setup for Reference
Dim dicOffBooks 'Declaring dictionary object
Dim strOffBooks As String 'declaring books variable as string
Dim i As Integer
Set dicOffBooks = CreateObject("Scripting.Dictionary")
'Go through the reference sheet and add all the books to the dictionary
i = 1
Do
strOffBooks = refWorksheet.Cells(i + 1, intRefLctOffColumn).Value
dicOffBooks.Add i, strOffBooks
i = i + 1
Loop Until IsEmpty(refWorksheet.Cells(i + 1, intRefLctOffColumn).Value) 'loop the inx until the cells are empty
'Dictionary retrieval methods
Dim dicOffBooksItems, dicOffBooksKeys, dicOffBooksItemsCount
Dim lngDicItemsCount As Long
dicOffBooksKeys = dicOffBooks.keys
dicOffBooksItems = dicOffBooks.items
dicOffBooksItemsCount = dicOffBooks.Count
lngDicItemsCount = dicOffBooksItemsCount
'Set rngSrcBooks = srcWorksheet.Cells(2, intSrcBookColumn).Resize(intSrcTotalBooks - 1)
Set rngSrcBooks = srcWorksheet.Cells(2, 1).Resize(intSrcTotalBooks - 1, intSrcTotalColumns)
Dim arrSrcTotalBooks() As Variant
ReDim arrSrcTotalBooks(1 To intSrcTotalBooks, 1 To intSrcTotalColumns)
arrSrcTotalBooks = rngSrcBooks.Value
trgWorksheet.Cells(1, 1).Resize(intSrcTotalBooks - 1, intSrcTotalColumns).Value = arrSrcTotalBooks
|