problem moving columns around

TheMSsForum.com: The Microsoft Software Forum

All,

I need to re-order some columns in Worksheet. Here is the code which
works just fine (to a point):

.....
For fldCnt = 0 To numOfFlds - 1
' Check every name in the first row
'
For colNum = 1 To numOfCols
fldVal = wks.Cells(rowFirst, colNum).Value
If fldList(fldCnt) = fldVal Then
If colNum <> (found + 1) Then
' Column needs to be moved to 'curPos'
'
Set rngColToMove = wks.Range(wks.Cells(rowFirst, colNum),
wks.Cells(rowLast, colNum))
Set rngDst = wks.Range(wks.Cells(rowFirst, curPos),
wks.Cells(rowLast, curPos))
Range(rngColToMove.Address).Select
Selection.Cut
Range(rngDst.Address).Select
Selection.Insert (xlShiftToRight)
curPos = curPos + 1
End If
found = found + 1
Exit For
End If
Next colNum
Next fldCnt
.....

The problem is that when I move the column the contents of
"wks.Cells(rowFirst, colNum)" does not change to reflect the column's
movement.. In other words, if the original column order is a,b,c,d and
I change it to d,a,b,c what I see in the first row, programmatically,
is still a,b,c,d. It is as though I need some sore of "refresh" after
I do the cut/paste. I tried making it a Range object and then
resetting it after the cut/paste, but the order was still a,b,c,d.

Any ideas?

TIA,

Bill
Top

Re: problem moving columns around by Mark

Mark
Sat Mar 15 18:37:06 CDT 2008

