Type Mismatch Error

[zreitman]

I have been getting a type mismatch error with the following code. I believe it is happening within the 'if' statement.

Sub Hold()
Dim c As Range
Set c = Range("C2000:C2050")
Dim r1 As Long
Dim r2 As Long
Dim x As Long
Dim i As Long

For x = 2 To c.Cells.Count
r1 = c.Cells(x, 1).Value
r2 = c.Cells(x - 1, 1).Value

If r1 - r2 = 1 Then
Range("K5:K303").Value = Range("K5:K303").Value - Range("K5").Value
Range("G" & x - 1 & ":G" & x + 297).Copy Destination:=Range("K5")
Exit For
End If
Next x
End Sub

 

[Legacy 143009]

I am not quite sure that you can subtract something from a range. It must be a single value.

Range("K5:K303").Value - Range("K5").Value

 

[nemmi69]

Could you sum this?

Range("K5:K303").Value

 

[Joe4]

Welcome to the Board!

I think it might be a bit more helpful if you can show up a sample of your data and explain what you are trying to accomplish.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[zreitman]

Copy of Instron Results 3.1.23.xlsx
	C	D	E	F	G	H	I	J	K	L	M
3	Time	Displacement	Force	Preamp	Time	Compressive Stress	Resistance		Time	Compressive Stress	Resistance
4	(s)	(mm)	(kgf)	(V)	min	kgf/cm^2	mΩ cm^2		min	kgf/cm^2	mΩ cm^2
5	0	1.1121	5.1813	-13.2275	0	0.255635658	-134.04946		0	40.010876	-27.6023832
6	0.02	1.1128	5.2331	-13.228	0.000333	0.258191374	-134.05453		0.6751	39.992344	-27.7087918
7	0.04	1.1136	5.3589	-13.2264	0.000667	0.26439811	-134.03832		0.67513333	39.980562	-27.802026
8	0.06	1.1144	5.4936	-13.2258	0.001	0.271043957	-134.03224		0.67546667	40.019623	-27.7959455
9	0.08	1.1152	5.5824	-13.2261	0.001333	0.275425183	-134.03528		0.6755	40.015992	-27.8243211
10	0.1	1.116	5.5341	-13.2269	0.001667	0.273042151	-134.04338		0.69216667	40.010022	-27.831415
11	0.12	1.1168	5.607	-13.2269	0.002	0.276638901	-134.04338		0.70883333	40.002197	-27.8445894
12	0.14	1.1176	5.7296	-13.2246	0.002333	0.282687756	-134.02007		0.7255	39.992764	-27.8547235
13	0.16	1.1184	5.7818	-13.2233	0.002667	0.285263206	-134.0069		0.74216667	39.996252	-27.8466162
14	0.18	1.1193	5.8118	-13.2238	0.003	0.28674335	-134.01197		0.75883333	40.005301	-27.8425626
15	0.2	1.1201	5.8798	-13.2249	0.003333	0.290098343	-134.02311		0.7755	39.99493	-27.8526967
16	0.22	1.1211	5.9182	-13.225	0.003667	0.291992927	-134.02413		0.79216667	39.992418	-27.843576
17	0.24	1.1221	6.0298	-13.223	0.004	0.297499063	-134.00386		0.80883333	39.999622	-27.8273613
18	0.26	1.1231	6.0821	-13.2219	0.004333	0.300079447	-133.99271		0.8255	40.002444	-27.8324284
19	0.28	1.1243	6.0709	-13.2224	0.004667	0.29952686	-133.99778		0.84216667	39.999849	-27.8486431
20	0.3	1.1254	6.1574	-13.2236	0.005	0.303794608	-134.00994		0.85883333	40.006085	-27.8334418
21	0.32	1.1266	6.2056	-13.2244	0.005333	0.306172706	-134.01805		0.8755	40.008971	-27.8172272
22	0.34	1.1278	6.2169	-13.223	0.005667	0.306730227	-134.00386		0.89216667	40.001388	-27.7999991
23	0.36	1.129	6.1891	-13.222	0.006	0.305358627	-133.99373		0.90883333	40.001275	-27.7908784
24	0.38	1.1302	6.2077	-13.2225	0.006333	0.306276316	-133.99879		0.9255	40.004876	-27.7756772
25	0.4	1.1315	6.2059	-13.2235	0.006667	0.306187507	-134.00893		0.94216667	40.003278	-27.7665564
26	0.42	1.133	6.1848	-13.2238	0.007	0.305146473	-134.01197		0.95883333	40.003115	-27.7543955
27	0.44	1.1344	6.2155	-13.2223	0.007333	0.306661153	-133.99677		0.9755	40.003973	-27.7513552
Test

Cell Formulas
Range	Formula
G5:G27	G5	=C5/60
H5:H27	H5	=E5/PI()/2.54^2
I5:I27	I5	=10*F5*PI()*2.54^2/20
L5:M27	L5	=H2034

 

[zreitman]

Copy of Instron Results 3.1.23.xlsx
	C	D	E	F	G	H	I	J	K	L	M
