Run-time error '91'
Object variable or With block variable not set
Please find a example below:
PHP Code:
Sub teste()
Dim mes As Integer
Dim ano As Integer
Dim pais As String
mes = Application.InputBox(Prompt:="Digite o número do mês que esteja com todos os dias completos (ex:2 para Favereiro)", Title:="Mês", Type:=1)
ano = Application.InputBox(Prompt:="Digite o ano (Menor anos possível: 2012)", Title:="Ano", Type:=1)
pais = Application.InputBox(Prompt:="Digite o país (ex: Brazil)", Title:="País", Type:=2)
You are getting that error when the search criteria does not exist. I would suggest assigning the search to a range variable and then testing whether it exists:
The problem is that you are trying to activate a cell, but if no result is found, then you can't activate nothing. When the search finds a result there is no error, so I suggest something like:
Marking threads as closed once your issue is resolved. How? The Thread Tools at the top
Any reputation (*) points appreciated. Not just by me, but by all those helping, so if you found someone's input useful, please take a second to click the * at the bottom left to let them know
There are 10 kinds of people in this world... those who understand binary, and those who don't.
It has happened again. Please find the whole Macro.
Thank you
PHP Code:
Sub teste()
Dim mes As Integer
'given month
Dim ano As Integer
'given year
Dim pais As String
'given country
Dim find As Range
Dim find2 As Range
Dim find3 As Range
Dim valor As String
Dim col_pais As Integer
Dim lin_data As Integer
Dim lin_sum As Integer
Dim sum As Integer
Dim DataMin As String
Dim DataMax As String
Dim n As Integer
mes = Application.InputBox(Prompt:="Digite o número do mês que esteja com todos os dias completos (ex:2 para Favereiro)", Title:="Mês", Type:=1)
ano = Application.InputBox(Prompt:="Digite o ano (Menor anos possível: 2012)", Title:="Ano", Type:=1)
pais = Application.InputBox(Prompt:="Digite o país (ex: Brazil)", Title:="País", Type:=2)
sum = 0
valor = DateSerial(ano, mes, 1)
'valor is equal to the first day of the given month within the given year
Set find = Cells.find(What:=valor, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False)
If Not find Is Nothing Then
find.Activate
lin_data = ActiveCell.Row
DataMin = valor
DataMax = DateSerial(ano, (mes + 1), 0)
find = Nothing
Set find = Cells.find(What:=DataMax, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False)
If Not find Is Nothing Then
find.Activate
lin_sum = ActiveCell.Row
While (lin_data - 1) <> lin_sum
Cells(lin_sum, col_pais).Select
sum = sum + Cells(lin_sum, col_pais)
lin_sum = lin_sum - 1
Wend
MsgBox sum
Else
MsgBox "erro"
End If
Else
MsgBox "erro"
End If
Else
MsgBox "Menor ano possível: 2012"
End If
Else
MsgBox "Digite um mês de 1 a 12"
End If
What do you mean when you say second find doesn't work? You have a line that says "find = nothing" but find is a range, and should say "SET". That is because find is a range. I'm not sure what else is wrong, maybe if you attach your workbook? When I tested your code, I got an error on your line of code "find = nothing" but the error stopped when I changed it to "set find = nothing"
Ok so the code is working, it is just not doing what you want. The problem is you are looking for a date like 01/02/15 but your date column is formatted differently. Therefore there is no match.
Ok so the code is working, it is just not doing what you want. The problem is you are looking for a date like 01/02/15 but your date column is formatted differently. Therefore there is no match.
If you're refering to "Digite o número do mês que esteja com todos os dias completos (ex:2 para Favereiro)" that's just a normal message suggesting to type 2 for February as example.
Pedro, instead of Dim valor as string, try dim valor as date. Change DataMax and any other date variable to a date instead of a string. That should help. Also, you have a line
Pedro, instead of Dim valor as string, try dim valor as date. Change DataMax and any other date variable to a date instead of a string. That should help. Also, you have a line
As I said, if you dim your date variables as date and not string, then it should find the date.
There are more problems with the code too, since you have a while loop that will crash when the rest works. I don't know what it is supposed to do, so hard to help with that.
Pedro, instead of Dim valor as string, try dim valor as date. Change DataMax and any other date variable to a date instead of a string. That should help. Also, you have a line
As I said, if you dim your date variables as date and not string, then it should find the date.
There are more problems with the code too, since you have a while loop that will crash when the rest works. I don't know what it is supposed to do, so hard to help with that.
Sorry. Let me try to explain.
Basically you type march (3) of 2014 (2014) for Brazil (Brazil) and you'll have a sum that will be the result from all revenues from the given month inside the given year. In other words, from the first until the 31st march, what's the rev?
What is the value of sum when it overflows? I see sum is an integer, so maybe the value is too big. What happens if you dim sum as long instead of integer?
What is the value of sum when it overflows? I see sum is an integer, so maybe the value is too big. What happens if you dim sum as long instead of integer?
I've found it.
The problem was that dates were organized from most recent to oldest. lin_sum was going further from the first day of the month and the value was too bizarre.
The solution was:
Change:
lin_data - 1 --> lin_data +1
lin_sum = lin_sum - 1 --> lin_sum = lin_sum +1
You are welcome
I hope things are working better now? The +1 does make more sense
I would still dim sum as long and not integer, integers have a max value that COULD be a problem, depending on how big the revenues are
LinkBack URL
About LinkBacks
Have no idea but I have a problem, error 91
Register To Reply
Re: Have no idea but I have a problem, error 91
Originally Posted by Arkadi
Re: Have no idea but I have a problem, error 91
Bookmarks