Type Mismatch Error

zreitman

New Member
Joined
Mar 16, 2023
Messages
4
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
I am not quite sure that you can subtract something from a range. It must be a single value.
VBA Code:
Range("K5:K303").Value - Range("K5").Value
 
Upvote 0
Could you sum this?
VBA Code:
Range("K5:K303").Value
 
Upvote 0
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.
 
Upvote 0
Copy of Instron Results 3.1.23.xlsx
CDEFGHIJKLM
3TimeDisplacementForcePreampTimeCompressive StressResistance Time Compressive StressResistance
4(s)(mm)(kgf)(V)minkgf/cm^2mΩ cm^2minkgf/cm^2mΩ cm^2
501.11215.1813-13.227500.255635658-134.04946040.010876-27.6023832
60.021.11285.2331-13.2280.0003330.258191374-134.054530.675139.992344-27.7087918
70.041.11365.3589-13.22640.0006670.26439811-134.038320.6751333339.980562-27.802026
80.061.11445.4936-13.22580.0010.271043957-134.032240.6754666740.019623-27.7959455
90.081.11525.5824-13.22610.0013330.275425183-134.035280.675540.015992-27.8243211
100.11.1165.5341-13.22690.0016670.273042151-134.043380.6921666740.010022-27.831415
110.121.11685.607-13.22690.0020.276638901-134.043380.7088333340.002197-27.8445894
120.141.11765.7296-13.22460.0023330.282687756-134.020070.725539.992764-27.8547235
130.161.11845.7818-13.22330.0026670.285263206-134.00690.7421666739.996252-27.8466162
140.181.11935.8118-13.22380.0030.28674335-134.011970.7588333340.005301-27.8425626
150.21.12015.8798-13.22490.0033330.290098343-134.023110.775539.99493-27.8526967
160.221.12115.9182-13.2250.0036670.291992927-134.024130.7921666739.992418-27.843576
170.241.12216.0298-13.2230.0040.297499063-134.003860.8088333339.999622-27.8273613
180.261.12316.0821-13.22190.0043330.300079447-133.992710.825540.002444-27.8324284
190.281.12436.0709-13.22240.0046670.29952686-133.997780.8421666739.999849-27.8486431
200.31.12546.1574-13.22360.0050.303794608-134.009940.8588333340.006085-27.8334418
210.321.12666.2056-13.22440.0053330.306172706-134.018050.875540.008971-27.8172272
220.341.12786.2169-13.2230.0056670.306730227-134.003860.8921666740.001388-27.7999991
230.361.1296.1891-13.2220.0060.305358627-133.993730.9088333340.001275-27.7908784
240.381.13026.2077-13.22250.0063330.306276316-133.998790.925540.004876-27.7756772
250.41.13156.2059-13.22350.0066670.306187507-134.008930.9421666740.003278-27.7665564
260.421.1336.1848-13.22380.0070.305146473-134.011970.9588333340.003115-27.7543955
270.441.13446.2155-13.22230.0073330.306661153-133.996770.975540.003973-27.7513552
Test
Cell Formulas
RangeFormula
G5:G27G5=C5/60
H5:H27H5=E5/PI()/2.54^2
I5:I27I5=10*F5*PI()*2.54^2/20
L5:M27L5=H2034
 
Upvote 0
Copy of Instron Results 3.1.23.xlsx
CDEFGHIJKLM
3TimeDisplacementForcePreampTimeCompressive StressResistance Time Compressive StressResistance
4(s)(mm)(kgf)(V)minkgf/cm^2mΩ cm^2minkgf/cm^2mΩ cm^2
501.11215.1813-13.227500.255635658-134.04946040.010876-27.6023832
60.021.11285.2331-13.2280.0003330.258191374-134.054530.675139.992344-27.7087918
70.041.11365.3589-13.22640.0006670.26439811-134.038320.6751333339.980562-27.802026
80.061.11445.4936-13.22580.0010.271043957-134.032240.6754666740.019623-27.7959455
90.081.11525.5824-13.22610.0013330.275425183-134.035280.675540.015992-27.8243211
100.11.1165.5341-13.22690.0016670.273042151-134.043380.6921666740.010022-27.831415
110.121.11685.607-13.22690.0020.276638901-134.043380.7088333340.002197-27.8445894
120.141.11765.7296-13.22460.0023330.282687756-134.020070.725539.992764-27.8547235
130.161.11845.7818-13.22330.0026670.285263206-134.00690.7421666739.996252-27.8466162
140.181.11935.8118-13.22380.0030.28674335-134.011970.7588333340.005301-27.8425626
150.21.12015.8798-13.22490.0033330.290098343-134.023110.775539.99493-27.8526967
160.221.12115.9182-13.2250.0036670.291992927-134.024130.7921666739.992418-27.843576
170.241.12216.0298-13.2230.0040.297499063-134.003860.8088333339.999622-27.8273613
180.261.12316.0821-13.22190.0043330.300079447-133.992710.825540.002444-27.8324284
190.281.12436.0709-13.22240.0046670.29952686-133.997780.8421666739.999849-27.8486431
200.31.12546.1574-13.22360.0050.303794608-134.009940.8588333340.006085-27.8334418
210.321.12666.2056-13.22440.0053330.306172706-134.018050.875540.008971-27.8172272
220.341.12786.2169-13.2230.0056670.306730227-134.003860.8921666740.001388-27.7999991
230.361.1296.1891-13.2220.0060.305358627-133.993730.9088333340.001275-27.7908784
240.381.13026.2077-13.22250.0063330.306276316-133.998790.925540.004876-27.7756772
250.41.13156.2059-13.22350.0066670.306187507-134.008930.9421666740.003278-27.7665564
260.421.1336.1848-13.22380.0070.305146473-134.011970.9588333340.003115-27.7543955
270.441.13446.2155-13.22230.0073330.306661153-133.996770.975540.003973-27.7513552
Test
Cell Formulas
RangeFormula
G5:G27G5=C5/60
H5:H27H5=E5/PI()/2.54^2
I5:I27I5=10*F5*PI()*2.54^2/20
L5:M27L5=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).
 
Upvote 0
I need that subtracted from each value in the range though
If so, this can be an option:
VBA Code:
Range("K5:K303").Formula = "=K5-" & Range("K5").Value

Edit: Unfortunately not.
Try the following:
VBA Code:
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
 
Last edited by a moderator:
Upvote 1

Forum statistics

Threads
1,224,828
Messages
6,181,206
Members
453,022
Latest member
RobertV1609

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top