Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

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

  1. #1
    Board Regular
    Join Date
    Apr 2002
    Location
    South Bend, IN
    Posts
    991
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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. #2
    Board Regular
    Join Date
    Apr 2002
    Location
    Greenwood, SC
    Posts
    677
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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. #3
    Board Regular
    Join Date
    Apr 2002
    Location
    South Bend, IN
    Posts
    991
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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. #4
    Board Regular
    Join Date
    Apr 2002
    Location
    South Bend, IN
    Posts
    991
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

  5. #5
    Board Regular
    Join Date
    Apr 2002
    Location
    South Bend, IN
    Posts
    991
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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