Posts by Krishnakumar

Hi

Little shorter.. =SUM(--(ISNUMBER(SEARCH("No",G14:G38))))

a non-array formula, just replace SUM with SUMPRODUCT

Kris

One closing parenthesis is missing and also remove the square brackets.

Code
1. [f10].FormulaArray = "=SUM(SUMIFS(Qty,product,"" = "" & f1, supplier,"" = "" & f2:f4,delDate,"" >= "" & f6))"

Hi

in the Sheet module

set the EnableEvents = False in the beginning and at the end reset to True

I think this should work

Code
1. MsgBox Split("YourString" & ".", ".")(0)

=if(d4=0,"",sumproduct(--(\$d\$4:\$ay\$4>d4),--(\$d\$4:\$ay\$4<>0))+1)

Put this code in the sheet module.

Re: Clear button in userform

Double click on Clear Entry button and paste the code there.

Re: VBA insert 2 blank rows between each row in range

Welcome to board !!!

[vb]Option Explicit

Sub kTest()

Dim LastRow As Long
Dim r As Range

With ActiveSheet
LastRow = Range("b" & .Rows.Count).End(xlUp).Row
.Columns(2).Insert 'a temporary column
.Range("b7").Value = 1
Set r = .Range("b7:b" & LastRow)
.Range("b7").AutoFill r, xlFillSeries 'fill serial number till the last row
r.Copy .Range("b" & LastRow + 1) 'copy the numbers below the last row
LastRow = Range("b" & .Rows.Count).End(xlUp).Row
r.Copy .Range("b" & LastRow + 1) 'repeat the action 1 more time
'now sort the whole range
LastRow = Range("b" & .Rows.Count).End(xlUp).Row
Set r = .Range("b7:f" & LastRow)
.Sort.SortFields.Clear
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With .Sort
.SetRange r
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
.Columns(2).Delete
End With

End Sub[/vb]

Re: Clear button in userform

[vb] Dim ctl As Control

With Me
For Each ctl In .Controls
If TypeName(ctl) = "TextBox" Then
ctl.Value = vbNullString
End If
Next
End With[/vb]

Re: Adding #N/A to the end of IF then formula and then deleting ALL #N/As found

Hi

Welcome to board !!!

To delete #N/A rows, try

[vb] Dim r As Range

On Error Resume Next
Set r = Range("D1:D21").SpecialCells(xlCellTypeFormulas, 16)

If Not r Is Nothing Then r.EntireRow.Delete[/vb]

Re: Populating ComboBox with unique values (case sensitivity)

an alternative solution... Welcome to board !!

Could you please attach a sample workbook along with expected results ?

Re: USD\$20 Merge some cells with same date based on column O

Hi

Please find attached. And thanks for the fee :cheers:

Re: USD\$20 Merge some cells with same date based on column O

I can look at this for you. BTW, have you paid the 10% to ozgrid ?

Re: Copying multiple sheets to new workbook, problem with array formulas

Rather than using the whole cells, use UsedRange

[vb]For Each wst In ActiveWorkbook.Worksheets
With wst.UsedRange
.Copy
.PasteSpecial xlPasteValues
Application.Goto .Range("A1")
End With
Next wst
Application.CutCopyMode = False[/vb]

Re: Find first value &lt;&gt;0 in subset

Hi

Try

=INDEX(C5:C14,MATCH(1,IF(A5:A14="Ben",IF(B5:B14<>0,1)),1))

This is an array formula. [arf]*[/arf]

Re: Accessing Values from Names Manager

or

TS = [Name_to_Lookup]

Note: It won't work if ActiveWorkbook is not ThisWorkbook

Re: Graph only required info

Create an additional column and put the formula,

=E27>0

Now create a pivot table > pivot chart. (Don't forget to filter the TRUE from the newly created column)

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