[Solved] Sequence Function Issue

[afoster]

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.

 

[Lutfor Rahman Shimanto]

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:

​

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:
   

   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:
   

   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

 

[afoster]

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

 

[Lutfor Rahman Shimanto]

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).

​

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:

​

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.
   

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

Regards
Lutfor Rahman Shimanto
Excel & VBA Developer
ExcelDemy

 

[afoster]

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

 

[afoster]

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

 

[afoster]

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.

 

[Lutfor Rahman Shimanto]

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

 

[Lutfor Rahman Shimanto]

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.

​

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

Regards
Lutfor Rahman Shimanto
Excel & VBA Developer
ExcelDemy

 

[afoster]

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?

 

[Lutfor Rahman Shimanto]

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:

​

Excel VBA Event Procedure:

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

 

[afoster]

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?

 

[Lutfor Rahman Shimanto]

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

 

[afoster]

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:

View attachment 1368​

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:

View attachment 1368​

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.

 

[Lutfor Rahman Shimanto]

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