VBA Code, If, End if, Block errors

C

caseyc17

New Member
Joined
Oct 26, 2010
Messages
31
Hello everyone,

I have a code that takes Access form info, transfers it to an excel template. works well, until now.

I had to create a new template and depending on data in the population determine which form to open.

So if there is a flag open one template, otherwise open the other.

My code is below. Im stuck on how to enter the End If's I keep getting errors. I've moved the End Ifs around a little to trouble shoot... going to pound head against wall now!!!

Any help would be appreciated.

CODE: ​
Private Sub Value_Calculation_Click()
On Error GoTo Err_Value_Calculation_Click
Dim oXL As Object
Dim oExcel As Object
Dim sFullPath As String
Dim sPath As String

If Value.Me.EMPLOYEE_CATEGORY_NAME = "Y" Then
Set oXL = CreateObject("Excel.Application")
sFullPath = CurrentProject.Path & "\Participants\Templates\Code Staff Stock Outstanding Template"
End If
 With oXL
.Visible = True
.workbooks.Open (sFullPath)
.Range("A8").Value = Me.[Active_Terms_Sept_End.Name].Value
.Range("c10").Value = Me.[2009 Award Plan.TOTAL_INITIAL_DEFERRED_AWARD].Value
.Range("c12").Value = Me.[June2010_Principle].Value
.Range("e12").Value = Me.[June2010_Interest].Value
.Range("f12").Value = Me.[Total_2010_Pmt].Value
.Range("c13").Value = Me.[June2011_Principle].Value
.Range("e13").Value = Me.[June2011_Interest].Value
.Range("f13").Value = Me.[Total_2011_Pmt].Value
.Range("c14").Value = Me.[June2012_Principle].Value
.Range("e14").Value = Me.[June2012_Interest].Value
.Range("f14").Value = Me.[Total_2012_Pmt].Value

.Range("c17").Value = Me.[2010 Award Plan.TOTAL_DEFERRED_AWARD].Value
.Range("c19").Value = Me.[June2010_Vesting_Principle].Value
.Range("e19").Value = Me.[June2010_Vesting_Interest].Value
.Range("h19").Value = Me.[June2010_Payment_Type].Value
.Range("i20").Value = Me.[June2011_Payment].Value
.Range("h20").Value = Me.[June2011_Payment_Type].Value
.Range("i21").Value = Me.[June2012_Payment].Value
.Range("h21").Value = Me.[June2012_Payment_Type].Value

.Range("c24").Value = Me.[2010 Top Slice Plan.TOTAL_INITIAL_DEFERRED_AWARD].Value
.Range("c26").Value = Me.[January2011_Vesting_Principle].Value
.Range("h26").Value = Me.[January2011_Payment_Type].Value
.Range("i26").Value = Me.[January2011_Shares].Value
.Range("c27").Value = Me.[January2012_Vesting_Principle].Value
.Range("h27").Value = Me.[January2012_Payment_Type].Value
.Range("i27").Value = Me.[January2012_Shares].Value
.Range("c28").Value = Me.[January2013_Vesting_Principle].Value
.Range("h28").Value = Me.[January2013_Payment_Type].Value
.Range("i28").Value = Me.[January2013_Shares].Value


If Value.Me.EMPLOYEE_CATEGORY_NAME Is Null Then

Set oXL = CreateObject("Excel.Application")
sFullPath = CurrentProject.Path & "\Participants\Templates\Stock Outstanding Template"
End If
 With oXL
.Visible = True
.workbooks.Open (sFullPath)
.Range("A8").Value = Me.[Active_Terms_Sept_End.Name].Value
.Range("c10").Value = Me.[2009 Award Plan.TOTAL_INITIAL_DEFERRED_AWARD].Value
.Range("c12").Value = Me.[June2010_Principle].Value
.Range("e12").Value = Me.[June2010_Interest].Value
.Range("f12").Value = Me.[Total_2010_Pmt].Value
.Range("c13").Value = Me.[June2011_Principle].Value
.Range("e13").Value = Me.[June2011_Interest].Value
.Range("f13").Value = Me.[Total_2011_Pmt].Value
.Range("c14").Value = Me.[June2012_Principle].Value
.Range("e14").Value = Me.[June2012_Interest].Value
.Range("f14").Value = Me.[Total_2012_Pmt].Value

