Sep 01 2022 02:30 AM - edited Sep 01 2022 03:03 AM
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 :
Please help to what VBA code should i write to autofill it ?
Sep 01 2022 03:25 AM
SolutionTry 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
Sep 02 2022 02:44 AM - edited Sep 02 2022 02:54 AM
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...
Sep 02 2022 04:24 AM
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.
Sep 27 2022 08:37 AM - edited Sep 27 2022 08:38 AM
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..
Sep 27 2022 12:59 PM
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
Sep 01 2022 03:25 AM
SolutionTry 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