Apr 26, 2009

[ Usage of --() or ()*1 ]

   Hi ! Here Suggest to you.

                                                  
  DataSheet= Sheet1
Let's Look & Find 
[ Usage of --() or ()*1 ]
10 
11 
12 
13 
14 
15 
16 
17 
18 
19 
20 
ABCDEFGHI
Get Textual Numeric Summary Want True's count
143333 143FALSE00
161111 161TRUE11
157777 135FALSE00
135555  011
 01616     
143333 143FALSE00
161111 161TRUE11
119999 119FALSE00
157777 157TRUE11
114444 135FALSE00
135555  022
 02929  022
 #VALUE!#VALUE!#VALUE!Formula is not FormulaArray
 02929Formula is FormulaArray
 02929SUMPRODUCT Auto Recognize Array
Answer Number from function(Left, Right , Mid Substitute) is Stored as Text
And Logical Value( True , False ) is not Number
so, cannot  count or summary for Textual Number , Logical Value
That time use method of  --() or ()*1

Used Formula ...(With Running MicrosoftExcel Ver 2003)
  If Pink Formula that is FormulaArray !!!
  WithOut {} Input Formula and Press Control+Shift+Enter
NoAddr'  If use below Formula, You'll Get Result as RightResultEtc
1B2=RIGHT(A2,1)3 
2 B2  His Formula Used This Cell  -> B2:B5  
3B6=SUM(B2:B5)0 
4 B6  His Formula Used This Cell  -> B6:D6  
5B7:B12=RIGHT(A7:A12,1)
Select All Range and Input Formula
3 
6B13=SUM(B7:B12)0 
7 B13  His Formula Used This Cell  -> B13:D13  
8B14=SUM(RIGHT(A7:A12,1))#VALUE! 
9B15=SUM(RIGHT(A7:A12,1))0 
10B16=SUMPRODUCT(RIGHT(A7:A12,1))0 
11C2=RIGHT(A2,1)*13 
12 C2  His Formula Used This Cell  -> C2:C5  
13C7:C12=RIGHT(A7:A12,1)*1
Select All Range and Input Formula
3 
14C14=SUM(RIGHT(A7:A12,1)*1)#VALUE! 
15C15=SUM(RIGHT(A7:A12,1)*1)29 
16C16=SUMPRODUCT(RIGHT(A7:A12,1)*1)29 
17D2=--RIGHT(A2,1)3 
18 D2  His Formula Used This Cell  -> D2:D5  
19D7:D12=--RIGHT(A7:A12,1)
Select All Range and Input Formula
3 
20D14=SUM(--RIGHT(A7:A12,1))#VALUE! 
21D15=SUM(--RIGHT(A7:A12,1))29 
22D16=SUMPRODUCT(--RIGHT(A7:A12,1))29 
23G2=F2>150FALSE 
24 G2  His Formula Used This Cell  -> G2:G4  
25G5=SUM(G2:G4)0 
26 G5  His Formula Used This Cell  -> G5:I5  
27G7:G11=F7:F11>150
Select All Range and Input Formula
FALSE 
28G12=SUM(G7:G11)0 
29 G12  His Formula Used This Cell  -> G12:I12  
30G13=SUMPRODUCT((E7:E11>150))0 
31H2=(F2>150)*10 
32 H2  His Formula Used This Cell  -> H2:H4  
33H7:H11=(F7:F11>150)*1
Select All Range and Input Formula
0 
34H13=SUMPRODUCT((F7:F11>150)*1)2 
35I2=--(F2>150)0 
36 I2  His Formula Used This Cell  -> I2:I4  
37I7:I11=--(F7:F11>150)
Select All Range and Input Formula
0 
38I13=SUMPRODUCT(--(F7:F11>150))2 
.

                                                  

Feb 27, 2009

Crazy and Shame Ugly Some Korean And Korea GOV.



too shame but this is truth what U should see...

Dec 10, 2008

QA Using Advance Filter for Conditional List

