Results 1 to 17 of 17

VBA Search for value in separate columns then paste the date 3 columns right

Threaded View

  1. #1
    Registered User
    Join Date
    09-09-2021
    Location
    London, Ontario, Canada
    MS-Off Ver
    Office 365
    Posts
    56

    VBA Search for value in separate columns then paste the date 3 columns right

    Sorry for the long header.

    I tried searching the forum and google but could not find precisely what I was looking for.
    I am very new to VBA.
    I have a worksheet with the following structure (this is a smaller example)
    It is not in a table format. Just individual cells with text in them

    Column A Column B Column C Column D Column E Column F Column G Column H Column I Column J Column K Column L
    Run Route Number Time Run Route Number Time Run Route Number Time
    100 150123 2079 200 810123 1309 300 840123 20146
    101 150101 2048 201 810101 2027 301 840102 20144
    102 150202 1325 202 810256 1305 302 840302 16190

    I can put a button on the worksheet and assign a subroutine to it (i know how to do this part).
    When i press that button I want an input box to pop up.
    Then I manually enter a 3 digit value, lets say 202, and press ok
    The subroutine should search the worksheet 202 I entered then input the current time as HH:MM in column D, H or L.

    I started with this code so far but then got kind of stumped on how to search only columns A, E, I.
    If there is a better or cleaner way to search for the values in only those columns, then I would love to see that.
    I cant search the entire worksheet because, in my 202 example, that 202 appears in other cells too.

    Here is the code I started with All it does is take the 202 value then place the time (HH:MM) beside it then moves to the next row and does it again with every new value I have.
    I don't know how to get it to do the search for 202 and place the time value (HH:MM) 3 columns to the right.
    Sorry about the excessive comments. I do that for my own benefit.

    If you need more clarification. please let me know.

    Option Explicit
    
    ' variables within a subroutine are saved, then used, then cleared after the subroutine ends
    Sub subVehicleClear()
        ' declare the variable type (dim = dimension)
        ' instead of a value we use an input box that allows the user to enter a value manually
        'Dim varClearTime As Date
        Dim varRoute As Integer
        varRoute = InputBox("Route Number")
        ' activate the worksheet that the table is on
        wsClears.Activate
        
        ' goes the the first available cell in column 1 in the table
        ' this needs to be a search of some kind for my entered 3 digit value
        Range("A2").End(xlDown).Offset(1, 0).Select
         
        ' enter new route number on the empty row
        ActiveCell.Value = varRoute
        ' enter the timestamp of the clear 1 column right ( i think Offset(0, 3) would put it 3 columns to the right.
        ActiveCell.Offset(0, 1).Value = Format(Now, "HH:mm")
            
    End Sub
    I feel like this needs a LOOP where it searches 1 column for the 3 digit value if if positive enter "HH:MM" and end sub and if negative, go to next column and repeat.

    Thank you, Gray.
    Last edited by GrayWolf; 09-30-2021 at 09:53 AM. Reason: more clarity added

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Search by columns headers and paste all matching columns to another sheet
    By maamon in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 08-13-2021, 10:10 AM
  2. Replies: 5
    Last Post: 01-17-2020, 06:21 AM
  3. Replies: 1
    Last Post: 01-09-2020, 02:48 PM
  4. Loop through all txt files and paste as separate columns in excel
    By bjcowen9000 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 09-20-2018, 12:26 AM
  5. Replies: 2
    Last Post: 06-01-2015, 05:25 AM
  6. Split date into 3 separate columns
    By paulr24 in forum Excel General
    Replies: 4
    Last Post: 01-25-2012, 01:51 PM
  7. Search columns and paste rows
    By chambone in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 03-04-2009, 10:56 AM

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