Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA - String concatanation problem
Exactly what I was looking for. It works like a charm!
Thanks : -- Message posted from http://www.ExcelForum.com |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Problem with VB string character limit. | Excel Worksheet Functions | |||
string problem | Excel Programming | |||
SQL string problem | Excel Programming | |||
string sorting problem | Excel Programming | |||
Formatting Problem on Double-Zero String | Excel Programming |