VBA Find, Copy, paste

[humility36]

Good Morning Friends,

I have an employee training spread sheet that lists out the employee and all the areas that they are trained in. However, the row is super long because their are a lot of areas across different departments that they could be trained in. My row currently goes all the way to BB so I have to scroll a long way to see what they are trained in and I'm only interested in what they are trained in. I have a legend the defines their training status: X=trained - NT=Needs test - NA =Needs assessment - S=safety / re-certify

I want to write some vba code that could "look" for any of these values in the cell and then copy the value and the cell to the right (job) and paste it in a list form.
CURRENT STATE - this is a sample of how my employee sheet looks like now.

A

B

C

D

E

G

H

I

J

K

L

M

N

1

ID

First Name

Last Name

Bid

Shift

trained

job

trained

job

trained

job

trained

job

2

12345

John

Doe

auditor

day

X

forklift

NA

loading

closer

NT

de-pallet

3

12345

John

Doe

auditor

day

small case

X

DC to DC

Mods

X

breakpack

4

The VBA code will "look" for any of the 4 values in the cell and simply
1. copy the cell value and the cell to the right and past it in the same worksheet just down some rows 27 - 31
2. transpose and paste ONCE the basic employee info in rows 20 -25

FUTURE STATE

	A	B	C	D
20	ID	12345
21	First Name	Johnn
23	Last Name	Doe
24	Bid	auditor
25	Shift	day
26
27			Forklift	X
29			loading	NA
29			De-pallet	NT
30			DC to DC	X
31			breakpack	X

This seems very simple to me, but I can't seem to get it right. Any help would be super appreciated and I hope this is clear and easy to understand. Since there is only 4 values that could be in any of the cells I thought maybe these values could be defined in the VBA. Because there might be multiple rows for the employee I also thought I would be to create a loop to search each of the rows. I don't know VBA code enough to write it out. Thanks everyone in advance for taking a look and helping out.

Best Regards,
- Humility36

 

[DanteAmor]

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	ID	First Name	Last Name	Bid	Shift		trained	job	trained	job	trained	job	trained	job
2	12345	John	Doe	auditor	day		X	forklift	NA	loading		closer	NT	de-pallet
3	12345	John	Doe	auditor	day			small case	X	DC to DC		Mods	X	breakpack
4
19
20		12345
21		John
22		Doe
23		auditor
24		day
25
26
27			forklift	X
28			loading	NA
29			de-pallet	NT
30			DC to DC	X
31			breakpack	X
Sheet

Is column G the first column "trained"?
Do you only have one employee in rows 2 to 19 or can there be several employees?

Try this:

Sub copy_paste()
  Dim i As Long, j As Long, k As Long
  k = 27
  For i = 2 To 19
    If Cells(i, "A") = Range("B20") And Cells(i, "B") = Range("B21") And _
       Cells(i, "C") = Range("B22") And Cells(i, "D") = Range("B23") And _
       Cells(i, "E") = Range("B24") Then
      For j = Columns("G").Column To Cells(i, Columns.Count).End(xlToLeft).Column Step 2
        Select Case Cells(i, j)
          Case "X", "NT", "NA", "S"
            Cells(k, "C") = Cells(i, j + 1)
            Cells(k, "D") = Cells(i, j)
            k = k + 1
        End Select
      Next
    End If
  Next
End Sub

 

[humility36]

Good Morning @DanteAmor ,

Thank you for your reply. To answer your questions:

Is column G the first column "trained"?
- yes. Column G is the first column named trained

Do you only have one employee in rows 2 to 19 or can there be several employees?
- yes. This will be 1 employee that would span rows 2 - 5 depending on what department they are trained in.

I've applied and tested your code and it partially works. Definitely on the right track here. The only issue that I'm having is that after the code runs and captures the info, it errors out instead of ends.

I'm getting a run-time error 13 - Type Mismatch -- When I click on debug to see where it's getting hung up on the line
- Case "X", "NT", "NA", "S"

Other than that, It seems to run a little slow, would it be faster to define the range? Somehow write it to search columns G through BB? When I run the code I see this flashing message that says "Calculating(8 threads) 70% . I timed it just to see how long it took for the code to run and it was 1 min 6 seconds.

Thanks again for your help with this! This is going to be super helpful and save me a lot of time.

As I'm learning to vba code, I pasted your code below and made comments of what I think the code is doing. I'm still a novice at VBA, but it's exercises like this that really help me learn. Please see my comments and let me know if I'm on the right track. As I think about the error, is there a way to put an error trap that says "if there is an error, end the code." The code seems to go all the way through the rows, it just doesn't find anymore of the variables and doesn't know what to do, so it pukes.

Sub copy_paste()
  Dim i As Long, j As Long, k As Long
  k = 27 'This is the starting row of where the info will be pasted
  For i = 2 To 19 ' This is the row range that will be searched
    If Cells(i, "A") = Range("B20") And Cells(i, "B") = Range("B21") And _ ' This is an if statement that checks to make sure the info in columns A through E match the info in B20 - B24
       Cells(i, "C") = Range("B22") And Cells(i, "D") = Range("B23") And _ 
       Cells(i, "E") = Range("B24") Then
      For j = Columns("G").Column To Cells(i, Columns.Count).End(xlToLeft).Column Step 2 ' This is a loop that nest a case statement that says look for these variables, if you find them then write them in columns C & D.
        Select Case Cells(i, j)
          Case "X", "NT", "NA", "S"
            Cells(k, "C") = Cells(i, j + 1)
            Cells(k, "D") = Cells(i, j)
            k = k + 1
        End Select
      Next
    End If
  Next
End Sub

-Humility36