Hi SANSHI,
You can use the direct method to calculate the PPF interest easily.
For calculating the PPF interest, we will be using the following dataset. Here, we have the total Balance, Deposits from April to March, and an Interest Rate of 5%.

• For the monthly interest rates use the following formula
=IF(C4=”Before 5th”,($A$2+B4)*(D4/12),($A$2)*(D4/12))

• For the first month of getting the balances, apply the following formula in cell F4.
=$A$2+B4

• To get the rest of the balances type the following formula
=$A$2+SUM($B$4:B5)
• Drag down the Fill Handle tool.

The final output will look like the following figure.

Hi Jeff Blackwell,
Thanks for reaching us. You can use the following code with some adjustments to split a worksheet based on column into some worksheets of the same workbook.
Here, we will split the following worksheet based on the Month column and so the worksheet will be split into 3 different sheets – January, February, March.

• Use the following code. Adjust the starting row number in nRow = 2, the column indicating letter in (the base column on which you will split the worksheet) objWorksheet.Range(“C” & nRow).

Sub SplitSheetIntoMultipleWorkbooksBasedOnColumn()
    Dim objWorksheet As Excel.Worksheet
    Dim nLastRow, nRow, nNextRow As Integer
    Dim strColumnValue As String
    Dim objDictionary As Object
    Dim varColumnValues As Variant
    Dim varColumnValue As Variant
    Dim xWS As Excel.Worksheet
    Set objWorksheet = ActiveSheet
    nLastRow = objWorksheet.Range("A" & objWorksheet.Rows.Count).End(xlUp).Row
    Set objDictionary = CreateObject("Scripting.Dictionary")
    For nRow = 2 To nLastRow
        strColumnValue = objWorksheet.Range("C" & nRow).Value
        If objDictionary.Exists(strColumnValue) = False Then
           objDictionary.Add strColumnValue, 1
        End If
    Next
    varColumnValues = objDictionary.Keys
    For i = LBound(varColumnValues) To UBound(varColumnValues)
        varColumnValue = varColumnValues(i)
       Set xWS = Sheets.Add(after:=Worksheets(Worksheets.Count))
        objWorksheet.Rows(1).EntireRow.Copy
        xWS.Name = varColumnValue
        xWS.Activate
        xWS.Range("A1").Select
        xWS.Paste
        For nRow = 2 To nLastRow
            If CStr(objWorksheet.Range("C" & nRow).Value) = CStr(varColumnValue) Then
               objWorksheet.Rows(nRow).EntireRow.Copy
               nNextRow = xWS.Range("A" & objWorksheet.Rows.Count).End(xlUp).Row + 1
               xWS.Range("A" & nNextRow).Select
               xWS.Paste
               xWS.Columns("A:D").AutoFit
            End If
        Next
    Next
End Sub

• Press F5.
Then, you will have 3 sheets- January, February, March.

Hi Laurene,
Thanks for staying with us. If the net income cash flows reduced, or 0 or negative, whatever it is. The value of the argument finance rate doesn’t depend on it. You must give the rate as input which is paid by you for cash flows. When you select the payment and incomes at specified intervals as the Values argument, the finance rate as the rate paid by you for your income, and finally the reinvestment rate, the MIRR function will calculate the rate by automatically adjusting the values.

Hello Jorge.F,
Thank you so much for your suggestion. I think you might want the following code incorporating your suggested lines. Moreover, I tried to select the range through an input box which may help you select the range.

Sub Insert_Blank_Rows()
Dim rng As Range
Dim CountRow As Integer
Dim i As Integer
Dim n As Integer
Application.CutCopyMode = False
ExcelTitleId = "Exceldemy"
Set rng = Application.Selection
Set rng = Application.InputBox("Select your range", ExcelTitleId, rng.Address, Type:=8)
n = Int(InputBox("Enter the Value of n: "))
k = Int(InputBox("Enter the Number of Blank Rows: "))
CountRow = rng.EntireRow.Count
Selection(1).Activate
For i = 1 To Int(CountRow / n)
    For j = 0 To k - 1
        ActiveCell.Offset(n + j, 0).EntireRow.Insert
    Next j
    ActiveCell.Offset(n + k, 0).Select
Next i

End Sub

Hi Milad,
Thanks for your question.
According to your requirement, I have created a random dataset containing 1M records within 1M rows of a dataset in Excel. Using a VBA code, I will split it into 10 different worksheets each containing 100,000 rows.

