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