using cells for less

[montecarlo2012]

Hi,
I am trying to make it shorter

Sub F_minus_E()
    Dim lastRow As Long
    Dim i As Long

    ' Find the last row in column F on Sheet1 (you can change the column as needed)
    lastRow = Sheets("Sheet1").Cells(Sheets("Sheet1").Rows.Count, "F").End(xlUp).Row

    ' Display the results on Sheet2         F2-E2 #### -D2 #####  -C2 @@@@ -B2
    For i = 1 To lastRow
        ' Perform calculations for each row
        Sheets("Sheet2").Range("B2").Offset(i - 1, 0).Value = Sheets("Sheet1").Range("F2").Offset(i - 1, 0).Value - Sheets("Sheet1").Range("E2").Offset(i - 1, 0).Value
        Sheets("Sheet2").Range("C2").Offset(i - 1, 0).Value = Sheets("Sheet1").Range("F2").Offset(i - 1, 0).Value - Sheets("Sheet1").Range("D2").Offset(i - 1, 0).Value
        Sheets("Sheet2").Range("D2").Offset(i - 1, 0).Value = Sheets("Sheet1").Range("F2").Offset(i - 1, 0).Value - Sheets("Sheet1").Range("C2").Offset(i - 1, 0).Value
        Sheets("Sheet2").Range("E2").Offset(i - 1, 0).Value = Sheets("Sheet1").Range("F2").Offset(i - 1, 0).Value - Sheets("Sheet1").Range("B2").Offset(i - 1, 0).Value
    Next i
End Sub

this one is working but still I want to be shorter so this is my approach

Sub montecarlo()
lastRow = Sheets("Sheet1").Cells(Sheets("Sheet1").Rows.Count, "F").End(xlUp).Row

For i = 2 To lastRow
For j = 2 To 5 Step -1
Sheets("Sheet2").Cells(2, i).Offset(i - 1, 0).Value = Sheets("Sheet1").Cells(2, 6).Offset(i - 1, 0).Value - Sheets("Sheet1").Cells(j, 2).Offset(i - 1, 0).Value
Next
Next
End Sub

do not work
thanks .

 

[6StringJazzer]

Change this

For j = 2 To 5 Step -1

to this

For j = 2 To 5

Also this

Sheets("Sheet2").Cells(2, i)

must be changed to this

Sheets("Sheet2").Cells(2, j)

When you use Step -1 it means to go backwards, but you are starting on 2 and you can't go backwards to 5. So it just stops.

 

[bebo021999]

Should be in shorter way:

Sub TEST()
Dim lr&, i&, ce As Range
Sheets("Sheet2").Activate
With Sheets("Sheet1")
    lr = .Cells(Rows.Count, "F").End(xlUp).Row ' last row in column F
    For Each ce In .Range("F2:F" & lr) ' loop each cell in col F
        For i = 1 To 4
             Cells(ce.Row, "A").Offset(0, i).Value = ce - ce.Offset(0, -i)
        Next
    Next
End With
End Sub

With Cells(ce.Row, "A").Offset(0, i).Value: destination cell in col A sheet 2, but same row with F in sheet 1, offset 1,2,3,4 column
ce.Offset(0, -i) : cell in F then offset left 1,2,3,4 column

 

[montecarlo2012]

6StringJazzer
Thank you for your lesson.​

 

[montecarlo2012]

bebo021999

"Big shoutout to Genius bebo021999 Your consistent invaluable help is MVP-worthy. Thanks for yet another fantastic answer. ".​

 

[bebo021999]

"Big shoutout to Genius bebo021999 Your consistent invaluable help is MVP-worthy. Thanks for yet another fantastic answer. ".

I truly appreciate your generous words! It's incredibly rewarding to know that my assistance has been valuable to you. Your feedback motivates me to keep offering help to the best of my ability.
If you ever have any more questions or need guidance in the future, don't hesitate to ask.
Your success and satisfaction are what drive me to continue contributing. Thanks again for your kind recognition and have a wonderful day