Forum Discussion

Excel's avatar
Excel
Iron Contributor
Sep 01, 2022
Solved

Related to VBA code

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...
Macros and VBA
  • HansVogelaar's avatar
    HansVogelaar
    Sep 01, 2022

    Excel 

    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
HansVogelaar's avatar
HansVogelaar
MVP
Sep 01, 2022

Excel 

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
Excel's avatar
Excel
Iron Contributor
Sep 02, 2022

HansVogelaar 

 

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

 

 

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Sep 02, 2022

    Excel 

    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.

    • Excel's avatar
      Excel
      Iron Contributor
      Sep 27, 2022

      HansVogelaar 

       

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

      • HansVogelaar's avatar
        HansVogelaar
        MVP
        Sep 27, 2022

        Excel 

        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

Resources