[Solved] Sequence Function Issue

afoster

New member
I have followed the instructions in How To Create a Credit Card Payment sheet and everything is working except that the rows are not hidden if the value in column b is empty. This is because the tutorial uses the Sequence function and my version of Excel does not allow the sequence function. Therefore, the VBA code that is inserted in the spreadsheet is not working. Is there a way around this or am I just stuck with what I have? Great tutorial by the way.

Also, I can't seem to figure out how to display cells in red background if the value is a negative number.
 
Last edited:
I have followed the instructions in How To Create a Credit Card Payment sheet and everything is working except that the rows are not hidden if the value in column b is empty. This is because the tutorial uses the Sequence function and my version of Excel does not allow the sequence function. Therefore, the VBA code that is inserted in the spreadsheet is not working. Is there a way around this or am I just stuck with what I have? Great tutorial by the way.

Also, I can't seem to figure out how to display cells in red background if the value is a negative number.
Hello Afoster

Welcome to ExcelDemy Forum! Thanks for reaching out and sharing your problem.

You mentioned that your Excel version does not have the SEQUENCE function. Do not worry! I have come up with a solution that generates sequences based on the number of payments using an Excel VBA User-defined function. Besides, you wanted to learn how to display cells with a red background if the value is a negative number. To do so, I have developed the Worksheet_Calculate event procedure.

SOLUTION Overview:
Output of using Excel VBA user-defined function to generate sequence and using Excel VBA event...gif

Follow these steps:
  1. Press Alt+F11 to open the VBA editor.
  2. Click on Insert followed by Module.
  3. Paste the following code in the module and save it:
    Code:
    Function GenerateSequence(ByVal numberOfPayments As Long) As Variant
        
        Dim i As Long
        Dim roundedPayments As Long
        Dim sequence() As Variant
        
        roundedPayments = Round(numberOfPayments, 0)
        
        ReDim sequence(1 To roundedPayments, 1 To 1)
        
        For i = 1 To roundedPayments
            sequence(i, 1) = i
        Next i
        
        GenerateSequence = sequence
        
    End Function
  4. Return to the sheet and select the intended cell.
  5. Insert the following formula and hit Enter:
    =GenerateSequence(H7)
  6. Open the VBA editor again and right-click on sheet1 from the Microsoft Excel Object list.
  7. Later, click on View Code, paste the following code into the sheet module and save it:
    Code:
    Option Explicit
    
    Private Sub Worksheet_Change(ByVal Cell_Target As Range)
    
        Dim Cell_Range As Range
        
        Application.ScreenUpdating = False
        
        For Each Cell_Range In Range("B7:B100")
            If Cell_Range.Value = "" Then
                Cell_Range.EntireRow.Hidden = True
        
            Else
                Cell_Range.EntireRow.Hidden = False
            End If
        Next Cell_Range
        
        Application.ScreenUpdating = True
        
    End Sub
    
    Private Sub Worksheet_Calculate()
    
        Dim cell As Range
        
        Application.ScreenUpdating = False
        
        For Each cell In UsedRange
            If IsNumeric(cell.Value) And cell.Value < 0 Then
                cell.Interior.Color = RGB(255, 0, 0)
            Else
                cell.Interior.ColorIndex = xlColorIndexNone
            End If
        Next cell
        
        Application.ScreenUpdating = True
        
    End Sub
I hope you have found the solution helpful. I have also attached the solution workbook; good luck.

Regards
Lutfor Rahman Shimanto
Excel & VBA Developer
ExcelDemy
 

Attachments

  • Afoster (Solved).xlsm
    25.2 KB · Views: 4
Thank you so much for your help. I am still having trouble thought and I imagine is that I am not doing it right. Which cell do you mean when you say "Select intended cell" in number 4. Also, even though I add the code for the module, there is no listed module when I view the macros. Is it not supposed to be there?

As for the spreadsheet you attached, I am getting an error message that says:

Ambiguous name detected: GenerateSequence
 
Thank you so much for your help. I am still having trouble thought and I imagine is that I am not doing it right. Which cell do you mean when you say "Select intended cell" in number 4. Also, even though I add the code for the module, there is no listed module when I view the macros. Is it not supposed to be there?

As for the spreadsheet you attached, I am getting an error message that says:

Ambiguous name detected: GenerateSequence
Dear Afoster

Thanks for thanking me. You were almost there. However, you faced several problems. Do not worry! I will try to explain the procedures more clearly.

Let us discuss your first problem. In my previous reply, I tried to refer to cell B5 by writing the intended cell (in step 4).
Select cell B5, insert the given formula and hit Enter.png

