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

dpiano1984

New Member
Joined
Feb 8, 2013
Messages
14
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
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)
 

Some videos you may like

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

jimrward

Well-known Member
Joined
Feb 24, 2003
Messages
1,784
Office Version
  1. 2016
  2. 2013
  3. 2011
  4. 2010
  5. 2007
Platform
  1. Windows
where is your end if and also your next i
 

dpiano1984

New Member
Joined
Feb 8, 2013
Messages
14
I have that in the code. I just didn't place it in the example. The code is a bit extensive:

Code:
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.
 

dpiano1984

New Member
Joined
Feb 8, 2013
Messages
14

ADVERTISEMENT

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

Code:
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

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,711
Office Version
  1. 2010
Platform
  1. Windows
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

New Member
Joined
Feb 8, 2013
Messages
14
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

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,711
Office Version
  1. 2010
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,173
Messages
5,600,133
Members
414,365
Latest member
UUR

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
Top