Excel VBA code to copy value in column and paste to another

November 2, 2018 at 13:41:17
Specs: Windows 10
I have worker numbers in a column mixed in with work order numbers. I need to copy only the work numbers and paste them in the adjacent column as many times as there are work orders until a new worker number appears.

data example

A B
AIA21
231
332
221
226
AIA22
111
118
AIA23
555
551
557
550

desired result
A B
AIA21
AIA21 231
AIA21 332
AIA21 221
AIA21 226
AIA22
AIA22 111
AIA22 101
AIA23
AIA23 555
AIA23 551
AIA23 557
AIA23 550

The idea is to search through B until get to worker number ( AIA22), copy it and paste it into column A beside the work numbers until new worker number is encountered in B.

message edited by Hodocka


See More: Excel VBA code to copy value in column and paste to another

Reply ↓  Report •

#1
November 2, 2018 at 19:56:13
First, a posting tip:

Please click on the How-To link at the end of this post and read the instructions on how to format example data so that it is easier for us to read. Then edit/repost your data so that the columns line up correctly. Don't forget to use Column letters and Row numbers as shown in the example.

Thanks!

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

#2
November 4, 2018 at 12:40:16
 A           B
             AIA21
             231
             332
             221
             226
             AIA22
             111
             118
             AIA23
             555
             551
             557
             550

desired result
       A          B
                  AIA21
      AIA21       231
      AIA21       332
      AIA21       221
      AIA21       226
                  AIA22
      AIA22       111
      AIA22       101
                  AIA23
      AIA23       555
      AIA23       551
      AIA23       557
      AIA23       550

The idea is to search through B in a loop and each time new worker number ( AIA21) is encountered, copy and paste it in column A besides the work numbers in B.


Reply ↓  Report •

#3
November 4, 2018 at 16:54:52
Ask for some help and we'll see what we can come up with.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

Related Solutions

#4
November 4, 2018 at 18:51:46
I do need help. I have been using code to offset the worker ID over 1 and down 1 and down 2 when I knew
there was only 2 work orders per worker but now I have a report that has different work orders per worker so I need conditional code to copy and paste the worker ID in column A until new worker ID is encountered
in column B. What else do I need to provide.

Reply ↓  Report •

#5
November 5, 2018 at 07:18:15
The point of my previous post was that you told us that you "need" something. There was no request for help, there was no "Thank you in advance", etc.

We are all volunteers here and it makes us feel better when we are actually asked for help as opposed to simply being told what someone "needs" or "wants", with the expectation that we will simply drop what we are doing and fulfill that "need".

As far as your question, in order for VBA to determine where one employee's list ends and the next one begins, it needs something to "check". I see that in your example, the employee ID's all start with AIA. Therefore, if the code checks for a string containing AIA in Column B, it will know that a new ID has been found.

Are these actual values or just examples? The code I am offering will work for that data, but it may not work for your actual data. The comparison concept/method should be valid, but the actual "If...Like" instruction may not be. Obviously, if your ID's don't contain AIA, that instruction will not work for you.

If you don't know how to modify the code to make it work, let us know what your actual data looks like and we can be more specific. However, be sure not to divulge any personal information.

Sub CopyId()
Dim lastRw As Long
Dim nxtRw As Long
Dim idRw As Long

'Determine Last Row with data in Column B
    lastRw = Cells(Rows.Count, 2).End(xlUp).Row

'Initialize ID Row
    idRw = 1

'Loop through data, copying ID to Column A if Column B doesn't contain ID
   For nxtRw = 2 To lastRw
      If Not Cells(nxtRw, 2) Like "*AIA*" Then
      Cells(nxtRw, 1) = Cells(idRw, 2)

'If Column B does contains an ID, reset idRw to use new ID
      Else: idRw = nxtRw
      End If
  Next
End Sub

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

message edited by DerbyDad03


Reply ↓  Report •

#6
November 5, 2018 at 07:31:47
Thank you so much! I appreciate it.
The numbers, etc in my example was an example but I can make changes in your code to fit my actual data.

Reply ↓  Report •

#7
November 5, 2018 at 09:00:49
Glad I could help.

BTW...I just edited the code slightly. I had Dimmed a variable named nextRw when it should have been Dimmed as nxtRw since that is what I used in the code. In code this simple, it probably would never have made any difference (unless you use Option Explicit) but it was a mistake, so I fixed it.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

