Posts by junho lee

Dim ctrl
'For i = 1 To 152
'Cells(i, j + 1) = i
'Cells(i, j + 2) = CommandBars(i).Name
'For Each ctrl In CommandBars(i).Controls
'j = j + 1
'Cells(i, j + 2) = ctrl.Caption
'Next
'j = 0
'Next
'''''for me 'play' is within CommandBars(1)


'For Each ctrl In CommandBars(1).Controls
'Debug.Print j; ctrl.ID; ctrl.Caption
'Next
'''''for me >>>>>>id:=1 PLAY


Application.CommandBars("Worksheet Menu Bar").FindControl(ID:=1).Delete

Dim varSrsValue
Dim cnt As Integer
Dim EmntSrsValue
varSrsValue=ActiveChart.SeriesCollection(1).Values
For Each EmntSrsValue In varSrsValue
cnt = cnt + 1
With ActiveChart.SeriesCollection(1).Points(Cnt)
Select Case EmntSrsValue
Case Is > 60
.Interior.Color = vbGreen
Case Is > 50
.Interior.Color = vbYellow
Case Is < 50
.Interior.Color = vbRed
Case Else
End Select
End With
Next

Option Explicit
Sub CntTxt()
    Dim dic As Object
    Dim varRange
    Dim objSheet As Worksheet
    Dim rCell As Range
    Dim i As Long, j As Long
    Set objSheet = Worksheets("Tag Cloud Visualization")
    Set rCell = objSheet.Range("B6:C6")
    Set dic = CreateObject("scripting.dictionary")
    varRange = Sheets("TextData").UsedRange
    For i = 1 To UBound(varRange, 1)
        For j = 1 To UBound(varRange, 2)
            If Not IsEmpty(varRange(i, j)) Then dic(varRange(i, j)) = dic(varRange(i, j)) + 1
        Next
    Next
    With Application
        rCell(1).Resize(dic.Count) = .Transpose(dic.keys)
        rCell(2).Resize(dic.Count) = .Transpose(dic.items)
        'rCell(2).Offset(dic.Count) = .Sum(rCell(2).Resize(dic.Count))
    End With
    'rCell(2).Offset(dic.Count).Copy
    'rCell(2).Resize(dic.Count).PasteSpecial Paste:=xlPasteValues, Operation:=xlDivide, _
    '    SkipBlanks:=False, Transpose:=False
    'rCell(2).Offset(dic.Count).ClearContents
End Sub

Dim ws As Worksheet
Dim chk As Boolean
Dim cnt As Integer
For Each ws In ThisWorkbook.Worksheets
If InStr(ws.Name, "Imported") > 0 Then
chk = True
cnt = cnt + 1
End If
Next
If chk Then
Sheets.Add.Name = "Imported" & cnt
Else
Sheets.Add.Name = "Imported"
End If

Sub Macro1()
    With ActiveSheet.QueryTables.Add(Connection:= _
        "URL;http://www.aia.com.sg/FundPrices_New.aspx?Area=AIA&FundCode=Q0JG#ResQ0JG" _
        , Destination:=Range("A1"))
        .Name = "FundPrices_New.aspx?Area=AIA&FundCode=Q0JG#ResQ0JG"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .WebSelectionType = xlSpecifiedTables
        .WebFormatting = xlWebFormattingNone
        .WebTables = "14" '<--------------------each fund have different tables also shortcut is differ
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
    End With
End Sub