.Range("c17").Value = Me.[2010 Award Plan.TOTAL_DEFERRED_AWARD].Value
.Range("c19").Value = Me.[June2010_Vesting_Principle].Value
.Range("e19").Value = Me.[June2010_Vesting_Interest].Value
.Range("h19").Value = Me.[June2010_Payment_Type].Value
.Range("i20").Value = Me.[June2011_Payment].Value
.Range("h20").Value = Me.[June2011_Payment_Type].Value
.Range("i21").Value = Me.[June2012_Payment].Value
.Range("h21").Value = Me.[June2012_Payment_Type].Value

.Range("c24").Value = Me.[2010 Top Slice Plan.TOTAL_INITIAL_DEFERRED_AWARD].Value
.Range("c26").Value = Me.[January2011_Vesting_Principle].Value
.Range("h26").Value = Me.[January2011_Payment_Type].Value
.Range("i26").Value = Me.[January2011_Shares].Value
.Range("c27").Value = Me.[January2012_Vesting_Principle].Value
.Range("h27").Value = Me.[January2012_Payment_Type].Value
.Range("i27").Value = Me.[January2012_Shares].Value
.Range("c28").Value = Me.[January2013_Vesting_Principle].Value
.Range("h28").Value = Me.[January2013_Payment_Type].Value
.Range("i28").Value = Me.[January2013_Shares].Value

End With

Exit_Value_Calculation_Click:
Exit Sub
Err_Value_Calculation_Click:
MsgBox Err.Description
Resume Exit_Value_Calculation_Click

End Sub
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
Sort by date Sort by votes
It appears that you are missing an <End with> for the first With 0xl. Also if the two With 0xl are the same except for the path it would be cleaner to do this:

Rich (BB code): 
​
Private Sub Value_Calculation_Click()
On Error GoTo Err_Value_Calculation_Click
Dim oXL As Object
    Dim oExcel As Object
    Dim sFullPath As String
    Dim sPath As String

If Value.Me.EMPLOYEE_CATEGORY_NAME = "Y" Then
    sFullPath = CurrentProject.Path & "\Participants\Templates\Code Staff Stock Outstanding Template"
else
    sFullPath = CurrentProject.Path & "\Participants\Templates\Stock Outstanding Template"
end if

Set oXL = CreateObject("Excel.Application")
With oXL
        .Visible = True
        .workbooks.Open (sFullPath)
        .Range("A8").Value = Me.[Active_Terms_Sept_End.Name].Value
        .Range("c10").Value = Me.[2009 Award Plan.TOTAL_INITIAL_DEFERRED_AWARD].Value
        .Range("c12").Value = Me.[June2010_Principle].Value
        .Range("e12").Value = Me.[June2010_Interest].Value
        .Range("f12").Value = Me.[Total_2010_Pmt].Value
        .Range("c13").Value = Me.[June2011_Principle].Value
        .Range("e13").Value = Me.[June2011_Interest].Value
        .Range("f13").Value = Me.[Total_2011_Pmt].Value
        .Range("c14").Value = Me.[June2012_Principle].Value
        .Range("e14").Value = Me.[June2012_Interest].Value
        .Range("f14").Value = Me.[Total_2012_Pmt].Value
        
        .Range("c17").Value = Me.[2010 Award Plan.TOTAL_DEFERRED_AWARD].Value
        .Range("c19").Value = Me.[June2010_Vesting_Principle].Value
        .Range("e19").Value = Me.[June2010_Vesting_Interest].Value
        .Range("h19").Value = Me.[June2010_Payment_Type].Value
        .Range("i20").Value = Me.[June2011_Payment].Value
        .Range("h20").Value = Me.[June2011_Payment_Type].Value
        .Range("i21").Value = Me.[June2012_Payment].Value
        .Range("h21").Value = Me.[June2012_Payment_Type].Value
        
        .Range("c24").Value = Me.[2010 Top Slice Plan.TOTAL_INITIAL_DEFERRED_AWARD].Value
        .Range("c26").Value = Me.[January2011_Vesting_Principle].Value
        .Range("h26").Value = Me.[January2011_Payment_Type].Value
        .Range("i26").Value = Me.[January2011_Shares].Value
        .Range("c27").Value = Me.[January2012_Vesting_Principle].Value
        .Range("h27").Value = Me.[January2012_Payment_Type].Value
        .Range("i27").Value = Me.[January2012_Shares].Value
        .Range("c28").Value = Me.[January2013_Vesting_Principle].Value
        .Range("h28").Value = Me.[January2013_Payment_Type].Value
        .Range("i28").Value = Me.[January2013_Shares].Value

