PDA

View Full Version : Type mismatch error, code previously worked



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

NoSparks
04-22-2015, 10:42 AM
Have you tried declaring your variables individually ?
All those one line declarations may not be what you think.

h ttp://www.cpearson.com/excel/declaringvariables.aspx

Paul_Hossler
04-22-2015, 08:12 PM
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


ms is a Variant, mee is a Variant, ...., d is a Date


1. What you want is each one specifically Dim-ed

Dim ms as Date, mee as Date, ....
Dim r as Integer,

2. More self-documenting variable names would help us also

Dim MaxStartTime as Date, etc.....

I like to use a prefix on mine (personal style): dtMaxStart as Date, iRadius as Long, sFormattedMaxStartDate as string, etc.

Again ... very personal style

3. The line in blue 'should' work, but

Go into the Immediate window and type ?mas and ?h to see if they're values that can be used