#8
November 5, 2018 at 09:54:36
Thanks!
In my actual data, I have columns A,B,C with the data from C needing copied to A. I modified your code with no problem to loop through C instead of B. I have actual different ID's. I have tried many modifications trying to add say DID along with AIA but can't get it to work. I tried "*AIA*", "*DID*" and "*AIA, DID*" but can't get it to work. Thanks again for your help...

Reply ↓  Report •

#9
November 5, 2018 at 10:37:17
Try this:

      If Not Cells(nxtRw, 2) Like "*AIA*" And Not Cells(nxtRw, 2) Like "*DID*" Then

It doesn't work, you'll need to tell us, with more accuracy, what your actual data looks like. With each additional requirement that you add, we have to rework what we've already done, set up a test environment, etc. The more detail you give us up front, the easier it is for all.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

#10
November 5, 2018 at 11:21:16
It didn't work. It gets the AIA worker numbers right but puts the same AIA worker numbers for the DID worker, when it gets to a new AIA worker, it gets it right.

Reply ↓  Report •

#11
November 5, 2018 at 11:36:02

 Before				After		
A	B	C		A	B	C
	345	AIA200		AIA200	345	AIA200
	345	1		AIA200	345	1
	345	1		AIA200	345	1
	345	1		AIA200	345	1
	345	1		AIA200	345	1
	345	1		AIA200	345	1
	345	DID221		AIA200	345	DID221
	345	2		AIA200	345	2
	345	2		AIA200	345	2
	345	2		AIA200	345	2
	345	2		AIA200	345	2
	345	2		AIA200	345	2
	345	AIA223			345	AIA223
	345	111		AIA223	345	111
	345	111		AIA223	345	111
	345	111		AIA223	345	111
	345	111		AIA223	345	111
	345	111		AIA223	345	111
	345	111		AIA223	345	111
	345	111		AIA223	345	111
	345	111		AIA223	345	111
	345	111		AIA223	345	111
	345	111		AIA223	345	111

code Sub CopyAndPasteId()
Dim lastRw As Long
Dim nxtRw As Long
Dim idRw As Long

'Determine Last Row with data in Column C
lastRw = Cells(Rows.Count, 3).End(xlUp).Row

'Initialize ID Row
idRw = 1

'Loop through data, copying ID to Column A if Column C doesn't contain ID
For nxtRw = 2 To lastRw
If Not Cells(nxtRw, 3) Like "*AIA*" And Not Cells(nxtRw, 2) Like "*DID*" Then
Cells(nxtRw, 1) = Cells(idRw, 3)

'If Column C does contains an ID, reset idRw to use new ID
Else: idRw = nxtRw
End If
Next
End Sub

Afterde


Reply ↓  Report •

#12
November 5, 2018 at 12:35:30
First, all the way back in Response #1, when I asked you to read the instructions on how to post example data in the forum, I said:

"Don't forget to use Column letters and Row numbers as shown in the example."

You have not used Row numbers, so you've confused matters a bit. It looks like you have significantly changed the layout of your data from what you showed before and Row numbers would help so that I could refer to exact cell references without guessing or counting.

In your earlier example, the ID's were in a row by themselves. Now you have job numbers in the same rows as you have ID's. I ran your modified code against your modified data and I did not get the same results as you. You show an output with an ID number in what I think is A1 of your example. That can't happen because the first Row that the code writes ID's to is Row 2:

For nxtRw = 2 To lastRw
            ^

In other words, you could not have gotten the "After" that you are showing. A1 should be empty because it starts out empty and the code never writes anything into Row 1.

Second, you should be using the pre tags for the macro also. When you don't, you lose all of the formatting (indents, etc.) as can be seen in your response.

Third, look at this line very carefully. This is why your code did not pick up the DID ID's.

If Not Cells(nxtRw, 3) Like "*AIA*" And Not Cells(nxtRw, 2) Like "*DID*" Then
                                                         ^

However, now that you've changed the data layout (using job numbers on the same rows as the ID's) I'm no longer sure what your output is supposed to look like.

It's really hard to work with inconsistent example data.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

#13
November 5, 2018 at 14:36:01
Sorry DerbyDad03 about changing the data before posting.
" If Not Cells(nxtRw, 3) Like "*AIA*" And Not Cells(nxtRw, 2) Like "*DID*" Then"

This was it, changed nxtRw, 2 to nxtRw, 3 and it worked!

Thanks so Much!! I really appreciated it..
^


Reply ↓  Report •

Ask Question