VBA - Run time error when modules combined???

[Madduck]

Hi team,

I have built a program in Excel and when I built it I tested each module
along the way..

Now when I combine all the modules to run on the button click I get a run
time error every time.

I'm not sure why....

it says

Run-time error '-2147417848 (80010108)

Method 'Value' of object 'Range' failed


I did have a different error..something about disconnected data??? but spil
the code to run in segments...

i.e press button one to run one half of the code..
press buton 2 to complete the rest of the code...

any ideas?

 

[Joel]

Post the code. You are not referencing the correct worksheet or you selected
cells are in a different place when you run the code togetther verses runn9ng
the code seperately.

 

[Madduck]

Thanks Joel..

I'm happy to post the lot... but its quite large, so I'll try and pick what
I think are the relevant sections...


* note I added

lastrow = Worksheets("Rift_raid").Range("A200").End(xlUp).Row
Range("A" & lastrow + 1).Select
Application.CutCopyMode = False
after each subroutine call to try and stop the error... it did not work..

Hopefully this all makes sense... thanks in advance for your help...

--------------------------------------------------------------------
Sub update_lists()

Application.ScreenUpdating = False

Enter_false
lastrow = Worksheets("Rift_raid").Range("A200").End(xlUp).Row
Range("A" & lastrow + 1).Select
Application.CutCopyMode = False

Find_and_Clear
lastrow = Worksheets("Rift_raid").Range("A200").End(xlUp).Row
Range("A" & lastrow + 1).Select
Application.CutCopyMode = False

Re_Insert_non_winners
lastrow = Worksheets("Rift_raid").Range("A200").End(xlUp).Row
Range("A" & lastrow + 1).Select
Application.CutCopyMode = False

Move_up
lastrow = Worksheets("Rift_raid").Range("A200").End(xlUp).Row
Range("A" & lastrow + 1).Select
Application.CutCopyMode = False

Re_Insert_Winners
lastrow = Worksheets("Rift_raid").Range("A200").End(xlUp).Row
Range("A" & lastrow + 1).Select
Application.CutCopyMode = False

Application.ScreenUpdating = True

End Sub
--------------------------------------------------------------------
**** Note .. this enters "FALSE" into the linked cell of a tick box... the
tick box drives the subroutine In_raid() ... I think by entering False in
the linked cell it is the same as clicking the tickbox, well at least it
seems to work that way ****

Sub Enter_false()
Dim testrange

' Application.ScreenUpdating = False

Sheets("Rift_raid").Select
lastrow = WorksheetFunction.CountA(Range("A:A")) + 3

Do While lastrow > 3
testrange = "A" & lastrow
If Worksheets("Rift_raid").Cells(lastrow, 1).Value = "False" Then

Else
Worksheets("Rift_raid").Cells(lastrow, 1).Value = "False"
'Range("A" & lastrow).Value = "FALSE"
End If
lastrow = lastrow - 1
Loop

lastrow = Worksheets("Last_Raid_Report").Range("A28").End(xlUp).Row
Range("x" & lastrow + 1).Select

Application.CutCopyMode = False


End Sub
-----------------------------------------------------
*****Note: this is an example of the tickbox code *****

Private Sub twentyseven_Click()

strName = Range("C30")
strLogic = Range("A30")
IntNameset = Range("B30")
strPaste = Range("J30")
in_raid
End Sub
----------------------------------------------------------

Sub in_raid()

Dim lastrow As Long

lastrow = 350

' Application.ScreenUpdating = False

If strLogic = False Then
If Worksheets("Rift_raid").Range("k:k").Find(What:=strName,
After:=[K1], LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows,
SearchDirection:=xlNext, MatchCase:=True) Is Nothing Then

Else
Cells(Worksheets("Rift_raid").Range("k:k").Find(What:=strName,
After:=[K1], LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows,
SearchDirection:=xlNext, MatchCase:=True).Row, 10).Value = ""
Cells(Worksheets("Rift_raid").Range("k:k").Find(What:=strName,
After:=[K1], LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows,
SearchDirection:=xlNext, MatchCase:=True).Row, 11).Value = ""
End If

Else
lastrow = Worksheets("Rift_raid").Range("J200").End(xlUp).Row

