Looking for Pair of Values in Same Column - VBA

Hiya,

Would you be able to upload a sample workbook please? It will help us work through your use case and provide assistance

Edit your post > click "Go Advanced" > "Manage Attachments" > Upload your file(s) > Submit

Hi! I've uploaded the sample workbook. Thank you for all your help!

Hi, this should work for you, not sure where you want the date to be pasted to.

Please Login or Register  to view this content.

Thank you Poizhan! I was looking at the code, but couldn’t see how it scans for the pair of values, 0 and 1.

Sorry if I wasn’t very clear. I would like to scan Column H for a pair of numbers, 1 followed by 0. If the code finds a pair, it will take the date in Column G, on the same row where the 0 is, and copy that to the next available row, Column C (Starting at C4). The code will scan the entire Column H for the pair (1 to 0) and copy the dates to Column C.
If you can help me again, I’ll appreciate it very much. Thanks again Poizhan!

Hi, awsexcel123
Try this:

Please Login or Register  to view this content.

Hi Akuini,

Thanks for your help. Is there a way you can keep adding to the last available row in Column C. If I import new values to Column H, and run the code, the values in Column C are all replaced. I'd like to keep adding values to Column C. I've provided a picture below as an example. Thanks!Capture12.PNG

When I import new values to column G:H, I delete all the old values and import the new ones. Theoretically, after running the code, new values from Column G will be copied to the next available row in Column C. Your code from before worked exactly how I want it to, but it doesn't let me import new G:H values and add to Column C, it replaces the values instead.

Thank you for all your help Akuini! I hope you can help me with this.

Ok, try this:

Please Login or Register  to view this content.

Hi Akuini, that is what I'd like the code to do!

I wanted to ask, if you don't mind. If I wanted to do the opposite, and look for values 0 to 1 in Column H, instead of values of 1 to 0 like before. How would you write the code. The new G:H values would be added to Column D, instead of Column C.

Can this code be written with the code from before, so they can be run the same time. If it is difficult to write together, that'll be ok.

Capture15.PNG

Thank you Akuini! I appreciate any help you can give me.

Can you show the desired result (using both criteria)?
Is it ok to assume that in col G before & after '1' is always 0?

Hi Akuini. Here is a picture of results for both criteria. It wouldn't be ok to assume that in Column G there will always be a '0' before and after a '1.' I showed a case of this in the picture.

Thanks again Akuini!

Capture16.PNG

Using your example above:
In Col B data ends at row 6, but in col C data ends at row 5.
If you import new data (then run the macro) where do you fill the data in col C, from row 6 or 7?

Hi Akuini. If new data is imported and the macro is run, the new data will fill in the next available row in Column C (In the case in the example, this will be Row 7). All new data, when macro is run, will imported to the next available cell in each column.

Thanks Akina! You've been great. I appreciate your patience.

Originally Posted by awsexcel123

Hi Akuini. If new data is imported and the macro is run, the new data will fill in the next available row in Column C (In the case in the example, this will be Row 7). All new data, when macro is run, will imported to the next available cell in each column.

Thanks Akina! You've been great. I appreciate your patience.

But next available cell in col C is row 6 not 7 ???

Originally Posted by Akuini

But next available cell in col C is row 6 not 7 ???

Hi Akuini. Youre right. Last available or empty row in Column C would be Row 6. Thanks!

Here’s the new code. I can see you change the data layout, the imported data now in col F:G, and the result in col B:C.

Please Login or Register  to view this content.

Thank you Akuini! You've helped me greatly! I appreciate all your time and patience in showing me how to code. You've solved my dilemma.

Just a quick question. If I wanted to use the same code for another worksheet in the workbook, would this be correct in the picture? I added "Dim ws As Worksheet" and "For Each ws in Worksheets" and "Next ws."

Capture1.JPG

Thank you!!

Originally Posted by awsexcel123

Thank you Akuini! You've helped me greatly! I appreciate all your time and patience in showing me how to code. You've solved my dilemma.

Just a quick question. If I wanted to use the same code for another worksheet in the workbook, would this be correct in the picture? I added "Dim ws As Worksheet" and "For Each ws in Worksheets" and "Next ws."

Attachment 594729

Thank you!!

Try this one:

Please Login or Register  to view this content.