custom vba dictionary

Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

Forum home » Delegate support and help forum » Microsoft Excel Training and help » Custom VBA Dictionary

Custom VBA Dictionary

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

Edited on Tue 7 Oct 2008, 13:08

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

RE: Custom VBA Dictionary

Thankyou Laura,

I think I tried using the Dim Array As Range before and still couldn't get it to work. Didn't think to Set the Array so will give that a go and post the outcome.

Thanks again,

Ben

 

Training courses

 

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
What does 'Resolved' mean?

Any suggestions, questions or comments? Please post in the Improve the forum thread.


 

Excel tip:

Move or Highlight Cells

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

Use in combination with the Ctrl key for quicker movements.

View all Excel hints and tips


Server loaded in 0.08 secs.