Solved: Run time error 91

[abraham30]

Dear colleagues,
one simple doubt as I got the solution from this forum.
when I change the value in code from Cells(3, 8).Value = 2 to
Cells(3, 8).Value = 5 then I get an error: "Object variable or with block not set" in below code

sBanks = rBanks.Find(What:=Sheets("Sheet1").Range("A" & i).Value, After:=rBanks.Cells(1, 1), LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False) _
.Offset(0, 1).Value



Is there any problem in the code.
I want to change the value at any variable and get the output as required

[Teeroy]

Abraham30,

When you changed the value in the lookup array did you check whether tehre were still any instances of value "2" in column A? If there were the .FIND will throw an error as there is now no matching object for "2" in the lookup array. If this may occur in service then you'll need to add in a Null test for the .FIND result object before you do the offset and take its value in the string generator lines.

[abraham30]

Dear Teeroy,
Please check the macro which I have updated. The value of sheet2 will be automatically populated in sheet1. Plz check whether any mistake is there
Advance thanks...

[Teeroy]

Abraham30,

I'll step you through the error I described earlier. Your data has Bank values of 1,2 and 3 and your lookup array has those values. When you change from Cells(3, 8).Value = 2 to Cells(3, 8).Value = 5 your lookup table now has Bank values of 1,5 and 3. At the second line of data you encounter a Bank value of "2". There is no longer a match in the lookup array so the .FIND returns a Null object. The object is embedded in a line to return an .Offset(0, 1).Value and there can't be an .Offset(0, 1).Value for an object that doesn't exist so you get the run-time error. So if you just added another row with Bank "5" and another name and changed the reference of the rBanks Range then you wouldn't get the error.

There are two things you can do:
1/ Return a range object from the .FIND. Test for Null. If not Null return the .Offset(0, 1).Value of that range.
2/ Ensure that you have all values in the lookup array that occur in the data. You could even use data validation on the data cells in the spreadsheet.

Try the above methods and come back to me if you can't make progress.

[abraham30]

Dear Teeroy,

I tried a lot but not getting the solution. I am in learing stage, so not able to find the error. Please have a look on the macro in the spreadsheet.
Advance thanks for your help.

[Teeroy]

Hi Abraham30,

You've fixed the error in the "Banks" data column but not in any of the others. For example there's a value of -1 in the "Country" data but only 1,2 or 3 in the lookup array. The same type of error exists in "Zone". Fix these and it will work.

Now this will solve your problem but coding the tables in at run-time then looking them up isn't the most efficient. If you don't want a separate (perhaps hidden?) lookup sheet then I would change methods completely and load the lookup pairs into a dictionary (well 4 actually, 1 for Banks, 1 for country and so on).

[Teeroy]

Hi Abraham30,

You've fixed the error in the "Banks" data column but not in any of the others. For example there's a value of -1 in the "Country" data but only 1,2 or 3 in the lookup array. The same type of error exists in "Zone". Fix these and it will work.

Now this will solve your problem but coding the tables in at run-time then looking them up isn't the most efficient. If you don't want a separate (perhaps hidden?) lookup sheet then I would change methods completely and load the lookup pairs into a dictionary (well 4 actually, 1 for Banks, 1 for country and so on).


Here's an example of the improved method I referred to. It is important that you have Microsoft Scripting Runtime checked in VBE|Tools|References or you'll get an error.


Sub CombineData()
'Must have Microsoft Scripting Runtime Reference checked to access the Dictionary Object

Dim sBanks As String
Dim sCountry As String
Dim sZone As String
Dim sTime As String
Dim Entry1 As String
Dim Entry2 As String
Dim Entry3 As String
Dim Entry4 As String

Dim i As Integer

Dim dBanks As Dictionary
Dim dCountry As Dictionary
Dim dZone As Dictionary
Dim dTime As Dictionary
Dim objDic As Dictionary

Set dBanks = New Dictionary
Set dCountry = New Dictionary
Set dZone = New Dictionary
Set dTime = New Dictionary
Set objDic = New Dictionary

'set dBanks Dictionary
With dBanks
.Add 1, "CITYBANK"
.Add 2, "CITYBANK"
.Add 11, "STAND CHARTED"
End With

'set dcountry Dictionary
With dCountry
.Add 1, "US"
.Add 2, "USSR"
.Add 3, "OTHER"
End With