If lastrow = 1 Then
Worksheets("Rift_raid").Range("J200").End(xlUp).Offset(3, 0) =
IntNameset
Worksheets("Rift_raid").Range("J200").End(xlUp).Offset(0, 1) =
strName
Else
Worksheets("Rift_raid").Range("J200").End(xlUp).Offset(1, 0) =
IntNameset
Worksheets("Rift_raid").Range("J200").End(xlUp).Offset(0, 1) =
strName
End If
End If

Application.CutCopyMode = False

Range("j4:k200").Select

***** NOTE: This next line is where the Run time error seems to occur *****

Selection.Sort Key1:=Range("j4"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortTextAsNumbers
Worksheets("Rift_raid").Range("J200").End(xlUp).Select

Application.ScreenUpdating = True

End Sub

------------------------------------------------------------------------------

 

[Madduck]

I've done some more testing...

the error is definately in the Enter_False module...

I have seperated all the modules out, it works sometime , but errors alot..

The last run time error I just recieved is

Run-time error '-2147417848 (80010108)

Automation error
The object invoked has disconnected from its clients.


I hit "Debug" and then get the other Run-time error Value of object Range
failed.. as already stated....

maybe ( hopefully) this gives you more to go by?

 

[Joel]

Here is how I would write the code (see below). I avoid the select method
like you did below. I specifically reference each sheet and range as
required to avoid mistakes like you jusdt made.

The main problem is with the statement Range("x" & lastrow + 1).Select. It
was selectiung the wrong worksheet. You had Sheets("Rift_raid") selected but
the last row was from sheet Worksheets("Last_Raid_Report").

Why do you have False as a string with double quotes around False?

Sub Enter_false()
Dim testrange

' Application.ScreenUpdating = False

with Sheets("Rift_raid")
lastrow = WorksheetFunction.CountA(.Range("A:A")) + 3

Do While lastrow > 3
testrange = "A" & lastrow
If .Range("A" & lastrow).Value<> False Then
.Range("A" & lastrow).Value = False
End If
lastrow = lastrow - 1
Loop
end with

lastrow = Worksheets("Last_Raid_Report").Range("A28").End(xlUp).Row
Worksheets("Last_Raid_Report").Range("x" & lastrow + 1).Select

Application.CutCopyMode = False

End Sub

 

[Madduck]

Thanks Joel..

I like what you've done there...
I do love the learning that comes with VB scripting

I have updated the code... I am at a loss though, I still get the error ...

I have found if I open the spreadsheet and just run that subroutine it
works.. if I close and do it again, it works, if I try a third time it gives
me the Run-time error....

Also if I run any other module before doing it it seems to error more
often...

Do you think this could be a memory issue as opposed to a coding issue?

 

[Joel]

the may be some data that is corrupted on the worksheet. Here is a quick way
I somtimes fix these problems.

Go to the row after you last row and click the row number. Then press
Shift-Cntl and press the down arrow so all the rows are highlighted. Then
right click any of the highlighted row numbers and select delete. Then do
the same thing for you columns.

If the above doesn't work then copy you data to a new worksheet. Delete the
old worksheet and rename the name the new worksheet the same as the old
worksheet.

 

[Madduck]

Hi Joel.

thanks again.. but unfortunatley it does not rectify the issue....

Thanks Dana, yes the logic is correct, as the data starts from Row 4 and
there is nothing (at this stage ) in rows 1-3

Thanks again Joel...

I seem to have gotten around the problem by recoding the tickbox code.


Now before placing "False" in the cells causing the tickbox code to run, I
clear A:A
then in each of the tick box codes I entered this code... therefore it only
does anything extra when there is a value in the controlling cell....

If Range("A4").Value <> "" Then

strName = Range("C85")
strLogic = Range("A85")
IntNameset = Range("B85")
strPaste = Range("J85")

in_raid
End If

lastrow = WorksheetFunction.CountA(.Range("A:A")) + 3

Hi. With data in A100, LastRow returns 4. Is the logic ok?

Sub Demo()
Dim LastRow As Long
[A:A].Clear
[A100] = "Junk"

'Last Row is 4
LastRow = WorksheetFunction.CountA(Range("A:A")) + 3
End Sub

--
Dana DeLouis

I've done some more testing...

the error is definately in the Enter_False module...

I have seperated all the modules out, it works sometime , but errors alot..

The last run time error I just recieved is

Run-time error '-2147417848 (80010108)

Automation error
The object invoked has disconnected from its clients.


I hit "Debug" and then get the other Run-time error Value of object Range
failed.. as already stated....

maybe ( hopefully) this gives you more to go by?