Results 1 to 3 of 3

[SOLVED]Help with VBA Macro - Returning Entire Row

Threaded View

  1. #1
    Registered User
    Join Date
    02-04-2013
    Location
    Texas
    MS-Off Ver
    Excel 2010
    Posts
    25

    [SOLVED]Help with VBA Macro - Returning Entire Row

    Wats up,

    I'm trying to return an entire row of data based on the values in one column.

    Attached is what I have so far. This is all being done via Button 1.

    User is to input a min and max value, which is then searched in Column E. If it is found, the row is to be returned in addition to the adjacent row, which is located above or below it. One row has the date in Column A, and the other row has the time in Column A.

    The below code was given to me by jaslake, but currently all it does is put an asterisk next to the two rows.
    Sub Button1_Click()
        Dim LR As Long
        Dim rng As Range
        Dim cel As Range
        Dim ws As Worksheet
        Set ws = Sheets("Sheet1")
        With ws
            LR = .Range("E" & .Rows.Count).End(xlUp).Row
            Set rng = .Range(.Cells(5, 5), .Cells(LR, 5))
            With rng
                For Each cel In rng
                    If cel >= ws.Range("B19").Value And cel <= ws.Range("B20").Value Then
                        If InStr(ws.Range("A" & cel.Row).Text, "-") <> 0 Then
                            ws.Range("A" & cel.Row).Offset(0, 3).Resize(2, 1).Value = "*"
                        ElseIf InStr(ws.Range("A" & cel.Row).Text, ":") <> 0 Then
                            ws.Range("A" & cel.Row).Offset(-1, 3).Resize(2, 1).Value = "*"
                        End If
                    End If
                Next cel
            End With
        End With
    End Sub
    The code actually checks to see if the row has a DATE or TIME in column A. The DATE is always in the first row and the TIME is always in the 2nd row. Based on Column A (After column E is searched), it determines which is the adjacent row.

    Now I just need the data for both rows to be extracted to Sheet 2, each value receiving its own column.

    Ex:
    A B C D
    2-6 Wed 3:00 2a 8
    2-6 Wed 3:00 2b 9

    *Adjacent rows share the same date and time.

    Thanks for reading and for the help if possible.
    Attached Files Attached Files
    Last edited by BamBamMoneyBags; 02-08-2013 at 12:36 AM.

Thread Information

Users Browsing this Thread

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

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