Excel VBA - String concatanation problem
 

Hello, I'm working on a macro which takes data from a custom piece of
hardware, performs a linear regression analysis, then corrects the data
according to that function.

The each set of data consists of 1000 points. Where things get tricky
is that there are generally 10-20 sets of data per worksheet
(10000-20000 points). I already created a macro which performs a rough
correction on all the points by using the regression data from the first
data set to correct all of the data sets. I've copied it below so you
can get a better idea of what I'm doing (since I don't always explain
things well ;)).
Code:
--------------------
Sub RoughCorrection()
'
' Rough Correction Macro
' Macro recorded 7/27/2004 by Ryan VanderMeulen
'
' This macro is designed to take the depth data for a surface profiling test
' and do a very rough linear correction on it, using the best fit line from
' the first pass of the profiling to correct the all of the points.
'
Count = 2 'Start at 2 because first row contains cell titles
Do While IsEmpty(Range("A" & Count).Value) = False 'Stop loop at end of worksheet data
Range("D" & Count).Select
ActiveCell.FormulaR1C1 = "=(RC[-2]*LINEST(R2C3:R1001C3,R2C2:R1001C2))-RC[-1]"
Count = Count + 1
Loop
Range("D1").Select
ActiveCell.FormulaR1C1 = "Corrected"
Columns("D:D").EntireColumn.AutoFit
End Sub
--------------------
What I need to be able to do is incorporate the count into the LINEST
string, so that for the first 1000 points, it goes from rows 2-1001,
for the second 1000 points, 1002-2001, etc.

Any help you guys could provide would be greatly appreciated. Thanks!


---
Message posted from http://www.ExcelForum.com/

Excel VBA - String concatanation problem
 

Not tested, but does this do what you want?

ActiveCell.FormulaR1C1 = "=" & Count &
"&(RC[-2]*LINEST(R2C3:R1001C3,R2C2:R1001C2))-RC[-1]"


--
HTH

-------

Bob Phillips
"RyanVM " wrote in message
...
Hello, I'm working on a macro which takes data from a custom piece of
hardware, performs a linear regression analysis, then corrects the data
according to that function.

The each set of data consists of 1000 points. Where things get tricky
is that there are generally 10-20 sets of data per worksheet
(10000-20000 points). I already created a macro which performs a rough
correction on all the points by using the regression data from the first
data set to correct all of the data sets. I've copied it below so you
can get a better idea of what I'm doing (since I don't always explain
things well ;)).
Code:
--------------------
Sub RoughCorrection()
'
' Rough Correction Macro
' Macro recorded 7/27/2004 by Ryan VanderMeulen
'
' This macro is designed to take the depth data for a surface profiling
test
' and do a very rough linear correction on it, using the best fit line
from
' the first pass of the profiling to correct the all of the points.
'
Count = 2 'Start at 2 because first row contains cell titles
Do While IsEmpty(Range("A" & Count).Value) = False 'Stop loop at end of
worksheet data
Range("D" & Count).Select
ActiveCell.FormulaR1C1 =
"=(RC[-2]*LINEST(R2C3:R1001C3,R2C2:R1001C2))-RC[-1]"
Count = Count + 1
Loop
Range("D1").Select
ActiveCell.FormulaR1C1 = "Corrected"
Columns("D:D").EntireColumn.AutoFit
End Sub
--------------------
What I need to be able to do is incorporate the count into the LINEST
string, so that for the first 1000 points, it goes from rows 2-1001,
for the second 1000 points, 1002-2001, etc.

Any help you guys could provide would be greatly appreciated. Thanks!


---
Message posted from http://www.ExcelForum.com/

Excel VBA - String concatanation problem
 

Exactly what I was looking for. It works like a charm!

Thanks :

--
Message posted from http://www.ExcelForum.com

Excel VBA - String concatanation problem
 

That was luck as I didn't really know how to test it<g

--
HTH

-------

Bob Phillips
"RyanVM " wrote in message
...
Exactly what I was looking for. It works like a charm!

Thanks :)


---
Message posted from http://www.ExcelForum.com/

Excel VBA - String concatanation problem
 

Here's a related question. I'm trying to use the value of a variable i
an equation. The variable Slope is a double precision value resultin
from a LINEST equation performed earlier. I'm trying to set up the VB
code so that the Excel formula looks like =(B1*Slope)-C1 where Slope i
that value calculated earlier. I've tried a few differen
combinations, but with no luck.

I'd imagine it's something like this, but it's not working
Code
-------------------
ActiveCell.FormulaR1C1 = "=(RC[-2]*" & Slope & ")-RC[-1]
-------------------


--
Message posted from http://www.ExcelForum.com