Hi, below is the header for a vba macro I'm trying to create. The reason for this macro is twofold: First and foremost, we have had too many mistakes with this calculation, and I need to make it as "user-proof" as possible. Second, several parts of this will be extremely useful for other things- so I would like to look at this in a more piece by piece way.

The parts that I think I could use help with, I've left un-REM'd.

'query user for:

1. Tool name.
2. Product and Layer used.
3. username.
4. How many alignment points were used in the recipe. (Typically 5, should be the default value) 'I have so far not had a lot of luck with multiple entry boxes. It would be nice if the file selections below are on the same user query, for a better user experience)


directory where data is contained-- let the user pick the two files. Once files are picked, files need to be read, and row 1 (contains the header info) values then put into a drop-down box for each file, allowing the user to select the proper columns.

Use same InputBox for two different inputs (multiple files)- there are two files required for the calculation. RIght now, I have the file reads hard-coded.

REM Input the two files, extracting the data in the row/column specified for both csv files

REM paste that data from both into an xlsx,

REM some formatting etc.

Next, must ensure that there are no missing data points in the last two columns from either file. If there is, then that entire data row must be completely deleted (for both files). '(*need help with this)

REM calclulations.

REM The third and fourth columns from each file give x and y coordinates, but in a really funky way.
REM So, insert two columns next to the 3rd and 4th for each file; perform coordinate transformation. *This isn't in the macro I have below.
using these transformed x,y coordinates and the averaged values columns, graph the values in a contour plot, and put the plot on its own new chart (sheet).

Finally, lock the worksheet grey it all out, with the exception of 4 cells that are way over to the right of the rest of the data- and centered in the window. Lock the window.
X TIS (cell 1) = the actual, not absolute, highest (most positive or most negative) value in the first averaged value column in the cell directly below that title
Y TIS = "" in the second averaged column etc

The two cells for each the X and the Y TIS should be conditionally formatted, green if the value is below a threshold (call it 1), red if above. *haven't figured out conditional formatting in vba

REM Now that the data is "crunched", save the created workbook, using the userinput from above:


REM destination filename - toolname_product_layer_datetime_"TIS".xlsx


// '
// '
// '
//
// 'Some other questions that are related, but not well understood (by me) yet. snippets or "here's where to look" appreciated.
// '
// '
// ' Parse directory info by filename then time/date stamp (order in which files are processed, also need to query timedatestamp for various graphing uses)
// ' How to Input userinfo into DoWhile loop?
// '
// '
// '
// '
// ' reads in multiple csv file data, in accordance with userinput for 1) a directory full of csv files (all files to be read) and 2) the right cell columns (or selected header info like above) as well as file properties for each- need filename and file time/date
// '
// ' puts file data, including file date/timestamp (row 1), file headers (row 2), raw data (row 3 thru xx), for the selected csv files in workbook with name (from userinput), on sheet titled RAWDATA_fileheader. BUT BE CAREFUL: //'the csv files are named with a poor convention, such that the names themselves end up >31 char. Need the last 12 characters in the filename!
// '
// ' Calculates statistics and puts them on sheet RAWSTATS. (order/desired stats unknown currently; at minimum, average, mean, median, stdev, min, max for each-
// ' data file inputted). blah blah blah. Really, I need a way to start using vba to call JMP scripts.
// '+
// ' For each variable used (in the userinput column selections- this is the header value) plot value vs. coordinate.
// ' -coordinates are given in absolute wafer coordinates. Need to transform those numbers into something meaningful- cartesian or radial (radial would be best)
// ' -output the coordinates into appropriate columns
// ' -graph the data vs. new coordinates onto new sheet, GRAPH1
// '
// 'SO far, here's what I have. Any and all help appreciated.


Sub Macro1()
'
' Macro1 Macro
'

'
ChDir "C:\temp\CDREG"

'create new spreadsheet titled TIS
Dim mytime As String
mytime = Format(Time, "hh:mm")

Set NewBook = Workbooks.Add
With NewBook
.Title = "TIS"
.Subject = "TIS"
.SaveAs Filename:=("TIS" & mytime & ".xlsx")
End With

