fallen2151
04-22-2015, 06:53 AM
I wrote a macro that would take some data in two excel sheets, change a couple strings, and output values to a table. It had originally worked, but when I changed the start time that is being used I got a type mismatch error for the line that is in blue and the lines in red took a lot longer to run through. Switching the start time changed the referenced values, which was the only thing different. This is a section of the code, there's some more, but it's essentially the same, but for the minimum peak as opposed to the max. I'm not sure why I got a type mismatch since it's just populating a cell with a string and the fact that it worked before. Thanks for helping!
Sub Retention()
Dim ms, mee, mins, mine, Tr, s, d As Date
Dim r, rr, m, sec As Integer
Dim h, ar, w, t As Double
Dim mas, mae, mis, mie, a As String
'Activate analytical injestion 1s sheet
Sheets("Analytical injection 1s").Activate
'Get row number for the hplc batch being used
r = InputBox("enter the row of the analytical injection")
a = InputBox("Enter min or max")
'store the max start and end times
ms = Cells(r, 16).Value
mee = Cells(r, 18).Value
'Activate find tr by area sheet
Sheets("Find Tr by area").Activate
'Displays the start and end times for max
Cells(12, 14).Value = "Max"
Cells(12, 15).Value = ms
Cells(12, 16).Value = mee
'Turns the time into a string
mas = Format(ms, "d-mmm-yy h:mm:ss")
mae = Format(mee, "d-mmm-yy h:mm:ss")
'Rewrites the pi code to find the retention time from that injection
Cells(23, 14).Value = "tagtot('filename', '" & mas & "', '" & mae & "')"
h = Cells(23, 16).Value
Cells(24, 12).Value = "tagtot('filename', '" & mas & "', '*')"
Cells(25, 12).Value = "(tagtot('filename', '" & mas & "', '*') >= " & h & ")"
Tr = Cells(25, 15).Value
Sub Retention()
Dim ms, mee, mins, mine, Tr, s, d As Date
Dim r, rr, m, sec As Integer
Dim h, ar, w, t As Double
Dim mas, mae, mis, mie, a As String
'Activate analytical injestion 1s sheet
Sheets("Analytical injection 1s").Activate
'Get row number for the hplc batch being used
r = InputBox("enter the row of the analytical injection")
a = InputBox("Enter min or max")
'store the max start and end times
ms = Cells(r, 16).Value
mee = Cells(r, 18).Value
'Activate find tr by area sheet
Sheets("Find Tr by area").Activate
'Displays the start and end times for max
Cells(12, 14).Value = "Max"
Cells(12, 15).Value = ms
Cells(12, 16).Value = mee
'Turns the time into a string
mas = Format(ms, "d-mmm-yy h:mm:ss")
mae = Format(mee, "d-mmm-yy h:mm:ss")
'Rewrites the pi code to find the retention time from that injection
Cells(23, 14).Value = "tagtot('filename', '" & mas & "', '" & mae & "')"
h = Cells(23, 16).Value
Cells(24, 12).Value = "tagtot('filename', '" & mas & "', '*')"
Cells(25, 12).Value = "(tagtot('filename', '" & mas & "', '*') >= " & h & ")"
Tr = Cells(25, 15).Value