+ Reply to Thread
Results 1 to 2 of 2

Copy a Cell if equal to a certain value - Excel VBA

Hybrid View

  1. #1
    Registered User
    Join Date
    02-25-2015
    Location
    Birmingham, England
    MS-Off Ver
    MS Office 2010
    Posts
    29

    Copy a Cell if equal to a certain value - Excel VBA

    Help!

    This may be a simple answer but I'm really struggling as I am quite new to vba.


    Basically, I have an invoice value and a supplier ID on one sheet and I am trying to write a macro to lookup an invoice number relating to the value and supplier ID on another sheet. All the supplier ID's that I am interested in start with the value 3.

    The way I am doing it is I am starting at the top of a list, and doing a while loop to work through the list until the value in the invoice column is nothing.

    fvalue = invoice value
    svalue = supplier ID

    On another sheet I have the invoices listed with supplier ID and invoice amounts and I am trying to search for the value in this sheet. Once it has found the value I want it to check that it is the correct supplier ID. If it is then I want it to copy the cell in the same row column C and paste it next to the data in the original sheet.

    Here is the code.

    Sub FuelINV()
        Sheets("GL").Select
        Range("K2").Select
        
        While Not (ActiveCell(1, 2).Value = "")
            If Left(ActiveCell(1, 3), 1) = 3 Then
                fvalue = Application.Round((-ActiveCell(1, 2).Value * 1.2), 2)
                svalue = ActiveCell(1, 3).Value
                Sheets("BIFF").Select
                Cells.Select
                
                Selection.Find(What:=fvalue, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
                :=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
                False, SearchFormat:=False).Activate
                
                If ActiveSheets(ActiveCell.Row, 3) = svalue Then
                ActiveCell(1, 2).Copy (Sheets("GL").Range("Q3"))
                
                
                
                End If
                
                
                Sheets("GL").Select
            Else
                ActiveCell(2, 1).Select
            End If
            
            
        Wend
    End Sub

    This is the part that isnt working:


    If ActiveSheets(ActiveCell.Row, 3) = svalue Then
                ActiveCell(1, 2).Copy (Sheets("GL").Range("Q3"))

    I am using Range at the minute because I can't work out how to paste it back to the respective row.

    Please Help!

    Thanks!

  2. #2
    Forum Expert NeedForExcel's Avatar
    Join Date
    03-16-2013
    Location
    Pune, India
    MS-Off Ver
    Excel 2016:2019, MS 365
    Posts
    3,879

    Re: Copy a Cell if equal to a certain value - Excel VBA

    I haven't studied your code as I am a bit in a hurry.

    However, Try this. It should work -

    If ActiveSheet.Cells(ActiveCell.Row, 3).Value = svalue Then
        Cells(1, 2).Copy Sheets("GL").Range("Q3")
    You can also do it like this -

    If ActiveSheet.Cells(ActiveCell.Row, 3).Value2 = svalue Then
        Sheets("Connection").Range("F20") = Cells(1, 2).Value2
    Last edited by NeedForExcel; 08-06-2015 at 05:05 AM.
    Cheers!
    Deep Dave

+ 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: 6
    Last Post: 10-03-2014, 12:18 PM
  2. If Dates in range equal today then copy cell x
    By Greg420 in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 15
    Last Post: 03-20-2014, 09:47 AM
  3. Want to copy coulumns cells only if another cell in same row is equal to a value.
    By Jhail83 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-13-2014, 03:38 PM
  4. Copy a row that is equal to another cell with a macro
    By halexxx in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-13-2013, 10:38 PM
  5. macro to copy data where the ID is equal to what is copied from another cell
    By blink359 in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 10-19-2010, 07:27 AM
  6. automaticaly copy rows into other tab if cell is equal to
    By reinoutg in forum Excel - New Users/Basics
    Replies: 8
    Last Post: 10-19-2009, 01:40 PM
  7. automatically copy row into other sheet if cell is equal to
    By reinoutg in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 10-19-2009, 01:31 PM

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