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