PDA

View Full Version : [SOLVED] Shift select data



JLandrum
05-15-2015, 07:26 AM
Good afternoon everyone, need some help. Im trying to move THE DATA from each column listed from 1, 2, 3, 4, to 11, 22, 33, 44 in VBA. There's other data but not relevant to what I need. So, whatever data that is inputted into 1,2,3,4 it will automatically move that data to 11,22,33,44. Then it will need to be sorted by column 11,44,33. Any and all help is greatly appreciated, thank you

mperrah
05-15-2015, 09:21 AM
Based on your test page the columns are 3, 4, 6 and 7
moving to 14, 15, 12 and 13 respectively
not elegant but functional:

Sub vbax52603()

lr = Cells(Rows.Count, 1).End(xlUp).Row
Range("C3:C" & lr).Copy Destination:=Range("N3") ' copies column 3(C) to 14(N) yellow to yellow
Range("D3:D" & lr).Copy Destination:=Range("O3") ' copies 4(D) to 15(O) green to green
Range("F3:F" & lr).Copy Destination:=Range("L3") ' copies 6(F) to 12(L) blue to blue
Range("G3:G" & lr).Copy Destination:=Range("M3") ' copies 7(G) to 13(M) red to red
Range("C2").Select

End Sub

-mark

mperrah
05-15-2015, 09:28 AM
This was an attempt at an array solution, but I cant get it to work...
if anyone wants to play with it be my guest.
I think I'm looping through the array variables to much, that over rights the values, but not sure


Sub vbax52603()
'move data from columns to other on same sheet

Dim x, s, d, lr As Long
Dim mySrc, myDst As Variant

lr = Cells(Rows.Count, 1).End(xlUp).Row
ReDim mySrc(1 To 4)
ReDim myDst(1 To 4)
mySrc = Array("C", "D", "F", "G")
myDst = Array("N", "O", "L", "M")

For d = LBound(myDst) To UBound(myDst)
For s = LBound(mySrc) To UBound(mySrc)
For x = 3 To lr
Cells(x, myDst(d)).Value = Cells(x, mySrc(s)).Value
Next x
Next s
Next d

End Sub

JLandrum
05-15-2015, 09:39 AM
Hey Mark, thanks for the help. The first one worked for moving the data, any ideas how to sorted by column 11,44,33. Any and all help is greatly appreciated, thank you

SamT
05-15-2015, 09:47 AM
move THE DATA from each column listed from 1, 2, 3, 4, to 11, 22, 33, 44 in VBA

I would use Brute Force on so few moves.
Copy 1
11.PasteSpecial(xlValues)
1.clearContents
Copy 2
22.PasteSpecial(xlValues)
2.ClearContents

You can make the code read better by

LastRow = CStr(Cells(Rows.Count, 1).End(xlUp).Row)
Dim EmpSatus1 As Range
Dim EmpStatus11 As Range
Etc

Set EpmStatus 1 = Range("C3:C" & LastRow)
Set EmpStatus11 = Range("N3:N" & LastRow)
Etc

EmpStatus1.Copy
EmpStaus11.PasteSpecial xlValues
EmpStatut1.ClearContens
Etc

Make it easy: Copy each of the three sections above and paste them into the code 4 times, then edit the numbers and Column Letters

JLandrum
05-15-2015, 10:00 AM
Thanks for the help but you lost me, Where do I enter this, into the existing code?

mperrah
05-15-2015, 10:11 AM
What column do you want to sort by?
Do you mean first sort by the column with 11 in Row 2 (which is column N of your sample)
then by 44 (which is column M)
and then by 33 (which is column L) ?

if so try this:

Sub vbax52603_Sort()

lr = Cells(Rows.Count, 1).End(xlUp).Row

With ActiveWorkbook.Worksheets("Sheet1").Sort
Range("L2:O" & lr).Select
.SortFields.Clear
.SortFields.Add Key:=Range("N3:N" & lr) _
, SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
.SortFields.Add Key:=Range("M3:M" & lr) _
, SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
.SortFields.Add Key:=Range("L3:L" & lr) _
, SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
.SetRange Range("L2:O" & lr)
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

