# Posts by nilem

• ## Convert h:mm:ss into minutes number divide by 60 and again answer in h:mm format

Re: Convert h:mm:ss into minutes number divide by 60 and again answer in h:mm format

HiSumit Dey,
maybe so
=HOUR(F3)+MINUTE(F3)/60

and this
=SUMIF(C3:C16,"Productive",G3:G16)

• ## Format concatenated text in the formula bar

Re: Format concatenated text in the formula bar

try so

Code
``= "this number is " & TEXT(SUM(A1:A3),"## ###.00") & ", and it is a very good number."``
• ## Replace middle words with dash '-'; as in years, also with commas if not continuous

Re: Replace middle words with dash '-'; as in years, also with commas if not continuo

UDF

sample

Code
``=ConcNum(A1)``
• ## Replace middle words with dash '-'; as in years

Re: Replace middle words with dash '-'; as in years

try

• ## UDF function to store values in range

Re: UDF function to store values in range

Hi BaraaKhalil
try

Code
``````Sub Test()
MsgBox ConcatenateArrayElements(Application.Transpose(Range("A1:A10")), ", ")
End Sub``````
• ## Excel crashes during VBA cell copy

Re: Excel crashes during VBA cell copy

Hi Morten,
maybe so

Code
``WS1.UsedRange.Copy WSasapio.Range("A1")``

Hi Londonbound,
try this (in D7)

Code
``=INDEX(\$J\$6:\$N\$6,,MATCH(D2,INDEX(\$J\$7:\$N\$14,MATCH(E2,\$I\$7:\$I\$14,0),),0))``
• ## Select 1000 columns (every other column) and delete them

Re: Select 1000 columns (every other column) and delete them

Hi
try it

Code
``````Sub test()
With Range("A1").Resize(, 1000)
.FormulaR1C1 = "=IF(MOD(COLUMN(RC),2)=0,TRUE,1)"
.SpecialCells(xlCellTypeFormulas, xlLogical).EntireColumn.Delete
.ClearContents
End With
End Sub``````
• ## [Ask Help]How to use Max/Min/Sum in VBA ? How to set the Dynamic range for a NAME ?

Re: [Ask Help]How to use Max/Min/Sum in VBA ? How to set the Dynamic range for a NAM

Hi FWChan,
try so

Code
``MyResult = Application.WorksheetFunction.Max(Range("MyRange"))``
• ## Reverse strings udf function to reverse numbers

Re: Reverse strings udf function to reverse numbers

Hi BaraaKhalil,
maybe so

Code
``````Public Function strReverse(ByVal cell As Range) As String
strReverse = "(" & VBA.strReverse(Replace(Replace(cell.Value, ")", ""), "(", "")) & ")"
End Function``````
• ## VBA to go to different worksheets from data in a Userform ComboBox

Re: VBA to go to different worksheets from data in a Userform ComboBox

Hi Paul103,
maybe something like this

Code
``````Private Sub CommandButton1_Click()    'Enter Data button
Dim wshName As String
With Me.ComboBox1
wshName = .List(.ListIndex, 1)
End With
Sheets(wshName).Cells(Rows.Count, 1).End(xlUp)(2, 1).Resize(, 2).Value _
= Array(Me.TextBox1, Me.TextBox2)
End Sub``````
• ## Activating Option Button in VBA code

Re: Activating Option Button in VBA code

Hi Mike,
try this

Code
``````Sub Clear_All_R1()
Range("G6:G13").ClearContents
With ActiveSheet
.DrawingObjects("Option Button 64").Value = True
.DrawingObjects("Option Button 71").Value = True
.DrawingObjects("Option Button 76").Value = True
End With
End Sub``````

or just this

Code
``````Sub Clear_All_R1()
Range("G6:G13").ClearContents
Range("J6,J9,J12").Value = 4
End Sub``````
• ## Lookup multiple values in a single cell without using Excel VBA

Re: Lookup multiple values in a single cell without using Excel VBA

Hi Nabilah,

• ## UDF to repeat strings according to parameters in a random sequence

Re: UDF to repeat strings according to parameters in a random sequence

... and maybe so

• ## Get the last sequence of numbers in a cell

Re: UDF to get the last sequence of numbers in a cell

or maybe so

Code
``````Function crt(s As String) As Long
Dim i&
For i = Len(s) To 1 Step -1
If Not IsNumeric(Mid(s, i, 1)) Then Exit For
Next
crt = Mid(s, i + 1)
End Function``````
• ## how to optmise vba code

Re: how to optmise vba code

Hi lcmto,
try to make a few lines out of the loop
something like this

• ## Vba function to concatenate header with exclusive values

Re: Vba function to concatenate header with exclusive values

slight changes

• ## UDF results changing when clicking on different sheets

Re: UDF results changing when clicking on different sheets

ah, probably, so

Code
``For j = 1 To limite``

Code
``For j = 4 To limite + 3``
• ## Macro runs slow when multiple subs are called

Re: Macro runs slow when multiple subs are called

Hi Artie5678,
it may be easier to use cell shading instead of creating AutoShapes. Try changing this part of the code in the CreateChartBars procedure

Code
``````...
Else
'Determine location of rectangle
'        Left = 1 + Range(Cells(1, 1), Cells(1, LeftBarCol - 1)).Width
'        Top = 1 + Range(Cells(1, 1), Cells(a - 1, 1)).Height
'        Width = -1 + Range(Cells(1, LeftBarCol), Cells(1, RightBarCol)).Width
'        Height = Cells(a, 1).Height
Range(Cells(a, LeftBarCol), Cells(a, RightBarCol)).Interior.ColorIndex = 43 '<~~~ This line
End If
...``````

and I think you've written a lot of redundant code

• ## UDF results changing when clicking on different sheets

Re: UDF results changing when clicking on different sheets

maybe so