Method 'Cells' Of Object '_Worksheet' Failed

I

isaiasenzo

New Member
Joined
Feb 26, 2019
Messages
4
Hello,
I am a bit of a newbie to VBA and macros.
I am currently using the following code to download data in real time. The data is downloaded from an Excel sheet that is updated in real time.
At first it gives an error, I give it ok and the data is saved, but when I want to add this data, an error appears: Error

Runtime error '-2147417848':
Method 'Rows' of object '_Global' Error

and the macro stops running.
Can someone help me with this?

The code is the following:


//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

Dim Habla As String
Dim Habla1 As String
Dim Temp1 As Double
Dim TempC1 As Double
Dim TempC2 As Double
Dim Temp2 As Double
Dim TempP1 As Double
Dim TempP2 As Double
Dim hablatemp1 As Double
Dim hablatemp2 As Double

Private Sub Worksheet_Calculate()

Dim ultimafilaauxiliarZN1 As Long

If (Range("D23").Value <> Temp1) Then

If (ThisWorkbook.Sheets("Hoja1").Range("U" & Rows.Count).End(xlUp).Row) > (ThisWorkbook.Sheets("Hoja1").Range("L" & Rows.Count).End(xlUp).Row) Then
ultimafilaauxiliarZN1 = ThisWorkbook.Sheets("Hoja1").Range("U" & Rows.Count).End(xlUp).Row - 1
Else
ultimafilaauxiliarZN1 = ThisWorkbook.Sheets("Hoja1").Range("L" & Rows.Count).End(xlUp).Row
End If

If ((Range("D23").Value - Temp1) > Range("N1").Value) Then

If (Temp1 > 0) Then
ThisWorkbook.Sheets("Hoja1").Cells(ultimafilaauxiliarZN1 + 1, 14) = Range("D23").Value - Temp1 'HERE GIVES THE ERROR THE DEBUG
ThisWorkbook.Sheets("Hoja1").Cells(ultimafilaauxiliarZN1 + 1, 16) = Range("D33").Value - TempC1
ThisWorkbook.Sheets("Hoja1").Cells(ultimafilaauxiliarZN1 + 1, 17) = Range("D34").Value - TempC2
End If
ThisWorkbook.Sheets("Hoja1").Cells(ultimafilaauxiliarZN1 + 1, 12) = Range("D23").Value
ThisWorkbook.Sheets("Hoja1").Cells(ultimafilaauxiliarZN1 + 1, 13) = Range("D44").Value
ThisWorkbook.Sheets("Hoja1").Cells(ultimafilaauxiliarZN1 + 1, 15) = Time
hablatemp1 = Range("D23").Value - Temp1
ThisWorkbook.Sheets("Hoja1").Cells(ultimafilaauxiliarZN1 + 1, 18) = Range("D48").Value
ThisWorkbook.Sheets("Hoja1").Cells(ultimafilaauxiliarZN1 + 1, 19) = Range("D49").Value

If ((hablatemp1) > "1") Then
Habla1 = Range("D2")
Application.Speech.Speak Habla1
End If
End If

Temp1 = Range("D23").Value
TempC1 = Range("D33").Value
TempC2 = Range("D34").Value

End If

If (Range("D24").Value <> Temp2) Then

If (ThisWorkbook.Sheets("Hoja1").Range("U" & Rows.Count).End(xlUp).Row) > (ThisWorkbook.Sheets("Hoja1").Range("L" & Rows.Count).End(xlUp).Row) Then
ultimafilaauxiliarZN1 = ThisWorkbook.Sheets("Hoja1").Range("U" & Rows.Count).End(xlUp).Row
Else
ultimafilaauxiliarZN1 = ThisWorkbook.Sheets("Hoja1").Range("L" & Rows.Count).End(xlUp).Row
End If

If ((Range("D24").Value - Temp2) > Range("AA1").Value) Then

If (Temp2 > 0) Then
ThisWorkbook.Sheets("Hoja1").Cells(ultimafilaauxiliarZN1 + 1, 27) = Range("D24").Value - Temp2
ThisWorkbook.Sheets("Hoja1").Cells(ultimafilaauxiliarZN1 + 1, 29) = Range("E33").Value - TempP1
ThisWorkbook.Sheets("Hoja1").Cells(ultimafilaauxiliarZN1 + 1, 30) = Range("E34").Value - TempP2
End If