3	Time	Displacement	Force	Preamp	Time	Compressive Stress	Resistance		Time	Compressive Stress	Resistance
4	(s)	(mm)	(kgf)	(V)	min	kgf/cm^2	mΩ cm^2		min	kgf/cm^2	mΩ cm^2
5	0	1.1121	5.1813	-13.2275	0	0.255635658	-134.04946		0	40.010876	-27.6023832
6	0.02	1.1128	5.2331	-13.228	0.000333	0.258191374	-134.05453		0.6751	39.992344	-27.7087918
7	0.04	1.1136	5.3589	-13.2264	0.000667	0.26439811	-134.03832		0.67513333	39.980562	-27.802026
8	0.06	1.1144	5.4936	-13.2258	0.001	0.271043957	-134.03224		0.67546667	40.019623	-27.7959455
9	0.08	1.1152	5.5824	-13.2261	0.001333	0.275425183	-134.03528		0.6755	40.015992	-27.8243211
10	0.1	1.116	5.5341	-13.2269	0.001667	0.273042151	-134.04338		0.69216667	40.010022	-27.831415
11	0.12	1.1168	5.607	-13.2269	0.002	0.276638901	-134.04338		0.70883333	40.002197	-27.8445894
12	0.14	1.1176	5.7296	-13.2246	0.002333	0.282687756	-134.02007		0.7255	39.992764	-27.8547235
13	0.16	1.1184	5.7818	-13.2233	0.002667	0.285263206	-134.0069		0.74216667	39.996252	-27.8466162
14	0.18	1.1193	5.8118	-13.2238	0.003	0.28674335	-134.01197		0.75883333	40.005301	-27.8425626
15	0.2	1.1201	5.8798	-13.2249	0.003333	0.290098343	-134.02311		0.7755	39.99493	-27.8526967
16	0.22	1.1211	5.9182	-13.225	0.003667	0.291992927	-134.02413		0.79216667	39.992418	-27.843576
17	0.24	1.1221	6.0298	-13.223	0.004	0.297499063	-134.00386		0.80883333	39.999622	-27.8273613
18	0.26	1.1231	6.0821	-13.2219	0.004333	0.300079447	-133.99271		0.8255	40.002444	-27.8324284
19	0.28	1.1243	6.0709	-13.2224	0.004667	0.29952686	-133.99778		0.84216667	39.999849	-27.8486431
20	0.3	1.1254	6.1574	-13.2236	0.005	0.303794608	-134.00994		0.85883333	40.006085	-27.8334418
21	0.32	1.1266	6.2056	-13.2244	0.005333	0.306172706	-134.01805		0.8755	40.008971	-27.8172272
22	0.34	1.1278	6.2169	-13.223	0.005667	0.306730227	-134.00386		0.89216667	40.001388	-27.7999991
23	0.36	1.129	6.1891	-13.222	0.006	0.305358627	-133.99373		0.90883333	40.001275	-27.7908784
24	0.38	1.1302	6.2077	-13.2225	0.006333	0.306276316	-133.99879		0.9255	40.004876	-27.7756772
25	0.4	1.1315	6.2059	-13.2235	0.006667	0.306187507	-134.00893		0.94216667	40.003278	-27.7665564
26	0.42	1.133	6.1848	-13.2238	0.007	0.305146473	-134.01197		0.95883333	40.003115	-27.7543955
27	0.44	1.1344	6.2155	-13.2223	0.007333	0.306661153	-133.99677		0.9755	40.003973	-27.7513552
Test

Cell Formulas
Range	Formula
G5:G27	G5	=C5/60
H5:H27	H5	=E5/PI()/2.54^2
I5:I27	I5	=10*F5*PI()*2.54^2/20
L5:M27	L5	=H2034

I have not included the entire file because it is very large, but essentially what I am trying to do it search through column C and find where the time begins to increase by 1 second (about celll 2000) then I want it to offset by 4 columns and copy the corresponding values from G (as well as the next 298) into column K starting in K5. I then want each value in K to have the value of K5 subtracted from it (k5 should equal 0).

 

[zreitman]

Could you sum this?

Range("K5:K303").Value

I need that subtracted from each value in the range though

 

[Legacy 143009]

I need that subtracted from each value in the range though

If so, this can be an option:

Range("K5:K303").Formula = "=K5-" & Range("K5").Value

Edit: Unfortunately not.
Try the following:

Sub Hold()
  Dim c As Range
  Set c = Range("C2000:C2050")
  Dim r1 As Long
  Dim r2 As Long
  Dim x As Long
  Dim i As Long
  Dim tmp As Double

  For x = 2 To c.Cells.Count
    r1 = c.Cells(x, 1).Value
    r2 = c.Cells(x - 1, 1).Value

    If r1 - r2 = 1 Then
      tmp = Range("K5").Value
      For i = 5 to 303
        Range("K" & i).Value = Range("K" & i).Value - tmp
      Next
      Range("G" & x - 1 & ":G" & x + 297).Copy Destination:=Range("K5")
      Exit For
    End If
  Next x
End Sub