.value is this wrong?

[Jonasraf]

is this worng?

For Each Cell In rng
With Cell
.Value = .Offset(, 1) * .Offset(, 2)
End With
Next Cell

 

[iggydarsa]

make sure you declared rng

Sub test()

'--------------------------------
Dim rng As Range
Set rng = Range("A1:A5")
'--------------------------------

For Each Cell In rng
With Cell
.Value = .Offset(, 1) * .Offset(, 2)
End With
Next Cell
End Sub

 

[Scott Huish]

It might help if you told us what problem you are having.

You do need a few things though:
You need a Sub and End Sub Line
Cell and rng need to be dimmed as ranges
rng needs to be set to a range.
You might also want to put in a check to see if the values in .Offset(,1) and .Offset(,2) are actually numbers:

Sub test2()
Dim cell As Range, rng As Range, t As Boolean
Set rng = Range("A1:A10")
For Each cell In rng
    With cell
        t = Application.WorksheetFunction.Count(.Offset(, 1).Resize(, 2)) = 2
        If t Then .Value = .Offset(, 1) * .Offset(, 2)
    End With
Next cell
End Sub

 

[Jonasraf]

Yes I have, in my case it is

Dim rng as Range
Set rng = range("D12:D22,D24")

 

[Scott Huish]

So what problem are you having?

 

[Jonasraf]

This is all the code,

Private Sub CheckBox1_Click()
Dim rng As Range
Dim rng2 As Range
Dim Cell As Range
Set rng = Range("D12:D22,D24")
Set rng2 = Range("F12:F22,F24")

Application.ScreenUpdating = False
Worksheets("Data").Unprotect
Worksheets("WogB").Unprotect
Range("A27:A33,b27,D27,B28:C29,B30:D31").Locked = False
Range("b27,D27,B28:C29,B30:D31,B32:B33").Interior.ColorIndex = 4
'Range("A27:A30").Font = Right
Range("A27").FormulaR1C1 = "Pilots"
Range("A28").FormulaR1C1 = "Front Pax"
Range("A29").FormulaR1C1 = "Center Pax"
Range("A30").FormulaR1C1 = "Aft Pax"
Range("A31").FormulaR1C1 = "Cargo"
Range("A32").FormulaR1C1 = "Fuel"
Range("A33").FormulaR1C1 = "Hook"

Worksheets("Data").Select
'Range("A11:F25").Locked = False
Range("A12").FormulaR1C1 = "Pilot"
Range("A13").FormulaR1C1 = "Co-Pilot"
Range("A14").FormulaR1C1 = "FL-Pax"
Range("A15").FormulaR1C1 = "FR-Pax"
Range("A16").FormulaR1C1 = "CL-Pax"
Range("A17").FormulaR1C1 = "CR-Pac"
Range("A18").FormulaR1C1 = "RL-Pax"
Range("A19").FormulaR1C1 = "RC-Pax"
Range("A20").FormulaR1C1 = "RR-Pax"
Range("A21").FormulaR1C1 = "Cargo"
Range("A22").FormulaR1C1 = "Hook"
Range("A23").FormulaR1C1 = "ZFW"
Range("A24").FormulaR1C1 = "Fuel"
Range("A25").FormulaR1C1 = "TOW"
Range("B11").FormulaR1C1 = "Lbs"
Range("C11").FormulaR1C1 = "Arm"
Range("D11").FormulaR1C1 = "Mom"
Range("E11").FormulaR1C1 = "Arm"
Range("F11").FormulaR1C1 = "Mom"
Range("B23").FormulaR1C1 = "=sum(b12:b21)"
Range("B25").FormulaR1C1 = "=B23+B24"
Range("C12:C13").Value = 168.2
Range("C14:C15").Value = 201
Range("C16:C17").Value = 229.2
Range("C18:C20").Value = 257
Range("C21").Value = 324
Range("C24").Value = 263.3
Range("E13:E14,E16,E18").Value = -14
Range("E12,E20").Value = 16
Range("E15,E17.E19,E21,E22,E24").Value = 0

For Each Cell In rng
With Cell
.Value = .Offset(, 1) * .Offset(, 2)
End With
Next Cell

For Each Cell In rng2
With Cell
.Value = .Offset(, 1) * .Offset(, 4)
End With
Next Cell

Range("A22").Font.Bold = True


End Sub
------------------------------------

Private Sub Worksheet_Change(ByVal target As Range)
If CheckBox1.Value = True Then
Application.ScreenUpdating = False
Worksheets("Data").Unprotect
Worksheets("Data").Range("B12").Value = Range("D27").Value
Worksheets("Data").Range("B13").Value = Range("B27").Value
Worksheets("Data").Range("B14").Value = Range("B28").Value
Worksheets("Data").Range("B15").Value = Range("C28").Value
Worksheets("Data").Range("B16").Value = Range("B29").Value
Worksheets("Data").Range("B17").Value = Range("C29").Value
Worksheets("Data").Range("B18").Value = Range("B30").Value
Worksheets("Data").Range("B19").Value = Range("C30").Value
Worksheets("Data").Range("B20").Value = Range("D30").Value
Worksheets("Data").Range("B21").Value = Range("B31").Value
Worksheets("Data").Range("B22").Value = Range("D32").Value
Worksheets("Data").Range("B24").Value = Range("B32").Value
Worksheets("Data").Protect
Application.ScreenUpdating = True
End If

End Sub

 

[Jonasraf]

go

 

[Scott Huish]

OK, but what problem are you having?

As an aside, I wouldn't use FormulaR1C1 when you're not using R1C1 format, and the text values don't need it at all.

 

[Jonasraf]

The problem is that

.value = .offset(,1) * .offset(,2)

is Type mismatch

The reason for formulaR1C1 is because this is the only way I know of to type text into cells. if there is better way please tell me.

 

[Scott Huish]

I suspect that 1 or more of the values in the Offsets is not a number, thus my suggestion to check for that.

But as far as the FormulaR1C1:

For your text:
Range("A33") = "Hook"

For your formulas:
Range("B25").Formula = "=B23+B24"