0

Here is the code:

numLoansSoldPrev = Range("LoansSold:NewHedges").Cells.Count
If numLoansSoldPrev > 3 Then
    Set rngLoansSoldStart = ActiveWorkbook.Sheets("Email").Range("LoansSold").Offset(1, 1)
    Let strLoansSoldStart = rngLoansSoldStart.Address
    Set rngLoansSoldEnd = ActiveWorkbook.Sheets("Email").Range("NewHedges").Offset(-2, 5)
    Let strLoansSoldEnd = rngLoansSoldEnd.Address
    Range(strLoansSoldStart & ":" & strLoansSoldEnd).Select
    Selection.ClearContents
End If

The commands below the beginning of the if statement work just fine on their own, but every time I try to execute this, I get "block if without end if" despite clearly having one at the bottom.

I have numerous of these if statements in the file but they are all in the same format, so it's not like one if statement is missing an end if.

Any idea?

Expanded Code:

' DECLARE NEW LONGS VARIABLES
Dim numNewLoansPrev As Integer
Dim rngLoansStart As Range
Dim rngLoansEnd As Range
Dim strLoansStart As String
Dim strLoansEnd As String

' DECLARE NEW LOANS SOLD VARIABLES
Dim numLoansSoldPrev As Integer
Dim rngLoansSoldStart As Range
Dim rngLoansSoldEnd As Range
Dim strLoansSoldStart As String
Dim strLoansSoldEnd As String

' DECLARE NEW HEDGES VARIABLES
Dim numNewHedges As Integer
Dim rngNewHedgesStart As Range
Dim rngNewHedgesEnd As Range
Dim strNewHedgesStart As String
Dim strNewHedgesEnd As String
Dim xcess As Integer

' Active E-mail Tab
Worksheets("Email").Activate

' CLEAR EXCESS NEW LONG POSITIONS
numNewLoansPrev = Range("NewLongs:LoansSold").Cells.Count
If numNewLoansPrev > 3 Then
    Set rngLoansStart = ActiveWorkbook.Sheets("Email").Range("NewLongs").Offset(1, 1)
    Set strLoansStart = rngLoansStart.Address
    Set rngLoansEnd = ActiveWorkbook.Sheets("Email").Range("LoansSold").Offset(-2, 5)
    Set strLoansEnd = rngLoansEnd.Address
    Range(strLoansStart & ":" & strLoansEnd).Select
    Selection.ClearContents
End If
    
' CLEAR EXCESS SOLD LONG POSITIONS
numLoansSoldPrev = Range("LoansSold:NewHedges").Cells.Count
If numLoansSoldPrev > 3 Then
    Set rngLoansSoldStart = ActiveWorkbook.Sheets("Email").Range("LoansSold").Offset(1, 1)
    Set strLoansSoldStart = rngLoansSoldStart.Address
    Set rngLoansSoldEnd = ActiveWorkbook.Sheets("Email").Range("NewHedges").Offset(-2, 5)
    Set strLoansSoldEnd = rngLoansSoldEnd.Address
    Range(strLoansSoldStart & ":" & strLoansSoldEnd).Select
    Selection.ClearContents
End If
    
' CLEAR EXCESS NEW HEDGES POSITIONS
numNewHedges = Range("NewHedges:Pnl").Cells.Count
If numNewHedges > 3 Then
    Set rngNewHedgesStart = ActiveWorkbook.Sheets("Email").Range("NewHedges").Offset(1, 1)
    Set strNewHedgesStart = rngNewHedgesStart.Address
    Set rngNewHedgesEnd = ActiveWorkbook.Sheets("Email").Range("PnL").Offset(-2, 5)
    Set strNewHedgesEnd = rngNewHedgesEnd.Address
    Range(strNewHedgesStart & ":" & strNewHedgesEnd).Select
    Selection.ClearContents
End If
2ManyQuestions
  • 1
  • 1
  • 3
    Need to share more code it seems. Btw, `Let` is deprecated. Also suggest you [avoid using Select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). – BigBen Apr 04 '22 at 13:25
  • 2
    i.e. you may have forgotten another `End if`, `End Select`, `Next`, `Loop`, `Wend` or whatnot. – VBasic2008 Apr 04 '22 at 13:59
  • @BigBen I have expanded the code for your review. Also changed from Let to Set per your suggestion. – 2ManyQuestions Apr 04 '22 at 16:09
  • Please note that my comment didn't say change `Let` to `Set`. `Let` is deprecated, so it should be removed entirely. – BigBen Apr 04 '22 at 16:09
  • @BigBen Thank you. So I can just do this? strLoansStart = rngLoansStart.Address Instead of "Set" or "Let" preceding those items? Also as mentioned, more code has been posted. – 2ManyQuestions Apr 04 '22 at 16:12
  • Objects require `Set`. Anywhere you had `Let` in the original code snippet, you should drop `Let`. – BigBen Apr 04 '22 at 16:13
  • @BigBen Thank you, have done so. Any idea on the topic of the post? As mentioned I've added expanded code – 2ManyQuestions Apr 04 '22 at 16:40
  • What you have posted looks fine as far as `If... End If`s, so there's something else in your code that is not included above. – BigBen Apr 04 '22 at 16:41
  • @VBasic2008 I've added updated code for your review – 2ManyQuestions Apr 04 '22 at 16:42
  • @BigBen Even if I just pull one of those if statements and isolate it into the immediate window, the if statement isn't recognized. So it basically gives me the error on the if line (if without end if), and then executes the commands below it. – 2ManyQuestions Apr 04 '22 at 16:44
  • I think you need Range objects to effectively build the arguments of the range. Do without the String variables and use the Address of the rngLoansSoldStar variable directly; i.e.: Set rng= ActiveWorkbook.Sheets("Email").Range(rngLoansSoldStart.Address & ':" & rngNewHedgesEnd.Address). Otherwise, what I think is going on is that you need a set of opening and closing quotes for each range object, if you are going to keep the strings – epicUsername Apr 04 '22 at 17:25

0 Answers0