# Posts by junho lee

• ## How Excel computes RSQ for Trendlines - is there a BUG?

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

Hi ksatyamahesh,
Regards, junho

• ## Selecting a dynamic range

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

• ## returning textbox name and text

Re: returning textbox name and text

Hi pakgy910,
Nice to meet you.
Try this.

Code
``Application.Caller``

Regards, junho

• ## Need help on this Macro.

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.
Regards, junho

• ## Count occurences based on first X characters in string of numbers

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

• ## Nested For Loop

Re: Nested For Loop

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

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

Regards, junho

• ## How Excel computes RSQ for Trendlines - is there a BUG?

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

Hi ksatyamahesh,
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

• ## How Excel computes RSQ for Trendlines - is there a BUG?

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

• ## Copying specific Cell values fron multiple sheets to specified cell of another sheet

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

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

Regards, junho

• ## Excel query

Re: Excel query

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

• ## Web query with dynamic parameter picked up from cell values

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

Hi rohit.razdan,
For me this works.

Regards, junho

• ## Error handle for invalid entries when running background web query

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

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

Regards, junho

• ## Tree Diagram with Parent/Child nodes from Excel data

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

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

• ## Web query with dynamic parameter picked up from cell values

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.

Code
``````....&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

• ## Deleting menu bar in Excel 2007 using VBA

Re: Deleting menu bar in Excel 2007 using VBA

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

• ## Replace decimal separator in time format to colon

Re: Time formats

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