Posts by Tom.Jones

Re: Populating multiple combo-boxes on a UserForm

Try to remove "." marked in red

Private Sub UserForm_Initialize()


Me.cbCalRun.List = ThisWorkbook.Sheets("Calibration").Range("A2", .Range("A2").End(xlDown)).Value
Me.cbIdentS.List = ThisWorkbook.Sheets("Scope_of_Work").Range("B2", .Range("B2").End(xlDown)).Value

End Sub

I mean

Private Sub UserForm_Initialize() 
     
     
    Me.cbCalRun.List = ThisWorkbook.Sheets("Calibration").Range("A2", Range("A2").End(xlDown)).Value 
    Me.cbIdentS.List = ThisWorkbook.Sheets("Scope_of_Work").Range("B2", Range("B2").End(xlDown)).Value 
     
End Sub

Re: Extract similar words from selected sentences

Excellent. Excellent. Superb.
jindon you are the best of the best.
Thank you very much.

I hard tested your UDF, and never missed a word

Re: Extract similar words from selected sentences

Thanks.
in attach is my file.

Re: Extract similar words from selected sentences

Thanks so much for code, but the sentences of column have many words that are found in all sentences.
VBA code, it only lists one word.
If you can help me that would be great.

Thank you.

Hi, jindon

with your code from http://www.ozgrid.com/forum/showthread.php?t=197406 I need exact oposite, I made it with small change of your code and I need to exclude some words like "to, and. it, is....ect)

same example :

I am happy and sad
I am extremely happy
I am happy
I am very happy
I am very very happy

In all sentences are word happy.
and in column B need:
happy
(not if exist: I, am, are, to, is, it...)

and if are more words then put all in column B

Not consider same word, room and rooms. Need only exact word.

Can you help me?
Thank you.

Re: Filters or Arrays used to condense data to a smaller selection

Hi,

Quote

Also are there any methods available from a formula perspective (INDEX, ARRAY, etc.) that could also accomplish this same task, VBA looks cleaner and I like the results, but I'd like to see if a Formula method is available. I'm leaning heavily to the VB method.

Both formula, are array formula and you have to use Control+Shift+Enter
G6 =IFERROR(INDEX(Table1[Wk Day],SMALL(IF(Table1[Activity Code]=$C$5,ROW(Table1[Activity Code])-2),ROWS(G$5:G5))),"")
H6 =IFERROR(INDEX(Table1[Activity],SMALL(IF(Table1[Activity Code]=$C$5,ROW(Table1[Activity Code])-2),ROWS(G$5:G5))),"")

Re: delete entire row if cell value is the same as the cell above

AMQ

Quote

almost..... :?

Where in your post 1 you say that your data start in C5
We have to guess how you got data and offer you a solution?
Code of Mumps is very good, only be amended depending on what you've remembered later, ie:

Quote

My table starts at cell ("C5") and continues down to an unknown number of rows.

Re: Use formula to auto number adjacent column to data being used

Hi,

You can use this formula =Count(B:B) or =Subtotal(2,B:B)

Re: convert rupees in to words formula without using vba code

Cross post http://www.mrexcel.com/forum/e…rt-value-words-excel.html

Re: Split multiline cell in columns

A small change to the excellent formula of Batman. If not would add that one in red, and if that P1&.. in the last line, would be followed by 2 or 3 digits, the result of the formula would have a digit less.

=VALUE(TRIM(MID($A2;FIND(B$1;$A2)+LEN(B$1)+1;IFERROR(FIND("¬";SUBSTITUTE($A2;CHAR(10);"¬");FIND(B$1;$A2));LEN($A2)+1)-(FIND(B$1;$A2)+LEN(B$1)+1))))

Re: split alt+enter values of a cells into multiple cells

You can do it with formula if you want.

Re: multiple Find and replace

KjBox,

Excelent. Thank you.

Re: multiple Find and replace

Ok. File from attach.

Re: multiple Find and replace

Hi Pike,

Thanks for reply. Yes, I mean w = range("D2:D40")

Hello,

In column A is text. Part of the text, say, A2 = aa, bb = A3, A4 = cc, 5 = dd etc. Some of them are repeated several times.
In another text B2 I say PION. I would like VBA code to replace aa, cc and dd with PION. There are about 5000 rows. Not to
write in code all those words (w = array (aa, cc, dd etc ...)) I want to use something like Range ("D2: D40") - here are the
words that I want to replace . And in E2 is word to replace
Can anyone help me?

Re: Split data in first row for every 24 records

it is about those comma at the end?

Re: Split data in first row for every 24 records

My English is not so good.
I wanted to say that I tested the file posted by OP, and VBA code of SNB works (with string or numbers).

Re: Split data in first row for every 24 records

With your file, snb's code work very well.

Re: Combine 2 VBA codes in one

This is crosspost, adile you have to say if you post elsewhere.

http://www.excelforum.com/showthread.php?t=1081950

Re: Search for array of substrings in a string

Quote from jindon;745415

Code

Function Contains(strBaseString As String, CompareMode As VbCompareMethod, ParamArray a()) As Boolean
    Dim e, r As Range
    For Each e In a
        If TypeOf e Is Range Then
            For Each r In e
                If Not IsEmpty(r) Then
                    If InStr(1, strBaseString, r.Value, CompareMode) Then
                        Contains = True: Exit For
                    End If
                End If
            Next
        Else
            If InStr(1, strBaseString, e, CompareMode) Then
                Contains = True: Exit For
            End If
        End If
        If Contains Then Exit For
    Next
End Function

Display More

=Contains(A1,1,$F$1:$F$3,"OK","No Good",$G$1:$G$100)

Jindon how can I use this function?
What should be in F1:F3 and G1:G100

apo can you show me some example of text to use that UDF?
I ask you cause you're online.

Thanks.