How to refer to back-to-back rows in code

Hello all,

I am attempting to write a macro that will ask the user to input a number at the beginning and then use that number for calculation later. Before the calculation however, I would like it to loop through Column B (in example) and check for a sign change. For example in row 11, it is negative and the next row is positive, so then it would take the value of Column C row 11 for its calculation. I'm not sure how to refer to row 11 and row 12 when trying to check for sign change. Any thoughts/comments on this so far? Any help is appreciated.

Sub Test()

Dim l As Long
Dim lRow As Long
Dim Weight As Long
Dim New_file

Weight = Application.InputBox(prompt:="Input Weight", Type:=1 + 64)

1Row = Range("B" & Rows.Count).End(x1Up).Row

If 1Row1*<0 and 1Row2*>0 Then
Range("S" & 1).Formula = "=CRow1/Weight"
End If
Next l

New_file = Application.GetSaveAsFilename("Microsoft Excel Workbook (*.xls),*.xls")
Sheets("Name").Copy
ActiveWorkbook.SaveAs Filename:=New_file, FileFormat:=xlNormal
ActiveWindow.Close


End Sub

example.xlsx

Your loop doesn't have a For statement. Is this what you are trying to do?

lRow = Range("B" & Rows.count).End(3)(2).Row
For l = lRow To 2 Step -1
If Range("B" & l) = "-" And Range("B" & l).Offset(1) = "+" Then
Range("S" & l).Formula = "=" & Range("C" & l).Value & "/" & Weight
End If
Next l

I think you want to use the Offset(1) to refer too the next row.

Yess, that is exactly what I'm trying to do. However, I forgot to mention that the positive and negatives are actually positive and negative numbers. That would change the code a tad bit, correct?

Maybe:

Sub JaydenK()

Dim l As Long
Dim lRow As Long
Dim Weight As Long
Dim New_file

Weight = Application.InputBox(prompt:="Input Weight", Type:=1 + 64)

lRow = Range("B" & Rows.count).End(3)(2).Row
For l = lRow To 2 Step -1
If Range("B" & l) < 0 And Range("B" & l).Offset(1) > 0 Then
Range("S" & l).Formula = "=" & Range("C" & l).Value & "/" & Weight
End If
Next l

New_file = Application.GetSaveAsFilename("Microsoft Excel Workbook (*.xls),*.xls")
Sheets("Name").Copy
ActiveWorkbook.SaveAs Filename:=New_file, FileFormat:=xlNormal
ActiveWindow.Close


End Sub

Thank you, that is what I'm looking for. Learned quite a bit from this, thanks again!

You're welcome. Glad to help out, and thanks for the feedback. Please comply with Forum Rule No. 9.

9. Acknowledge the responses you receive, good or bad. If your problem is solved, please say so clearly, and mark your thread as Solved: Click Thread Tools above your first post, select "Mark your thread as Solved". Or click the Edit button on your first post in the thread, Click Go Advanced, select [SOLVED] from the Prefix dropdown, then click Save Changes. If more than two days have elapsed, the Dropdown option or Edit button will not appear -- ask a moderator to mark it.