When Want Make the List Conditional List
Step 1. Select ListRange(What Original Data Range)
Step 2. Data/Filter/Advance Filter Menu Select
Step 3. Select Copt to Another Location
Step 4. Set Range as below
List Range = Selected ListRange(What Original Data Range)(Maybe Auto Wrote)
Criteria Range = Manual Select
Copy to = Manual Select

Important
Criteria Range /Copy to / Range's Header Must Same Value List Range's Header Value


Nov 6, 2008

Programmatic access to Visual Basic ProJect is not Trusted

When VBE Command is Correct But...
See Err' Message in Excel VBE(VBA)
"Programmatic access to Visual Basic ProJect is not trusted"

This Capture

When Error

1. Close VBE indow
And
Change Tools Option
2. Secyrity Select
3. Check Trust Access to VB ProJect

Nov 3, 2008

Convert Number to TEXT (UDF NUM2DOLLAR97)

   Hi ! Here Suggest to you

 [ Convert Number to DOLLAR (UDF NUM2DOLLAR97) ] Let's Look & Find
   --->
    You Can Convert Number to DOLLAR

    Sample - 1 NUM2DOLLAR97 (Number)

    Default Money Display = Dollar(s)

    Sample - 2 NUM2DOLLAR97 (Number, "fran")
10 
11 
12 
13 
14 
15 
16 
17 
18 
19 
20 
21 
22 
23 
24 
25 
26 
27 
28 
29 
30 
31 
32 
33 
34 
35 
AB
1One Dollar.
109One Hundred Nine Dollars.
218Two Hundred Eighteen Dollars.
326Three Hundred Twenty Six Dollars.
435Four Hundred Thirty Five Dollars.
1,443One Thousand Four Hundred Forty Three Dollars.
2,452Two Thousand Four Hundred Fifty Two Dollars.
12,460Twelve Thousand Four Hundred Sixty Dollars.
22,469Twenty Two Thousand Four Hundred Sixty Nine Dollars.
30,000Thirty Thousand Dollars.
30,003Thirty Thousand Three Dollars.
40,000Forty Thousand Dollars.
50,009Fifty Thousand Nine Dollars.
7,000,015Seven Million Fifteen Dollars.
7,001,015Seven Million One Thousand Fifteen Dollars.
1One Dollar.
109.01One Hundred Nine Dollars and One Cents.
218.02Two Hundred Eighteen Dollars and Two Cents.
326.08Three Hundred Twenty Six Dollars and Eight Cents.
435.09Four Hundred Thirty Five Dollars and Nine Cents.
1,043.10One Thousand Forty Three Dollars and Ten Cents.
2,052.91Two Thousand Fifty Two Dollars and Ninety One Cents.
12,060.00Twelve Thousand Sixty Dollars.
22,069.01Twenty Two Thousand Sixty Nine Dollars and One Cents.
30,010.02Thirty Thousand Ten Dollars and Two Cents.
30,013.08Thirty Thousand Thirteen Dollars and Eight Cents.
7,001,005.11Seven Million One Thousand Five Dollars and Eleven Cents.
7,001,015.11Seven Million One Thousand Fifteen Dollars and Eleven Cents.
102.1333One Hundred Two Dollars and Thirteen Cents and Three Three.
102.2536One Hundred Two Dollars and Twenty Five Cents and Three Six.
102.9042One Hundred Two Dollars and Ninety Cents and Four Two.
110.1351One Hundred Ten Dollars and Thirteen Cents and Five One.
200.1354Two Hundred Dollars and Thirteen Cents and Five Four.
1One franc.
109One Hundred Nine franc's.

Used Formula ...(With Running MicrosoftExcel Ver 97)
NoAddr'  If use below Formula, You'll Get Result as RightResultEtc
1B1=NUM2DOLLAR97(A1)One Dollar. 
2 B1  His Formula Used This Cell  -> B1:B33  
3B34=NUM2DOLLAR97(A34,"franc")One franc. 
4 B34  His Formula Used This Cell  -> B34:B35  

      How about this suggest?


