Different references for same formula (more complicated than it sounds)

[jn83666]

Hello pros!

I have 300 files concatenated into one spreadsheet totaling 82,200 lines of information. It looks something like this:

Experiment
X -3.46
Y 1.36
Data
0 0 4.0 7.13
0 0 3.5 6.24
0 0 3.0 7.97
0 0 2.5 7.45
0 0 2.0 7.08
Experiment
X -3.45
Y 1.37
Data
0 0 4.0 8.01
0 0 3.5 7.66
0 0 3.0 6.52
0 0 2.5 6.22
0 0 2.0 6.39


The word "Experiment" separates the info from the first file from the second. The 0's are just some unimportant numbers. This is a very simplified version of my spreadsheet.

What I'm trying to do is add a column to the right in the "Data" section which contains a function of the X and Y values and only applies that function for the associated set of data. I obviously need to apply this method to all 82,200 lines in the most efficient manner.

Rather than calculating the function repeatedly, I'm thinking it will be less memory intensive to write a code which solves the function once and then pastes it a certain number of times. Then, skip some lines, recalculate the next function, and paste it again.

I'm imagining this will require some for loops for which I can set the parameters (total number of lines, number of times to paste each calculated value, and number of blanks to skip in between), but I'll leave it to you pros. I'm new to VBA and figured it'd be best to outsource this one.

Hope my description is clear. If not, please let me know, and thanks in advance!

[SamT]

To make it easier to code the function section

Sub InsertBeforeExperiment()
Dim Cel As Range
Dim lr as Long 'lr = # of Last used row
lr = Cells(Rows.Count, "A").End(xlUp).Row

Application.ScreenUpdating = False

For Each Cel in Range("A1:A" & LR)
If Cel= "Experiment" Then Cel.EntireRow.Insert
Next

Application.ScreenUpdating = True
End Sub

That code will insert an empty row before each "Experiment"

The start of the Function section of code is

Sub VBAXBySamT()
Dim Cel As Range
Dim X as Double
Dim Y As Double
Dim DataBlock As Variant
Dim arResults As Variant
Dim i As Long

'Starting point
Set Cel = Range("A2)" 

Do While Cel.Row <>Rows.Count
X = Cel.Offset(1)
Y = Cel.Offset(2)
DataBlock = Range(Cel.Offset(4), Cel.End(xlDown).Offset(, 5))
Resize(arResults, UBound(DataBlock)) 'Give arResults the same number of "Rows" as DataBlock

'At this point in the code, DataBlock is an array of values 5 columns wide
'The first Row or the Array is = LBound(DataBlock), and the last row is =UBound(DataBlock)
'Values in the array are referenced like Cells in a Worksheet are; Value = Array(Row#, Column#)

'Loop thru this DataBlock
For i = LBound(DataBlock) to UBound(DataBlock)
arResults(i) = YourFunction(DataBlock(i), X, Y)
Next

'Put the results next to the Data on the worksheet
Cel.Offset(4, 5) = Results
Results  = 0
DataBlock = 0

'New Start Point
Set Cel = Cel.End(xlDown).End(xlDown)
Loop
End Sub

Now you get to write the Function

Function YourFunction(DataRow As Variant, X As Double, Y As Double) As Double
Dim i As Long
Dim 
Temp as Double
For i = Lbound(DataRow) To '. . . Your turn :)
'Temp = ???
'
'
'
This Function by Name = Temp
'Ex: assume the code is GetAnswer(DataRow As Variant, X As Double, Y As Double) As Double
'then that line is "GetAnswer = Temp"
End Function

[SamT]

Learn and test on a short set of data. Only 2 or3 experiments with a half a dozen lines of data each. You will want to hand check the results. When it is perfected, we can add some standardized code to speed it up.

[jn83666]

Thanks a lot!


I was able to come up with something a little simpler which will work for my purposes.


I'll definitely study your code though! This has been a great learning experience for me.


Thanks again.

[SamT]

Can you post your simpler code here?

[jn83666]

Sub yaxis()

Dim rownum As Integer
Dim k As Double
Dim point As Integer
Dim file As Integer

rownum = 6

For file = 1 To 100
    Selection.Offset(17, 0).Select
    Selection.Value = "Position (m)"
    Selection.Offset(1, 0).Select
    k = (Cells(rownum, 2).Value ^ 2 + Cells(rownum + 1, 2).Value ^ 2) ^ (1 / 2)
    For point = 1 To 256
        Selection.Value = k
        Selection.Offset(1, 0).Select
    Next point
    rownum = rownum + 274
Next file

End Sub

I haven't had a chance to parse through your code yet, but I think I made you do a lot of extra work by not clarifying my definition of the word "function." I simply meant a mathematical function (in this case, sqrt(x^2+y^2). Really sorry about that.

My code requires me to know the total number of files, the separation between X's, and the number of data points to use as parameters. It also requires me to select the first cell in the column I'm filling (E1 in my case) before running the macro. Finally, you'll see that I only do 100 files at a time (so I have to run the macro 3 times for my 300 files) to prevent an overflow error. I'm sure your method is much smarter.

[snb]

you should use arrays in such cases

sub M_snb()
  sn=sheet1.cells(1,1).resize(27400,2)

  for j=1 to 100
    sn(j*17,1)="Position (m)"

    y=(sn(6+274*(j-1),2)^2 +sn(7+274*(j-1),2)^1)^/2
    for jj =2 to 256
     sn(j*17+jj,1)=y
    next
  next

  sheet1.cells(1).resize(257,2)=sn
end sub

[SamT]

With a fixed data format? Definitely use arrays. My offering was based on the idea that the number of rows in each block of data varied and that the data would be used in the function.

My bad, that is not what you said, but it was very early in the AM and it had been a long day.


BTW, it was clear that you meant mathematical function.

[jn83666]

Sam and snb, thanks a lot. I'll definitely look into using arrays instead of rolling selections.

Sam, no apology necessary! You really helped me out with understanding this stuff. I have a lot to learn.