VBA Loop

[stansmith]

Hello,

I have a giant file that I am trying to use VBA to quickly work through.

In column D, there are ~4500 unique identifiers. Somehow, i need to copy and paste those 4500 identifiers in the cell B1.

Any idea how to code this on VBA?


EDIT: Sorry to be clear - i am trying to find a way to copy cell "D5" paste it in cell "B1" then to "D6" and paste back into "B1" and so on...

 

[jim may]

Hi Stan
Welcome to the Board. This is the place to be if you are interested in INCREASING your knowledge of Excel.

Kindly provide a few additional items needed to clarify a few things.

In column D, there are ~4500 unique identifiers. (Obviously there are a substantial number of other duplicate values in Column D, which could number 10,000+ or more, RIGHT?)

Also, when you place these unique values into cell B1 - You want the 4500 values to be Listed downward in your sheet into Cells B1:B4500 (NOT OVERRIDING any other values which
may ALREADY be in the Cells B1:B4500, RIGHT?

Let us know ASAP.

Again Welcome to the Board

Jim

 

[stansmith]

Hi Jim,

Unfortunately the 4490 identifiers in column D are unique , so there are not any duplicate values in this column.

Your second point is exactly the roadblock i am running into. I am trying to override the contents of B1 individually with each unique identifier.

I have tried a few different variations of loops, but keep getting stuck.

Essentially this is the file:

A B C D
1 ID Code:

2 ABC123

3 DEF234


I am not sure if this makes sense, but i am trying to move "ABC123" (D2) to cell B1. Then put "DEF234" (D3) in cell B1, essentially overriding what i just pasted in B1.

Does that make sense?

 

[Fluff]

How about

Sub stansmith()
   Dim Cl As Long
   
   For Each Cl In Range("D2", Range("D" & Rows.Count).End(xlUp))
      Range("B1").Value = Cl.Value
   Next Cl
End Sub

 

[stansmith]

Fluff - thank you for the reply.
I am sorry i am very new to VBA, when i run this i am getting the error "compile error: For Each control variable must be Variant or Object" (and the vba has highlighted the "Cl" in the row that starts with "For Each Cl..."
Any idea on this?

 

[Fluff]

Apologies there's a typo, it should be

Sub stansmith()
   Dim Cl As [COLOR=#ff0000]Range[/COLOR]
   
   For Each Cl In Range("D5", Range("D" & Rows.Count).End(xlUp))
      Range("B1").Value = Cl.Value
   Next Cl
End Sub

 

[stansmith]

This worked!!!

Is there any way to add a wait time (say for one second) after pasting in cell B1?

 

[Fluff]

Try

Sub stansmith()
   Dim Cl As Range
   
   For Each Cl In Range("D2", Range("D" & Rows.Count).End(xlUp))
      Range("B1").Value = Cl.Value
      Application.Wait Now + TimeValue("00:00:01")
   Next Cl
End Sub

 

[stansmith]

i seriously cant thank you enough.
This is perfect. thank you

 

[Fluff]

Glad to help & thanks for the feedback