can someone pls take a look at my code

kdt2006

Board Regular
Joined
Apr 6, 2006
Messages
88
I simply want to switch column data between to adjacent columns IF the column A1 contains a "<" character in it.

Code:
Sub test()
Dim tempval As String
Dim cell As Range
    For Each cell In Selection
        If cell.Offset(-2, 0).Value = "<" Then
            tempval = ActiveCell.Value
            ActiveCell.Value = ActiveCell.Offset(-1, 0).Value
            ActiveCell.Offset(-1, 0).Value = tempval
         End If
   Next cell
End Sub

this doesn't work at all. As this is the first stuff I've done in excel programming I'd be extremely thankful :)
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

Datsmart

Well-known Member
Joined
Jun 19, 2003
Messages
7,985
This worked for me when I selected the first column of data.
Code:
Sub TestIt()
Dim tempval As String
Dim c As Range
    
    For Each c In Selection
        If c.Offset(0, -1).Value = "<" Then
            tempval = c.Value
            c.Value = c.Offset(0, 1).Value
            c.Offset(0, 1).Value = tempval
         End If
   Next c

End Sub
You want to avoid using variables of reserved words like CELL.
 

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,454
Hello kdt2006,
The problem with your posted code was with two things.
One was the offset settings. Your use of Offset(-2, 0) is referring an offset of 2 rows up, and not two columns to the left. (The structure for offset is .Offset(Rows, Columns).

The second was your use of ActiveCell in place of cell in your loop.

Your code amended to this will work for what you've described in your text.
Code:
Sub test()
Dim tempval As String
Dim cell As Range
    For Each cell In Selection
        If cell.Offset(, -2).Value = "<" Then
            tempval = cell.Value
            cell.Value = cell.Offset(, -1).Value
            cell.Offset(, -1).Value = tempval
         End If
   Next cell
End Sub

Now, if you know the range you want to loop through ahead of time this can also be done without having to select it manually.
 

kdt2006

Board Regular
Joined
Apr 6, 2006
Messages
88
cheers guys :D

Thanks a lot for sorting this out to both of you. HalfAce, I took your advice and modified it to loop ahead of time. I am really impressed from the speed of response you get from this forum. you all so helpful :)

cheers again

*a very happy person*
 

Forum statistics

Threads
1,136,370
Messages
5,675,369
Members
419,567
Latest member
Naveen27mech

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top