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

**GrayWolf** · 09-30-2021, 09:40 AM

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.

Please Login or Register  to view this content.

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.

**BadlySpelledBuoy** · 09-30-2021, 10:05 AM

Give this a try for one method.

Please Login or Register  to view this content.

BSB

**PaulM100** · 09-30-2021, 10:06 AM

Or to this method:

Please Login or Register  to view this content.

**GrayWolf** · 09-30-2021, 10:16 AM

This one gives me an error
Runtime error "91":
Object variable or With block variable not set

the debug hi-lights the following line.

Please Login or Register  to view this content.

**GrayWolf** · 09-30-2021, 10:17 AM

Originally Posted by BadlySpelledBuoy

Give this a try for one method.

Please Login or Register  to view this content.

BSB

This one gives me an error
Runtime error "91":
Object variable or With block variable not set

the debug hi-lights the following line.

Please Login or Register  to view this content.

**BadlySpelledBuoy** · 09-30-2021, 10:21 AM

That would be because you entered a number that it couldn't find.

It can be error handled to cope with that.

Please Login or Register  to view this content.

BSB

**jindon** · 09-30-2021, 10:27 AM

Assuming the header in 1st row.
Search the value in "Run" column(s) and put time to the 3 cells to the right.
Find method.

Please Login or Register  to view this content.

**GrayWolf** · 09-30-2021, 11:43 AM

Originally Posted by PaulM100

Or to this method:

Please Login or Register  to view this content.

Hi PaulM100, I like your method and I understand most of it so it is easy for me to alter based on the size of other tables.
This code however runs slower than the other methods and so won't be as useful for me.
I need the search and paste of time to be almost instant due to the rapid nature in which I'll need to use this code.
Also in hind sight I noticed that a few of the values I am searching in the "RUN #" look like M-202 or 202-M instead of the normal 202. Is there a way for the search to still fine 202?

Thank you for your assistance.

**GrayWolf** · 09-30-2021, 11:52 AM

Originally Posted by BadlySpelledBuoy

That would be because you entered a number that it couldn't find.

It can be error handled to cope with that.

Please Login or Register  to view this content.

BSB

Hello again BadlySpelledBuoy,
I was able to run the code in the file you provided but I was not able to adapt this to my full size worksheet.
This will find the values in Column A and accurately stamp the HH:MM in the correct column (with some tweaks) but cannot find values in any other columns.
I tried to modify this value in the code :

Please Login or Register  to view this content.

to a different number but it would not work on any other columns than just column A.
since my RUN columns on the larger sheet are in A, G, M, S, Y (5 columns between Runs.

Also I noticed that a few of the values I am searching in the "RUN #" look like M-202 or 202-M instead of the normal 202.

Thank you very much for you assistance.

**GrayWolf** · 09-30-2021, 11:55 AM

Originally Posted by jindon

Assuming the header in 1st row.
Search the value in "Run" column(s) and put time to the 3 cells to the right.
Find method.

Please Login or Register  to view this content.

Thank you very much jindon. This seems to be working very well for me although I do not understand most of this at all.
I did have 2 questions:
1) a few of the values I am searching in the "RUN #" look like M-202 or 202-M instead of the normal 202. Is there a way for the search to still fine 202?
2) how do I add some error handling for a value if not found similar to what BadlySpelledBuoy had in their code.

Thank you very much for your help.

**BadlySpelledBuoy** · 09-30-2021, 11:58 AM

Attach a copy of your workbook layout and we can adapt code to fit it. I wrote the code to work on the layout you demonstrated in your original post and it works fine on that. I'm sure it would be an easy tweak to make it work on your actual data layout.

As for M-202 / 202-M, if you change xlWhole to xlPart in my code it will find 202 in any part of a cell.

BSB

**GrayWolf** · 09-30-2021, 12:10 PM

Originally Posted by BadlySpelledBuoy

Attach a copy of your workbook layout and we can adapt code to fit it. I wrote the code to work on the layout you demonstrated in your original post and it works fine on that. I'm sure it would be an easy tweak to make it work on your actual data layout.

As for M-202 / 202-M, if you change xlWhole to xlPart in my code it will find 202 in any part of a cell.

BSB

Here is the attached file.
The AM clear columns need the HH:MM timestamp and you can see some of the fields have -M appended to the route number. on other sheets it's M-

Hope that helps.

**BadlySpelledBuoy** · 09-30-2021, 12:16 PM

I said it would be a nice easy tweak to make it work!

The parts in red I've changed.

Please Login or Register  to view this content.

BSB

**GrayWolf** · 09-30-2021, 12:23 PM

Originally Posted by BadlySpelledBuoy

I said it would be a nice easy tweak to make it work!

The parts in red I've changed.

Please Login or Register  to view this content.

BSB

WOW thank you ... this is kind of weird though, because my tweaks lookd identical to yours with the exception of xlPart and xlWhole and yet mind did not search anything after the first column.
But when I copied your code over top of mine and saved it and ran it.. BOOM .. worked fine.
Oh well. It works. ha ha .

Would it be too much to ask what is going on in the code, so I can comment it for myself, and others.

Thank you so much for your help.

**BadlySpelledBuoy** · 09-30-2021, 12:30 PM

Happy to help and glad we got there in the end!

Code again below with some comments added to explain what's going on.

Please Login or Register  to view this content.

Let me know if you need more of an explanation than that.

BSB

**GrayWolf** · 09-30-2021, 01:14 PM

Huge thank you to BadlySpelledBuoy for you help and patience.
Special thank you to everyone who offered Assistance. Thank you very much everyone.
Marked as Solved.

**BadlySpelledBuoy** · 09-30-2021, 01:16 PM

Glad we could help

BSB

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

LinkBack

Thread Tools

Rate This Thread

Display

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Thread Information

Users Browsing this Thread

Similar Threads

Search by columns headers and paste all matching columns to another sheet

Count date occurrences between date periods within 2 separate columns (start and end date)

Count date occurrences between date periods within 2 separate columns (start and end date)

Loop through all txt files and paste as separate columns in excel

Search for text in 2 separate columns then highlight/colour cell in another sheet

Split date into 3 separate columns

Search columns and paste rows

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions