Excel VBA - Conditional (If/Then) Copy Multiple Dates to New Sheet/Workbook.Sheet

[Jeff the ERA]

Good afternoon everyone!


I would like to thank you kind people on this site for being so influencial in my own learning. I am a newer VBA user and am just amazed at the kind of work that it can streamline.


I'm running on Windows 7: Excel 2010.


I've been fighting this problem for about two weeks now and I feel I have exhausted my Googling efforts.


What I'm trying to do is to make a macro button that pulls up a UserForm and has one
TextBox (txtDateReq) and one Command button (cmdGenerateLog). When I type in a date (M/D/YYYY format) in the textbox and press the Command Button, I would like to do the
following tasks:

Data is listed in the table Below
Sheets("DispatchLog")
[TABLE="width: 500, align: center"]
<tbody>[TR]
[TD]Date Requested[/TD]
[TD]Job Number[/TD]
[TD]Work Location[/TD]
[TD]Employee Number[/TD]
[TD]Task[/TD]
[/TR]
[TR]
[TD]8/8/2013 (Cell A2)[/TD]
[TD]10 (Cell B2)[/TD]
[TD]House (Cell C2)[/TD]
[TD]1 (Cell D2)[/TD]
[TD]A (Cell E2)[/TD]
[/TR]
[TR]
[TD]8/8/2013[/TD]
[TD]14[/TD]
[TD]Work[/TD]
[TD]2[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]8/15/2013[/TD]
[TD]10[/TD]
[TD]Work[/TD]
[TD]3[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]8/11/2013[/TD]
[TD]12[/TD]
[TD]Work[/TD]
[TD]4[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]8/8/2013[/TD]
[TD]14[/TD]
[TD]House[/TD]
[TD]5[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]8/15/2013[/TD]
[TD]13[/TD]
[TD]Yard[/TD]
[TD]6[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]8/9/2013[/TD]
[TD]10[/TD]
[TD]Yard[/TD]
[TD]7[/TD]
[TD]G[/TD]
[/TR]
</tbody>[/TABLE]

