Posts by asdzxc

asdzxc · Aug 24th 2014

Code

Range("C3:E3").Select
Selection.AutoFill Destination:=Range("C3:E" & Range("E" & Rows.Count).End(xlUp).Row), Type:=xlFillDefault

What's wrong with the above code

asdzxc · Oct 13th 2013

Re: picking last data in a week (week ended Friday)

Quote from royUK;687510

If you want help with PhotoShop attach an image, if you want help with Excel then attach a workbook.

attached

asdzxc · Oct 13th 2013

last item in we 13-9-2013 is 13-9-2013
last item in we 20-9-2013 is 13-18-2013
last item in we 4-11-2013 is 30-9-2013 as shown in the attached
Plse write a vba.

asdzxc · Oct 13th 2013

Re: Count date Cells

Solved. thank you

asdzxc · Oct 11th 2013

Code

Dim Var As Integer
B2 = Weekday(A2)
B3 = Weekday(A3)
B4 = Weekday(A4)
Var = count("cell.Value <5", Range("B2:B4"))
If Var < 3 Then
Columns("A:A").Select
End Sub

What's wrong with the above vba

asdzxc · Sep 30th 2013

vba for sum of Fridays in A2 to A6. if A2 and A3 are Friday and A4 to A6 not, the sum is 2

asdzxc · Sep 2nd 2013

Re: delete all files except

your vba don't delete charts

asdzxc · Sep 2nd 2013

When closing Excel, deleting all sheets except "Input"

asdzxc · May 21st 2012

Re: Compare 2 Dates In 2 Cells & Fill Down

Quote from Dave Hawley;609573

Sorry, no idea what you are talking about. My code does exactly what you asked

[QUOTE]go to last date in col A and fill-down 4 cells

Code

If Range("A3") > Range("A2") Then
    With Range("A" & Rows.Count).End(xlUp)
    .AutoFill .Resize(4)
    End With
    End If

asdzxc · May 21st 2012

Re: Compare 2 Dates In 2 Cells & Fill Down

Thank you for your code.
my last code is wrong, plse check

Code

If Range("A3") > Range("A2") Then _
    Lt = Range("A2").End(xlDown).Offset(3, 0).Row
        Selection.AutoFill Destination:=Range("A2:A" & Lt)

asdzxc · May 21st 2012

If date in A3 is later than in A2, go to A5 and fill-down from A5 to A10
Plse supply code.

asdzxc · May 20th 2012

Re: VBA Web Query

Quote

put w or m in H1, give same result

Code

StrURL = "URL;http://ichart.finance.yahoo.com/table.csv?s=" & [Sheet1!J1].Value & "&a=10&b=13&c=2011&d=06&e=6&f=2013&g=" & IIf(Len([H1]), "w", "m") & "&ignore=.xlsx"
 With ActiveSheet.QueryTables.Add(Connection:=StrURL, Destination:=Range("A1"))
            .FieldNames = True
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .BackgroundQuery = True
            .RefreshStyle = xlInsertDeleteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .WebSelectionType = xlAllTables
            .WebFormatting = xlWebFormattingNone
            .WebPreFormattedTextToColumns = True
            .WebConsecutiveDelimitersAsOne = True
            .WebSingleBlockTextImport = False
            .WebDisableDateRecognition = False
            .Refresh BackgroundQuery:=False
End With
End Sub

Display More

asdzxc · May 20th 2012

In J1, put ^dji
What's wrong with the code:

Code

Dim StrURL As String
StrURL = "URL;http://ichart.finance.yahoo.com/table.csv?s=" & _
    Worksheets("Sheet1").Range("J1").Value & "&a=10&b=13&c=2011&d=06&e=6&f=2013& " & g = "&ignore=.xlsx"
      If Range("H1").Value = "" Then
        
      StrURL = StrURL & "&g=w&ignore=.xlsx"
      Else
      
      StrURL = StrURL & "&g=m&ignore=.xlsx"
      End If

asdzxc · May 18th 2012

Re: compile error

put w or m in I2 but both produce daily data
[ATTACH=CONFIG]45876[/ATTACH]

asdzxc · May 18th 2012

Re: compile error

Quote from cytop;609200

Probably because you have embedded quote characters in the Connection property.