'set dZone Dictionary
With dZone
.Add 0, "Eastzone"
.Add 1, "Westzone"
End With

'set dTime Dictionary
With dTime
.Add 1, "Daytime"
.Add 2, "Midtime"
.Add 3, "Nighttime"
End With

With objDic
.Comparemode = vbTextCompare
.Add "USSR CITYBANK MEMP", 0
.Add "USSR CITYBANK FEMP", 0

.Add "USSR EASTZONE STAND CHARTED MEMP", 0
.Add "USSR EASTZONE STAND CHARTED FEMP", 0

.Add "US MIDTIME CITYBANK MEMP", 0
.Add "US MIDTIME CITYBANK FEMP", 0

.Add "US DAYTIME CITYBANK MEMP", 0
.Add "US DAYTIME CITYBANK FEMP", 0

For i = 2 To Range("A1").End(xlDown).Row - 1
'define translation strings
sCountry = dCountry(Sheets("Sheet1").Range("B" & i).Value)
sBanks = dBanks(Sheets("Sheet1").Range("A" & i).Value)
sZone = dZone(Sheets("Sheet1").Range("C" & i).Value)
sTime = dTime(Sheets("Sheet1").Range("D" & i).Value)
gender = Range("E" & i).Value
myCount = Range("F" & i).Value

'Now to deal with RANDOM DATA fields
If sCountry = "" Then sCountry = "DATA ERROR"
If sBanks = "" Then sBanks = "DATA ERROR"
If sZone = "" Then sZone = "DATA ERROR"
If sTime = "" Then sTime = "DATA ERROR"

'Bulid data strings
Entry1 = sCountry & " " & sBanks & " " & gender
Entry2 = sCountry & " " & sZone & " " & sBanks & " " & gender
Entry3 = sCountry & " " & sTime & " " & sBanks & " " & gender
Entry4 = sBanks & "/" & sCountry & "/" & sZone & "/" & sTime & "/" & gender

'Add to existing data fields where possible
Select Case True
Case .exists(Entry1): .Item(Entry1) = .Item(Entry1) + myCount
Case .exists(Entry2): .Item(Entry2) = .Item(Entry2) + myCount
Case .exists(Entry3): .Item(Entry3) = .Item(Entry3) + myCount
Case .exists(Entry4): .Item(Entry4) = .Item(Entry4) + myCount

'Add new data field if no other combination exists
Case Else: .Add Entry4, myCount
End Select
Next i

Dim zzz() As String
ReDim zzz(objDic.Count)
Dim Key As Variant
Dim Value As String

i = 1
For Each Key In objDic
If objDic(Key) = 0 Then
Value = "No Cases"
Else
Value = objDic(Key)
End If
zzz(i) = Key & "=" & Value
i = i + 1
Next
Range("H23").Resize(UBound(zzz)) = WorksheetFunction.Transpose(zzz)

End With

'clean up the memory
Set objDic = Nothing
Set dBanks = Nothing
Set dCountry = Nothing
Set dZone = Nothing
Set dTime = Nothing
End Sub

[abraham30]

Dear Teeroy,
Thanks for spending your valuable time in checking the macro. It is working fine. but counting are not matching for last four criteria. I have used this in my real time project. It works excellent, but counting mismatch. IF you have some time, plz check.:help

Hats off... :bow:

Thanks once again

[abraham30]

Hi Teeroy,
Please check the latest sheet. Only one case number difference is there. Everything is fine.

[Teeroy]

Sorry Abraham,

When I was writing the code I think I got an id-10-T error :banghead:.
The output array starts from 0 not 1 so the code needs to change from (second line shown to help find the location);

i = 1
For Each Key In objDic


to;


i = 0
For Each Key In objDic

Who knew that being one number out could be so hard to trace :dunno.

BTW the changes you made to the block labelled "Now to deal with RANDOM DATA fields" has made it redundant. You can remove this section.

[abraham30]

Dear Teeroy,
Thanks for your continious help.
when I change the data, still the case number is not matching. Check the third parameter in attached excel. It displayed nocases in stead of '1'.
This number is printed at last.

[Teeroy]

Hi Abraham,

Your definition table for dReport has
.Add 11, "CT"
where it should read
.Add 11, "CTs"
Since it's doing a string compare from the built string against the the objDic Dictionary without the "s" the string doesn't match the preloaded value you'd put in to add to the counter.