.value is this wrong?

Jonasraf

New Member
Joined
Jun 19, 2007
Messages
40
is this worng?

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

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

iggydarsa

Well-known Member
Joined
Jun 28, 2005
Messages
1,647
make sure you declared rng

Code:
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

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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:

Code:
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

New Member
Joined
Jun 19, 2007
Messages
40
Yes I have, in my case it is

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

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

So what problem are you having?
 

Jonasraf

New Member
Joined
Jun 19, 2007
Messages
40
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
 

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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

New Member
Joined
Jun 19, 2007
Messages
40
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

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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"
 

Watch MrExcel Video

Forum statistics

Threads
1,130,169
Messages
5,640,550
Members
417,151
Latest member
ChickenTenderer

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
Top