+ Reply to Thread
Results 1 to 9 of 9

Code not pasting work in right cell

Hybrid View

comp23 Code not pasting work in... 07-16-2012, 07:02 AM
romperstomper Re: Code not pasting work in... 07-16-2012, 07:10 AM
comp23 Re: Code not pasting work in... 07-16-2012, 07:26 AM
romperstomper Re: Code not pasting work in... 07-16-2012, 07:27 AM
comp23 Re: Code not pasting work in... 07-16-2012, 08:29 AM
romperstomper Re: Code not pasting work in... 07-16-2012, 08:37 AM
comp23 Re: Code not pasting work in... 07-16-2012, 08:57 AM
romperstomper Re: Code not pasting work in... 07-16-2012, 09:00 AM
comp23 Re: Code not pasting work in... 07-16-2012, 09:22 AM
  1. #1
    Registered User
    Join Date
    07-05-2012
    Location
    london
    MS-Off Ver
    Excel 2007
    Posts
    51

    Code not pasting work in right cell

    So I have this code below that seperately works perfectly fine together however theres a slight problem that I need help fixing.

    Code Below:


    Sub mickeyb()
    
    Dim EndOf1 As Integer
    Dim EndOf2 As Integer
    
    Sheets("dataload").Select
    
    EndOf1 = Sheets("dataload").UsedRange.Rows.Count
    EndOf2 = Sheets("rawdata").UsedRange.Rows.Count + 1
    
    Sheets("dataload").Select
    Range("A2:D" & EndOf1).Select
    Selection.Copy
    
    Sheets("rawdata").Activate
    If Range("A2") = "" Then
        Range("A2").Select
        Else: Range("A" & EndOf2).Select
    End If
    ActiveSheet.Paste
    
    
    Dim dbTemp As Integer
    
    Dim s1 As Integer
    Dim s2 As Integer
    
    Dim nRow As Long
    
    
    Dim strTemp1 As String
    Dim strTemp2 As String
    Dim strTemp3 As String
    Dim strTemp4 As String
    
    Sheets("dataload").Select
    Range("A1").Select
    
    Sheets("datamanip").Select
    Range("C1").Select
    sRowPos = 2
    
    A120_Loop:
    
    If Cells(sRowPos, 3) = "" Then
        GoTo A120_Exit
    End If
    
    Cells(sRowPos, 3).Select
    sRowPos = sRowPos + 1
    GoTo A120_Loop
    
    A120_Exit:
    
    Sheets("dataload").Select
    Range("A1").Select
    s1 = 2
    
    A140_Loop:
    
    Sheets("dataload").Select
    If Cells(s1, 1) = "" Then
        GoTo A140_exit
    End If
    
    Cells(s1, 1).Select
    
    strTemp1 = Cells(s1, 1)
    strTemp2 = Cells(s1, 2)
    strTemp3 = Cells(s1, 3)
    strTemp4 = Cells(s1, 4)
    
    Cells(s1, 1) = ""
    Cells(s1, 2) = ""
    Cells(s1, 3) = ""
    Cells(s1, 4) = ""
    
    Sheets("datamanip").Select
    
    Cells(sRowPos, 3).Select
    
    Cells(sRowPos, 3) = strTemp1
    Cells(sRowPos, 4) = strTemp2
    Cells(sRowPos, 5) = strTemp3
    Cells(sRowPos, 6) = strTemp4
    
    s1 = s1 + 1
    sRowPos = sRowPos + 1
    GoTo A140_Loop
    
    A140_exit:
    
    Sheets("datamanip").Select
    
        For nRow = 2 To Cells(Rows.Count, "C").End(xlUp).Row
            If Not IsEmpty(Cells(nRow, "C")) _
            And Not IsEmpty(Cells(nRow, "D")) _
            And Not IsEmpty(Cells(nRow, "E")) _
            And Not IsEmpty(Cells(nRow, "F")) Then
                Cells(nRow, "A") = Date
            End If
        Next nRow
    
    End Sub
    The problem is this part of the code instead of the code

    Dim EndOf1 As Integer
    Dim EndOf2 As Integer
    
    Sheets("dataload").Select
    
    EndOf1 = Sheets("dataload").UsedRange.Rows.Count
    EndOf2 = Sheets("rawdata").UsedRange.Rows.Count + 1
    
    Sheets("dataload").Select
    Range("A2:D" & EndOf1).Select
    Selection.Copy
    
    Sheets("rawdata").Activate
    If Range("A2") = "" Then
        Range("A2").Select
        Else: Range("A" & EndOf2).Select
    End If
    ActiveSheet.Paste
    Instead of the code pasting the data directly under the old data in the next empty cell for some reason it is pasting the data in row 19 missing a whole load of empty cells. I can't really see whats wrong with the code, because in its own macro it works perfect. so please have a look guys

  2. #2
    Forum Expert romperstomper's Avatar
    Join Date
    08-13-2008
    Location
    England
    MS-Off Ver
    365, varying versions/builds
    Posts
    21,979

    Re: Code not pasting work in right cell

    Usedrange is unreliable. I suggest you use:
    with Sheets("rawdata")
    EndOf2 = .cells(.Rows.Count, "A").End(xlup).Row + 1
    End With
    instead of
    EndOf2 = Sheets("rawdata").UsedRange.Rows.Count + 1
    Everyone who confuses correlation and causation ends up dead.

  3. #3
    Registered User
    Join Date
    07-05-2012
    Location
    london
    MS-Off Ver
    Excel 2007
    Posts
    51

    Re: Code not pasting work in right cell

    cheers romperstomper i'm relatively new to vba so is there any chance you can show me what needs changing in that code. it would be really appreciate thanks again for the reply

  4. #4
    Forum Expert romperstomper's Avatar
    Join Date
    08-13-2008
    Location
    England
    MS-Off Ver
    365, varying versions/builds
    Posts
    21,979

    Re: Code not pasting work in right cell

    I thought I just did?

  5. #5
    Registered User
    Join Date
    07-05-2012
    Location
    london
    MS-Off Ver
    Excel 2007
    Posts
    51

    Re: Code not pasting work in right cell

    even though romperstomper seems to have solved the problem, i'm so new to vba that i still haven't gotta clue whats going on. so i'm gonna post again

    this code below is selecting to much data in the data load sheet it should only be selecting cells that are in a2:d that have data in them. then it should copy them and paste them in the rawdata sheet. currently its copying to much so when I paste data a second time the data is not in the next empty cell

    Dim EndOf1 As Integer
    Dim Endof2 As Integer
    
    EndOf1 = Sheets("dataload").UsedRange.Rows.Count
    Endof2 = Sheets("rawdata").UsedRange.Rows.Count + 1
    
    Sheets("dataload").Select
    Range("a2:d" & EndOf1).Select
    Selection.Copy
    
    Sheets("rawdata").Activate
    If Range("a2") = "" Then
        Range("a2").Select
        Else: Range("A" & Endof2).Select
    End If
    ActiveSheet.Paste

  6. #6
    Forum Expert romperstomper's Avatar
    Join Date
    08-13-2008
    Location
    England
    MS-Off Ver
    365, varying versions/builds
    Posts
    21,979

    Re: Code not pasting work in right cell

    Replace:
    EndOf2 = Sheets("rawdata").UsedRange.Rows.Count + 1
    with
    with Sheets("rawdata")
    EndOf2 = .cells(.Rows.Count, "A").End(xlup).Row + 1
    End With
    is what I was saying. Usedrange is unreliable for working out where the next blank cell is.

  7. #7
    Registered User
    Join Date
    07-05-2012
    Location
    london
    MS-Off Ver
    Excel 2007
    Posts
    51

    Re: Code not pasting work in right cell

    cheers romperstomper I was just being an idiot for a second.

    It does work really well so i have no problems but out of interest why is the code still copying more cells than the data is actually in, its still copying a load of blank cells.

  8. #8
    Forum Expert romperstomper's Avatar
    Join Date
    08-13-2008
    Location
    England
    MS-Off Ver
    365, varying versions/builds
    Posts
    21,979

    Re: Code not pasting work in right cell

    Same reason - you are using Usedrange to work out how many rows to copy. If you use similar code to calculate Endof1 the problem should be removed.

  9. #9
    Registered User
    Join Date
    07-05-2012
    Location
    london
    MS-Off Ver
    Excel 2007
    Posts
    51

    Re: Code not pasting work in right cell

    thanks alot romper stomper you've been great help thanks

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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