End With
             
Exit_Value_Calculation_Click:
    Exit Sub
Err_Value_Calculation_Click:
    MsgBox Err.Description
    Resume Exit_Value_Calculation_Click
 
End Sub

Jack
 
Upvote 0
Thank you so much for responding Jack.

The ranges and values will be changing, I wanted to make sure i can use this type of coding and logic before I grind through that part.

What am I missing with the oXL? Shoudl there be 2 separate oXL's?
 
Upvote 0
caseyc17 said:
Thank you so much for responding Jack.

The ranges and values will be changing, I wanted to make sure i can use this type of coding and logic before I grind through that part.

What am I missing with the oXL? Shoudl there be 2 separate oXL's?
Click to expand...

His sentence was a bit cut off. He meant to say you were missing an

"End With"

just before your END IF that falls before the second IF.
 
Upvote 0
Ok, I changed it, but now it is saying "Object Required." If I put the "End with" after the "end if" then the "with oXL"'s don't work.

I'm wondering if I need to re-do the coding from scratch... Seem to remember a case where you could "Name" the Subs and run a different sub depending on the if then?


If possible, i would like to stick with what I have so far... Any additional help would be appreciated.

Here's the updated code:

Private Sub Value_Calculation_Click()
On Error GoTo Err_Value_Calculation_Click
Dim oXL As Object
Dim oExcel As Object
Dim sFullPath As String
Dim sPath As String

If Value.Me.EMPLOYEE_CATEGORY_NAME = "Y" Then

Set oXL = CreateObject("Excel.Application")
sFullPath = CurrentProject.Path & "\Participants\Templates\Code Staff Stock Outstanding Template"
End If
With oXL
.Visible = True
.workbooks.Open (sFullPath)
.Range("A8").Value = Me.[Active_Terms_Sept_End.Name].Value
.Range("c10").Value = Me.[2009 Award Plan.TOTAL_INITIAL_DEFERRED_AWARD].Value
.Range("c12").Value = Me.[June2010_Principle].Value
.Range("e12").Value = Me.[June2010_Interest].Value

End With

If Value.Me.EMPLOYEE_CATEGORY_NAME Is Null Then

Set oXL = CreateObject("Excel.Application")
sFullPath = CurrentProject.Path & "\Participants\Templates\Stock Outstanding Template"
End If

With oXL
.Visible = True
.workbooks.Open (sFullPath)
.Range("A8").Value = Me.[Active_Terms_Sept_End.Name].Value
.Range("c10").Value = Me.[2009 Award Plan.TOTAL_INITIAL_DEFERRED_AWARD].Value

End With
Exit_Value_Calculation_Click:
Exit Sub
Err_Value_Calculation_Click:
MsgBox Err.Description
Resume Exit_Value_Calculation_Click

End Sub
 
Upvote 0
You also have an extra Set oXL = CreateObject...etc. which you don't need. And you have some strange stuff (see my notes) which doesn't make sense. Also, you should be using an workbook object so you can use the same oXL object for any of them (you only need the one application object). Also you seem to be trying to open the same template more than once. What is with that?

Also, you must use the file extension as well.


Rich (BB code): 
Private Sub Value_Calculation_Click()
On Error GoTo Err_Value_Calculation_Click
Dim oXL As Object
Dim oExcel As Object <---You really don't need this one too
Dim sFullPath As String
Dim sPath As String
 
If Value.Me.EMPLOYEE_CATEGORY_NAME = "Y" Then
 
   Set oXL = CreateObject("Excel.Application")
   sFullPath = CurrentProject.Path & "\Participants\Templates\Code Staff Stock Outstanding Template.xls" <---Needs the file extension
End If
 
With oXL
   .Visible = True
   .workbooks.Open (sFullPath)
   .Range("A8").Value = Me.[Active_Terms_Sept_End.Name].Value
   .Range("c10").Value = Me.[2009 Award Plan.TOTAL_INITIAL_DEFERRED_AWARD].Value <---What is this?  It looks like you are trying to refer to a table or query and you can't do it like that.
 
 
   .Range("c12").Value = Me.[June2010_Principle].Value
   .Range("e12").Value = Me.[June2010_Interest].Value
 
End With
 
