Object Variable or With Block Variable not set????

[dpiano1984]

Hey everyone. I'm trying to run some code but I keep getting the Object Variable or With Block Variable Not Set message. I'm not using any object in the line that is causing the error. Could someone take a look at the code and let me know what I'm doing wrong?

I'm getting an error on the last line of given code

Private Sub addDescriptions(BucketTotal as Double, Descriptions as String)
Dim DCELL as Range

'''Loop Through Controls'''
For i = 1 to 20
    if Controls("TR" & i).Value = "" Then
        Exit For
    Else
        Set DCELL = Columns("F").Find(Controls("DivRate" & i), Lookin:=xlValues, Lookat:=xlWhole).Offset(0,3)

 

[jimrward]

where is your end if and also your next i

 

[dpiano1984]

I have that in the code. I just didn't place it in the example. The code is a bit extensive:

Private Sub addDescriptions(BucketTotal As Double, Description As String)


    Sheets("Client Report").Activate
    Dim DCELL As Range


    '''Loop through the TR Controls'''
    For i = 1 To 20
        If Controls("TR" & i).Value = "" Then '''Control is blank. Break loop'''
            Exit For
        Else
            If BucketTotal <> 0 Then
                '''Add the following description to the DCELL'''
                If isChecked(Controls("FO" & i)) = True Then
                    Set DCELL = Columns("F").Find(Controls("Bucket" & i), LookIn:=xlValues, Lookat:=xlWhole).Offset(0, 3) '''Look for Bucket Amount in Column F'''
                    DCELL.Value = "" & Description & " " & Controls("TR" & i) & " " & TheDate & " Fund Only With Bucket " & Controls("Bucket" & i) & " Not Posted to Surpas"
                    DCELL.Offset(0, -3).ClearContents
                Else
                    Set DCELL = Columns("F").Find(Controls("Bucket" & i), LookIn:=xlValues, Lookat:=xlWhole).Offset(0, 3) '''Look for Bucket Amount in Column F'''
                    DCELL.Value = "" & Description & " " & Controls("TR" & i) & " " & TheDate & " With Bucket " & Controls("Bucket" & i) & " Not Posted to Surpas"
                    DCELL.Offset(0, -3).ClearContents
                End If
            Else
                If isChecked(Controls("FO" & i)) = True Then
                    Set DCELL = Columns("F").Find(Controls("DivRate" & i), LookIn:=xlValues, Lookat:=xlWhole).Offset(0, 3) '''Look for Div Amount in Column F'''
                    DCELL.Value = "" & Description & " " & Controls("TR" & i) & " " & TheDate & " Fund Only"
                    DCELL.Offset(0, -3).ClearContents
                Else
                    Set DCELL = Columns("F").Find(Controls("DivRate" & i), LookIn:=xlValues, Lookat:=xlWhole).Offset(0, 3) '''Look for Div Amount in Column F'''
                    DCELL.Value = "" & Description & " " & Controls("TR" & i) & " " & TheDate & ""
                    DCELL.Offset(0, -3).ClearContents
                End If
            End If
        End If
    Next i






End Sub

That's the code in it's entirety.

 

[Rick Rothstein]

That's the code in it's entirety.

What about the code in the isChecked function that you call from this code?

 

[dpiano1984]

Would that affect this method? Here's the code for isChecked:

Private Function isChecked(CheckBox As Control) As Boolean



'''Checking to see if Checkbox is checked or not'''
isChecked = False '''Default Value


If CheckBox.Value = True Then
    isChecked = True '''Checkbox is checked, so this is true
Else
    isChecked = False '''Checkbox is unchecked, so this is false
End If




End Function

It's really just seeing if a checkbox is checked. I'm wondering now if that's just Redundant in the literal sense of the word.

 

[Rick Rothstein]

It's really just seeing if a checkbox is checked. I'm wondering now if that's just Redundant in the literal sense of the word.

Yes, it is definitely redundant. As to your problem, I could be missing something that might become more obvious if I could see the actual workbook, but I do not see what the problem could be by just reading your code... it all looks correct to me. One thing that could screw things up is if you do not have a control for each iteration of your For..Next loop (if there was, say, no TR5 control when your loop's i counter hit 5), but other than that, I do not see anything obvious. Can you post your workbook to one of the free file sharing websites out there for us to see directly? If so, http://www.box.net/files is a good one to use (remember to note the URL they give you for your file and post it back here so we can download it).

 

[dpiano1984]

Due to security, I can't.

Can I recode that in another way, though? Maybe it's the way the code is written that's causing the error.

 

[Rick Rothstein]

Due to security, I can't.

Can I recode that in another way, though? Maybe it's the way the code is written that's causing the error.

As I said, I did not see anything obvious that was wrong with your code, but without having the setup in front of me, I cannot test for whatever it is that we are both overlooking. If the security issue is data related, can you remove or replace the sensitive data with junk data? Also, I would not necessarily need full sheets of information... I would think 10 rows would be more than enough. If you could do that and then post it, test the doctored worksheet to make sure the problem still exists before posting it.