Now, let us solve the error you mentioned earlier. The "Ambiguous Name Detected: GenerateSequence" error means there is a problem because the workbook has two user-defined functions named "GenerateSequence."

So, assuming I already added the code for the GenerateSequence user-defined function in module 1 in my attached file, when you downloaded the file, you inserted another module and added the code again. It seems you are an Error like the following GIF:
The Ambiguous Name Detected GenerateSequence error.gif

Solution:
  1. In module 1, keep the source code of the GenerateSequence user-defined function.
  2. Keep the code responsible for raising events in the sheet module of sheet 1.
    Output of using the user-defined function and event procedures to generate sequence of numbers.gif

Hopefully, you understand why you are getting in trouble; good luck.

Regards
Lutfor Rahman Shimanto
Excel & VBA Developer
ExcelDemy
 

Attachments

  • Afoster (Solved).xlsm
    24.3 KB · Views: 3
In following your second directions, I get the following error message when I try to copy the value in cell b5 to b24. I also get the same error message when I try to change any of the values in column H of your solved spreadsheet.View attachment 1358

View attachment 1358
 

Attachments

  • error.png
    error.png
    18.8 KB · Views: 2
My version of Excel is 2010 and I am wondering if that is the cause of the error messages and problems I am having getting this to work.
 
My version of Excel is 2010 and I am wondering if that is the cause of the error messages and problems I am having getting this to work.
Dear Afoster

Perhaps the Excel Version is not the reason. The Worksheet_Calculate event is available in almost all versions of Excel VBA, including the most widely used versions, including Excel 2003 and onwards (2007, 2010, 2013, etc.).

Do not worry! Currently, I am investigating your problem. Hopefully, I will come up with an idea. Stay connected.

Regards
Lutfor Rahman Shimanto
ExcelDemy
 
In following your second directions, I get the following error message when I try to copy the value in cell b5 to b24. I also get the same error message when I try to change any of the values in column H of your solved spreadsheet.View attachment 1358

View attachment 1358
Dear Afoster

Thanks for staying with ExcelDemy Forum. I have found the reason why you are having trouble. You are getting an error on the highlighted line because you are not correctly using the GenerateSequence user-defined function in cell B4. You do not need to copy the formula down to cell B24 using the Fill Handle or double-clicking. All you need to do is insert the formula in cell B4.

So, before following the previous 2nd instruction, you must generate the sequence in column B [in other words, before pasting codes of the event procedures (Worksheet_Change and Worksheet_Calculate) in the sheet module of sheet1].

Follow these steps:
  1. Select cell B4.
  2. Insert the following formula:
    =GenerateSequence(H7)
  3. Hit Enter.
As a result, you will see an output like the following GIF.

Generating number sequences using the Excel VBA user-defined function.gif

Hopefully, following the idea, you will overcome your situation; good luck.

Regards
Lutfor Rahman Shimanto
Excel & VBA Developer
ExcelDemy
 
I have set up another spreadsheet from the start and only added the vba code for the module before entering the vba code that goes into the Sheet1 area. When I enter the formula =GenerateSequence(H7) it only adds the number 1 in the first cell of the B column, directly under the heading Month. Any other ideas?
 
I have set up another spreadsheet from the start and only added the vba code for the module before entering the vba code that goes into the Sheet1 area. When I enter the formula =GenerateSequence(H7) it only adds the number 1 in the first cell of the B column, directly under the heading Month. Any other ideas?
Dear Afoster

Thanks for staying with ExcelDemy Forum! I have reviewed your problem and come up with a solution. I have made two Excel VBA Sub-procedures and called them in a Worksheet_Change event.

So, instead of using the GenerateSequence user-defined function, all you need to do is put the following code in the sheet module and save it. Later, go to the sheet and make changes to see an output like the following GIF:

Create a Credit Card Payoff Spreadsheet in Excel.gif
Excel VBA Event Procedure:

Code:
Option Explicit

Public Sub PopulateValues()
    
    Dim ws As Worksheet
    Dim i As Integer
    Dim numPayments As Integer
    Dim currentRow As Integer
    
    Set ws = ThisWorkbook.Sheets("Sheet1")
    
    numPayments = Round(ws.Range("H7").Value)
    
    currentRow = 5
    
    For i = 1 To numPayments
        ws.Cells(currentRow, 2).Value = i
        currentRow = currentRow + 1
    Next i
    
End Sub

