Power Query Course in Spanish
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: Conditional Find

  1. #1
    New Member
    Join Date
    May 2002
    Location
    Rba Ltd
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hello, thanks in advance to anyone who can assist me. I have a spreadsheet that gets updated every week. Each week a new sheet is created and formatted by pulling data from a database, and then running macros to format the data. After that I would like to add a macro that will pull data from "last weeks" spreadsheet. The macro I have searches for a number, (in this case, an invoice #), I would like it to cut and paste relative data, IF it finds the same number in the previous worksheet. This is the code I have:


    Sub Update_Comments()

    'This macro performs a search and copy for updating a new spreadsheet with previously entered comments.

    Dim counter As Integer
    Dim Findcell, stringtofind As String
    Dim dummy As Integer

    newshtname = Application.InputBox(Prompt:= _
    "Please enter this weeks worksheet name", Type:=2)

    oldshtname = Application.InputBox(Prompt:= _
    "Please enter the previous weeks worksheet name", Type:=2)

    For counter = 7 To 250

    Cells(counter, 4).Select
    Selection.Copy
    stringtofind = Selection
    Workbooks.Open Filename:=(oldshtname)
    Workbooks(oldshtname).Activate

    Set Findcell = Cells.Find(what:=stringtofind)


    If Findcell Is Nothing Then
    dummy = 1

    Else
    Findcell.Select
    ActiveCell.Offset(rowOffset:=0, columnOffset:=4).Activate
    Selection.Copy
    Workbooks(newshtname).Activate
    Cells(counter, .Select
    ActiveSheet.Paste
    End If

    Next counter
    End Sub


    This works half way. It will copy the correct data if it finds the invoice # on the previous sheet, however, if it runs into the situation where it does not find it, it handles the error and continues, but then copies data not
    relevant to the current invoice number being looked for.

    Any help is appreciated, I can't seem to write the correct code to handle not finding the invoice #....

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi


    If Findcell Is Nothing Then
    dummy = 1
    'shouldn't you close the active workbook here?
    'Workbooks(newshtname).Activate here?



    Tom

  3. #3
    New Member
    Join Date
    May 2002
    Location
    Rba Ltd
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I was using the 'dummy = 1' to set a variable I could in another if statement, had been set after the findcell.select statement, to check to
    make sure that it only continued to move the data if the number had been found, but that had not worked properly either..

  4. #4
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi again
    I gotta run.
    Post all of your code and maybe someone else will help you.
    Irregardless, I'll save your thread for later.
    Tom

  5. #5
    New Member
    Join Date
    May 2002
    Location
    Rba Ltd
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I had a friend help me with this, anyway, thanks, here is the code, could be useful.

    Sub Update_Comments()

    'This macro performs a search and copy for updating a new spreadsheet with previously entered comments.

    Dim counter As Integer
    Dim Findcell, stringtofind, comment As String
    Dim dummy As Integer
    Dim NewSheet, OldSheet As Worksheet

    newshtname = Application.InputBox(Prompt:= _
    "Please enter this weeks worksheet name", Type:=2)

    oldshtname = Application.InputBox(Prompt:= _
    "Please enter the previous weeks worksheet name", Type:=2)

    Set OldSheet = Workbooks.Open(Filename:=(oldshtname)).Worksheets(1)
    Set NewSheet = Workbooks.Open(Filename:=(newshtname)).Worksheets(1)

    For counter = 7 To 250
    stringtofind = NewSheet.Cells(counter, 4).Value

    Set Findcell = OldSheet.Cells.Find(what:=stringtofind)

    If Not (Findcell Is Nothing) Then
    comment = OldSheet.Cells(Findcell.Row, Findcell.Column + 4).Value
    NewSheet.Cells(counter, .Value = comment
    End If

    Next counter
    End Sub

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •