+ Reply to Thread
Results 1 to 3 of 3

VBS to Find and Update

  1. #1
    Registered User
    Join Date
    06-23-2019
    Location
    Earth
    MS-Off Ver
    365
    Posts
    5

    VBS to Find and Update

    Hi. Not really sure how to go about this.
    I have a master table that I constantly update. I download another list with the current status and dates of the received items.

    Column C may already contain data that I do not want overwritten.
    Column H will enter the status under Column C and Column D, if Column G finds a match with Column A. If no match found in A, then it will move onto the next item, but should not overwrite anything if there is no match.
    =IFERROR(INDEX($g$2:$h$16, MATCH(A2,$h$2:$h$500,0)),cell) returns #name? error
    How do I set the range to last found line, instead of $h$500?

    I hope I explained it clearly enough. I am fairly new to this so not sure how to go about doing this update.

    I tried many codes I found on the internet and it always returned some error.
    I tried a match code as well.
    This code in the spreadsheet is the latest one I've tried.


    I also found this one, which I do not quite understand how to integrate this:

    ------------------------------------------------------------------
    [code]
    Dim fpath As String
    Dim owb As Workbook
    Dim Master As Worksheet
    Dim Slave As Worksheet 'the following declares both master and slave as worksheets

    fpath = "location of master workbook"


    Set owb = Application.Workbooks.Open(fpath) 'opens the file path

    Set Master = ThisWorkbook.Worksheets("name of sheet in child workbook") 'declares this workbook and sheet as "master"
    Set Slave = owb.Worksheets("name of sheet in master you are pasting to") 'declares the workbook and sheet you're copying to as "slave"


    For j = 1 To 2000 '(the master sheet) 'goes through each row from 1 to 2000

    For i = 1 To 2000 '(the slave sheet) 'again does the same and the slave sheet
    If Trim(Master.Cells(j, 4).Value2) = vbNullString Then Exit For 'if the ID is blank it will exit and move on to the next row
    If Master.Cells(j, 4).Value = Slave.Cells(i, 4).Value Then 'the 4 represents column D, if cell in column D matches the cell in column D in the masterwork book then it will..
    Slave.Cells(i, 1).Value = Master.Cells(j, 1).Value 'cell in column A child workbook equals cell in column A in master workbook
    Slave.Cells(i, 2).Value = Master.Cells(j, 2).Value
    Slave.Cells(i, 3).Value = Master.Cells(j, 3).Value 'same for B and C


    End If
    Next


    Next


    MsgBox ("Data Transfer Successful")

    With owb
    .Save
    .Close
    End With
    [code]
    Attached Files Attached Files
    Last edited by Parsley; 08-13-2020 at 01:08 PM. Reason: Admin told me to

  2. #2
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,409

    Re: Find and Replace

    Administrative Note:

    Welcome to the forum.

    We would very much like to help you with your query, however the thread title does not really convey what your request is about. Tell us what you are trying to do, not how you think it should be done.

    Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

    Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

    (Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)
    Quang PT

  3. #3
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Find and Replace

    Additionally:

    Administrative Note:

    We would very much like to help you with your query, however you need to include code tags around your code.

    Please take a moment to add the tags. Posting code between tags makes your code much easier to read and copy for testing, and it also maintains VBA formatting.

    Please see Forum Rule #2 about code tags and adjust accordingly. Click on Edit to open your post, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

    (Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)
    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: 1
    Last Post: 02-27-2018, 11:22 AM
  2. [SOLVED] Using find replace to first find two double quotes and replace with a single double quote
    By welchs101 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-17-2015, 10:13 AM
  3. [SOLVED] Find and replace where contents a formula so can't be seen by Find and replace.
    By markDuffy in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-26-2015, 02:24 PM
  4. Replies: 16
    Last Post: 12-02-2014, 03:38 PM
  5. Replies: 3
    Last Post: 11-25-2014, 06:08 AM
  6. find in excel replace in word: find/replace text in text boxes and headers
    By dean.rogers in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-10-2012, 12:40 PM
  7. [SOLVED] find and replace - replace data in rows to separated by commas
    By msdker in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-14-2006, 08:10 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