Posts by Krishnakumar

Hi Guys,

I have created the Wordle game in Excel. It's not exactly the same as the original one. But I tried my best not to lose the spirit of the game.

You are free to change the code to make it a better one. I have just protected the VBA to make sure there is no accidental changes in the overall game process.

For those who wanted to see the codes, the password is Wordle.

Enjoy playing Excel Wordle!

Kris

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.

[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

May be this one...
https://www.regular-expressions.info/vbscriptexample.html

I think this should work

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.

Option Explicit


Private Sub Worksheet_Change(ByVal Target As Range)


    Dim Addr    As String


    Addr = Target.Address(0, 0)


    Select Case Addr
        Case "D8", "D9"
            If LCase(Me.Range("d8").Value) = "product" And LCase(Me.Range("d9").Value) = "assembly" Then
                Application.EnableEvents = False
                Me.Range("d14:d98").Value = "Mfg Line"
            Else
                Me.Range("d14:d98").Value = vbNullString
            End If
    End Select
    Application.EnableEvents = True


End Sub

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
.Sort.SortFields.Add Key:=.Range("B7"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With .Sort
.SetRange r
.Header = xlNo
.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...

Option Explicit


Private Dic As Object


Private Sub UserForm_Activate()
    
    'Populate ComboBox1
    Dim ws As Worksheet, v, i As Long
    Dim sKey As String, sItems As String
    
    Set Dic = CreateObject("Scripting.Dictionary")
    Set ws = Worksheets("Quartz")
    
    v = ws.Range("B4", ws.Cells(Rows.Count, "B").End(xlUp).Offset(, 1)).Value
    
    Me.ComboBox1.Clear
    
    For i = 1 To UBound(v, 1)
        sKey = v(i, 1)
        If Len(sKey) Then
            sItems = Dic.Item(sKey)
            If Len(sItems) Then
                If InStr(1, sItems, v(i, 2), 1) = 0 Then
                    Dic.Item(sKey) = sItems & "|" & v(i, 2)
                End If
            Else
                Dic.Item(sKey) = v(i, 2)
            End If
        End If
    Next
    
    If Dic.Count Then
        Me.ComboBox1.List = Dic.keys
    End If
    
End Sub
 
Private Sub ComboBox1_Change()
    
    'Populate ComboBox2
    Dim v, sKey As String
    
    Me.ComboBox2.Clear
    
    If Me.ComboBox1.ListIndex = -1 Then Exit Sub
    
    sKey = ComboBox1.Value
    v = Split(Dic.Item(sKey), "|")
    
    Me.ComboBox2.List = v


End Sub

Re: Please Help in Wring Macro to lookup reference and exclude secondary value

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

Quote from sysss;800693

Hello,

try this one:

Code

For Each wst In ActiveWorkbook.Worksheets            
             With wst
                .Cells.Copy
                .Cells.PasteSpecial xlPasteValues
                Application.Goto .Range("A1")
            End With
        Next wst
        Application.CutCopyMode = False

s.

Display More

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)