Function NUM2DOLLAR97(WhatsNumber As Double, Optional MoneyType As String = "Dollar")
Dim iLoop As Integer               'Loop
Dim CommaNumbr(1 To 5) As String   'Share by 3 Digit
Dim StrngNum_1 As String           'String Number to Format 000 000...
Dim StrngNum_2 As String           'String Number of Decimal
Dim WorkNumber As Variant          '000 000... Split
Dim NumInt     As String           'Number Integer
Dim NumDec     As String           'Decimal Number
Dim PosDot     As Integer
Dim PosDec     As Integer
Dim DecCent    As String

On Error Resume Next
     CommaNumbr(1) = " Trillion ": CommaNumbr(2) = " Billion "
     CommaNumbr(3) = " Million ":  CommaNumbr(4) = " Thousand "
     
PosDot = InStr(WhatsNumber, ".")

If WhatsNumber = 0 Then
          NUM2DOLLAR97 = "No Dollar."
     If MoneyType <> "Dollar" Then
          NUM2DOLLAR97 = Replace97(NUM2DOLLAR97, "Dollar", MoneyType)
     End If
          Exit Function
ElseIf WhatsNumber = 1 Then
          NUM2DOLLAR97 = "One Dollar."
     If MoneyType <> "Dollar" Then
          NUM2DOLLAR97 = Replace97(NUM2DOLLAR97, "Dollar", MoneyType)
     End If
          Exit Function

End If

If PosDot = 0 Then
     StrngNum_1 = Format(WhatsNumber, " 000 000 000 000 000")
Else
     StrngNum_1 = Format(Left(WhatsNumber, PosDot - 1), " 000 000 000 000 000")
     StrngNum_2 = Mid(WhatsNumber, PosDot + 1)
     PosDec = Len(StrngNum_2)
End If
     WorkNumber = Split97(StrngNum_1, " ")

For iLoop = 1 To UBound(WorkNumber)
     If WorkNumber(iLoop) <> "000" Then
     NumInt = NumInt & String_1(Left(WorkNumber(iLoop), 1))
     NumInt = NumInt & String_2(Right(WorkNumber(iLoop), 2)) & CommaNumbr(iLoop)
     End If
Next

If NumInt = "" Then
     NumInt = "Zero Dollar"
Else
     NumInt = NumInt & " Dollars "
End If

     If PosDot = 0 Then
          NUM2DOLLAR97 = NumInt
     Else
          If PosDec = 1 Then
                    NUM2DOLLAR97 = NumInt & " and " & String_2(StrngNum_2 & "0") & " Cents "
          ElseIf PosDec = 2 Then
                    NUM2DOLLAR97 = NumInt & " and " & String_2(StrngNum_2) & " Cents "
          Else
               DecCent = Left(StrngNum_2, 2)
               If DecCent = "00" Then
                    NumDec = String_2(DecCent) & " NoCent "
               Else
                    NumDec = String_2(DecCent) & " Cents and"
               End If
               For iLoop = 3 To PosDec
                    If Mid(StrngNum_2, iLoop, 1) = "0" Then
                    NumDec = NumDec & " Zero"
                    Else
                    NumDec = NumDec & " " & String_3(Mid(StrngNum_2, iLoop, 1))
                    End If
               Next
                    NumDec = NumDec & " "
                    NUM2DOLLAR97 = NumInt & " and " & NumDec
          End If
     End If
     
     NUM2DOLLAR97 = RTrim(Replace97(NUM2DOLLAR97, "  ", " "))
If MoneyType <> "Dollar" Then
     NUM2DOLLAR97 = Replace97(NUM2DOLLAR97, "Dollar", MoneyType & "'")
     'If Want "francs" Instead of "franc's" then
     'Use This
     'NUM2DOLLAR97 = Replace(NUM2DOLLAR97, "Dollar", MoneyType)
End If
     NUM2DOLLAR97 = NUM2DOLLAR97 & "."
End Function

Function String_1(MyString As String)   ' 100's Number
If MyString <> "0" Then
     String_1 = String_3(MyString) & " Hundred "
End If
End Function