If Value.Me.EMPLOYEE_CATEGORY_NAME Is Null Then  <---This is wrong
If IsNull(Me.EMPLOYEE_CATEGORY_NAME) Then <--- This is correct instead
  Set oXL = CreateObject("Excel.Application") <---Get rid of this
 
   sFullPath = CurrentProject.Path & "\Participants\Templates\Stock Outstanding Template.xls" <---Needs the file extension (also this is the same file as before
End If
 
With oXL
   .Visible = True
   .workbooks.Open (sFullPath)
   .Range("A8").Value = Me.[Active_Terms_Sept_End.Name].Value
   .Range("c10").Value = Me.[2009 Award Plan.TOTAL_INITIAL_DEFERRED_AWARD].Value <---What is this?  It looks like you are trying to refer to a table or query and you can't do it like that.
 
End With
 
Exit_Value_Calculation_Click:
Exit Sub
Err_Value_Calculation_Click:
MsgBox Err.Description
Resume Exit_Value_Calculation_Click
 
End Sub
 
Upvote 0
Thanks for your help Bob,

It still isn't working. I'm getting a can't find object error now. I went through some of my texts. I'm stumped.
 
Upvote 0
Code: 
Private Sub Value_Calculation_Click()
 
On Error GoTo Err_Value_Calculation_Click
 
 Dim oXL As Object
 Dim oExcel As Object <---You really don't need this one too
 Dim sFullPath As String
 Dim sPath As String
 
 Set oXL = CreateObject("Excel.Application") 
 
 If Value.Me.EMPLOYEE_CATEGORY_NAME = "Y" Then
  
  sFullPath = CurrentProject.Path & "\Participants\Templates\Code Staff Stock Outstanding Template.xls" <---Needs the file extension
  
  With oXL
   .Visible = True
   .workbooks.Open (sFullPath)
   .Range("A8").Value = Me.[Active_Terms_Sept_End.Name].Value
   .Range("c10").Value = Me.[2009 Award Plan.TOTAL_INITIAL_DEFERRED_AWARD].Value <---What is this?  It looks like you are trying to refer to a table or query and you can't do it like that.
      
   .Range("c12").Value = Me.[June2010_Principle].Value
   .Range("e12").Value = Me.[June2010_Interest].Value
  End With
 else
  If Value.Me.EMPLOYEE_CATEGORY_NAME Is Null Then  <---This is wrong
  If IsNull(Me.EMPLOYEE_CATEGORY_NAME) Then <--- This is correct instead
    sFullPath = CurrentProject.Path & "\Participants\Templates\Stock Outstanding Template.xls" <---Needs the file extension (also this is the same file as before
   
   With oXL
    .Visible = True
    .workbooks.Open (sFullPath)
    .Range("A8").Value = Me.[Active_Terms_Sept_End.Name].Value
    .Range("c10").Value = Me.[2009 Award Plan.TOTAL_INITIAL_DEFERRED_AWARD].Value <---What is this?  It looks like you are trying to refer to a table or query and you can't do it like that.
   End With
 end if
 
 ' ********************
 ' add code to close and save excel and set oXL = nothing
 ' ********************
 
Exit_Value_Calculation_Click:
 
 Exit Sub
 
Err_Value_Calculation_Click:
 
 MsgBox Err.Description
 
 Resume Exit_Value_Calculation_Click
 
End Sub
 
Upvote 0
caseyc17 said:
Thanks for your help Bob,

It still isn't working. I'm getting a can't find object error now. I went through some of my texts. I'm stumped.
Click to expand...

Post your corrected code that is still getting errors (because you would have had to modify the stuff I posted because I didn't include some and I didn't remove some, etc. I just posted notes on it basically).
 
Upvote 0
Thanks James. I don't want to close or save excel. I'm basically updating an excel template that the user would then save to their directory. I feel like I'm missing something silly. I dumbed down the code and it still isn't working.

I think it has to do with the "if" statement. I've tried several different variations of ""'s, )'s and ]'s.
 
Upvote 0
You must log in or register to reply here.

Similar threads

W
  • Question Question
Trying to learn how to loop code
Replies
1
Views
237
mumps
mumps
C
  • Question Question
Print from database
Replies
0
Views
278
Charlreena
C
N
  • Question Question
Count - specific consecutive order
Replies
6
Views
1K
Peter_SSs
Peter_SSs
S
  • Solved
Can you help me to shorten this code? Thanks a lot
Replies
7
Views
578
saftawy1
S
J
  • Question Question
open view
Replies
4
Views
365
rollis13
rollis13

Forum statistics

Threads
1,224,596
Messages
6,179,807
Members
452,943
Latest member
Newbie4296

Share this page

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
Back
Top