Greetings,
I have a file with 1 million + rows. I have data in 4 col's that needs moved if a certain condition is met.
Col B if blank, ignore and proceed
If Data is present in col B, and it equals "First", then data remains in col B
if data is present in col B, and it equals "second", move to col C, same row
if data is present in col B, and it equals "third", move to col D, same row
if data is present in col B, and it equals "fourth", move to col E, sale row
Cycle thru until end of data.
Thanks,
Brad
Move data from one cell to another
-
- 4StarLounger
- Posts: 539
- Joined: 30 Mar 2010, 18:49
- Location: United States
-
- Administrator
- Posts: 78558
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Move data from one cell to another
Try this. I don't know how long it'll take.
Code: Select all
Sub MoveData()
Dim r As Long
Dim v
v = Range("B1:E1048576").Value
For r = 1 To 1048576
Select Case LCase(v(r, 1))
Case "second"
v(r, 2) = "second"
v(r, 1) = ""
Case "third"
v(r, 3) = "third"
v(r, 1) = ""
Case "fourth"
v(r, 4) = "fourth"
v(r, 1) = ""
End Select
Next r
Range("B1:E1048576").Value = v
End Sub
Best wishes,
Hans
Hans
-
- 4StarLounger
- Posts: 539
- Joined: 30 Mar 2010, 18:49
- Location: United States
Re: Move data from one cell to another
Hans,
When I substitute the v(r, 2) = "second" word second, with the real text, it is not processing the move. the rea data is text in this format: XXXX_XXX_XXXXXXX=XXX_XXXX_XXX The text will be uppercase.
Case "second"
v(r, 2) = "second"
v(r, 1) = ""
Thoughts?
When I substitute the v(r, 2) = "second" word second, with the real text, it is not processing the move. the rea data is text in this format: XXXX_XXX_XXXXXXX=XXX_XXXX_XXX The text will be uppercase.
Case "second"
v(r, 2) = "second"
v(r, 1) = ""
Thoughts?
-
- Administrator
- Posts: 78558
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
-
- 4StarLounger
- Posts: 539
- Joined: 30 Mar 2010, 18:49
- Location: United States
Re: Move data from one cell to another
Here is a sample
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78558
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Move data from one cell to another
I'm really confused now. Can you explain how this relates to your question in the first post. I don't see "first", "second", "third" or "fourth" anywhere.
Best wishes,
Hans
Hans
-
- 4StarLounger
- Posts: 539
- Joined: 30 Mar 2010, 18:49
- Location: United States
Re: Move data from one cell to another
the terms First, Second, Third, Fourth are generic. I cannot post the real words...
-
- 4StarLounger
- Posts: 539
- Joined: 30 Mar 2010, 18:49
- Location: United States
Re: Move data from one cell to another
in this sample, the XXXX_XXX_XXXXXXX=XXX_XXXX_XXX would move to col C as if it were the term Second
-
- Administrator
- Posts: 78558
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
-
- 4StarLounger
- Posts: 539
- Joined: 30 Mar 2010, 18:49
- Location: United States
Re: Move data from one cell to another
could it be as simple as upper vs. lower case?
-
- 4StarLounger
- Posts: 539
- Joined: 30 Mar 2010, 18:49
- Location: United States
Re: Move data from one cell to another
I played around with upper vs. lower and that seems to be the problem so I will continue to mess around and ping back if I fail..
-
- 4StarLounger
- Posts: 586
- Joined: 14 Nov 2012, 16:06
Re: Move data from one cell to another
Why sending 2 empty worksheets ?
In your sample file:
- empty column C (except its header)
- use
In your sample file:
- empty column C (except its header)
- use
Code: Select all
Sub M_snb()
sn = Sheet1.Cells(1).CurrentRegion
For j = 1 To UBound(sn)
If InStr(" CDE", Left(sn(j, 2), 1)) Then sn(j, InStr(" CDE", Left(sn(j, 2), 1))) = sn(j, 2)
Next
sheet1.Cells(20, 1).Resize(UBound(sn), UBound(sn, 2)) = sn
End Sub