increment by input message box and interpolate between removed data

[Brandon M]

I am trying to write a code that will take column B and increment the values in the column by a value that can be entered into an input message box by the user. In the example provided below the user wants 0.1 as his or her increment for column B. Column A should be linearly interpolated as column B is shortened.
So far my code is just set up to grab data from the specified machine and bring it into the sheet where the raw data will be cleaned into usable data. I’ve been researching for days and can’t quite get my head around how to set the increment and interpolate the data.

Raw data looks similar to this but much larger. There is a lot of data missing here. One of the files has nearly 13000 rows. A completely parsed file will have less than 400 rows.
A B
[TABLE="width: 130"]
<tbody>[TR]
[TD]0.00[/TD]
[TD]0.01[/TD]
[/TR]
[TR]
[TD]0.00[/TD]
[TD]0.01[/TD]
[/TR]
[TR]
[TD]0.00[/TD]
[TD]0.01[/TD]
[/TR]
[TR]
[TD]0.00[/TD]
[TD]0.01[/TD]
[/TR]
[TR]
[TD]0.00[/TD]
[TD]0.01[/TD]
[/TR]
[TR]
[TD]0.00[/TD]
[TD]0.02[/TD]
[/TR]
[TR]
[TD]0.00[/TD]
[TD]0.02[/TD]
[/TR]
[TR]
[TD]0.00[/TD]
[TD]0.02[/TD]
[/TR]
[TR]
[TD]0.00[/TD]
[TD]0.03[/TD]
[/TR]
[TR]
[TD]0.17[/TD]
[TD]0.03[/TD]
[/TR]
[TR]
[TD]0.17[/TD]
[TD]0.03[/TD]
[/TR]
[TR]
[TD]0.17[/TD]
[TD]0.04[/TD]
[/TR]
[TR]
[TD]0.34[/TD]
[TD]0.04[/TD]
[/TR]
[TR]
[TD]0.17[/TD]
[TD]0.05[/TD]
[/TR]
[TR]
[TD]0.17[/TD]
[TD]0.05[/TD]
[/TR]
[TR]
[TD]0.34[/TD]
[TD]0.06[/TD]
[/TR]
[TR]
[TD]0.34[/TD]
[TD]0.07[/TD]
[/TR]
[TR]
[TD]0.51[/TD]
[TD]0.07[/TD]
[/TR]
[TR]
[TD]0.51[/TD]
[TD]0.07[/TD]
[/TR]
[TR]
[TD]0.51[/TD]
[TD]0.08[/TD]
[/TR]
[TR]
[TD]0.68[/TD]
[TD]0.09[/TD]
[/TR]
[TR]
[TD]0.68[/TD]
[TD]0.10[/TD]
[/TR]
[TR]
[TD]0.68[/TD]
[TD]0.10[/TD]
[/TR]
[TR]
[TD]0.84[/TD]
[TD]0.11[/TD]
[/TR]
[TR]
[TD]0.84[/TD]
[TD]0.12[/TD]
[/TR]
[TR]
[TD]0.84[/TD]
[TD]0.12[/TD]
[/TR]
</tbody>[/TABLE]


