Solved: Select and Paste if Criteria Met

[koala]

Hello Forum

Is someone able to assist with some code that will:

Check each cell in column B from row 7 to the bottom can be any number of rows

Working up from the bottom, if a value in a cell in column B is also in a predefined range, called ?Mandatory?, copy that value, then look at the next row above, and if column F is ?AAA? and column H is ?BBB? then paste the copied value, then start again on the next row up. Column F and column H must both be true before pasting. If they are not both true, then check the next column above until two trues are found.

In my raw data, not all cells in columns B, F and H have values, some will be blank

A small example is attached. Sheet 1 is a sample of raw data, sheet 2 is how I would like the finished product to look after the macro is run. (Highlighting is for explanation purposes only)

Hoping someone is able to assist.

Cheers
Koala

[OBP]

If you format Coulmn B as text right side then this will do what you want

Private Sub CommandButton1_Click()
Dim currentrow As String, searchstring As String, flag As String
currentrow = 6
Application.ScreenUpdating = False
For Count = 1 To 1000
ActiveSheet.Range("k" & currentrow).Select
ActiveCell.Offset(1, 0).Select
searchstring = ActiveCell
If searchstring = "" Then Exit For
GoSub Findmandatory
currentrow = currentrow + 1
Next Count
Application.ScreenUpdating = True
Exit Sub

Findmandatory:
ActiveSheet.Range("b65536").End(xlUp).Select
Do
tempracename = ActiveCell
If ActiveCell = searchstring Then
GoSub Findcode
Exit Do
End If
ActiveCell.Offset(-1, 0).Select
Loop Until ActiveCell.Address = "$B$7"
Return

Findcode:
upcount = -1
Do Until ActiveCell.Offset(upcount, 4).Address = "g7"
If ActiveCell.Offset(upcount, 4) = "AAA" And ActiveCell.Offset(upcount, 6) = "BBB" Then
ActiveCell.Offset(upcount, 0) = searchstring
Exit Do
End If
upcount = upcount - 1
Loop
Return



End Sub

[koala]

Thanks you for your help OBP.

The code works fine on the sample I gave, unfortunately it hasnt quite worked the way I need it to on my actual sheet. The code runs through without error, but the values are not copied and pasted to an address above.

I think it has to do with this line

ActiveSheet.Range("k" & currentrow).Select

Maybe I gave a false impression because of my cut down example I tried to simplify things.

The range "Mandatory" is actually on another sheet in the workbook, and is about 1200 rows long x 1 column. It has already been defined as "Mandatory" before I get to this part. (so I thought I could use the range name without redefining it.). The values I need to match are all in "Mandatory"

When I copied all the values in the range "Mandatory, and pasted then to the worksheet with the raw data, the code works as expected, (which is why I think it is that line of code), however if possible I dont want to have to copy and paste each time.

Can you assist further by showing me how to reference the range ("Mandatory") rather than Column K as in the line of code

thanks in advance
Koala

[OBP]

If the range on the second sheet is named then you can use the
Application.Goto Reference:=Mandatory
You will then have the problem that you are on the wrong sheet for the rest of the Code, so you will either have to return to Sheet1 with
Sheets("Sheet1").Select
either put it in the first section of code before the
GoSub Findmandatory or at the start of the subroutine "findmandatory".
Or you could use the Macro to copy and paste the Mandatory data to sheet1 range k7 downwards before you run the rest of the code.

[koala]

Thanks for your help OBP.

I did the copy and paste, and it all works fine.

It takes around 3 and 1/2 minutes to run, but, in this case, my raw data is 45345 rows and the Mandatory range is 1167 rows. (there is usually only approx 50 matches). It varies each day, but this is a typical file.

3 and 1/2 minutes is much better than doing it manually.

I will mark it solved.

cheers
Koala

[OBP]

Koala, you might be able to speed it up a bit with more refined VB.
Are they aircraft flights?