Have you been able to put the formula in successfully by hand.
> "=RSQ(vX, vY)"
When I look at the help, it asks for Y first and then X, so I can see that
you have the arguments reversed, for a start. (I assume your variables are
appropriately named.)
You need to run your script first, so that column I is filled in and then
you need to type an RSQ formula directly into the cell and make sure it
gives you a proper result.
It'll probably look something like
=RSQ(D2:D10,I2:I10)
Now you need to get exactly that in as a formula in J2.
You can either put an exact replica of the formula in as a string, or you
can use the R1C1 mode. In both cases, your vX and vY range variables are
not going to help you, unless you extract the actual string that is the
range from them. So go back a level. Using a string representation of the
formula:
Bottom row of D (is LastRow3 ... (PLEASE use more meaningful variable names)
Bottom row of I is LastRow2
Top row of both is always 2.
ActiveCell.Formula = "=RSQ(D2:D" & LastRow3 & ", I2:I" & LastRow2 & ")"
Note the .Formula, rather than .FormulaR1C1.
You could also use the R1C1 method, but I'd need to fiddle with it to get it
right, and it will be harder to read than the above.
NB the statement just typed into this message, not tested, but should be
close.
Regards, Dave S
----- Original Message -----
From: "edo rs" <edoido_modis@yahoo.com>
To: <ExcelVBA@yahoogroups.com>
Sent: Wednesday, October 24, 2012 3:48 PM
Subject: [ExcelVBA] RSQ calculation using VBA
Dear Excel VBA experts,
I had tried to modified a vba script for processing my multiple xls files. I
wish I can get enlightenment through this forum.
My multiple xls files has different amount of row. The example of file as
below :
A B C D E F G H
-12 114 3 0.2 1 0.1 0.4 0.5
-13 112 4 0.6 1 0.1 0.4 0.8
I would like to square of H and put the results on I. So the expected output
is :
A B C D E F G H I
-12 114 3 0.2 1 0.1 0.4 0.5 0.25
-13 112 4 0.6 1 0.1 0.4 0.8 0.64
The next step, I would like to calculate Rsquare using formula
RSQ=(I2:I3,D2:D3), and put the RSQ results on cell J2. I had tried to
modified a vba script, but the result is not good as expected. Below is my
modified script:
Dim strDocPath As String
Dim strCurrentFile As String
Dim Fname As String
Application.ScreenUpdating = False
Dim LastRow1 As Long
Dim LastRow2 As Long
Dim LastRow3 As Long
LastRow1 = Range("I3").End(xlDown).Row
LastRow2 = Range("I2").End(xlDown).Row
LastRow3 = Range("D2").End(xlDown).Row
Set vX = Range("I2:I" & LastRow2)
Set vY = Range("D2:D" & LastRow3)
strDocPath =
"D:\workfolder\hycom_be_daily\se_project\exploration_t_s_u\be_se2004_2006_19oct2012\be_se2007_validation\tes\"
'strCurrentFile = Dir(strDocPath & "*.*")
strCurrentFile = Dir(strDocPath & "*.xls")
Do While strCurrentFile <> ""
Workbooks.Open Filename:=strDocPath & strCurrentFile
Range("I2").Select
ActiveCell.FormulaR1C1 = "=RC[-1]^2"
Range("I2").Select
Selection.Copy
Range("I3:I" & LastRow1).Select
ActiveSheet.Paste
Range("J2").Select
Application.CutCopyMode = False
'ActiveCell.FormulaR1C1 = "=RSQ(vX, vY)"
Application.WorksheetFunction.RSq(vX, vY)
ActiveWorkbook.Save
ActiveWorkbook.Close
strCurrentFile = Dir
Loop
Application.ScreenUpdating = True
Your help is really appreciated
Regards
Ed
[Non-text portions of this message have been removed]
------------------------------------
----------------------------------
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go
here: http://www.mousetrax.com/techtrax to enter the ezine, then search the
ARCHIVES for EXCEL VBA.
----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA
----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com
----------------------------------Yahoo! Groups Links
-----
No virus found in this message.
Checked by AVG - www.avg.com
Version: 10.0.1427 / Virus Database: 2441/5350 - Release Date: 10/23/12
| Reply via web post | Reply to sender | Reply to group | Start a New Topic | Messages in this topic (3) |
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.
----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA
----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com
----------------------------------
Tidak ada komentar:
Posting Komentar