+ Reply to Thread
Results 1 to 5 of 5

increment row number using variable

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    08-29-2012
    Location
    Hyderbad
    MS-Off Ver
    Excel 2003
    Posts
    123

    increment row number using variable

    Can any please.......ASAP..
    increment row number using variable
    If the data is available in the rst, the result should add to the existing data and increase rows by pasting the result


    Code works :
    sheet lst values (column A) mapped with sheet checks value (column I) - IF MATCHED, range should be copied from A:O and the result should pasted in rst sheet name (as same copied without any changes).

    If data available : find the last row and add to the existing data.

    Below code working fine : up to pastespecial....

    my request : increment row number using variable
    If the data is available in the rst, the result should add to the existing data and increase rows by pasting the result


    Sub test3()
        Dim x As Integer, y
          Dim s As Integer, j
            x = Sheets("lst").Range("a" & Rows.Count).End(xlUp).Row
            s = Sheets("checks").Range("I" & Rows.Count).End(xlUp).Row
            a = 1
                For y = 1 To x
                  For j = 1 To s
                    If Sheets("lst").Cells(y, 1) = Sheets("checks").Cells(j, 9) Then
                    a = a + 1
                        Sheets("checks").Range("A" & j & ":O" & j).Copy
                        Sheets("rst").Range("A1").PasteSpecial
                    End If
                  Next j
                Next y
    End Sub
    test workbook attached. Please find the attachment.

    Thanks in advance...
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: increment row number using variable

    Use this line

    Sheets("rst").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlValues
    Instead of

    Sheets("rst").Range("A1").PasteSpecial

  3. #3
    Forum Contributor
    Join Date
    08-29-2012
    Location
    Hyderbad
    MS-Off Ver
    Excel 2003
    Posts
    123

    Re: increment row number using variable

    Hi AB33,

    suggested code line working fine,

    but on the first run of macro - result is pasted from Row-A2 when RowA1 is blank row.
    And subsequently code works fine by find the last row and offsetting the row.

    ------
    Additionally
    Can u help me how the code works in case of 'FIND METHOD' code posted in #1
    (Range.Find Method - using the loops).
    Last edited by jagadeesh.rt; 03-15-2015 at 03:04 PM.

  4. #4
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: increment row number using variable

    Sub test3()
        Dim x As Integer, y
          Dim s As Integer, j
            x = Sheets("lst").Range("a" & Rows.Count).End(xlUp).Row
            s = Sheets("checks").Range("I" & Rows.Count).End(xlUp).Row
            a = 1
                For y = 1 To x
                  For j = 1 To s
                    If Sheets("lst").Cells(y, 1) = Sheets("checks").Cells(j, 9) Then
                      'Sheets("checks").Cells(j, 9).Interior.ColorIndex = 3
                        Sheets("checks").Range("A" & j & ":O" & j).Copy
                        Sheets("rst").Range("A" & a).PasteSpecial xlValues
                        a = a + 1
                    End If
                  Next j
                Next y
    End Sub

  5. #5
    Forum Contributor
    Join Date
    08-29-2012
    Location
    Hyderbad
    MS-Off Ver
    Excel 2003
    Posts
    123

    Re: increment row number using variable

    Hi AB33

    Still not working for me. I have attached sample workbook. Please find the attachment and do
    needful.

    Thank You.
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Calculate average of variable number of cells variable number of times
    By WeirnetherlandsBart in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-06-2014, 10:11 AM
  2. Search worksheet for certain text + number combo and then increment the number
    By hal9000 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-09-2013, 06:09 PM
  3. Increment Time variable reference cell from known range
    By Mudhafar.M in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 09-20-2013, 06:38 PM
  4. Increment Variable from User Form Selection
    By jonhfl in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 04-01-2010, 11:29 AM
  5. Replies: 3
    Last Post: 03-15-2010, 02:33 PM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1