HELP -> Perl 'Hash' Functionality Required In VBA

[tommo_blade]

Hi,
I am new to VBA programming but understand a little of Perl, I am
trying to read the contents of all Excel worksheets within a workbook
and then summarise this data on a seperate worksheet, a lot of the
data in the worksheets is diplicated and I do not want this
duplication appearing on the new summarised worksheet. What I was
hoping to do was store the data in a hash table (like perl) and then
test for the existance of the key each time I added data to it or
simply just put the new key over the top as the value would be the
same as well as the key. Is there a way I can do this within VBA and
if possible give me an example..

many thanks, Mark.

 

[Joel]

Basic is not a great language for building Hash tables. C Language is better
for building has tables because of the pointers in C Language.

Hash Table are good if you have a lot of data and ae trying to speed up the
algorithm. It also requires lots of memory (if your table is large).

First try the simplier method with excel. Put each piece of data on a new
wroksheet and check to make usre the data isn't on the new worksheet before
putting the new data. A simple find works quickly and should do the job.


CheckData = "abc"
set c = worksheet("new").columns("A").find(what:=Checkdata, _
lookin:=xlvalues,lookat:=xlwhole)
if not c is nothing then

' enter your code
else
msgbox("Could not find : " & CheckData)
end if

 

[tommo_blade]

thanks, that has worked a treat

 

[Tim Williams]

You can use a Scripting Dictionary for this. It's close enough to a hash
table.

http://www.4guysfromrolla.com/webtech/102898-1.shtml

Tim

 

[tommo_blade]

just to expand on the 'find' method which worked ok for me, can
someone tell me how I can recall the row & column number when the find
is succesfull, the code I am using is detailed below:

CheckData = "abc"
set c = worksheet("sheet 1").columns("A").find(what:=Checkdata, _
lookin:=xlvalues,lookat:=xlwhole)
if not c is nothing then


msgbox "Found the data: " & Checkdata"
else
msgbox "Could not find : " & CheckData
end if



cheers.

 

[Tim Williams]

c.Row
c.Column

Tim

 

[tommo_blade]

thanks that seems to have done the trick but I have another issue now
that I cannot understand, I am getting the error 'next without for' in
the following code, can anyone see the issue, I have highlighted the
problem 'next' statements with a '=>' prior to the statement - can you
not have more than one next statement for a single 'for' statement - i
just want the code to skip to the next in the loop once those
conditions are met ?

-----------------------------------------------------------------------------------------------------------------------------------------
For z = 1 To 1000
If ActiveSheet.Cells(z, 1) <> "" Then
MyData = Split(ActiveSheet.Cells(z, 1), ":")
player = MyData(2)
club = MyData(1)
position = MyData(0)
Else
'msgbox "EMPTY CELL B"

=>Next z

End If

If ActiveSheet.Cells(z, 2) <> "N" Then
goals_scored = ActiveSheet.Cells(z, 2)
MsgBox "MATCH DATA FOUND: " & player & " GOAL SCORED: " &
goals_scored
Else
'MsgBox "NO MATCH DATA FOR: " & player

=>Next z

End If

For x = 1 To Worksheets.Count
If InStr(1, Worksheets(x).Cells(1, 1), "Team Name") = 1 Then
MsgBox "WORKSHEET: " & Worksheets(x).Name & "Looking
for PLAYER:" & player
Set f = Worksheets(x).Columns("B").Find(what:=player,
LookIn:=xlValues, lookat:=xlWhole)
If Not f Is Nothing Then
MsgBox player & " FOUND IN ROW " & f.row
Worksheets(x).Cells(f.row, col) = goals_scored
'MsgBox player & " FOUND IN ROW " & f.row &
"UPDATING DATA: " & f.row & f.col & goals_scored
Else
MsgBox player & "FOUND NOT FOUND ON WORKSHEET:" &
Worksheets(x).Name
End If
'Next x
End If
Next x

Next z

 

[ward376]

try changing

'MsgBox "NO MATCH DATA FOR: " & player
=>Next z
End If
to

'MsgBox "NO MATCH DATA FOR: " & player
End If
=>Next z


Cliff Edwards

 

[tommo_blade]

if i change that as you state then it complains about the 1st '=>next
z' statement, i.e.

'msgbox "EMPTY CELL B"

=>Next z

End If

if I also modify this part of the code so the 'next z' follows the
'end if' then the warning I get reverts back to the original 'next z'
statement for which you have already commented upon.

I take it that I can have multiple next statement within a single for
loop ?

cheers.

 

[ward376]

You have to nest the ifs completely between the fors and nexts and you
can only have one next for each for. (afaik)You'll have to nest an if
in an if to test for two conditions.

Cliff Edwards

 

[ward376]

Maybe something like this -

For z = 1 To 1000
If ActiveSheet.Cells(z, 1) <> "" Then
MyData = Split(ActiveSheet.Cells(z, 1), ":")
player = MyData(2)
club = MyData(1)
Position = MyData(0)
If ActiveSheet.Cells(z, 2) <> "N" Then
goals_scored = ActiveSheet.Cells(z, 2)
MsgBox "MATCH DATA FOUND: " & player & " GOAL SCORED: " &
goals_scored
For x = 1 To Worksheets.Count
If InStr(1, Worksheets(x).Cells(1, 1), "Team Name") = 1
Then
MsgBox "WORKSHEET: " & Worksheets(x).Name & "Looking
for PLAYER:" & player
Set f = Worksheets(x).Columns("B").Find(what:=player,
LookIn:=xlValues, lookat:=xlWhole)
If Not f Is Nothing Then
MsgBox player & " FOUND IN ROW " & f.Row
Worksheets(x).Cells(f.Row, col) = goals_scored
MsgBox player & " FOUND IN ROW " & f.Row & "UPDATING
DATA: " & f.Row & f.col & goals_scored
Else
MsgBox player & "FOUND NOT FOUND ON WORKSHEET:" &
Worksheets(x).Name
End If
End If
Next x
End If
End If
Next z

I didn't test it, but I think the structures are right.

Cliff Edwards

 

[tommo_blade]

worked a treat, many thanks for this

Mark.