Excel Hints

Results 1 to 6 of 6

Thread: Solved: VBA to assign positions for outputs

  1. #1

    Solved: VBA to assign positions for outputs

    Hello all, I have a code to analyze my data according to a certain formular. The outputs will be added to Sheets1/Column A/last row. My problem is the outputs are only added to range A1. Can anybody suggest me a solution for this problem? Any help would be appreciated.

    [VBA]Sub Part03()

    Application.ScreenUpdating = False
    Worksheets("Precursor_ions").Activate
    Dim finalrow As Long, _
    i As Long
    Dim lngPasteRow As Long

    finalrow = Cells(Rows.Count, 1).End(xlUp).Row
    For i = 1 To finalrow
    If Not Cells(i, 3) = "" Then
    If ((Cells(i, 3) * Cells(i, 1)) - (Cells(i, 3) * 1.007825)) > 0 Then

    On Error Resume Next
    lngPasteRow = Sheets("sheet1").Range("A").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1

    On Error GoTo 0

    If lngPasteRow = 0 Then
    lngPasteRow = 1
    End If

    Sheets("sheet1").Range("A" & lngPasteRow) = ((Cells(i, 3) * Cells(i, 1)) - (Cells(i, 3) * 1.007825))
    End If
    End If
    Next i

    End Sub[/VBA]

  2. #2
    Distinguished Lord of VBAX VBAX Grand Master xld's Avatar
    Joined
    Apr 2005
    Posts
    24,101
    Location
    Maybe

    [vba]
    Sub Part03()

    Application.ScreenUpdating = False
    Worksheets("Precursor_ions").Activate
    Dim finalrow As Long, _
    i As Long
    Dim lngPasteRow As Long

    finalrow = Cells(Rows.Count, 1).End(xlUp).Row
    For i = 1 To finalrow
    If Not Cells(i, 3) = "" Then
    If ((Cells(i, 3) * Cells(i, 1)) - (Cells(i, 3) * 1.007825)) > 0 Then

    On Error Resume Next
    lngPasteRow = Sheets("sheet1").Range("A").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1

    On Error Goto 0

    lngPasteRow = lngPasteRow + 1
    End If

    Sheets("sheet1").Range("A" & lngPasteRow) = ((Cells(i, 3) * Cells(i, 1)) - (Cells(i, 3) * 1.007825))
    End If
    End If
    Next i
    End Sub [/vba]
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  3. #3

  4. #4
    Could you please tell me one more thing? Can the output start from A4?

  5. #5
    Distinguished Lord of VBAX VBAX Grand Master xld's Avatar
    Joined
    Apr 2005
    Posts
    24,101
    Location
    [vba]Sub Part03()

    Application.ScreenUpdating = False
    Worksheets("Precursor_ions").Activate
    Dim finalrow As Long, _
    i As Long
    Dim lngPasteRow As Long

    finalrow = Cells(Rows.Count, 1).End(xlUp).Row
    lngPasteRow = 3
    For i = 1 To finalrow
    If Not Cells(i, 3) = "" Then
    If ((Cells(i, 3) * Cells(i, 1)) - (Cells(i, 3) * 1.007825)) > 0 Then

    On Error Resume Next
    lngPasteRow = Sheets("sheet1").Range("A").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1

    On Error Goto 0

    lngPasteRow = lngPasteRow + 1
    End If

    Sheets("sheet1").Range("A" & lngPasteRow) = ((Cells(i, 3) * Cells(i, 1)) - (Cells(i, 3) * 1.007825))
    End If
    Next i
    End Sub [/vba]
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  6. #6

Posting Permissions

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