Public Sub RemoveMonth()
    
    Dim ws As Worksheet
    Dim lastRow As Long
    
    Set ws = ThisWorkbook.Sheets("Sheet1")
    
    lastRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row
    
    If lastRow >= 5 Then

        ws.Range("B5:B" & lastRow).ClearContents
    
    End If
    
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim ws As Worksheet
    
    Set ws = ThisWorkbook.Sheets("Sheet1")
    
    If Not Intersect(Target, ws.Range("H4:H8")) Is Nothing Then
        
        Call RemoveMonth
        Call PopulateValues
        
    End If
    
    Application.ScreenUpdating = False

    Dim cell As Range
    For Each cell In UsedRange
        If IsNumeric(cell.Value) And cell.Value < 0 Then
            cell.Interior.Color = RGB(255, 0, 0)
        Else
            cell.Interior.ColorIndex = xlColorIndexNone
        End If
    Next cell

    Dim Cell_Range As Range
    For Each Cell_Range In ws.Range("B7:B100")
        If Cell_Range.Value = "" Then
            Cell_Range.EntireRow.Hidden = True
        Else
            Cell_Range.EntireRow.Hidden = False
        End If
    Next Cell_Range

    Application.ScreenUpdating = True
    
End Sub

I hope the solution will help you overcome your situation. I have attached the solution workbook; good luck.

Regards
Lutfor Rahman Shimanto
Excel & VBA Developer
ExcelDemy
 

Attachments

  • Afoster (SOLVED).xlsm
    25.9 KB · Views: 1
Last edited:
It works! Thank you so much.

I noticed that if I enter any information that requires a number of payments over 20, the month column (Column B) populates, but columns C, D and E do not populate beyond row 20. Can that be corrected and if so how do I do it?
 
It works! Thank you so much.

I noticed that if I enter any information that requires a number of payments over 20, the month column (Column B) populates, but columns C, D and E do not populate beyond row 20. Can that be corrected and if so how do I do it?
Dear Afoster, Thanks for letting us know that the previously provided VBA Sub-Procedure and Event procedure worked perfectly on your end.

I have improved the file and fulfilled your requirements by dragging the existing formulas for the C, D, and E columns up to 100 rows. Please check the following:
Output of using Excel VBA user-defined function to generate sequence and using Excel VBA event...gif

I have attached the workbook. Stay connected. Good luck.

Regards
Lutfor Rahman Shimanto
ExcelDemhy
 

Attachments

  • Afoster (SOLVED) (1).xlsm
    32.7 KB · Views: 2
It works! Thank you so much.

I noticed that if I enter any information that requires a number of payments over 20, the month column (Column B) populates, but columns C, D and E do not populate beyond row 20. Can that be corrected and if so how do I do it?
Dear Afoster, Thanks for letting us know that the previously provided VBA Sub-Procedure and Event procedure worked perfectly on your end.

I have improved the file and fulfilled your requirements by dragging the existing formulas for the C, D, and E columns up to 100 rows. Please check the following:

I have attached the workbook. Stay connected. Good luck.

Regards
Lutfor Rahman Shimanto
ExcelDemhy
Thanks, it does work, but it does not delete the rows below the number of payments.
Dear Afoster, Thanks for letting us know that the previously provided VBA Sub-Procedure and Event procedure worked perfectly on your end.

I have improved the file and fulfilled your requirements by dragging the existing formulas for the C, D, and E columns up to 100 rows. Please check the following:

I have attached the workbook. Stay connected. Good luck.

Regards
Lutfor Rahman Shimanto
ExcelDemhy
Excuse the delay in getting back to you...I see that the revised spreadsheet has 100 rows and I wanted to thank you for your help on this.
 
It works! Thank you so much.

I noticed that if I enter any information that requires a number of payments over 20, the month column (Column B) populates, but columns C, D and E do not populate beyond row 20. Can that be corrected and if so how do I do it?

Thanks, it does work, but it does not delete the rows below the number of payments.

Excuse the delay in getting back to you...I see that the revised spreadsheet has 100 rows and I wanted to thank you for your help on this.
Dear Afoster, Thanks for thanking me! You are welcome. Your appreciation means a lot to us.

You are right about not deleting the rows below the number of payments; it hides the rows.

Thanks once again for letting us know that the previously provided worked perfectly as you intended. Stay blessed.

Regards
Lutfor Rahman Shimanto
ExcelDemy
 

Online statistics

Members online
0
Guests online
14
Total visitors
14

Forum statistics

Threads
318
Messages
1,408
Members
583
Latest member
mibr fan token
Top