Sheets("DailyLog") or NewWorkbook.Sheets("DailyLog")
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD](Cell A3)[/TD]
[TD]Date Requested:[/TD]
[TD]____ (Cell C3)[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]#[/TD]
[TD]Job Number[/TD]
[TD]Work Location[/TD]
[TD]Employee Number[/TD]
[TD]Task[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD](Cell B6)[/TD]
[TD](Cell C6)[/TD]
[TD](Cell D6)[/TD]
[TD](Cell E6)[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


1. Search Column "A" and match the date from the textbox to the value of the cell.
Note: The actual spreadsheet is over 500 entries with about 30 entries per day.


2. If the value of the cell in Column "A" = the TextBox value (.txtDateReq), I would like


to copy items from the same row, in the Columns "B, C, D, & E" to a Template File I have saved called "DailyLog."
Note: I did not set up my sample spreadsheet to show this, so I will post my code
here and adjust it as nessisary.


3. I would like it to put the value of the Textbox entry into cell "C3."


4. I would like it to add ALL values that match the textbox entry to the next available


line on the "DailyLog" sheet.


5. I would perfer to use VLookup unless someone can help me with the coding an Index/Match case.

The reason I would perfer VLookup is because I will eventually use OptionButtons on the userform to generate different Logs based on different information in the "DispatchLog" master excel file (eg. Reports for Work location, for employee numbers, for tasks...) With VLookup, All I would need to change in the code to pull the information I need would be to add a new textbox and change VLookup(txtdatereq) to VLookup (txtWorkLoc) (And yes, I do know that VLookup is limited to searching items from the first column of the range, hence why I'm open to learning more about Index/Match cases)


My code follows:

Private Sub cmdGenerateLog_Click()


Dim WB As Workbook, SH As Worksheet, FN As String


    Set WB = Workbooks.Open("\\Desktop\Project_Reporting.xlsx")
    Set SH = WB.Sheets("DailyLog")
    
    'save to: location'
    FP = "\\Desktop\Daily_Reports\"
    
    'save as: DT_Job #_Date of Inspection_Inspector_Inspection Type'
    FN = "DR_" & Format(txtDateReq.Value, "mm-dd-yy")
        
    For Rw = 1 To lastrow
    
        
    'write information into [boxed] cell'
    SH.[B6] = Format(Me.txtDateReq.Value, "mm-dd-yy")   'Puts Date Requested On Log as 


Date'
    SH.[A & rw] = Application.VLookup((Me.txtDateReq), Worksheets("DispatchLog"), Range(B4, 


R1500), 4, False)
    SH.[B & rw] = Application.VLookup((Me.txtDateReq), Worksheets("DispatchLog"), Range(B4, 


R1500), 5, False)
    SH.[C & rw] = Application.VLookup((Me.txtDateReq), Worksheets("DispatchLog"), Range(B4, 


R1500), 4, False)
    SH.[D & rw] = Application.VLookup((Me.txtDateReq), Worksheets("DispatchLog"), Range(B4, 


R1500), 4, False)
    
    Next Rw
    
    WB.SaveAs FP & FN, xlExcel7                        ' save new workbook to location w/ 


custom name as Excel7 format'
    WB.Close                                           ' close workbook
      
    Me.Hide                                            ' closes Form box'
    
End Sub

I've been Frankenstining code as I go along, but this one has me baffled. I have tried


various different methods of Copy.EntireRow and limiting the range but all it seems to do


is copy every value even if I have an "If txtDateReq = ... then" condition.


Below are additional codes that I've tried to use but have garnered similar undesired


results and errors:


Below Copies all data but with the correct Ranges, If statement still copies all data

Private Sub CommandButton2_Click()


'This works at copying selected range values. Incorporate this data
'Into a previous range copy method.
'Note, Row H gets added as well, possibly fix




    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim i As Integer, k As Integer
    Dim ws1LR As Long, ws2LR As Long


    Set ws1 = Sheets("Dispatch Log")
    Set ws2 = Sheets("DailyLog")




    ws1LR = ws1.Range("E" & Rows.Count).End(xlUp).Row + 1
    ws2LR = ws2.Range("A" & Rows.Count).End(xlUp).Row + 1


    i = 2
    k = ws2LR
    Do Until i = ws1LR
        With ws1
            .Range(.Cells(i, 5), .Cells(i, 9)).Copy
        End With


        With ws2
            .Cells(k, 1).PasteSpecial
            .Cells(k, 1).Offset(1, 0).PasteSpecial
        End With


        k = k + 2
        i = i + 1
    Loop




End Sub

Doesn't copy anything

Private Sub CommandButton3_Click()
Dim srcrow As Integer
Dim dstrow As Integer
Application.ScreenUpdating = False


srcrow = 1
dstrow = 1
While (Sheets("Dispatch Log").Range("A" & srcrow).Value <> "")
If (Sheets("Dispatch Log").Range("B" & srcrow).Value = txtDateReq) Then
Range("E" & srcrow & ":I" & srcrow).Copy
Sheets("DailyLog").Select
Range("A" & dstrow).Select
ActiveSheet.Paste
Sheets("Dispatch Log").Select
dstrow = dstrow + 1
End If
srcrow = srcrow + 1
Wend


Application.CutCopyMode = False
Application.ScreenUpdating = True


End Sub

States that it cannot find the value

Private Sub CommandButton4_Click()
Dim Rw As Long, myVAL As String


myVAL = Me.txtDateReq


On Error Resume Next
Rw = Application.WorksheetFunction.Match(myVAL, Sheets("Dispatch Log").Range("B:B"), 0)
On Error GoTo 0
If Rw = 0 Then
    MsgBox "The search value '" & myVAL & "' was not found"
    Exit Sub
End If


'MsgBox "The search value '" & myVAL & "' was found on row: " & Rw


With Sheets("Formatted")
    .Range("B3").Value = Sheets("DailyLog").Range("A" & Rw).Value   'name
    .Range("B4").Value = Sheets("DailyLog").Range("B" & Rw).Value   'address
    .Range("C3").Value = Sheets("DailyLog").Range("C" & Rw).Value   'phone
    'etc....
End With


End Sub

This forum has been a very useful resource for me in learning by example, I hope you will
be able to help me out with this.


Thank you! (Sorry I couldn't figure out how to attach my vba sample file)

 

[Jerry Sullivan]

Hi Jeff and Welcome to the Board,

I'd encourage you break your project into smaller steps so you can "master" (or at least understand) each part before moving on to the next.

There several aspects of the project you outline that can be tricky when learning VBA.
1. Lookups using dates
2. Finding multiple matches
3. Getting values from non-contiguous ranges
4. Transferring values to the next blank row in a target worksheet

Worksheet functions Vlookup and Index-Match aren't a good fit for your task because they only find the first match, and you need to find multiple matches.

Approaches for multiple matches include:
Range.Find and Range.FindNext
Autofilters
AdvancedFilter
Processing the lookup in an Array
Queries

If you have control of the formatting of your lookup data on the DispatchLog sheet (to ensure it's consistency), then Find-FindNext could be a good approach for learning VBA.

Is there a reason that you want the user interface through a UserForm instead of just using a worksheet cell for the date and a button?

 

[Jeff the ERA]

Hi Jeff and Welcome to the Board,

I'd encourage you break your project into smaller steps so you can "master" (or at least understand) each part before moving on to the next.

There several aspects of the project you outline that can be tricky when learning VBA.
1. Lookups using dates
2. Finding multiple matches
3. Getting values from non-contiguous ranges
4. Transferring values to the next blank row in a target worksheet

Worksheet functions Vlookup and Index-Match aren't a good fit for your task because they only find the first match, and you need to find multiple matches.

Approaches for multiple matches include:
Range.Find and Range.FindNext
Autofilters
AdvancedFilter
Processing the lookup in an Array
Queries

If you have control of the formatting of your lookup data on the DispatchLog sheet (to ensure it's consistency), then Find-FindNext could be a good approach for learning VBA.

Is there a reason that you want the user interface through a UserForm instead of just using a worksheet cell for the date and a button?

Thank you for your response!

The reason I'm using a Userform is because I'm developing macro-based text entry into my (Dispatch Log) workbook. The workbook already has
several working (and fully functional) UserForms which allow our Dispatcher to enter the correct data for the kind of work we do. I've been
Frankensteining different macros together to get my desired effect: a workbook that runs like a computer data entry program. It has several
different macro enabled buttons on a freeze-pane at the top that assist him in auto-filling the correct information (from another separate workbook),
creating a "Dispatch Ticket" that we give to our employee's automatically with all pertinant information.

Also, using the Userform will allow me to develop this particular piece of coding to be modified via OptionButton, so we can not only generate a
report based upon just (txtDateReq) but maybe on (Work Location) etc using a modified version of this code currently. (For example, an option
button that runs this macro, but another option button set up exactly the same way but searching a different column for a different textbox entry).
This is why I'm spending so much time on this particular piece of code, because I will modify and develop it to help generate other reports.

So far this "Button" has been the most difficult to date to program a macro for (it makes populating textboxes, updating entries, and auto-generating
an email to the specific employee look like child's play!)

I appreciate your assistance, because after you mentioned the "Range.Find" I found a plethora of resources that assist me.

I was able to utilize what you suggested with Range.Find and once again Frankenstein this macro together. My problems are listed above the code.


I have two problems with my new code:
1. Column A lists the Date our Dispatcher Entered the data initially, where column B is the Date Requested (My find term is txtDateReq).
When I run my Macro, it successfully pulls all the correct data and puts it in the correct location, however it also pulls matching data from
Column A. This can't happen, because this macro button is designed to generate a report based only on the Date Requested, not the Date
Entered.
2. I think I may have resolved this issue with the Range.Find feature, but before when I was trying this code out, sometimes it would not copy the
last row.

Private Sub CommandButton2_Click()

    'Defining the variables.
    Dim GCell As Range
    Dim Page$, Txt$, MySheet$


    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim i As Integer, k As Integer
    Dim ws1LR As Long, ws2LR As Long
    
    
    Set ws1 = Sheets("Dispatch Log")
    Set ws2 = Sheets("DailyLog")


    ws1LR = ws1.Range("E" & Rows.Count).End(xlUp).Row
    ws2LR = ws2.Range("A" & Rows.Count).End(xlUp).Row


   
    'The text for which to search.
    Txt = Me.txtDateReq.Value                                      ' defines the textbox entry as "Txt" '
        
   
    i = 1
    k = ws2LR
    Do Until i = ws1LR                                                  ' I guess you call this a Loop condition? '


    'Search for the specified text
 Set GCell = ws1.Range("B:B").Find(Txt)                         ' My Range.Find ' 
    
    
    With ws1
        GCell.Range(.Cells(i, 4), .Cells(i, 7)).Copy               ' Copy Range.Find answers from ws1 ("Dispatch Log") '
    End With
    
    With ws2
        .[B6] = Me.txtDateReq.Value                                ' Uses textbox with txtDateReq and places it in appropriate spot on ws2 ("DailyLog") '
           
        .Cells(k, 1).Offset(1, 0).PasteSpecial                     ' (K, x) x = column number // Pastes copied values to ws2 ("DailyLog") '
        End With


        k = k + 1                                                          ' +1 = next line, +2 = skip one row then paste '
        i = i + 1
    Loop




End Sub

Also note: I know originally I mentioned a separate workbook. I've gotten pretty good at doing that kind of macro, so for quickness' sake and so
I can easily see which action represents what, I've put the ("DailyLog") sheet in the same workbook as the ("Dispatch Log").

 

[Jerry Sullivan]

Thanks for sharing how this part fits into your broader application.

A UserForm is a good approach. I only asked about why you chose that because it added a little extra complexity to a problem you were already having a difficult time solving.

A couple comments on your last attempt...

1. You don't need to step through each row of your data with a Do Until loop. Here's a simple example from the Excel Developer Reference (VBA Help) that shows how to use Range.Find and Range.FindNext...

With Worksheets(1).Range("a1:a500")
    Set c = .Find(2, lookin:=xlValues)
    If Not c Is Nothing Then
        firstAddress = c.Address
        Do
            c.Value = 5
            Set c = .FindNext(c)
        Loop While Not c Is Nothing And c.Address <> firstAddress
    End If
End With

Try adapting this for your code.

2. When using the .Find method, make sure to use the parameters to be explicit about whether to find partial matches, match case, etc. These settings are retained from the previous Find (including those done by the user interface), which can lead to results that seem inconsistent if you do not use parameters.