Run time error 91

[victorel21]

I have this code and i am getting an error 91 on the find line.

Every variable has been correctly defined in another module, this worked just find two days ago, now all of them are giving me problems,

Any ideas?

Appreciate the help.

Sub todatabase2()

Sheets("Form").Activate
ID = Range("F1")
GR = Range("A3")
Departamento = Range("C3")
Semana = Range("D3")
Comentarios = Range("B25")

Sheets("Datalist").Activate
Month = Cells(Range("E:E").Find(Semana, Lookat:=xlWhole).Row, 6)


With Sheets("Employee_List")
Set RngCol = .Range("A2", .Cells(Rows.Count, 1).End(xlUp))
End With

Sheets("Employee_List").Activate
LastRow = RngCol.Rows.Count
Range("A2:G" & LastRow + 1).Copy

Sheets("Historico").Activate
last_line = Range("F" & Rows.Count).End(xlUp).Row + 1

Sheets("Historico").Range("F" & last_line).PasteSpecial Paste:=xlPasteValues


Do While Cells(last_line, 6) <> ""

If Sheets("Historico").Range("F" & last_line) <> "" Then
Sheets("Historico").Range("A" & last_line) = ID
Sheets("Historico").Range("B" & last_line) = GR
Sheets("Historico").Range("C" & last_line) = Departamento
Sheets("Historico").Range("D" & last_line) = Semana
Sheets("Historico").Range("M" & last_line) = Comentarios
Sheets("Historico").Range("E" & last_line) = Month

End If

last_line = last_line + 1

Loop

ThisWorkbook.Worksheets("Historico").Cells.EntireColumn.AutoFit

End Sub

 

[Fluff]

Slight flaw in that idea, if you have already used something like

Dim month

in any module, it will automatically be lower case in all modules.

 

[Joe4]

Slight flaw in that idea, if you have already used something like

Dim month

in any module, it will automatically be lower case in all modules.

Right. You would need to use that technique before declaring those variables (and/or be aware what you may have done in other modules).
If you are starting a new project and beginning from scratch, and do that as you are declaring the variables, you should be good.

 

[victorel21]

It was great help, I changed from Month to Mes, and i Got the same error as before.

I dont get why the code is so volatile.

Whatever help will be appreciated, thanks.

Sub todatabase2()

Dim ID, GR, Departamento, Semana, Comentarios, Mes As Range

With Sheets("Form")
ID = Range("F1")
GR = Range("A3")
Departamento = Range("C3")
Semana = Range("D3")
Comentarios = Range("B25")
End With

Set Mes = Sheets("Datalist").Cells(Range("E:E").Find(Semana, Lookat:=xlWhole).Row, 6)

With Sheets("Employee_List")
Set RngCol = .Range("A2", .Cells(Rows.Count, 1).End(xlUp))
LastRow = RngCol.Rows.Count
.Range("A2:G" & LastRow + 1).Copy
End With

With Sheets("Historico")
last_line = .Range("F" & Rows.Count).End(xlUp).Row + 1
.Range("F" & last_line).PasteSpecial Paste:=xlPasteValues
Do While Cells(last_line, 6) <> ""
If .Range("F" & last_line) <> "" Then
.Range("A" & last_line) = ID
.Range("B" & last_line) = GR
.Range("C" & last_line) = Departamento
.Range("D" & last_line) = Semana
.Range("M" & last_line) = Comentarios
.Range("E" & last_line) = Mes
End If
last_line = last_line + 1
Loop
End With

ThisWorkbook.Worksheets("Historico").Cells.EntireColumn.AutoFit

End Sub

 

[Joe4]

It was great help, I changed from Month to Mes, and i Got the same error as before.

That often means it cannot find the value you are looking for in the range you have defined.

Try running this variation, which will tell you which value it cannot find:

Sub todatabase2()

Dim ID, GR, Departamento, Semana, Comentarios, Mes As Range

With Sheets("Form")
    ID = Range("F1")
    GR = Range("A3")
    Departamento = Range("C3")
    Semana = Range("D3")
    Comentarios = Range("B25")
End With

On Error GoTo err_chk:
Set Mes = Sheets("Datalist").Cells(Range("E:E").Find(Semana, Lookat:=xlWhole).Row, 6)
On Error GoTo 0

With Sheets("Employee_List")
    Set RngCol = .Range("A2", .Cells(Rows.Count, 1).End(xlUp))
    LastRow = RngCol.Rows.Count
    .Range("A2:G" & LastRow + 1).Copy
End With

With Sheets("Historico")
    last_line = .Range("F" & Rows.Count).End(xlUp).Row + 1
    .Range("F" & last_line).PasteSpecial Paste:=xlPasteValues
    Do While Cells(last_line, 6) <> ""
        If .Range("F" & last_line) <> "" Then
            .Range("A" & last_line) = ID
            .Range("B" & last_line) = GR
            .Range("C" & last_line) = Departamento
            .Range("D" & last_line) = Semana
            .Range("M" & last_line) = Comentarios
            .Range("E" & last_line) = Mes
        End If
        last_line = last_line + 1
    Loop
