Excel macros

  • Thread starter Thread starter giki822
  • Start date Start date
G

giki822

I'm trying to loop thru a number of input rows from the
SAP worksheet and format it into an Access database layout
in Excel. How do I get a number of input rows but up to a
certain point, let say up to a row called "stop" and
identify these rows in a separate column, let
say "Positive", and continue to loop thru the rest of the
input rows beyond the row "stop" and identify it in the
same column, let say "Negative?

Thanks in advance!
 
You have a worksheet with data extracted from SAP, an
accounting package presumably.
Rows don't have names as such in Excel, they have numbers.
So when you mention a row called "stop", do you mean a
cell in column A that has the value "Stop"?

The following code will find the cell containing "Stop"
then place the word "Positive" in all cells in column B
above this and "Negative" in all cells below.

Copy the followign code into a standard code module:-
Option Explicit
Sub SetValues()
Dim cell As Range
Dim StopRow As Long
Dim LastRow As Long
Set cell = Range("A:A").Find("STOP")

If cell Is Nothing Then
MsgBox "Stop not found!"
Else
StopRow = cell.Row
LastRow = Range("A65000").End(xlUp).Row
If StopRow > 2 Then
With Range(Range("A2"), Cells(StopRow -
1, "A"))
.Offset(0, 1).Value = "Positive"
End With
End If
If LastRow > StopRow + 1 Then
With Range(Cells(StopRow + 1, "A"), Cells
(LastRow, "A"))
.Offset(0, 1).Value = "Negative"
End With
End If
End If
End Sub


Method: The code Finds the first cell in column A with
the word Stop. Assuming row 1 is headers, then in B, from
row 2 to the row ablove stop, the Positive work is
placed. Then in the cells below stop, until the last cell
in column A, the word Negative is placed in column B.
Note that no loop is involved here.

HTH
Patrick Molloy
Microsoft Excel MVP

Code tested OK. Send direct for workbook demo.
 
Back
Top