+ Reply to Thread
Results 1 to 6 of 6

VBA - Match Cell Value and Paste Specific Cells

Hybrid View

Simon.xlsx VBA - Match Cell Value and... 09-19-2014, 08:54 AM
:) Sixthsense :) Re: VBA - Match Cell Value... 09-19-2014, 09:46 AM
Simon.xlsx Re: VBA - Match Cell Value... 09-22-2014, 07:33 AM
:) Sixthsense :) Re: VBA - Match Cell Value... 09-22-2014, 07:38 AM
Simon.xlsx Re: VBA - Match Cell Value... 09-25-2014, 04:18 AM
Simon.xlsx Re: VBA - Match Cell Value... 09-25-2014, 10:42 AM
  1. #1
    Forum Contributor
    Join Date
    02-20-2014
    Location
    Ireland
    MS-Off Ver
    Excel 2013
    Posts
    108

    VBA - Match Cell Value and Paste Specific Cells

    Hi All,

    I am new enough to VBA,

    I have a bit of working code that looks at 2 cell values on sheet 1, and matches them on sheet 2, then copies cell values from sheet 1 and pastes them to that location in sheet 2

    Sub matchEm()
    
    Sheets("Timesheet Log").Unprotect "ops9999"
    
    Application.Screenupdating = False
    
        Dim vRow, vColumn
        Dim sht1 As Excel.Worksheet
        Dim sht2 As Excel.Worksheet
            
        Set sht1 = Sheets("Sheet1")
        Set sht2 = Sheets("Timesheet Log")
        vRow = Application.Match(sht1.Range("A2").Value, sht2.Range("B:B"), 0)
        If Not IsError(vRow) Then
            vColumn = Application.Match(sht1.Range("E2").Value2, sht2.Range("2:2"), 0)
            If Not IsError(vColumn) Then sht2.Cells(vRow, vColumn).Resize(5).Value = Application.Transpose(sht1.Range("G2:J2").Value)
    End If
    
    Sheets("Timesheet Log").Protect "ops9999", True, True
    Application.Screenupdating = True
    
    End Sub
    I want this code to run through the first 100 lines performing the same task for each line, while I could copy and paste this code replacing the necessary cells, this sounds messy and slow.

    If anybody knows any tips or tricks to do this faster and simpler, that would be great

    Thanks in advance for any replies

    Simon

  2. #2
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,788

    Re: VBA - Match Cell Value and Paste Specific Cells

    Try this untested code

    Sub matchEm()
    Dim rRow As Range, rCol As Range
    Dim Sht1 As Excel.Worksheet, Sht2 As Excel.Worksheet, sPassword As String
    
    sPassword = "ops9999"
    
    Set Sht1 = Sheets("Sheet1")
    Set Sht2 = Sheets("Timesheet Log")
    
    Application.ScreenUpdating = False
    
    With Sht2
        .Unprotect sPassword
        Set rRow = .Range("B:B").Find(Sht1.Range("A2").Value, , , xlWhole)
        If Not rRow Is Nothing Then
            Set rCol = .Range("2:2").Find(Sht1.Range("E2").Value2, , , xlWhole)
            If Not rCol Is Nothing Then
                Sht1.Range("G2:J2").Copy
                .Cells(rRow.Row, rCol.Column).PasteSpecial xlPasteValues, , , True
            End If
        End If
        .Protect sPassword, True, True
    End With
    
    Application.ScreenUpdating = True
    
    End Sub


    If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
    Mark your thread as Solved


    If the suggestion helps you, then Click *below to Add Reputation

  3. #3
    Forum Contributor
    Join Date
    02-20-2014
    Location
    Ireland
    MS-Off Ver
    Excel 2013
    Posts
    108

    Re: VBA - Match Cell Value and Paste Specific Cells

    Hi, Thanks for you reply, unfortunately that code doesn't seem to do the trick. It runs without error but I cannot see any changes the code has done

  4. #4
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,788

    Re: VBA - Match Cell Value and Paste Specific Cells

    Run it in break more by pressing F8 which runs the code line by line and see what it's doing and where it fails

  5. #5
    Forum Contributor
    Join Date
    02-20-2014
    Location
    Ireland
    MS-Off Ver
    Excel 2013
    Posts
    108

    Re: VBA - Match Cell Value and Paste Specific Cells

    Hi sorry about the delay in the reply, Im not too sure how the break thing works, Im hitting F8 but it just cycles through every line highligthing each one yellow

  6. #6
    Forum Contributor
    Join Date
    02-20-2014
    Location
    Ireland
    MS-Off Ver
    Excel 2013
    Posts
    108

    Re: VBA - Match Cell Value and Paste Specific Cells

    Ok so after much messing around I have ended up with a final working code, I will post it here for anybody have a similar issue

    Sub match()
    
    Sheets("Timesheet Log").Unprotect "ops9999"
    
    Application.Screenupdating = False
    
    Dim vRow, vColumn
    Dim sht1 As Excel.Worksheet: Set sht1 = Sheets("Sheet1")
    Dim sht2 As Excel.Worksheet: Set sht2 = Sheets("Timesheet Log")
    
    'Here is where the loop starts
    For i = 1 To 200
          vRow = Application.match(sht1.Range("A" & i).Value, sht2.Range("B:B"), 0)
          If Not IsError(vRow) Then
                   vColumn = Application.match(sht1.Range("E2").Value2, sht2.Range("2:2"), 0)
         
                   sht2.Cells(vRow, vColumn).Resize(5).Value = Application.Transpose(sht1.Range("G" & i & ":K" & i).Value)
          End If
    
    Next
    
    Sheets("Timesheet Log").Protect "ops9999", True, True
    Application.Screenupdating = True
    
    End Sub
    Thanks for the replies

+ 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. Replies: 3
    Last Post: 09-20-2013, 04:24 PM
  2. [SOLVED] Match Cells across 2 Work sheets then copy/paste a cell
    By tripey in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 05-13-2013, 11:45 PM
  3. [SOLVED] Cut specific text from cells and paste in neighbour cell
    By andrewzzz in forum Excel Programming / VBA / Macros
    Replies: 15
    Last Post: 04-10-2013, 02:08 AM
  4. Returning the contents of a specific cell using a MATCH of 2 other cells
    By oneblondebrow in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 04-08-2013, 08:41 AM
  5. [SOLVED] Copy and Paste cells that match another cell
    By nenadmail in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-08-2012, 08:53 AM

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