Function String_2(MyString As String)   ' 10's Number (20-90)
Select Case Left(MyString, 1)
     Case "1"
          String_2 = String_21(MyString)
          Exit Function
     Case "2": String_2 = "Twenty ":    Case "3": String_2 = "Thirty "
     Case "4": String_2 = "Forty ":     Case "5": String_2 = "Fifty "
     Case "6": String_2 = "Sixty ":     Case "7": String_2 = "Seventy "
     Case "8": String_2 = "Eighty ":    Case "9": String_2 = "Ninety "
End Select
          String_2 = String_2 & String_3(Right(MyString, 1))
End Function

Function String_21(MyString As String)  ' 10-19's Number
Select Case MyString
     Case "10": String_21 = "Ten":      Case "11": String_21 = "Eleven"
     Case "12": String_21 = "Twelve":   Case "13": String_21 = "Thirteen"
     Case "14": String_21 = "Fourteen": Case "15": String_21 = "Fifteen"
     Case "16": String_21 = "Sixteen":  Case "17": String_21 = "Seventeen"
     Case "18": String_21 = "Eighteen": Case "19": String_21 = "Nineteen"
End Select
End Function

Function String_3(MyString As String)   ' 1-9's Number
Select Case MyString
     Case "1": String_3 = "One":        Case "2": String_3 = "Two"
     Case "3": String_3 = "Three":      Case "4": String_3 = "Four"
     Case "5": String_3 = "Five":       Case "6": String_3 = "Six"
     Case "7": String_3 = "Seven":      Case "8": String_3 = "Eight"
     Case "9": String_3 = "Nine"
End Select
End Function

Function Split97(OriStr, Optional OptStr As String = ",")
Dim GetText    As String
Dim GetPoss    As Double
Dim MidStr     As Variant
Dim AnsStr     As Variant
ReDim MidStr(0 To Len(CStr(OriStr)))
Dim i As Double, j As Double
GetText = OriStr
For i = 1 To Len(CStr(OriStr))
GetPoss = InStr(GetText, OptStr)
     If GetPoss > 0 Then
          MidStr(j) = Left(GetText, GetPoss - 1)
          GetText = Mid(GetText, GetPoss + 1)
          j = j + 1
     Else
          MidStr(j) = Mid(GetText, GetPoss + 1)
          Exit For
     End If
Next
ReDim AnsStr(0 To j)
For i = 0 To j
     AnsStr(i) = MidStr(i)
Next
Split97 = AnsStr
End Function

Function Replace97(OriStr As Variant, _
               Str1 As String, Str2 As String, _
               Optional Pos As Double = 1)
Dim i9 As Double
Dim Fn As WorksheetFunction
Set Fn = Application.WorksheetFunction
If Pos = 1 Then
    Replace97 = Fn.Substitute(OriStr, Str1, Str2)
Else
Replace97 = OriStr
    For i9 = Pos To Len(OriStr)
    Replace97 = Fn.Substitute(Replace97, Str1, Str2, i9)
    Next
End If
End Function

Any Bug Or Ask ... Mail Or Post Reply

Oct 1, 2008

Sheet2Web How to Easy Sheet Data Post 2.2


When Post Excel Sheet data to web / Html
for advice to other man or post your blog...
this tool should Useful
if Use stay this file on your Excel folder
XLStart or Library folder.

folder 1 : C:\Program Files\Microsoft Office\Office\XLStart
folder 2 : C:\Program Files\Microsoft Office\Office\Library

2008.10.01 New Tool Add And bug removed

Click to DownLoad Here ->> DownLoad XLA file or Zip file


Remove Some Bug
Add ColumnWidth AdJust

Sep 9, 2008

Convert Number to TEXT (UDF NUM2DOLLAR)

   Hi ! Here Suggest to you

 [ Convert Number to DOLLAR (UDF NUM2DOLLAR) ] Let's Look & Find
   --->
   You Can Convert Number to DOLLAR

   Sample - 1 NUM2DOLLAR (Number)

   Default Money Display = Dollar(s)

   Sample - 2 NUM2DOLLAR (Number, "fran")
