Run time error 91

V

victorel21

New Member
Joined
Jul 8, 2021
Messages
25
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
Sort by date Sort by votes
Im the other module, are they global variables? If so you will need to
VBA Code: 
Set ID = Range("F1")
Set GR = Range("A3")
Set Departamento = Range("C3")
Set Semana = Range("D3")
Set Comentarios = Range("B25")
If you haven't Dim them. I suggest you do in the top of this module or sub
 
Upvote 0
Also, hope you don't mind. I have cleaned up your code


VBA Code: 
Sub todatabase2()
Dim ID, GR, Departamento, Semana, Comentarios, Month As Range

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

Set Month = 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) = Month
        End If
        last_line = last_line + 1
    Loop
End With

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

End Sub
 
Upvote 0
EFANYoutube said:
Im the other module, are they global variables? If so you will need to
VBA Code: 
Set ID = Range("F1")
Set GR = Range("A3")
Set Departamento = Range("C3")
Set Semana = Range("D3")
Set Comentarios = Range("B25")
If you haven't Dim them. I suggest you do in the top of this module or sub
Click to expand...
hello set didnt work , since they are not objects I got an error that an object is needed.

I have dimmed the variables to eliminate any doubt please see below I still get the error.

Sub todatabase2()

Dim ID As Double
Dim GR As String
Dim Departamento As String
Dim semana As Double
Dim comentarios As String

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
 
Upvote 0
EFANYoutube said:
Also, hope you don't mind. I have cleaned up your code


VBA Code: 
Sub todatabase2()
Dim ID, GR, Departamento, Semana, Comentarios, Month As Range

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

Set Month = 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) = Month
        End If
        last_line = last_line + 1
    Loop
End With

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

End Sub
Click to expand...
Use this, in your version your still not setting the range variables, they must be "Set" as the are already a memory reference, your not creating them from nothing
 
Upvote 0
Solution
EFANYoutube said:
Also, hope you don't mind. I have cleaned up your code


VBA Code: 
Sub todatabase2()
Dim ID, GR, Departamento, Semana, Comentarios, Month As Range

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

Set Month = 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) = Month
        End If
        last_line = last_line + 1
    Loop
End With

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

End Sub
Click to expand...
This actually did solve the problem, so i just had to place set infront of month? Also why Dim them as ranges?
 
Upvote 0
victorel21 said:
This actually did solve the problem, so i just had to place set infront of month? Also why Dim them as ranges?
Click to expand...
Note that this line:
VBA Code: 
Dim ID, GR, Departamento, Semana, Comentarios, Month As Range
will only Dim "Month" as Range. All the others will be Variant.

If each variable is not explicitly declared like this:
VBA Code: 
Dim a as Long
Dim b as String
Dim c as Range
or this
VBA Code: 
Dim a as Long, b as String, c as Range
then they default to "Variant"

So in your original example, only "Month" is explicitly declared, so the rest are Variant.

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).
 
Upvote 0
victorel21 said:
This actually did solve the problem, so i just had to place set infront of month? Also why Dim them as ranges?
Click to expand...
Because they are ranges
The other way to do it would be to Dim them as their own spacific data types but you would then have to use, for example
VBA Code: 
With Sheets("Form")
    ID = Range("F1").Value
    GR = Range("A3").Value
    Departamento = Range("C3").Value
    Semana = Range("D3").Value
    Comentarios = Range("B25").Value
End With
Its just easier to let VBA do the leg work and assign as it is, only time I would suggest to do them seperatly like would be if you were checking they were the right inputs
 
Upvote 0
Joe4 said:
Note that this line:
VBA Code: 
Dim ID, GR, Departamento, Semana, Comentarios, Month As Range
will only Dim "Month" as Range. All the others will be Variant.

If each variable is not explicitly declared like this:
VBA Code: 
Dim a as Long
Dim b as String
Dim c as Range
or this
VBA Code: 
Dim a as Long, b as String, c as Range
then they default to "Variant"

So in your original example, only "Month" is explicitly declared, so the rest are Variant.

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).
Click to expand...
Thank you! didnt know, month was a function will change it.
 
Upvote 0
Easy way to know something is a reserved word is without Dim it, try to type it as all lower case, if it proper cases it after clicking off the line, its usually a reserved word
 
Upvote 0
You must log in or register to reply here.

Similar threads

G
  • Solved
Extract data without openning (msoFileDialogFilePicker) the file on the workbook only if it's an Excel type file
Replies
4
Views
642
Gwhaou
G
G
  • Solved
Find header row on the sheet and copy it
Replies
5
Views
463
Alex Blakenburg
A
V
  • Solved
Copy paste special error
Replies
4
Views
371
victorel21
V
willow1985
  • Solved
Find first blank cell in column A in a Table - Runtime error 91
Replies
4
Views
74
willow1985
willow1985
C
  • Solved
VBA Error on userform (Run-time error '91')
Replies
5
Views
79
cerberus1845
C

Forum statistics

Threads
1,214,651
Messages
6,120,738
Members
448,988
Latest member
BB_Unlv

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top