Range("A1").Activate

Workbooks.Open Filename:= _
"C:\temp\CDREG\somebiglongfilenamebiggerthan30char1.csv"

Range("B:B,C:C,E:E,F:F,CV:CV,DE:DE").Select
Range("DE1").Activate
Selection.Copy
Windows("TISmytime.xlsx").Activate
ActiveWindow.WindowState = xlNormal
ActiveSheet.Paste

Workbooks.Open Filename:= _
"C:\temp\CDREG\somebiglongfilenamebiggerthan30char2.csv"

Range("B:B,C:C,E:E,F:F,CV:CV,DE:DE").Select
Range("DE1").Activate
Selection.Copy
Windows("TISmytime.xlsx").Activate
Range("G1").Select
ActiveSheet.Paste
Rows("2:6").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlUp

Rows("1:1").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Range("A1:F1").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Merge
ActiveCell.FormulaR1C1 = "file one"
Range("G1:L1").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Merge
ActiveCell.FormulaR1C1 = "file two"
Range("P1:S1").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Merge
ActiveCell.FormulaR1C1 = "TIS"
Range("P2").Select
ActiveCell.FormulaR1C1 = "Y"
Range("Q2").Select
ActiveCell.FormulaR1C1 = "X"
Range("P3").Select
ActiveCell.FormulaR1C1 = "=AVERAGE(RC[-11],RC[-5])"
Range("P3").Select
'Selection.FillDown
' ActiveWindow.LargeScroll Down:=-2
'ActiveWindow.ScrollRow = 292
'ActiveWindow.ScrollRow = 297
'ActiveWindow.ScrollRow = 301
'ActiveWindow.ScrollRow = 303
'ActiveWindow.ScrollRow = 304
'ActiveWindow.ScrollRow = 305
'ActiveWindow.ScrollRow = 309
'ActiveWindow.ScrollRow = 312
'ActiveWindow.ScrollRow = 314
'ActiveWindow.ScrollRow = 318
'ActiveWindow.ScrollRow = 320
'ActiveWindow.ScrollRow = 324
'ActiveWindow.ScrollRow = 326
'ActiveWindow.ScrollRow = 333
'ActiveWindow.ScrollRow = 335
'ActiveWindow.ScrollRow = 337
'ActiveWindow.ScrollRow = 338
'ActiveWindow.ScrollRow = 339
'ActiveWindow.ScrollRow = 340
'ActiveWindow.ScrollRow = 343
'ActiveWindow.ScrollRow = 347
'ActiveWindow.ScrollRow = 350
'ActiveWindow.ScrollRow = 353
'ActiveWindow.ScrollRow = 355
'ActiveWindow.ScrollRow = 357
'ActiveWindow.ScrollRow = 358
ActiveWindow.ScrollRow = 360
Selection.FillRight


ActiveCell.FormulaR1C1 = "=MIN(RC[-3]:R[27]C[-3])"

Range("X2").Select
ActiveCell.FormulaR1C1 = "minY"
Range("Y2").Select
ActiveCell.FormulaR1C1 = "minX"
Range("Z2").Select
ActiveCell.FormulaR1C1 = "maxY"
Range("AA2").Select
ActiveCell.FormulaR1C1 = "maxX"
Range("Z3").Select
ActiveCell.FormulaR1C1 = "=MAX(RC[-5]:R[27]C[-5])"
Range("Z3").Select
Selection.AutoFill Destination:=Range("Z3:AA3"), Type:=xlFillDefault
Range("Z3:AA3").Select
Range("Y10").Select
ActiveCell.FormulaR1C1 = "TISY"
Range("Z10").Select
ActiveCell.FormulaR1C1 = "TISX"
Range("Y11").Select
ActiveCell.FormulaR1C1 = "=MAX(ABS(R[-8]C[-1]), ABS(R[-8]C[1]))"
Range("Y11").Select
Selection.AutoFill Destination:=Range("Y11:Z11"), Type:=xlFillDefault
Range("Y11:Z11").Select
Range("Z17").Select

ActiveWorkbook.Save
End Sub