PDA

View Full Version : vba to find data in excel



abraham30
03-31-2012, 08:02 AM
Hi everybody,
I am new to the macro. I want to get the output in excel. Can anybody help me in writing macro for that.
The macro is based on if-else condition.(Count the value based on requirement).
Please help
Output
101/clerk/10000/10=7102/clerk/10000/10=8103/sales,Doctor/20000/20,30=total

shrivallabha
03-31-2012, 08:56 AM
Welcome to VBAX.

Before working on a macro, have you checked the possibility of using a formula like SUMIFS? If you haven't then it is worth giving consideration.

I am attaching the file with formula.

=SUMIFS($E$1:$E$10,$A$1:$A$10,$A13,$B$1:$B$10,$B13,$C$1:$C$10,$C13,$D$1:$D$ 10,$D13)

shrivallabha
04-01-2012, 03:36 AM
For the last one week or so, I am trying desperately to understand the workings of Dictionary Object. It seems to be versatile tool for doing many things and is incredibly faster.

Here's my attempt at your macro requirement (using dictionary)
Sub CombineData()
Dim objDic As Object
Dim sStr As String

Set objDic = CreateObject("Scripting.Dictionary")
objDic.Comparemode = vbTextCompare

For i = 2 To 10
sStr = Range("A" & i).Value & "/" & Range("B" & i).Value & "/" & Range("C" & i).Value & "/" & _
Range("D" & i).Value
If objDic.exists(sStr) Then
objDic.Item(sStr) = objDic.Item(sStr) + Range("E" & i).Value
Else
objDic.Add sStr, Range("E" & i).Value
End If
Next i

With objDic
Range("G2").Resize(.Count, 2).Value = Application.Transpose(Array(.keys, .items))
End With

Set objDic = Nothing

End Sub

I am attaching the workbook.