ThisWorkbook.Sheets("Hoja1").Cells(ultimafilaauxiliarZN1 + 1, 25) = Range("D24").Value
ThisWorkbook.Sheets("Hoja1").Cells(ultimafilaauxiliarZN1 + 1, 26) = Range("D45").Value
ThisWorkbook.Sheets("Hoja1").Cells(ultimafilaauxiliarZN1 + 1, 28) = Time
hablatemp2 = Range("D24").Value - Temp2
ThisWorkbook.Sheets("Hoja1").Cells(ultimafilaauxiliarZN1 + 1, 31) = Range("D48").Value
ThisWorkbook.Sheets("Hoja1").Cells(ultimafilaauxiliarZN1 + 1, 32) = Range("D49").Value

If ((hablatemp2) > "1000") Then

Habla1 = Range("D2")
Application.Speech.Speak Habla1
End If
End If

Temp2 = Range("D24").Value
TempP1 = Range("E33").Value
TempP2 = Range("E34").Value

End If

End Sub




Regards

ISAIAS
 
Sort by date Sort by votes
You really ought to qualify your references.

Everywhere it says something like Range("A1").Value, I presume you want ActiveSheet.Range("A1").Value.

Where it says Rows.Count, you probably want ThisWorkbook.Sheets("Hoja1").Rows.Count.

Also, wherever it says something like ThisWorkbook.Sheets("Hoja1").Cells(ultimafilaauxiliarZN1 + 1, 14) =,
you should specify that you want ThisWorkbook.Sheets("Hoja1").Cells(ultimafilaauxiliarZN1 + 1, 14).Value =.
 
Upvote 0
It keeps giving the same error, I don't know what it could be.




VBA Code: 
Dim Habla As String
Dim Habla1 As String
Dim Temp1 As Double
Dim TempC1 As Double
Dim TempC2 As Double
Dim Temp2 As Double
Dim TempP1 As Double
Dim TempP2 As Double
Dim hablatemp1 As Double
Dim hablatemp2 As Double

Private Sub Worksheet_Calculate()