Would it be easier to use your macro recorder and copy the columns as you
need and in the order you want them in way out on the spreadsheet (like out
to columns AA, AB, AC, AD, etc...

Then you can just delete the original columns (and all blank columns) to
shift the corrected layout as needed.

Mark

"bill" <wgrigg@draper.com> wrote in message
news:a46cac07-c9c5-4db9-a500-012995486dd2@8g2000hse.googlegroups.com...
> All,
>
> I need to re-order some columns in Worksheet. Here is the code which
> works just fine (to a point):
>
> .....
> For fldCnt = 0 To numOfFlds - 1
> ' Check every name in the first row
> '
> For colNum = 1 To numOfCols
> fldVal = wks.Cells(rowFirst, colNum).Value
> If fldList(fldCnt) = fldVal Then
> If colNum <> (found + 1) Then
> ' Column needs to be moved to 'curPos'
> '
> Set rngColToMove = wks.Range(wks.Cells(rowFirst, colNum),
> wks.Cells(rowLast, colNum))
> Set rngDst = wks.Range(wks.Cells(rowFirst, curPos),
> wks.Cells(rowLast, curPos))
> Range(rngColToMove.Address).Select
> Selection.Cut
> Range(rngDst.Address).Select
> Selection.Insert (xlShiftToRight)
> curPos = curPos + 1
> End If
> found = found + 1
> Exit For
> End If
> Next colNum
> Next fldCnt
> .....
>
> The problem is that when I move the column the contents of
> "wks.Cells(rowFirst, colNum)" does not change to reflect the column's
> movement.. In other words, if the original column order is a,b,c,d and
> I change it to d,a,b,c what I see in the first row, programmatically,
> is still a,b,c,d. It is as though I need some sore of "refresh" after
> I do the cut/paste. I tried making it a Range object and then
> resetting it after the cut/paste, but the order was still a,b,c,d.
>
> Any ideas?
>
> TIA,
>
> Bill

Top

Re: problem moving columns around by bill

bill
Sun Mar 16 06:26:50 CDT 2008

On Mar 15, 7:37=A0pm, "Mark Ivey" <wmivey6311NOS...@hotmail.com> wrote:
> Would it be easier to use your macro recorder and copy the columns as you
> need and in the order you want them in way out on the spreadsheet (like ou=
t
> to columns AA, AB, AC, AD, etc...
>
> Then you can just delete the original columns (and all blank columns) to
> shift the corrected layout as needed.
>
> Mark
>
> "bill" <wgr...@draper.com> wrote in message
>
> news:a46cac07-c9c5-4db9-a500-012995486dd2@8g2000hse.googlegroups.com...
>
>
>
> > All,
>
> > I need to re-order some columns in Worksheet. Here is the code which
> > works just fine (to a point):
>
> > .....
> > =A0 =A0For fldCnt =3D 0 To numOfFlds - 1
> > =A0 =A0 =A0' Check every name in the first row
> > =A0 =A0 =A0'
> > =A0 =A0 =A0For colNum =3D 1 To numOfCols
> > =A0 =A0 =A0 =A0fldVal =3D wks.Cells(rowFirst, colNum).Value
> > =A0 =A0 =A0 =A0If fldList(fldCnt) =3D fldVal Then
> > =A0 =A0 =A0 =A0 =A0If colNum <> (found + 1) Then
> > =A0 =A0 =A0 =A0 =A0 =A0' Column needs to be moved to 'curPos'
> > =A0 =A0 =A0 =A0 =A0 =A0'
> > =A0 =A0 =A0 =A0 =A0 =A0Set rngColToMove =3D wks.Range(wks.Cells(rowFirst=
, colNum),
> > wks.Cells(rowLast, colNum))
> > =A0 =A0 =A0 =A0 =A0 =A0Set rngDst =3D wks.Range(wks.Cells(rowFirst, curP=
os),
> > wks.Cells(rowLast, curPos))
> > =A0 =A0 =A0 =A0 =A0 =A0Range(rngColToMove.Address).Select
> > =A0 =A0 =A0 =A0 =A0 =A0Selection.Cut
> > =A0 =A0 =A0 =A0 =A0 =A0Range(rngDst.Address).Select
> > =A0 =A0 =A0 =A0 =A0 =A0Selection.Insert (xlShiftToRight)
> > =A0 =A0 =A0 =A0 =A0 =A0curPos =3D curPos + 1
> > =A0 =A0 =A0 =A0 =A0End If
> > =A0 =A0 =A0 =A0 =A0found =3D found + 1
> > =A0 =A0 =A0 =A0 =A0Exit For
> > =A0 =A0 =A0 =A0End If
> > =A0 =A0 =A0Next colNum
> > =A0 =A0Next fldCnt
> > .....
>
> > The problem is that when I move the column the contents of
> > "wks.Cells(rowFirst, colNum)" does not change to reflect the column's
> > movement.. In other words, if the original column order is a,b,c,d and
> > I change it to d,a,b,c what I see in the first row, programmatically,
> > is still a,b,c,d. It is as though I need some sore of "refresh" after
> > I do the cut/paste. I tried making it a Range object and then
> > resetting it after the cut/paste, but the order was still a,b,c,d.
>
> > Any ideas?
>
> > TIA,
>
> > Bill- Hide quoted text -
>
> - Show quoted text -

Thanks for the tip. I also decided that was the best way.

Bill
Top

Re: problem moving columns around by Mark

Mark
Sun Mar 16 06:41:28 CDT 2008

No problem, glad to help....

I have had to do similar sorting in my own data many times before, and I
have found it is easier just to do it in the aforementioned method than to
work in the same region as the original data.

Mark


"bill" <wgrigg@draper.com> wrote in message
news:9dfbdee3-6195-4935-ad62-2f5d4cc273fa@v3g2000hsc.googlegroups.com...
> On Mar 15, 7:37 pm, "Mark Ivey" <wmivey6311NOS...@hotmail.com> wrote:
>> Would it be easier to use your macro recorder and copy the columns as you
>> need and in the order you want them in way out on the spreadsheet (like
>> out
>> to columns AA, AB, AC, AD, etc...
>>
>> Then you can just delete the original columns (and all blank columns) to
>> shift the corrected layout as needed.
>>
>> Mark
>>
>> "bill" <wgr...@draper.com> wrote in message
>>
>> news:a46cac07-c9c5-4db9-a500-012995486dd2@8g2000hse.googlegroups.com...
>>
>>
>>
>> > All,
>>
>> > I need to re-order some columns in Worksheet. Here is the code which
>> > works just fine (to a point):
>>
>> > .....
>> > For fldCnt = 0 To numOfFlds - 1
>> > ' Check every name in the first row
>> > '
>> > For colNum = 1 To numOfCols
>> > fldVal = wks.Cells(rowFirst, colNum).Value
>> > If fldList(fldCnt) = fldVal Then
>> > If colNum <> (found + 1) Then
>> > ' Column needs to be moved to 'curPos'
>> > '
>> > Set rngColToMove = wks.Range(wks.Cells(rowFirst, colNum),
>> > wks.Cells(rowLast, colNum))
>> > Set rngDst = wks.Range(wks.Cells(rowFirst, curPos),
>> > wks.Cells(rowLast, curPos))
>> > Range(rngColToMove.Address).Select
>> > Selection.Cut
>> > Range(rngDst.Address).Select
>> > Selection.Insert (xlShiftToRight)
>> > curPos = curPos + 1
>> > End If
>> > found = found + 1
>> > Exit For
>> > End If
>> > Next colNum
>> > Next fldCnt
>> > .....
>>
>> > The problem is that when I move the column the contents of
>> > "wks.Cells(rowFirst, colNum)" does not change to reflect the column's
>> > movement.. In other words, if the original column order is a,b,c,d and
>> > I change it to d,a,b,c what I see in the first row, programmatically,
>> > is still a,b,c,d. It is as though I need some sore of "refresh" after
>> > I do the cut/paste. I tried making it a Range object and then
>> > resetting it after the cut/paste, but the order was still a,b,c,d.
>>
>> > Any ideas?
>>
>> > TIA,
>>
>> > Bill- Hide quoted text -
>>
>> - Show quoted text -
>
> Thanks for the tip. I also decided that was the best way.
>
> Bill

Top