Thanks:  0
Likes:  0

# Thread: Macro needs to operate on rows with "start" through "end"

1. How do I create a macro that makes calculations starting just after a row (with "Qty" in col A) UP to a row that contains a unique entry called "Grand Total"
(in say Column B)

Calculation are like:
G8 = a8*e8 and
H8 = a8*e8-d8 etc

2. I like to use the Cells array for things like this.

dim i as integer
dim bStart as Boolean

bStart = False

For i = 1 to 100

if cells(i,2).value = "Grand Total" then Exit For

if bStart = True then

Cells(i,7).value = cells(i,1).value * cells(i,5).value
Cells(i,8).value = cells(i,7).value - cells(i,4).value
etc.

End If

if cells(i,1).value = "Qty" then bStart = True

Next

Hope this helps...

K

3. Thanks,

Here is what I created, its errors at the ???

-----
Dim i As Integer
Dim bStart As Boolean

bStart = False

For i = 1 To 100

If Cells(i, 2).Value = "Grand Total" Then Exit For ' define end

If bStart = True Then
If Cells(i, 1).Value <> 0 Then ' insure there is a qty
If Cells(i, = 0 And Cells(i, 9) = 0 Then ' use vendor data from col E - Unit List
Cells(i, 10).Value = Cells(i, 1).Value * Cells(i, 5).Value
ElseIf Cells(i, > 0 Then ' use percentage discount from col H - Discount %
Cells(i, 10).Value = Cells(i, 1).Value * (Cells(i, 5).Value - Cells(i, 5).Value * Cells(i, .Value / 100) ' ?????
ElseIf Cells(i, 9) > 0 Then ' use specified value in col J - Unit selling price
Cells(i, 10).Value = Cells(i, 1).Value * Cells(i, 10).Value
End If

End If
End If

If Cells(i, 1).Value = "Qty." Then bStart = True ' define start
Next
End Sub

4. That funkie character is an 8 (number eight) no idea why the display...

5. Also need to account for a "blank" field and treat it as if a "0" had been entered

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•