Help with Run-time error: "Type Mismatch"
 

Hallo,
I have made the next macro, but when I want to run it I get an error
message: "Run-time error 13: Type mimatch". Can anybody please tell me what
I'm doing wrong.

Sub Abs_Bkg()

Dim rng As Range
Dim lRownum As Long
Dim i As Long
Dim j As Long
Dim exSh As Worksheet
Dim wks As Worksheet
Set exSh = Worksheets("raw data from spad it")
Set wks = Worksheets("Calculated Data")

With exSh.UsedRange
lRownum = .Cells(.Rows.Count, 1).Row
End With
With wks
For j = 1 To 4
Set rng = Range("O2:O" & lRownum)
For i = 2 To lRownum
wks.Cells(i, rng.Offset(V, j)).Value = exSh.Cells(i, rng.Offset(O,
j)).Value - _
wks.Cells(rng.Offset("AL4", j)).Value
Next i
j = j + 1
Next
End With

End Sub

What this macro has to do is to substract AL4 (worksheet "wks") from O2
(worksheet "exSh") and put the solution in V2 ("wks"). And then go to the
next row but keep "AL4" absolute. Do the same calculation till the lastrow.
Then go to the next column, now is AL4 variable. So; P2 - AM4 = W2. When
going to the next row AM4 stays AM4.

Thanks,
-Metin-

Help with Run-time error: "Type Mismatch"
 

"Metin" wrote in message
...
Hallo,
I have made the next macro, but when I want to run it I get an error
message: "Run-time error 13: Type mimatch". Can anybody please tell me
what
I'm doing wrong.

Sub Abs_Bkg()

Dim rng As Range
Dim lRownum As Long
Dim i As Long
Dim j As Long
Dim exSh As Worksheet
Dim wks As Worksheet
Set exSh = Worksheets("raw data from spad it")
Set wks = Worksheets("Calculated Data")

With exSh.UsedRange
lRownum = .Cells(.Rows.Count, 1).Row
End With
With wks
For j = 1 To 4
Set rng = Range("O2:O" & lRownum)
For i = 2 To lRownum
wks.Cells(i, rng.Offset(V, j)).Value = exSh.Cells(i, rng.Offset(O,
j)).Value - _
wks.Cells(rng.Offset("AL4",
j)).Value
Next i
j = j + 1
Next
End With

End Sub

What this macro has to do is to substract AL4 (worksheet "wks") from O2
(worksheet "exSh") and put the solution in V2 ("wks"). And then go to the
next row but keep "AL4" absolute. Do the same calculation till the
lastrow.
Then go to the next column, now is AL4 variable. So; P2 - AM4 = W2. When
going to the next row AM4 stays AM4.

Thanks,
-Metin-

I think your macro attempts to subtract a number from text or similar. You
don't check if "wks.Cells(rng.Offset("AL4", j)).Value"
or "exSh.Cells(i, rng.Offset(O, j)).Value" is really a numeric value. Check
how many loops it does before there's an error.

/Fredrik

/Fredrik

Help with Run-time error: "Type Mismatch"
 

Metin,

Offset requires a number, not a string:

Offset("AL4", j)).Value won't work.

And there is no need to loop through. You can put formulas into all the
needed cells with one line: you can also convert them to values if you no
longer want formulas, also with a single line. See the example below.

HTH,
Bernie
MS Excel MVP

Sub Abs_Bkg2()

Dim rng As Range 'not needed
Dim lRownum As Long
Dim i As Long 'not needed
Dim j As Long 'not needed
Dim exSh As Worksheet 'not really needed, but OK
Dim wks As Worksheet 'not really needed, but OK
Set exSh = Worksheets("raw data from spad it")
Set wks = Worksheets("Calculated Data")

lRownum = exSh.Range("A1").SpecialCells(xlCellTypeLastCell). Row

With wks.Range("V2:Y" & lRownum)
.FormulaR1C1 = _
"='raw data from spad it'!RC[-7]-'Calculated Data'!R4C[16]"
'This next line is optional
' Use it to remove the formulas but leave values
.Value = .Value
End With
End Sub




"Metin" wrote in message
...
Hallo,
I have made the next macro, but when I want to run it I get an error
message: "Run-time error 13: Type mimatch". Can anybody please tell me
what
I'm doing wrong.

Sub Abs_Bkg()

Dim rng As Range
Dim lRownum As Long
Dim i As Long
Dim j As Long
Dim exSh As Worksheet
Dim wks As Worksheet
Set exSh = Worksheets("raw data from spad it")
Set wks = Worksheets("Calculated Data")

With exSh.UsedRange
lRownum = .Cells(.Rows.Count, 1).Row
End With
With wks
For j = 1 To 4
Set rng = Range("O2:O" & lRownum)
For i = 2 To lRownum
wks.Cells(i, rng.Offset(V, j)).Value = exSh.Cells(i, rng.Offset(O,
j)).Value - _
wks.Cells(rng.Offset("AL4",
j)).Value
Next i
j = j + 1
Next
End With

End Sub

What this macro has to do is to substract AL4 (worksheet "wks") from O2
(worksheet "exSh") and put the solution in V2 ("wks"). And then go to the
next row but keep "AL4" absolute. Do the same calculation till the
lastrow.
Then go to the next column, now is AL4 variable. So; P2 - AM4 = W2. When
going to the next row AM4 stays AM4.

Thanks,
-Metin-