Cleaned data should look something like this:
A B
[TABLE="width: 293"]
<tbody>[TR]
[TD]0.1[/TD]
[TD]0.527421817[/TD]
[/TR]
[TR]
[TD]0.2[/TD]
[TD]0.991580884[/TD]
[/TR]
[TR]
[TD]0.3[/TD]
[TD]1.586479186[/TD]
[/TR]
[TR]
[TD]0.4[/TD]
[TD]2.314630901[/TD]
[/TR]
[TR]
[TD]0.5[/TD]
[TD]2.835397098[/TD]
[/TR]
[TR]
[TD]0.6[/TD]
[TD]3.544292655[/TD]
[/TR]
[TR]
[TD]0.7[/TD]
[TD]4.022441664[/TD]
[/TR]
[TR]
[TD]0.8[/TD]
[TD]4.613159911[/TD]
[/TR]
[TR]
[TD]0.9[/TD]
[TD]5.105284463[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]5.593649288[/TD]
[/TR]
[TR]
[TD]1.1[/TD]
[TD]6.14812775[/TD]
[/TR]
[TR]
[TD]1.2[/TD]
[TD]6.687576899[/TD]
[/TR]
[TR]
[TD]1.3[/TD]
[TD]7.186948527[/TD]
[/TR]
[TR]
[TD]1.4[/TD]
[TD]7.721394663[/TD]
[/TR]
[TR]
[TD]1.5[/TD]
[TD]8.216697024[/TD]
[/TR]
[TR]
[TD]1.6[/TD]
[TD]8.832567724[/TD]
[/TR]
[TR]
[TD]1.7[/TD]
[TD]9.419757746[/TD]
[/TR]
[TR]
[TD]1.8[/TD]
[TD]10.02798463[/TD]
[/TR]
[TR]
[TD]1.9[/TD]
[TD]10.64584242[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]11.2516133[/TD]
[/TR]
[TR]
[TD]2.1[/TD]
[TD]11.91266204[/TD]
[/TR]
[TR]
[TD]2.2[/TD]
[TD]12.51039649[/TD]
[/TR]
[TR]
[TD]2.3[/TD]
[TD]13.09953086[/TD]
[/TR]
[TR]
[TD]2.4[/TD]
[TD]13.64477697[/TD]
[/TR]
[TR]
[TD]2.5[/TD]
[TD]14.36453506[/TD]
[/TR]
[TR]
[TD]2.6[/TD]
[TD]15.08429885[/TD]
[/TR]
[TR]
[TD]2.7[/TD]
[TD]15.67496777[/TD]
[/TR]
[TR]
[TD]2.8[/TD]
[TD]16.29622629[/TD]
[/TR]
[TR]
[TD]2.9[/TD]
[TD]16.91370968[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]17.60455092[/TD]
[/TR]
[TR]
[TD]3.1[/TD]
[TD]18.17145856[/TD]
[/TR]
</tbody>[/TABLE]


My code so far. This is basically just moving files from the file location to the target sheet and creating new tabs, naming tabs, etc. Some of the dims are not used yet as I’ve just been trying different things to make my code work.

Sub interpolate()
 
Dim i As Integer    'counter for raw data row count
Dim j As Integer    'counter for clean data column count
Dim k As Integer    'counter for raw data column count
Dim count As Integer
 
Dim Load(1 To 20000) As Variant
Dim Disp(1 To 20000) As Variant
Dim z1(1 To 20000) As Variant
Dim r
 
Dim DataFile As String
Dim DataName As String
Dim DataSheetName As String
Dim Message As String
Dim Title As String
Dim Default As String
Dim DataSheetVariant As Variant
Dim DataSheetNumber As Integer
Dim ProgramName As String
Dim WBf As Workbook
Dim WBt As Workbook
Dim DSA As Worksheet 'DSA=Data Set A
Dim fnameandpath As Variant
Dim path As String, filename As String, filetype As String
Dim SpacePos As Integer
Dim SV As Integer   'SV=Serial number Search Value
Dim Inc As Double  'deflection increment value
 
'**************TEST DATA LOCATION******************
ChDrive "U:\"
ChDir "U:\location"
fnameandpath = Application.GetOpenFilename()
If fnameandpath = False Then Exit Sub
'*************************************************
 
 
Set WBf = Workbooks.Open(fnameandpath)
Set WBt = ThisWorkbook
'Set RD = WBt.Sheets.Add(After:=Sheets(WBt.Sheets.Count))
 
'On Error GoTo User_Canceled:
 
'***************************
'Enter desired serial number and find in from file
'***************************
SV = InputBox("Please provide serial number.", "Serial Number")
'If SV = "" Then Exit Sub
Set found = Range("A1:FZ1").Find(What:=SV, LookIn:=xlValues, LookAt:=xlWhole)
If found Is Nothing Then
    MsgBox "Serial number not found."
    Else
    'selects 4 columns and 16000 rows from serial number designation
    found.Offset(0, 0).Resize(16000, 2).Copy
    found.Offset(1, 0).Select
End If
 
'************************
'Copy Data from file
'************************
 
'With WBf
'Sheets("Raw Data").Select
'Range(Cells(1, 1), Cells(15000, 65)).Select
'Selection.Copy
'End With
 
'*********************
'return to target worksheet
'*********************
WBt.Activate
'create new worksheet
Set DSA = WBt.Sheets.Add(after:=WBt.Sheets(ThisWorkbook.Sheets.count))
DSA.Name = "Data Set A"
 