Dim ultimafilaauxiliarZN1 As Long

     If (ActiveSheet.Range("D23").Value <> Temp1) Then

                If (ThisWorkbook.Sheets("Hoja1").Range("U" & ThisWorkbook.Sheets("Hoja1").Rows.Count).End(xlUp).Row) > (ThisWorkbook.Sheets("Hoja1").Range("L" & ThisWorkbook.Sheets("Hoja1").Rows.Count).End(xlUp).Row) Then
                        ultimafilaauxiliarZN1 = ThisWorkbook.Sheets("Hoja1").Range("U" & ThisWorkbook.Sheets("Hoja1").Rows.Count).End(xlUp).Row - 1
                Else
                        ultimafilaauxiliarZN1 = ThisWorkbook.Sheets("Hoja1").Range("L" & ThisWorkbook.Sheets("Hoja1").Rows.Count).End(xlUp).Row
                End If
   
                If ((ActiveSheet.Range("D23").Value - Temp1) > ActiveSheet.Range("N1").Value) Then
               
                If (Temp1 > 0) Then
        ThisWorkbook.Sheets("Hoja1").Cells(ultimafilaauxiliarZN1 + 1, 14) = ActiveSheet.Range("D23").Value - Temp1
        ThisWorkbook.Sheets("Hoja1").Cells(ultimafilaauxiliarZN1 + 1, 16) = ActiveSheet.Range("D33").Value - TempC1
        ThisWorkbook.Sheets("Hoja1").Cells(ultimafilaauxiliarZN1 + 1, 17) = ActiveSheet.Range("D34").Value - TempC2
        End If       
        ThisWorkbook.Sheets("Hoja1").Cells(ultimafilaauxiliarZN1 + 1, 12) = ActiveSheet.Range("D23").Value
        ThisWorkbook.Sheets("Hoja1").Cells(ultimafilaauxiliarZN1 + 1, 13) = ActiveSheet.Range("D44").Value
        ThisWorkbook.Sheets("Hoja1").Cells(ultimafilaauxiliarZN1 + 1, 15) = Time
        hablatemp1 = ActiveSheet.Range("D23").Value - Temp1
        ThisWorkbook.Sheets("Hoja1").Cells(ultimafilaauxiliarZN1 + 1, 18) = ActiveSheet.Range("D48").Value
        ThisWorkbook.Sheets("Hoja1").Cells(ultimafilaauxiliarZN1 + 1, 19) = ActiveSheet.Range("D49").Value
       
                      If ((hablatemp1) > "1") Then
                       Habla1 = Range("D2")
                        Application.Speech.Speak Habla1
                    End If
        End If

        Temp1 = ActiveSheet.Range("D23").Value
        TempC1 = ActiveSheet.ActiveSheet.Range("D33").Value
        TempC2 = ActiveSheet.Range("D34").Value
 
     End If

     If (ActiveSheet.Range("D24").Value <> Temp2) Then
    
                If (ThisWorkbook.Sheets("Hoja1").Range("U" & ThisWorkbook.Sheets("Hoja1").Rows.Count).End(xlUp).Row) > (ThisWorkbook.Sheets("Hoja1").Range("L" & ThisWorkbook.Sheets("Hoja1").Rows.Count).End(xlUp).Row) Then
                        ultimafilaauxiliarZN1 = ThisWorkbook.Sheets("Hoja1").Range("U" & ThisWorkbook.Sheets("Hoja1").Rows.Count).End(xlUp).Row
                Else
                        ultimafilaauxiliarZN1 = ThisWorkbook.Sheets("Hoja1").Range("L" & ThisWorkbook.Sheets("Hoja1").Rows.Count).End(xlUp).Row
                End If

            If ((ActiveSheet.Range("D24").Value - Temp2) > ActiveSheet.Range("AA1").Value) Then
                  
            If (Temp2 > 0) Then
            ThisWorkbook.Sheets("Hoja1").Cells(ultimafilaauxiliarZN1 + 1, 27) = ActiveSheet.Range("D24").Value - Temp2
            ThisWorkbook.Sheets("Hoja1").Cells(ultimafilaauxiliarZN1 + 1, 29) = ActiveSheet.Range("E33").Value - TempP1
            ThisWorkbook.Sheets("Hoja1").Cells(ultimafilaauxiliarZN1 + 1, 30) = ActiveSheet.Range("E34").Value - TempP2
            End If
                   
        ThisWorkbook.Sheets("Hoja1").Cells(ultimafilaauxiliarZN1 + 1, 25) = ActiveSheet.Range("D24").Value
        ThisWorkbook.Sheets("Hoja1").Cells(ultimafilaauxiliarZN1 + 1, 26) = ActiveSheet.Range("D45").Value
        ThisWorkbook.Sheets("Hoja1").Cells(ultimafilaauxiliarZN1 + 1, 28) = Time
        hablatemp2 = ActiveSheet.Range("D24").Value - Temp2
        ThisWorkbook.Sheets("Hoja1").Cells(ultimafilaauxiliarZN1 + 1, 31) = ActiveSheet.Range("D48").Value
        ThisWorkbook.Sheets("Hoja1").Cells(ultimafilaauxiliarZN1 + 1, 32) = ActiveSheet.Range("D49").Value
       
                     If ((hablatemp2) > "1000") Then
   
                       Habla1 = Range("D2")
                        Application.Speech.Speak Habla1
                    End If
            End If
       
        Temp2 = ActiveSheet.Range("D24").Value
        TempP1 = ActiveSheet.Range("E33").Value
        TempP2 = ActiveSheet.Range("E34").Value
       
     End If

End Sub
 
Last edited by a moderator:
Upvote 0
What kind of code has the error? What in the whole error message?
 
Upvote 0
You must log in or register to reply here.

Similar threads

sharky12345
  • Question Question
Loop Until number within For Each Cell
Replies
1
Views
36
Joe4
Joe4
J
  • Question Question
Send bulk email attachment error
Replies
3
Views
295
JOHATUP
J
K
  • Solved
vba run time error 424
Replies
3
Views
122
Peter_SSs
Peter_SSs
Burrgogi
  • Question Question
Need some help with VBA - evaluating a string of numbers and from VLOOKUP routine.
Replies
2
Views
84
StephenCrump
S
B
  • Question Question
Excel VBA Object Variable or With Block Variable Not Set Error
2 3
Replies
28
Views
596
Alex Blakenburg
A

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