Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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- |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
"Type mismatch" when I try to fill an Array variable with "+" | Excel Discussion (Misc queries) | |||
multiple file uploading - runtime error'13': type mismatch "While Counter <= UBound(FName)" | Excel Discussion (Misc queries) | |||
"FIND" generates "Type mismatch" error | Excel Programming | |||
Type mismatch: .Cells(i).Value = "=rc[-1]" / vl | Excel Programming | |||
Copying data to another worksheet gives "Type Mismatch" error | Excel Programming |