Related to VBA code

Super Contributor

Hello everyone, 

I want to calculate LEN function of Column K with the help of VBA code.

I have written the code for the function now i want result to displayed in n column can you help me to write code for autofill ?


Here is a attached screenshot :

Annotation 2022-09-01 145533.png


Please help to what VBA code should i write to autofill it ?

5 Replies
best response confirmed by Excel (Super Contributor)


Try this:

Sub Test()
    Dim m As Long
    m = Range("K" & Rows.Count).End(xlUp).Row
    With Range("N2:N" & m)
        .Formula = "=LEN(K2)-LEN(SUBSTITUTE(K2,"","",""""))"
        .Value = .Value
    End With
End Sub

@Hans Vogelaar 


Hello Sir, 


How to Autosum of JAN, FEB, MAR and TOTAL EXPENSES of every sheetwith the help of VBA code ?


Please help...


Here is a attached file...




You have asked very similar questions when you were active as Zan or Zain.

Look through your older discussions, you'll find the code you need.

@Hans Vogelaar 


Hello sir,

How are you ?


I have written code for vlookup function and it works.

But one problem is that for  lookup_value and Column index number is always manually change.


what should i add code to make it more dynamic and get correct result ?


Please help..?


Here is a attached file..


Sub VlookupFull()
    Dim rWs As Worksheet, detailsWs As Worksheet
    Dim qLastRow As Long, qLastCol As Long
    Dim detailsLastRow As Long, detailsLastCol As Long
    Dim r As Long, c As Long
    Dim idx As Long
    Dim dataRng As Range

    Application.ScreenUpdating = False

    Set rWs = ThisWorkbook.Worksheets("Entry At Once")
    ' Last used rowe and column
    qLastRow = rWs.Cells(rWs.Rows.Count, 1).End(xlUp).Row
    qLastCol = rWs.Cells(1, rWs.Columns.Count).End(xlToLeft).Column

    Set detailsWs = ThisWorkbook.Worksheets("Details")
    ' Last used row and column
    detailsLastRow = detailsWs.Cells(detailsWs.Rows.Count, 1).End(xlUp).Row
    detailsLastCol = detailsWs.Cells(1, detailsWs.Columns.Count).End(xlToLeft).Column

    ' Lookup range (dynamic)
    Set dataRng = detailsWs.Range(detailsWs.Cells(2, 1), detailsWs.Cells(detailsLastRow, detailsLastCol))

    ' Loop through columns of rWs
    For c = 2 To qLastCol
        ' Get the index of the column header on the detailsWs sheet
        idx = detailsWs.Rows(1).Find(What:=rWs.Cells(1, c).Value, LookAt:=xlWhole).Column
        ' Loop through the rows of rWs
        For r = 2 To qLastRow
            ' Look up the appropriate value
            rWs.Cells(r, c).Value = Application.VLookup(rWs.Cells(r, 1).Value, dataRng, idx, False)
        Next r
    Next c

    Application.ScreenUpdating = True
End Sub