Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel macros
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel macros
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. -----Original Message----- 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! . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2007 macros - how to merge 5 macros together into one | Excel Discussion (Misc queries) | |||
Training: More on how to use macros in Excel: Recording Macros | Excel Worksheet Functions | |||
Macros not appearing in the Tools Macro Macros list | Excel Programming | |||
Suppress the Disable Macros / Enable Macros Dialog | Excel Programming | |||
List the Macros that can be executed from Tools-Macros | Excel Programming |