End With

ThisWorkbook.Worksheets("Historico").Cells.EntireColumn.AutoFit

Exit Sub


err_chk:
    If Err.Number = 91 Then
        MsgBox "Cannot find value of " & Semana, vbOKOnly, "ERROR!!!"
    Else
        MsgBox Err.Number & ": " & Err.Description
    End If
        
End Sub

Also, when posting your VBA code, please use Code Tags! Trying to read unformatted code can be cumbersome, especially when it gets a little lengthy.
See: How to Post Your VBA Code

 

[dmt32]

N

Also, you should NEVER use reserved words like "Month" as a variable!!! This can cause errors and unexpected results.
Reserved words are words that are already used as functions, properties, methods, etc.
Since there is already a function named "Month", you should not use it as a variable name (or a procedure or function name).

whilst it is generally considered not a good idea to use any names that are the same as the function, statement, method used in Visual Basic as you end up shadowing the same keywords in the language, it is my understanding this does not mean you cannot do it.

To use a language function that conflicts with an assigned name, you need explicitly identify it by preceding with the name of the associated type library an example would be, if you have a variable called Left, you can only call the VBA the Left function by using VBA.Left or Month would be VBA.Month
Using truly reserved keywords like Date for instance, normally the compiler complains turning the declared variable red & should take that as a no.

But I do agree, all best avoided. much easier to prefix with the variable with the declared data type example Dim strMonth As String or whatever.

just my thoughts

Dave

 

[victorel21]

That often means it cannot find the value you are looking for in the range you have defined.

Try running this variation, which will tell you which value it cannot find:

Sub todatabase2()

Dim ID, GR, Departamento, Semana, Comentarios, Mes As Range

With Sheets("Form")
    ID = Range("F1")
    GR = Range("A3")
    Departamento = Range("C3")
    Semana = Range("D3")
    Comentarios = Range("B25")
End With

On Error GoTo err_chk:
Set Mes = Sheets("Datalist").Cells(Range("E:E").Find(Semana, Lookat:=xlWhole).Row, 6)
On Error GoTo 0

With Sheets("Employee_List")
    Set RngCol = .Range("A2", .Cells(Rows.Count, 1).End(xlUp))
    LastRow = RngCol.Rows.Count
    .Range("A2:G" & LastRow + 1).Copy
End With

With Sheets("Historico")
    last_line = .Range("F" & Rows.Count).End(xlUp).Row + 1
    .Range("F" & last_line).PasteSpecial Paste:=xlPasteValues
    Do While Cells(last_line, 6) <> ""
        If .Range("F" & last_line) <> "" Then
            .Range("A" & last_line) = ID
            .Range("B" & last_line) = GR
            .Range("C" & last_line) = Departamento
            .Range("D" & last_line) = Semana
            .Range("M" & last_line) = Comentarios
            .Range("E" & last_line) = Mes
        End If
        last_line = last_line + 1
    Loop
End With

ThisWorkbook.Worksheets("Historico").Cells.EntireColumn.AutoFit

Exit Sub


err_chk:
    If Err.Number = 91 Then
        MsgBox "Cannot find value of " & Semana, vbOKOnly, "ERROR!!!"
    Else
        MsgBox Err.Number & ": " & Err.Description
    End If
       
End Sub

Also, when posting your VBA code, please use Code Tags! Trying to read unformatted code can be cumbersome, especially when it gets a little lengthy.
See: How to Post Your VBA Code

This is great help as always, its says it cannot find the value, but it should.

please see

Overtime Request Form.V6 historic by employee ID.xlsm
	E	F
1	Week	Month
2	1	January
3	2	January
4	3	January
5	4	January
6	5	February
7	6	February
8	7	February
9	8	February
10	9	March
11	10	March
12	11	March
13	12	March
14	13	March
15	14	April
16	15	April
17	16	April
18	17	April
19	18	April
20	19	May
21	20	May
22	21	May
23	22	May
24	23	May
25	24	June
26	25	June
27	26	June
28	27	June
29	28	July
30	29	July
31	30	July
32	31	July
33	32	August
34	33	August
35	34	August
36	35	August
37	36	September
38	37	September
39	38	September
40	39	September
41	40	September
42	41	October
43	42	October
44	43	October
45	44	October
46	45	November
47	46	November
48	47	November
49	48	November
50	49	December
51	50	December
52	51	December
53	52	December
Datalist

Cell Formulas
Range	Formula
E2:E53	E2	=SEQUENCE(52)
Dynamic array formulas.

 

[Joe4]

What is the saying the value it cannot find is?

Is it really in column E on sheet "Datalist"?

Are you sure it is EXACTLY the same? Things like extra spaces can cause things not to match.

What cell is it in?

 

[victorel21]

What is the saying the value it cannot find is?

Is it really in column E on sheet "Datalist"?

Are you sure it is EXACTLY the same? Things like extra spaces can cause things not to match.

What cell is it in?

The value is 29, I dont know if its too early to claim victory but i removed the array formula, sequence and it did work this time.

May its not exactly the same because of that. Ill let you know after a few trials, thanks again.