Results 1 to 8 of 8

Loop not copying value into desired cell

Threaded View

  1. #1
    Registered User
    Join Date
    03-05-2020
    Location
    Stoke on Trent, England
    MS-Off Ver
    Excel 2016
    Posts
    59

    Question Loop not copying value into desired cell

    Hello All,

    I am trying to get data from a table into various cells in another worksheet. I am doing this by selecting a facility from a drop down list in V16 of the sheet I want the data to go to.
    I then click a button with the following code in it:

    Private Sub CommandButton9_Click()
    
    Dim Datasheet As Worksheet
    Dim EQUFACsheet As Worksheet
    Dim Facility As String
    Dim finalrow As Integer
    Dim i As Integer
    
    Set Datasheet = Sheets("Data")
    Set EQUFACsheet = Sheets("EQU FAC input sheet")
    Facility = EQUFACsheet.Range("V16").Value
    
    EQUFACsheet.Range("B8").MergeArea.ClearContents
    EQUFACsheet.Range("H8").MergeArea.ClearContents
    
    Datasheet.Select
    finalrow = Datasheet.Range("B200").End(xlUp).row
    
    For i = 6 To finalrow
        If Cells(i, 2) = Facility Then
        Range(Cells(i, 2)).Copy
        EQUFACsheet.Select
        Range("B8").PasteSpecial
        Datasheet.Select
        End If
        
    Next i
    EQUFACsheet.Select
    Range("V16").Select
    End Sub
    It doesn't throw up any errors but when I look at the code execution I can see that it runs through each row in the table but doesn't match the facility, it simply stops there?

    The destination for the lookup info is B8 in the EQUFACsheet

    Any ideas?
    Last edited by Richard Buttrey; 03-13-2020 at 11:18 AM. Reason: Missing code tags.

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Vba to insert picture into desired cell with cell size
    By meer_ali in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-21-2018, 03:52 AM
  2. [SOLVED] HELP copying multiple ranges per row based on cell criteria using a Loop
    By Jimbo42 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-28-2016, 01:23 PM
  3. [SOLVED] Index Small Function Pulling Cell Below Desired Cell
    By sskgintl in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 10-03-2015, 04:26 PM
  4. Replies: 8
    Last Post: 07-09-2014, 10:42 PM
  5. [SOLVED] Count amount of desired numbers in a single cell and return that value in another cell
    By jmallory76 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-20-2013, 10:09 AM
  6. [SOLVED] How can I move from one desired cell to another in Excel using ENT
    By pibedoro in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 08-15-2006, 09:45 PM
  7. For Next Loop...Copying from a cell one column to another...
    By bourbon84 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-29-2006, 10:15 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