End Sub

JLandrum
05-15-2015, 10:20 AM
Works great, do I just change Descending to ascending to get a,b,c, instead of c,b,a

mperrah
05-15-2015, 10:23 AM
Got my array solution working, yeah...

Sub vbax52603B()
'move data from columns to other on same sheet

Dim x, s, d, lr As Long
Dim mySrc, myDst As Variant

lr = Cells(Rows.Count, 1).End(xlUp).Row
ReDim mySrc(1 To 4)
ReDim myDst(1 To 4)
mySrc = Array(3, 4, 6, 7)
myDst = Array(14, 15, 12, 13)


For d = LBound(myDst) To UBound(myDst)
For s = LBound(mySrc) To UBound(mySrc)
For x = 3 To lr
Cells(x, myDst(d)).Value = Cells(x, mySrc(s)).Value
Next x
Next s
Next d

End Sub

and yes on the ascending:

Sub vbax52603_Sort()

lr = Cells(Rows.Count, 1).End(xlUp).Row

With ActiveWorkbook.Worksheets("Sheet1").Sort
Range("L2:O" & lr).Select
.SortFields.Clear
.SortFields.Add Key:=Range("N3:N" & lr) _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
.SortFields.Add Key:=Range("M3:M" & lr) _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
.SortFields.Add Key:=Range("L3:L" & lr) _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
.SetRange Range("L2:O" & lr)
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

End Sub

mperrah
05-15-2015, 10:32 AM
jlandrum:
just an observation, do you just want the dates sorted only
- excluding the names from Column A ?
This sort does that,
but if you need the names to stay in the corresponding rows as the dates this would need altering FYI
- mark

JLandrum
05-15-2015, 10:38 AM
I tried the new one, didn't work for me. Thank for all your help

SamT
05-15-2015, 10:40 AM
Thanks for the help but you lost me, Where do I enter this, into the existing code?

You don't

You use it as an Algorithm (Pattern) to write your own code.

Learn by doing. We will help you perfect your code.

Truth be told, I'm working on a chicken coop in the yard and only have time for suggestions. (when I'm on a break. :D

JLandrum
05-15-2015, 10:53 AM
Mark, thanks for your info. I was able to make a few changes and learn what you did. Im pretty decent at excel but VBA is a new area for me. Thanks for the lesson. Im sure I will be back on as im learning more. Thanks again

mperrah
05-15-2015, 11:06 AM
Glad to help.
I'm pushing ten years frequenting this forum. (just figured out how to change my avatar Lol)
Its awesome the kind of help you can find.
I tend to be verbose when asking for help, I think it scares some away.
I have found being open to having a solution that gets there in a way you hadn't thought of is key.
asking what your goal is rather than the method to get there is always a good starting point.

cheers
-mark

mperrah
05-15-2015, 11:07 AM
by the way, what part didn't work on the newer version
the sorting or the array

JLandrum
05-15-2015, 11:15 AM
The array didn't, sorting work after I changed to ascending.

JLandrum
05-15-2015, 11:27 AM
Mark, here something else if you would like to help me. I have a list of names in column A. First M Last, not everyone has a middle. Im trying to just separate the first and last into another column. I used a formula which worked but curious is there a way to sort a cell that has first m last. but needs to be sorted by last. Thanks

mperrah
05-15-2015, 11:50 AM
I would split the names to separate cells and sort after that.
there are plenty of examples how to do that in this forum (I'm not the best at string handling)

found this to start:

Sub SplitNames()
Dim LR As Long, i As Long, X As Variant
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To LR
With Range("A" & i)
X = Split(.Value)
.Offset(, 1).Resize(, UBound(X) + 1).Value = X
End With
Next i
End Sub

mperrah
05-15-2015, 12:00 PM
I see on the array.
If the data in the columns are missing the array fails.
I did a test with all the columns full of sample data, sorry