Object Required Error in excel VBA

Question

Dim g1val  g2val As Integer  Set g1val   0 Set g2val   0  For i   3 To 18     If g1val  gt  Cells 33  i  Value Then         g1val   g1val     Else         g1val   Cells 33  i  Value     End If Next i  For j   32 To 57     If g2val  gt  Cells 31  j  Value Then         g2val   g2val     Else         g2val   Cells 31  j  Value     End If Next j   Here on second line i am getting an error saying object required I have tried to make g1val and g2val as  Double  and tried to give 1 for their values at first But those didn t work out  Can u help

User · Accepted Answer

In order to set the value of integer variable we simply assign the value to it  eg g1val   0 where as set keyword is used to assign value to object   Sub test    Dim g1val  g2val As Integer    g1val   0   g2val   0      For i   3 To 18       If g1val  gt  Cells 33  i  Value Then         g1val   g1val     Else        g1val   Cells 33  i  Value      End If      Next i      For j   32 To 57         If g2val  gt  Cells 31  j  Value Then            g2val   g2val         Else           g2val   Cells 31  j  Value         End If     Next j  End Sub

User · Answer

The Set statement is only used for object variables  like Range  Cell or Worksheet in Excel   while the simple equal sign     is used for elementary datatypes like Integer  You can find a good explanation for when to use set here   The other problem is  that your variable g1val isn t actually declared as Integer  but has the type Variant  This is because the Dim statement doesn t work the way you would expect it  here  see example below   The variable has to be followed by its type right away  otherwise its type will default to Variant  You can only shorten your Dim statement this way   Dim intColumn As Integer  intRow As Integer   This creates two integers   For this reason  you will see the  Empty  instead of the expected  0  in the Watches window   Try this example to understand the difference   Sub Dimming      Dim thisBecomesVariant  thisIsAnInteger As Integer   Dim integerOne As Integer  integerTwo As Integer    MsgBox TypeName thisBecomesVariant    Will display  Empty    MsgBox TypeName thisIsAnInteger     Will display  Integer    MsgBox TypeName integerOne     Will display  Integer    MsgBox TypeName integerTwo     Will display  Integer      By assigning an Integer value to a Variant it becomes Integer  too   thisBecomesVariant   0   MsgBox TypeName thisBecomesVariant    Will display  Integer   End Sub     Two further notices on your code   First remark  Instead of writing   If g1val is bigger than the value in the current cell If g1val  gt  Cells 33  i  Value Then   g1val   g1val    Don t change g1val Else   g1val   Cells 33  i  Value   Otherwise set g1val to the cell s value End If   you could simply write   If g1val is smaller or equal than the value in the current cell If g1val  lt   Cells 33  i  Value Then   g1val   Cells 33  i  Value   Set g1val to the cell s value  End If   Since you don t want to change g1val in the other case   Second remark  I encourage you to use Option Explicit when programming  to prevent typos in your program  You will then have to declare all variables and the compiler will give you a warning if a variable is unknown

[vba] Object Required Error in excel VBA

Examples related to vba

Examples related to excel