Microsoft Excel Webinar

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.

    VB:
    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 
    
    
    Formatting tags added by mark007

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

    VB:
    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 
    
    
    Formatting tags added by mark007
    ____________________________________________
    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
    23,555
    Location
    VB:
    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 
    
    
    Formatting tags added by mark007
    ____________________________________________
    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
  •