With WBt
ary = Split(fnameandpath, "\")
bry = Split(ary(UBound(ary)), ".")
ary(UBound(ary)) = ""
path = Join(ary, "\")
filename = bry(0)
filetype = bry(1)
 
End With
 
'*********************************
'Copying file name to new tab name
'*********************************
With RD
Range("A1") = "Path:"
Range("A2") = "Filename:"
'Range("A3") = "Filetype:"
 
Range("B1") = path
Range("B2") = filename
'Range("B3") = filetype
 
SpacePos = InStr(Range("B2"), " ")  'Looks for space in string
 
ActiveSheet.Name = Left(Range("B2"), SpacePos + 0) 'use everything to the left of the space and 14 char to the right
 
'***********************
'Paste Data into new Tab
'***********************
Range("A4").PasteSpecial xlPasteAll
End With
 
Application.DisplayAlerts = False
WBf.Close False
 
‘This is just my first attempts at figuring out a way to get the increment working. It’s all wrong.
'Inc = InputBox("Please specify deflection increment value.")
Range("B7") = ActiveCell
i = 7
j = 7
i = i + 1
test = (ActiveCell.Value) - ActiveCell.Offset(1, 0).Value < 0.001
Do While ActiveCell <> ""
If test = True Then
    ActiveCell.Offset(1, 0).EntireRow.Delete
Else
    ActiveCell.Offset(1, 0).Select
End If
Loop
'Do While ActiveCell <> ""
'User_Canceled:
End Sub

Thanks in advance for any help. All suggestions are much appreciated.

Best,
Brandon

 

[Brandon M]

Lots of views and no replies. Is this because my question is too complex or because no one understands what I'm asking?

 

[AkaTrouble]

Lots of views and no replies. Is this because my question is too complex or because no one understands what I'm asking?

i won't say i don't understand what you are asking, just that i am confused by your examples and i can not see any logic to what you ask and what you show

i see that you are creating a list in column that increments by a value of 0.1 but where do we get the 0.527421817 from and how does the maths work to make this 0.991580884 in the next

maybe a little more explanation one example at a time and telling us your logic in plain words rather on relying on the examples (which mean something to you but not me, but may upon further detail)

 

[Brandon M]

i won't say i don't understand what you are asking, just that i am confused by your examples and i can not see any logic to what you ask and what you show

i see that you are creating a list in column that increments by a value of 0.1 but where do we get the 0.527421817 from and how does the maths work to make this 0.991580884 in the next

maybe a little more explanation one example at a time and telling us your logic in plain words rather on relying on the examples (which mean something to you but not me, but may upon further detail)

Thx for the reply, AKA Trouble. I’ll try to explain it better.

I have a series of two columns that will contain a lot of raw data. Column B is the controlling column. The values in column A is directly connected to the values in column B.

The first part of my goal is to increment column B by a number specified by the user. I would assume that input needs to come from a message box. After the user has entered their desired increment value, column B is then modified or the new array is copied to another location. This will be my x axis for my graph. I need it to be the same increment every time I run this macro for a different set of data. This value needs to be in nice round numbers.

The second part of my goal is to interpolate the values in column A as the size of column C is decreased.

To interpolate properly the macro needs to search col B for the numbers immediately smaller and larger than the newer, nicer column B value located in column E.

Interpolation eqn: y = y1+(x+x1)*((y2-y1) / (x2-x1))

Where:
y = newly interpolated column B value
y1 = column B value that corresponds to the column A value just less than the newly created column E value.
y2 = column B value that corresponds to the column A value just more than the newly created column E value.
x = column E à revised column B value
x1 = column B value just less than new column E value
x2 = column B value just more than new column E value

Ex. In raw form, column B is incremented by a very small amount (approx. 0.002). I would like to call a message box asking the user to enter an increment value. If the user enters 0.01 as the new increment, then the new values for column B will be:

[TABLE="width: 535"]
<tbody>[TR]
[TD]Column A raw data
[/TD]
[TD]Column B raw data
[/TD]
[TD][/TD]
[TD]Column D interpolated value
[/TD]
[TD]column E revised column B value
[/TD]
[/TR]
[TR]
[TD]0
[/TD]
[TD]0
[/TD]
[TD][/TD]
[TD]0.000000
[/TD]
[TD]0.00
[/TD]
[/TR]
[TR]
[TD]0.000000000000
[/TD]
[TD]0.0449829101562500
[/TD]
[TD][/TD]
[TD]0.000000
[/TD]
[TD]0.04
[/TD]
[/TR]
[TR]
[TD]0.168775439262
[/TD]
[TD]0.0499801635742187
[/TD]
[TD][/TD]
[TD]0.168775
[/TD]
[TD]0.05
[/TD]
[/TR]
[TR]
[TD]0.168775439262
[/TD]
[TD]0.0574798583984375
[/TD]
[TD][/TD]
[TD]0.168775
[/TD]
[TD]0.06
[/TD]
[/TR]
[TR]
[TD]0.168775439262
[/TD]
[TD]0.0624771118164062
[/TD]
[TD][/TD]
[TD]0.168775
[/TD]
[TD]0.07
[/TD]
[/TR]
[TR]
[TD]0.168775439262
[/TD]
[TD]0.0674743652343750
[/TD]
[TD][/TD]
[TD]0.168775
[/TD]
[TD]0.08
[/TD]
[/TR]
[TR]
[TD]0.168775439262
[/TD]
[TD]0.0749740600585937
[/TD]
[TD][/TD]
[TD]0.168775
[/TD]
[TD]0.09
[/TD]
[/TR]
[TR]
[TD]0.168775439262
[/TD]
[TD]0.0800018310546875
[/TD]
[TD][/TD]
[TD]0.168775
[/TD]
[TD]0.1
[/TD]
[/TR]
[TR]
[TD]0.168775439262
[/TD]
[TD]0.0875015258789062
[/TD]
[TD][/TD]
[TD]0.168775
[/TD]
[TD]0.11
[/TD]
[/TR]
[TR]
[TD]0.168775439262
[/TD]
[TD]0.0950012207031250
[/TD]
[TD][/TD]
[TD]0.281345
[/TD]
[TD]0.12
[/TD]
[/TR]
[TR]
[TD]0.168775439262
[/TD]
[TD]0.1025009155273440
[/TD]
[TD][/TD]
[TD]0.337551
[/TD]
[TD]0.13
[/TD]
[/TR]
[TR]
[TD]0.337550967932
[/TD]
[TD]0.1074981689453120
[/TD]
[TD][/TD]
[TD]0.393880
[/TD]
[TD]0.14
[/TD]
[/TR]
[TR]
[TD]0.168775439262
[/TD]
[TD]0.1149978637695310
[/TD]
[TD][/TD]
[TD]0.393729
[/TD]
[TD]0.15
[/TD]
[/TR]
[TR]
[TD]0.337550967932
[/TD]
[TD]0.1224975585937500
[/TD]
[TD][/TD]
[TD]0.506259
[/TD]
[TD]0.16
[/TD]
[/TR]
[TR]
[TD]0.337550967932
[/TD]
[TD]0.1299972534179690
[/TD]
[TD][/TD]
[TD]0.337613
[/TD]
[TD]0.17
[/TD]
[/TR]
[TR]
[TD]0.337550967932
[/TD]
[TD]0.1374969482421870
[/TD]
[TD][/TD]
[TD]0.506326
[/TD]
[TD]0.18
[/TD]
[/TR]
[TR]
[TD]0.506326436996
[/TD]
[TD]0.1449966430664060
[/TD]
[TD][/TD]
[TD]0.506326
[/TD]
[TD]0.19
[/TD]
[/TR]
[TR]
[TD]0.337550967932
[/TD]
[TD]0.1524963378906250
[/TD]
[TD][/TD]
[TD]0.562711
[/TD]
[TD]0.2
[/TD]
[/TR]
[TR]
[TD]0.506326436996
[/TD]
[TD]0.1599960327148440
[/TD]
[TD][/TD]
[TD]0.562450
[/TD]
[TD]0.21
[/TD]
[/TR]
[TR]
[TD]0.337550967932
[/TD]
[TD]0.1699981689453120
[/TD]
[TD][/TD]
[TD]0.675102
[/TD]
[TD]0.22
[/TD]
[/TR]
[TR]
[TD]0.506326436996
[/TD]
[TD]0.1749954223632810
[/TD]
[TD][/TD]
[TD]0.717271
[/TD]
[TD]0.23
[/TD]
[/TR]
[TR]
[TD]0.506326436996
[/TD]
[TD]0.1824951171875000
[/TD]
[TD][/TD]
[TD]0.562519
[/TD]
[TD]0.24
[/TD]
[/TR]
</tbody>[/TABLE]


I hope this clears things up a bit. I'm still trying to figure the code out, but getting nowhere. Thanks for your help on this one, everyone.

-Brandon

 

[Brandon M]

So, I'm guessing the above post did not help?

 

[Brandon M]

I know it's a tough one, but any help on any portion would be much appreciated.

Thanks, everyone.