CODEWITHSUNDEEP
vbaexcel
LBPLC
LBPLC

Reputation: 1571

VBA method 'range of object' _Worksheet failed suddenly coming up when running code?

This code used to run fine, then all of a sudden it started failing on "method 'range of object' _Worksheet failed"? ' I'm not an expert with VBA, but I can get myself around it. Is there something I'm missing?

Public Sub Run_Count_Click()

'// Set Ranges
Dim Cr_1, CR1_range, _
Cr_2, CR2_range, _
Cr_3, CR3_range, _
Cr_4, CR4_range, _
Cr_5, CR5_range _
As Range

'// Set Integers
Dim CR1, V1, CR1_Result, _
CR2, V2, CR2_Result, _
CR3, V3, CR3_Result, _
CR4, V4, CR4_Result, _
CR5, V5, CR5_Result, _
total_result, _
total_result2, _
total_result3, _
total_result4, _
total_result5 _
As Integer

'Set Strings
Dim V_1, V_2, V_3, V_4, V_5 As String

Dim ws As Worksheet



Set ws = Worksheets("database")

'// Get Criteria From Form And Search Database Headers
Set Cr_1 = ws.Cells.Find(What:=Me.Count_Criteria_1.Value, After:=Cells(1, 1), MatchCase:=False)

If Not Cr_1 Is Nothing Then

CR1 = Cr_1.Column '//Set CR1 as the Column in which the Criteria Header was found

Else
    MsgBox "Criteria 1 Has Not Been Found In The Database. Report Has Failed To Generate"
    Exit Sub
End If

'// Get Variable Value From Form And Set Shortcode
V_1 = Me.Criteria_1_Variable.Value


Set CR1_range = ws.Range(Cells(5, CR1), Cells(Rows.count, CR1))
CR1_Result = Application.CountIf(CR1_range, V_1)

If Me.Count_Criteria_2 = "Any" Then

Me.Count_Result.visible = True

Me.Count_Result.Value = "Based On Your Search Criteria Of:" & vbNewLine & _
"How many occurences of [" & Me.Criteria_1_Variable.Value & "] in the category [" & Me.Count_Criteria_1.Value & _
"] have occured between the dates..." & vbNewLine & vbNewLine & "The Results Are: " & CR1_Result

Exit Sub

The code fails on this line:

Set CR1_range = ws.Range(Cells(5, CR1), Cells(Rows.count, CR1))

Thanks in advance guys

Upvotes: 15

Views: 65317

Answers (3)

JeffSea
JeffSea

Reputation: 385

I ran into the same error message but the cause was different for me. I am only referencing the active worksheet. Apparently the default ReferenceStyle was changed for a few of my users that all have the same VBA logic. Not sure if this was a result of application updates or if multiple users managed to make the same change. Debugging the logic I found that the PrintArea representation would no longer convert to a Range. 

wS.Range(wS.PageSetup.PrintArea).Address

The PrintArea value was populated but shown in a format that unfamiliar to me. Turns out it was R1C1 style instead of the default A1 style cell reference. I found another post that referenced changing the reference style in their user settings but I did not want to force that change on my users. I decided to set the reference style for this workbook each time it was opened. I hooked into Workbook_Open and used xlA1.

Application.ReferenceStyle = xlA1

Upvotes: 0

azar
azar

Reputation: 11

Excel VBA method range of object _global failed?

Sub kar()
Dim i, j
For i = 8 To 15
   For j = 9 To 15

     If (Range("ji") = Range("jj") And Range("gi") = "خريد" And Range("gj") = "فروش" And (Abs(Range("ki") - Range("kj") <= 50))) Then
     Range("ui") = -(Range("ki") - Range("kj")) * hi
     Range("uj") = -(Range("ki") - Range("kj")) * hj
     End If

     If (Range("ji") = Range("jj") And Range("gi") = "فروش" And Range("gj") = "خريد" And (Abs(Range("ki") - Range("kj")) <= 50)) Then
     Range("uj").Value = -(Range("ki") - Range("kj")) * hi
     Range("ui").Value = -(Range("ki") - Range("kj")) * hj
     End If

   Next j
Next i
End Sub

Upvotes: 1

Joe Laviano
Joe Laviano

Reputation: 1048

The Cells are being referenced from the activesheet, not "database".

Set CR1_range = ws.Range(ws.Cells(5, CR1), ws.Cells(ws.Rows.count, CR1))

Upvotes: 30

Related Questions