# Posts by Krishnakumar

• ## USD\$45 Create wb with filtered results based on multiple steps

Re: USD\$45 Create wb with filtered results based on multiple steps

One question. There are 7 records for the following selection

Bdrms Prince Range
1 Under \$2500

• ## USD\$45 Create wb with filtered results based on multiple steps

Re: USD\$45 Create wb with filtered results based on multiple steps

This is doable.

• ## USD\$45 Create wb with filtered results based on multiple steps

Re: USD\$45 Create wb with filtered results based on multiple steps

I can look at this for you and let you know if I can help you

• ## Extract specific number from txt file

Re: Extract specific number from txt file

Or please post couple of text files and the excel file.

• ## Extract specific number from txt file

Re: Extract specific number from txt file

[vb]Option Explicit

Sub kTest()

Dim k, kk, i As Long, fn As String, Flg As Boolean
Dim Fldr As String, j As Long, wks As Worksheet

Fldr = "C:\Test" '<<< adjust this text file path

Const EndsWith As String = "BBB" 'case sensitive. In the file there is a parenthesis after BBB. It should not be there or add the parenthesis in the variable

If Not Right(Fldr, 1) = Application.PathSeparator Then Fldr = Fldr & Application.PathSeparator

Set wks = Worksheets("Sheet1") '<<< adjust the sheet name

k = wks.Range("a1").CurrentRegion.Resize(, 3).Value '<<< assume data starts from A1:Cx, where A1:C1 holds headers

For i = 2 To UBound(k, 1)
fn = Dir(Fldr & "*" & k(i, 1) & "*.txt")
If Len(fn) Then
kk = Split(CreateObject("scripting.filesystemobject").opentextfile(Fldr & fn).readall, vbNewLine)
Flg = False
For j = 0 To UBound(kk)
If Trim(kk(j)) Like ("*" & EndsWith) Then
Flg = True
End If
If Flg Then
If kk(j) Like "Total Number of*" Then '<<< % value
k(i, 3) = Val(Split(kk(j), "=")(1)) 'splitting the line using the delimiter '='. hope '=' will always be there
Exit For
End If
End If
Next
End If
Next

wks.Range("a1").CurrentRegion.Resize(, 3).Value = k

End Sub[/vb]

• ## USD 20 Need help to summarize

Re: Need help to summarize - 20\$

Your code is ready. Please make the payment and upon receiving the fee, will post the workbook here.

• ## USD 20 Need help to summarize

Re: Need help to summarize - 20\$

Would it work by this time tomorrow? Also can you provide the expected outlet?

• ## USD 20 Need help to summarize

Re: Need help to summarize - 20\$

If you need this today itself, open to other developers. Otherwise, I can take this up.

• ## USD 20 Need help to summarize

Re: Need help to summarize - 20\$

I can look at this for you.

• ## Selecting first 10 rows of filtered data

Re: Selecting first 10 rows of filtered data

Try this

[vb]Option Explicit

Sub kTest()

Dim Data As Range
Dim CopyRng As Range
Dim r As Long

With Worksheets("Data Dump")
r = .Range("a" & .Rows.Count).End(3).Row
Set Data = .Range("a8:h" & r)
End With

With Data
.AutoFilter Field:=8, Criteria1:="10", Operator:=xlTop10Items
Set CopyRng = .Offset(1, 1).Resize(.Rows.Count, .Columns.Count - 1).SpecialCells(12)
If Not CopyRng Is Nothing Then
CopyRng.Copy Worksheets("Top 10 Enquiries").Range("c3")
End If
End With

End Sub[/vb]

• ## Dynamic range inside an array

Re: Dynamic range inside an array

It would work as long as [vb]Range("Current_Week").Value[/vb] returns a number. BTW, what's the error you got ?

• ## Extract Random Sample of 1% for each item in colum A of Sheet 1 to Sheet 2

Re: Extract Random Sample of 1% for each item in colum A of Sheet 1 to Sheet 2

Hi

try this

[vb]Option Explicit

Sub kTest()

Dim k, kk, kkk(), dic As Object, i As Long, t
Dim r As Long, j As Long, n As Long, p As Long

