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.
Bookmarks