Not in a position to suggest the solution as I don't have access to Excel at the moment, but have a read of www.ozgrid.com/forum/showthread.php?t=61692

Thank you for your reply. Excel file attached.

asdzxc · May 18th 2012

Expected:list separator or )
original link : http://ichart.finance.yahoo.com/table.csv?s=^dji&a=08&b=13&c=2011&d=06&e=6&f=2013&g=w&ignore=.xlsx
In I2, to put d or w or m.
With Worksheets("Sheet1").QueryTables.Add(Connection:= _
"URL;http://ichart.finance.yahoo.com/table.csv?s=^dji&a=08&b=13&c=2011&d=06&e=6&f=2013&g=_
Worksheets("Sheet1").Range("I2").Value&ignore=.xlsx" _
, Destination:=Worksheets("Sheet1").Range("$A$1"))

Code

On Error Resume Next
ThisWorkbook.Connections(1).Delete
Err.Clear: On Error GoTo 0
With Worksheets("Sheet1").QueryTables.Add(Connection:= _
    "URL;http://ichart.finance.yahoo.com/table.csv?s=^dji&a=08&b=13&c=2011&d=06&e=6&f=2013&g=_
    Worksheets("Sheet1").Range("I2").Value&ignore=.xlsx" _
    , Destination:=Worksheets("Sheet1").Range("$A$1"))
    .Name = _
    "MyData"
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .BackgroundQuery = True
    .RefreshStyle = 0
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .WebSelectionType = xlSpecifiedTables
    .WebFormatting = xlWebFormattingNone
    .WebTables = "4"
    .WebPreFormattedTextToColumns = True
    .WebConsecutiveDelimitersAsOne = True
    .Refresh BackgroundQuery:=False

Display More

asdzxc · May 17th 2012

In I1, put formula =HYPERLINK(J1:J4)
click on a in J4, then move to J1
Plse see attached graph

asdzxc · May 10th 2012

extract data from website to excel

In I1, put formula =TEXT(J1)
In J1 ,put ^DJI,say. What's wrong with the code (red color),plse?

Code

On Error Resume Next  
ThisWorkbook.Connections(1).Delete 
Err.Clear: On Error Goto 0 
With Worksheets("Sheet1").QueryTables.Add(Connection:= _ 
    "URL;http://ichart.finance.yahoo.com/table.csv?s=" & Cells(1, "I")&a=08&b=13&c=2011&d=06&e=6&f=2013&g=w&ignore=.xlsx"
    , Destination:=Range("$A$1")) 
    .Name = _ 
    "MyData" 
    .FieldNames = True 
    .RowNumbers = False 
    .FillAdjacentFormulas = False 
    .PreserveFormatting = True 
    .RefreshOnFileOpen = False 
    .BackgroundQuery = True 
    .RefreshStyle = 0 
    .SavePassword = False 
    .SaveData = True 
    .AdjustColumnWidth = True 
    .RefreshPeriod = 0 
    .WebSelectionType = xlSpecifiedTables 
    .WebFormatting = xlWebFormattingNone 
    .WebTables = "4" 
    .WebPreFormattedTextToColumns = True 
    .WebConsecutiveDelimitersAsOne = True 
    .Refresh BackgroundQuery:=False

Display More

asdzxc · May 10th 2012

Re: extract data (attached) from website to excel

Plse see attached file. Put 14-Oct-2010 at J1 and run your macro
Result: start date 14-Oct-2011

asdzxc · May 9th 2012

link http://www.google.com/finance/…histperiod=weekly&num=30#

Posts by asdzxc

auto selection

picking last data in a week (week ended Friday)

picking last data in a week (week ended Friday)

Count date Cells

Count date Cells

Calculate totals for Fridays only.

delete all files except

delete all files except

Compare 2 Dates In 2 Cells & Fill Down

Compare 2 Dates In 2 Cells & Fill Down

Compare 2 Dates In 2 Cells & Fill Down

VBA Web Query

VBA Web Query

VBA Web Query Produces Compile Error

VBA Web Query Produces Compile Error

VBA Web Query Produces Compile Error

click and move to top of colume

Extract Data From Web To Excel

Extract Data From Web To Excel

Extract Data From Web To Excel