Set dic = CreateObject("scripting.dictionary")
dic.comparemode = 1

With Worksheets("Sheet1")
k = .Range("a1").CurrentRegion.Value2
End With

For i = 2 To UBound(k, 1)
If Len(k(i, 1)) Then
t = dic.Item(k(i, 1))
If IsEmpty(t) Then
dic.Item(k(i, 1)) = Array(1, i)
Else
t(0) = t(0) + 1
t(1) = t(1) & "|" & i
dic.Item(k(i, 1)) = t
End If
End If
Next

kk = Array(dic.keys, dic.items)
ReDim kkk(1 To UBound(k, 1), 1 To UBound(k, 2))

For i = 0 To UBound(kk(0))
p = kk(1)(i)(0) * 0.01
t = Split(kk(1)(i)(1), "|")
dic.RemoveAll
j = 1
Do While j <= p
r = Application.WorksheetFunction.RandBetween(1, UBound(t) + 1) - 1
If Not dic.exists(r) Then
dic.Item(r) = Empty
n = n + 1
kkk(n, 1) = k(t(r), 1)
kkk(n, 2) = k(t(r), 2)
j = j + 1
End If
Loop
Next

If n Then
With Worksheets("Sheet2")
.[a1].CurrentRegion.Offset(1).ClearContents
.[a2].Resize(n, 2).Value = kkk
End With
End If

End Sub[/vb]

• ## VBA code to store values in a range and then sum ranges stored in memory

Re: VBA code to store values in a range and then sum ranges stored in memory

Hi Welcome to board !!!

there may be better ways....

[vb]Sub kTest()

Dim i As Long
Dim n As Long
Dim j As Long
Dim CF As Variant
Dim CFs() As Double

n = 3
CF = Range("cfs").Value
ReDim CFs(1 To n, 1 To UBound(CF, 2))

For i = 1 To n
Range("var") = i
For j = 1 To UBound(CF, 2)
CFs(i, j) = CF(1, j)
Next
Next

For i = 1 To UBound(CFs, 2)
Range("output").Cells(1, i).Value = Application.Sum(Application.Index(CFs, 0, i))
Next

End Sub[/vb]

• ## Input box to scan barcode, vlookup and return value using a message box

Re: Input box to scan barcode, vlookup and return value using a message box

Attachment is missing.

• ## format shapes depending on linked cells.

Re: format shapes depending on linked cells.

Hi

Welcome to board !!!

• Book1.xlsm

• ## Sum all sheet based on column header

Re: Sum all sheet based on column header

In C3 and copied down,

=SUMPRODUCT(SUMIF(INDIRECT("'"&\$H\$2:\$H\$4&"'!\$B2:\$M2"),\$B\$2,INDIRECT("'"&\$H\$2:\$H\$4&"'!\$B"&ROWS(\$C\$2:C2)+2&":m"&ROWS(\$C\$2:C2)+2)))

where H2:H4 holds the sheet names

• ## £35 to sort, select and copy data to new sheets based on selected variables

Re: £35 to sort, select and copy data to new sheets based on selected variables

Hi

Your code is ready. Please make the payment and upon receiving the fund, will post the solution here.

• ## £35 to sort, select and copy data to new sheets based on selected variables

Re: £35 to sort, select and copy data to new sheets based on selected variables

Questions..

1. Can I use the col E values on 'Agent Data' sheet for the random number ?
2. Which version of Excel you are using and on what OS(Win/Mac) ?
3. I assume the format on Reports sheet won't change.

• ## £35 to sort, select and copy data to new sheets based on selected variables

Re: £35 to sort, select and copy data to new sheets based on selected variables

I can look at this for you.

• ## USD\$35 to automate email notifications from excel data sheet

Re: USD\$35 to automate email notifications from excel data sheet

Quote from desfoo;796073

Hi Kris,

2. Yes. They may open the file multiple times but each officer is only responsible for the unique case assigned to them, which is depending on the row. Also, yes this workbook is in a shared drive and is shared among multiple users.

Is there any mapping for 'Case No' versus Officers ? otherwise how would I know which 'Case No' is assigned to whom ?