98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View ReviewsForum home » Delegate support and help forum » Microsoft Excel Training and help » Custom VBA Dictionary
Custom VBA Dictionary
Resolved · Medium Priority · Version 2003
Ben has attended:
MOS Exam course
Excel VBA Intro Intermediate course
Custom VBA Dictionary
I have a report that has a column which has free flow text sentences that manually needs to be checked for key-words.
With over 15000 rows every month this is very time consuming so I have attempted to automate this by hard coding a 'Dictionary' using an array procedure.
Unfortunately this is not best practice but every time I try to change the Array reference to a range I recieve errors no matter what Dim statement I enter.
Code:
Sub WordVerification()
WordExceptionsRow = 2
Dim MyArray
'MyArray = Sheets("Sheet3").Range(WordRange)
MyArray = Array("Money", "Cash", "Pounds")
For i = LBound(MyArray) To UBound(MyArray)
Windows(YStatusIND).Activate
Workbooks(YStatusIND).Sheets(Y1StatusInd).Select
With Worksheets(Y1StatusInd).Range("R1:R18000")
Set C = .Find(MyArray(i), LookIn:=xlValues, Lookat:=xlPart, SearchOrder:=xlByColumns)
If Not C Is Nothing Then
FirstAddress = C.Address
Do
With C
C.Select
Selection.EntireRow.Copy Destination:=Workbooks(WStatusIND).Sheets("Word Verification").Cells(WordExceptionsRow, 1)
WordExceptionsRow = WordExceptionsRow + 1
End With
Set C = .FindNext(C)
Loop While Not C Is Nothing And C.Address <> FirstAddress
End If
End With
Next i
Windows(WStatusIND).Activate
Workbooks(WStatusIND).Sheets("Word Verification").Select
Cells.Select
Cells.EntireColumn.AutoFit
Range("A1").Select
CutCopyMode = False
End Sub
RE: Custom VBA Dictionary
Hi Ben
Thanks for the question.
Firstly you need to set the Dim to an As Range ie
Dim MyArray as Range
Then you need the word "Set", ie
Set MyArray = Sheets("Sheet3").Range(WordRange)
Then to refer to each value in your dictionary instead of MyArray(i) you will need to use
MyArray.Cells(i)
I hope that helps. Let me know if it works.
Laura GB
Training information:
See also:
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:Move or Highlight CellsUse any of your movement keys, cursor, Home, End, PgUp or PgDn to highlight cells rows or columns by holding down the Shift key as you move. |