• Type the following VBA code. Here, instead of using the InputBox method, we are directly declaring the total range and the split number in the code which will expediate running the code.

Sub SplitExcelSheet_into_MultipleSheets()
Dim WorkRng As Range
Dim xRow As Range
Dim SplitRow As Integer
Dim xWs As Worksheet
On Error Resume Next
Set xWs = Range("A1:A1000000").Parent
Set xRow = Range("A1:A1000000").Rows(1)
Application.ScreenUpdating = False
For i = 1 To 1000000 Step 100000
    resizeCount = 100000
    If (Range("A1:A1000000").Rows.Count - xRow.Row + 1) < 100000 Then resizeCount = Range("A1:A1000000").Rows.Count - xRow.Row + 1
    xRow.Resize(resizeCount).Copy
    Application.Worksheets.Add after:=Application.Worksheets(Application.Worksheets.Count)
    Application.ActiveSheet.Range("A1").PasteSpecial
    Set xRow = xRow.Offset(100000)
Next
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

• Press F5.
In this way, we have created 10 different sheets each with 100000 records.

Hi Tushar Chauhan,
kindly let me know which code is causing this problem.

Hi Kala,
Thanks for your question. According to your comment, you want to work with a table that’s why I have created the following table.

You can use the following formula
=IF(ROW()=””,””,TODAY())
Here, ROW() will return the corresponding row number for a row; like for Row 5, it will give the value 5, for Row 6 you will have 6.

Then, you can insert the names in the Name column.

Hi Andy,
Thanks for your query. Unfortunately, using the UNIQUE function you cannot do your desired job directly. So, I have come up with an easy alternative way.
Here, I have created the following dataset using your example. Using the PivotTable feature of Excel, I will convert the following three columns into a single column with unique values only.

• Press ALT+D and then P immediately to open up the PivotTable and PivotChart Wizard.

• In Step 1 of this wizard click on the options; Multiple consolidation ranges, PivotTable.
• Click on Next.

• In Step 2a of this wizard click on the Create a single page field for me option.
• Click on Next.

• Now, select the range of the words including a blank column prior to this range in the Range box.
• Select Add to enter the formula of the Range box to the All ranges box.

Afterward, the formula will be entered into the All ranges box, and finally, click on Next.

• In Step 3 of this wizard click on the New worksheet option.
• Click on Finish.

• Now, drag down the Value to the Rows area.
Finally, all of the unique words will be listed in a single column.

Hello Dan,
Thank you so much for your appreciation. Hope you will be benefitted more by staying with our Exceldemy site.

Hello Chandrakant, regarding your problem I have created the following two datasets where in both sheets I have your predefined text 762-V-231. After comparing the range of texts from Sheet1 with Sheet2 I will have the matched texts besides the Existing column.
In Sheet1 I have a range of texts and after the comparison, I will have matched texts in the Similar Text column.

The comparison will be done with Sheet2

To do this comparison you can use the following code

Sub find_text()
Dim source_txt As Range, find_txt As Range
For Each source_txt In Sheets(“Sheet1”).Range(“A2:A6”)
For Each find_txt In Sheets(“Sheet2”).Range(“A2:A6”)
If InStr(1, source_txt, find_txt, vbTextCompare) > 0 Then
source_txt.Offset(0, 1) = find_txt
Exit For
End If
Next
Next
Set source_txt = Nothing
Set find_txt = Nothing
End Sub

After pressing F5, you will have the following result

Hello Gabrielle, after going through your problem, I have understood it like this that you want to highlight those dates which are greater than 30 days (or January month) or 60 days (or February month), or 90 days (or March month) and the corresponding specific cells are blank for these dates. If the specific cells are not blank, then the coloring should be avoided. After understanding this scenario, I have created the following dataset like your sample to illustrate the process.

• Firstly, select the dates, and then go to the Home tab >> Conditional Formatting dropdown >> New Rule option.

• In the opening dialog box, choose the indicated option and then type the following formula in the box
=AND(B2=””,OR(A2>DATEVALUE(“1/30/2022”),A2>DATEVALUE(“2/28/2022”),A2>DATEVALUE(“3/31/2022”)))
• Click on Format

• In the Format Cells dialog box go to the Fill tab >> select your desired color >> press OK.

Then, the following result will appear.

Hello Muizz Shaikh,
Thanks for your question. The maximum number of entries should be within the limit of the maximum row numbers of Excel which is 1,048,576. So, you can merge the files as long as the entries of the combined file support this limit.