Posts by junho lee

Re: How Excel computes RSQ for Trendlines - is there a BUG?

Hi ksatyamahesh,
Thank you for giving answer.
Regards, junho

Re: Selecting a dynamic range

Hi Xenoun,
Nice to meet you.
I use data > list followed by name > Create.
If you use this, named range is auto adjusted.
Regards, junho

Re: returning textbox name and text

Hi pakgy910,
Nice to meet you.
Try this.

Application.Caller

Regards, junho

Re: Need help on this Macro.

Hi heartsdales,
Nice to meet you.
I've searched on this subject.
Maybe you are working with two file formats. ie, xls and 2007 format.
Try with same file formats.
http://www.ozgrid.com/forum/showthread.php?t=93527&page=1
Regards, junho

Re: Unable to search numeric strings for numbers starting with xxx

Hi pezhed618,
Nice to meet you.
It works to me.
=SUMPRODUCT((LEFT(A1:E1,3)="425")*1)
Regards, junho

Re: Nested For Loop

Hi mooshuchyken,
Nice to meet you.
This works to me.

For j = N To 1 Step -1
    For i = np To np - j + 1 Step -1

Regards, junho

Re: Parabolic SAR

Hi theriqueza,
Nice to meet you in this forum, and I find it.
http://www.anxw.org/2010/06/pa…sar-in-excel-spreadsheet/
Regards, jnho

Re: How Excel computes RSQ for Trendlines - is there a BUG?

Hi ksatyamahesh,
Sorry for replying late.
linear: y=ax+b : rsq(y,x)
logarithmetic: y=aln(bx) --> exp(y)=exp(a)+bx : rsq(exp(y),x)
polynomial : y=ax^2+bx+c : rsq(y,x^{1,2})
Not sure foumulas i offered, but theory is convert fomula to linest form.
Regards, junho

Re: How Excel computes RSQ for Trendlines - is there a BUG?

Hi ksatyamahesh,
Nice to meet you in this forum.
First convert linear data then apply formula.
Or try this function.
power trend:
=INDEX(LINEST(LN(B34:B105),LN(A34:A105),TRUE,TRUE),3,1)
or
=RSQ(LN(B34:B105),LN(A34:A105))
exponential trend:
=INDEX(LINEST(LN(B34:B105),A34:A105,TRUE,TRUE),3,1)
or
=RSQ(LN(B34:B105),A34:A105)
Regards, junho

Re: Copying specific Cell values fron multiple sheets to specified cell of another s

Hi nm766,
Nice to meet you.
Write something like..

Sub summarize()
    Application.Calculation = xlCalculationManual
    Dim summarysheet As Worksheet
    Dim rnamedsheets As Range
    Dim rnamedsheet As Range
    Set summarysheet = Sheets("summary")
    Set rnamedsheets = summarysheet.Range("named_area_for_sheets")'Range("a1:a66")
    For Each rnamedsheet In rnamedsheets
        summarysheet.Range("n" & rnamedsheet.Row) = Sheets(rnamedsheet).Range("z64")
        summarysheet.Range("o" & rnamedsheet.Row) = Sheets(rnamedsheet).Range("ab64")
        summarysheet.Range("p" & rnamedsheet.Row) = Sheets(rnamedsheet).Range("ad64")
    Next rnamedsheet
    Application.Calculation = xlCalculationAutomatic
End Sub

Regards, junho

Re: Excel query

Hi nivsam1210,
Nice to meet you.
Would you look at attatched?
http://www.mrexcel.com/pc09.shtml
Regards, junho

Re: Web query with dynamic parameter picked up from cell values

Hi rohit.razdan,
For me this works.

Sub DownXLS()
    Dim ie
    Dim url As String
    url = "[URL]http://www.powerexindia.com[/URL]"
    url = url & "/PXILReport/Reserved.ReportViewerWebControl.axd?Mode=true"
    url = url & "&ReportID=b261067beb5e460daf9e79ab2a2da63a"
    url = url & "&ControlID=729c53b1-799d-4a60-a12c-9439549b834f"
    url = url & "&Culture=1033&UICulture=1033"
    url = url & "&ReportStack=1"
    url = url & "&OpType=Export"
    url = url & "&FileName=PXIL_DAS_MCP_Report_09FEB2011"
    url = url & "&ContentDisposition=AlwaysAttachment"
    url = url & "&Format=Excel"
    Set ie = CreateObject("internetexplorer.application")
    ie.navigate url
End Sub

Regards, junho

Re: Web query with dynamic parameter picked up from cell values

Hi rohit.razdan,
Try this link.
http://www.google.com/#sclient=psy&hl=en&q=xa.yimg.com%2Fkq%2Fgroups%2F18094620
Regards, junho

Re: Web query with dynamic parameter picked up from cell values

If google with word : [http://www.powerexindia.com/PX…ed.ReportViewerWebControl].
There has [xls] file.
Regards, junho

Re: EXCEL VBA - Problem with Refresh BackgroundQuery:=False

Hi mjsimmons,
Nice to meet you.
I write startline of code then works.

On Error GoTo err_description
Set DataSheet = ActiveSheet
quote = "vvv"
qurl = "[URL]http://chart.yahoo.com/table.csv?s[/URL]=" & quote & "&a=1&b=3&c=2011&d=1&e=The"
With ActiveSheet.QueryTables.Add(Connection:="URL;" & qurl, Destination:=DataSheet.Range("C7"))
.BackgroundQuery = True
.TablesOnlyFromHTML = False
.Refresh BackgroundQuery:=False
.SaveData = True
End With
Range("C7").CurrentRegion.TextToColumns Destination:=Range("C7"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=True, Space:=False, other:=False
Range("C1:I1").Select
Selection.ColumnWidth = 8
err_description: Exit Sub

Regards, junho

Re: Tree Diagram with Parent/Child nodes from Excel data

Hi bradpallister,
Nice to meet you.
I saw drawing at newtonexcelbach's blog before and leave a link because it seems to be related with your needs.
http://newtonexcelbach.wordpre…rawings-from-coordinates/
Regards, junho

Re: Web query with dynamic parameter picked up from cell values

Hi rohit.razdan,
Nice to meet you.
First turn on macro recoder followed by web query with underlying url.
Then adjust macro like below.

....&ControlID=" [B]& range("controlid").value &[/B] "e2859e2be9fd&Culture=1033&UICulture=1033&ReportStack=1&OpType=ReportArea&Controller=" [B]& range("ClientControllerctl00_ContentPlaceHolder4_ReportViewerData").value &[/B] "&PageNumber=1&ZoomMode=Percent&ZoomPct=100&ReloadDocMap=true&EnableFindNext=False&LinkTarget=_top"
'......
.RefreshStyle = [B]xlOverwriteCells
[/B]

Regards, junho

Re: Deleting menu bar in Excel 2007 using VBA

Thank you.
You are my first teacher.
Regards, junho

Re: Time formats

Hi Strider,
Nice to meet you.
Try this function.
=SUBSTITUTE(A1,".",":")
Regards, junho

Re: Microsoft Forms Error

Hi kev,
Nice to meet you in ozgrid.
Try this one.
http://www.ascentive.com/suppo…html?dllname=MSCOMCT2.OCX
Regards, junho