10 
11 
12 
13 
14 
15 
16 
17 
18 
19 
20 
21 
22 
23 
24 
25 
26 
27 
28 
29 
30 
31 
32 
33 
34 
35 
AB
1One Dollar.
109One Hundred Nine Dollars.
218Two Hundred Eighteen Dollars.
326Three Hundred Twenty Six Dollars.
435Four Hundred Thirty Five Dollars.
1,443One Thousand Four Hundred Forty Three Dollars.
2,452Two Thousand Four Hundred Fifty Two Dollars.
12,460Twelve Thousand Four Hundred Sixty Dollars.
22,469Twenty Two Thousand Four Hundred Sixty Nine Dollars.
30,000Thirty Thousand Dollars.
30,003Thirty Thousand Three Dollars.
40,000Forty Thousand Dollars.
50,009Fifty Thousand Nine Dollars.
7,000,015Seven Million Fifteen Dollars.
7,001,015Seven Million One Thousand Fifteen Dollars.
1.00One Dollar.
109.01One Hundred Nine Dollars and One Cents.
218.02Two Hundred Eighteen Dollars and Two Cents.
326.08Three Hundred Twenty Six Dollars and Eight Cents.
435.09Four Hundred Thirty Five Dollars and Nine Cents.
1,043.10One Thousand Forty Three Dollars and Ten Cents.
2,052.91Two Thousand Fifty Two Dollars and Ninety One Cents.
12,060.00Twelve Thousand Sixty Dollars.
22,069.01Twenty Two Thousand Sixty Nine Dollars and One Cents.
30,010.02Thirty Thousand Ten Dollars and Two Cents.
30,013.08Thirty Thousand Thirteen Dollars and Eight Cents.
7,001,005.11Seven Million One Thousand Five Dollars and Eleven Cents.
7,001,015.11Seven Million One Thousand Fifteen Dollars and Eleven Cents.
102.1333One Hundred Two Dollars and Thirteen Cents and Three Three.
102.2536One Hundred Two Dollars and Twenty Five Cents and Three Six.
102.9042One Hundred Two Dollars and Ninety Cents and Four Two.
110.1351One Hundred Ten Dollars and Thirteen Cents and Five One.
200.1354Two Hundred Dollars and Thirteen Cents and Five Four.
1One franc.
109One Hundred Nine franc's.

Used Formula ...(With Running MicrosoftExcel Ver 2003)
NoAddr'  If use below Formula, You'll Get Result as RightResultEtc
1B1=NUM2DOLLAR(A1)One Dollar. 
2 B1  His Formula Used This Cell  -> B1:B33  
3B34=NUM2DOLLAR(A34,"franc")One franc. 
4 B34  His Formula Used This Cell  -> B34:B35  

      How about this suggest?

Option Explicit

Function NUM2DOLLAR(WhatsNumber As Double, Optional MoneyType As String = "Dollar")
Dim iLoop As Integer               'Loop
Dim CommaNumbr(1 To 5) As String   'Share by 3 Digit
Dim StrngNum_1 As String           'String Number to Format 000 000...
Dim StrngNum_2 As String           'String Number of Decimal
Dim WorkNumber As Variant          '000 000... Split
Dim NumInt     As String           'Number Integer
Dim NumDec     As String           'Decimal Number
Dim PosDot     As Integer
Dim PosDec     As Integer
Dim DecCent    As String

On Error Resume Next
     CommaNumbr(1) = " Trillion ": CommaNumbr(2) = " Billion "
     CommaNumbr(3) = " Million ":  CommaNumbr(4) = " Thousand "
     
PosDot = InStr(WhatsNumber, ".")

If WhatsNumber = 0 Then
          NUM2DOLLAR = "No Dollar."
     If MoneyType <> "Dollar" Then
          NUM2DOLLAR = Replace(NUM2DOLLAR, "Dollar", MoneyType)
     End If
          Exit Function
ElseIf WhatsNumber = 1 Then
          NUM2DOLLAR = "One Dollar."
     If MoneyType <> "Dollar" Then
          NUM2DOLLAR = Replace(NUM2DOLLAR, "Dollar", MoneyType)
     End If
          Exit Function

End If

If PosDot = 0 Then
     StrngNum_1 = Format(WhatsNumber, " 000 000 000 000 000")
