Excel VBA Run Time Error 424 object required

Question

I am totally new in VBA and coding in general  am trying to get data from cells from the same workbook  get framework path      and then to start application  QTP  and run tests   I am getting this error when trying to get values entered in excel cells   Run Time Error  424  object required   I believe I am missing some basic rules but I appreciate your help   Please see below the part of code in question   Option Explicit  Private Sub RunTest Click        Dim envFrmwrkPath As Range     Dim ApplicationName As Range     Dim TestIterationName As Range      Dim wb As Workbook      Dim Batch1 As Worksheets     Dim objEnvVarXML  objfso  app As Object     Dim i  Msgarea      Set envFrmwrkPath   ActiveSheet Range  D6   Value   error displayed here     Set ApplicationName   ActiveSheet Range  D4   Value     Set TestIterationName   ActiveSheet Range  D8   Value

User · Answer

Simply remove the  value from your code   Set envFrmwrkPath   ActiveSheet Range  D6   Value   instead of this  use    Set envFrmwrkPath   ActiveSheet Range  D6

User · Answer

You have two options   -If you want the value   Dim MyValue as Variant   or string date long     MyValue   ThisWorkbook Sheets 1  Range  A1   Value   -if you want the cell object   Dim oCell as Range    or object  but then you ll miss out on intellisense   and both can also contain more than one cell  Set oCell   ThisWorkbook Sheets 1  Range  A1

User · Answer

The first code line  Option Explicit means  in simple terms  that all of your variables have to be explicitly declared by Dim statements  They can be any type  including object  integer  string  or even a variant   This line  Dim envFrmwrkPath As Range is declaring the variable envFrmwrkPath of type Range   This means that you can only set it to a range     This line  Set envFrmwrkPath   ActiveSheet Range  D6   Value is attempting to set the Range type variable to a specific Value that is in cell D6   This could be a integer or a string for example  depends on what you have in that cell  but it s not a range   I m assuming you want the value stored in a variable  Try something like this   Dim MyVariableName As Integer MyVariableName   ActiveSheet Range  D6   Value   This assumes you have a number  like 5  in cell D6   Now your variable will have the value   For simplicity sake of learning  you can remove or comment out the Option Explicit line and VBA will try to determine the type of variables at run time     Try this to get through this part of your code  Dim envFrmwrkPath As String Dim ApplicationName As String Dim TestIterationName As String

User · Answer

Private Sub CommandButton1 Click        Workbooks  Textfile Receiving   Sheets  menu   Range  g1   Value   PROV Text     Workbooks  Textfile Receiving   Sheets  menu   Range  g2   Value   MUN Text     Workbooks  Textfile Receiving   Sheets  menu   Range  g3   Value   CAT Text     Workbooks  Textfile Receiving   Sheets  menu   Range  g4   Value   Label5 Caption      Me Hide      Run  filename   End Sub  Private Sub MUN Change       Dim r As Integer     r   2      While Range  m   amp  CStr r   Value  lt  gt             If Range  m   amp  CStr r   Value   MUN Text Then         Label5 Caption   Range  n   amp  CStr r   Value         End If         r   r   1     Wend  End Sub  Private Sub PROV Change       If PROV Text    LAGUNA  Then         MUN Text              MUN RowSource    Menu M26 M56      ElseIf PROV Text    CAVITE  Then         MUN Text              MUN RowSource    Menu M2 M25      ElseIf PROV Text    QUEZON  Then         MUN Text              MUN RowSource    Menu M57 M97      End If End Sub

[vba] Excel VBA Run Time Error '424' object required

Examples related to vba

Examples related to excel