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