Else
     StrngNum_1 = Format(Left(WhatsNumber, PosDot - 1), " 000 000 000 000 000")
     StrngNum_2 = Mid(WhatsNumber, PosDot + 1)
     PosDec = Len(StrngNum_2)
End If
     WorkNumber = Split(StrngNum_1)

For iLoop = 1 To UBound(WorkNumber)
     If WorkNumber(iLoop) <> "000" Then
     NumInt = NumInt & String_1(Left(WorkNumber(iLoop), 1)) & String_2(Right(WorkNumber(iLoop), 2)) & CommaNumbr(iLoop)
     End If
Next

If NumInt = "" Then
     NumInt = "Zero Dollar"
Else
     NumInt = NumInt & " Dollars "
End If

     If PosDot = 0 Then
          NUM2DOLLAR = NumInt
     Else
          If PosDec = 1 Then
                    NUM2DOLLAR = NumInt & " and " & String_2(StrngNum_2 & "0") & " Cents "
          ElseIf PosDec = 2 Then
                    NUM2DOLLAR = NumInt & " and " & String_2(StrngNum_2) & " Cents "
          Else
               DecCent = Left(StrngNum_2, 2)
               If DecCent = "00" Then
                    NumDec = String_2(DecCent) & " NoCent "
               Else
                    NumDec = String_2(DecCent) & " Cents and"
               End If
               For iLoop = 3 To PosDec
                    If Mid(StrngNum_2, iLoop, 1) = "0" Then
                    NumDec = NumDec & " Zero"
                    Else
                    NumDec = NumDec & " " & String_3(Mid(StrngNum_2, iLoop, 1))
                    End If
               Next
                    NumDec = NumDec & " "
                    NUM2DOLLAR = NumInt & " and " & NumDec
          End If
     End If
     
     NUM2DOLLAR = RTrim(Replace(NUM2DOLLAR, "  ", " "))
If MoneyType <> "Dollar" Then
     NUM2DOLLAR = Replace(NUM2DOLLAR, "Dollar", MoneyType & "'")
     'If Want "francs" Instead of "franc's" then
     'Use This
     'NUM2Dollar = Replace(NUM2Dollar, "Dollar", MoneyType)
End If
     NUM2DOLLAR = NUM2DOLLAR & "."
End Function

Function String_1(MyString As String)   ' 100's Number
If MyString <> "0" Then
     String_1 = String_3(MyString) & " Hundred "
End If
End Function

Function String_2(MyString As String)   ' 10's Number (20-90)
Select Case Left(MyString, 1)
     Case "1"
          String_2 = String_21(MyString)
          Exit Function
     Case "2": String_2 = "Twenty ":    Case "3": String_2 = "Thirty "
     Case "4": String_2 = "Forty ":     Case "5": String_2 = "Fifty "
     Case "6": String_2 = "Sixty ":     Case "7": String_2 = "Seventy "
     Case "8": String_2 = "Eighty ":    Case "9": String_2 = "Ninety "
End Select
          String_2 = String_2 & String_3(Right(MyString, 1))
End Function

Function String_21(MyString As String)  ' 10-19's Number
Select Case MyString
     Case "10": String_21 = "Ten":      Case "11": String_21 = "Eleven"
     Case "12": String_21 = "Twelve":   Case "13": String_21 = "Thirteen"
     Case "14": String_21 = "Fourteen": Case "15": String_21 = "Fifteen"
     Case "16": String_21 = "Sixteen":  Case "17": String_21 = "Seventeen"
     Case "18": String_21 = "Eighteen": Case "19": String_21 = "Nineteen"
End Select
End Function

Function String_3(MyString As String)   ' 1-9's Number
Select Case MyString
     Case "1": String_3 = "One":        Case "2": String_3 = "Two"
     Case "3": String_3 = "Three":      Case "4": String_3 = "Four"
     Case "5": String_3 = "Five":       Case "6": String_3 = "Six"
     Case "7": String_3 = "Seven":      Case "8": String_3 = "Eight"
     Case "9": String_3 = "Nine"
End Select
End Function

Any Bug Or Ask ... Mail Or Post Reply
Locations of visitors to this page