swap formula

cemery

New Member
Joined
Apr 23, 2009
Messages
3
I am trying to find a formula or something that will let me swap two cell ranges.

I have a starting range with TRUE and FALSE but I am looking for something that will see "if True swap data between G5 and I5 if G5 is greater than I5, otherwise leave as is."

If some one can help me out it will save me alot of time.
 

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,880
You will need a macro for this, but I'm confused by your description, you say your range contains TRUE and FALSE values, but you are wanting to do a greater than comparison, which sounds like you may have numbers in the cells, which is it?
 

cemery

New Member
Joined
Apr 23, 2009
Messages
3
I have 2 columns with TRUE and False followed by a couple Columns of Dimentions as numbers, and i am looking for a way to make 2 of the columns on numbers to switch places if the seccond column of TRUE and False is TRUE and the first column of numbers is greater than the seccond column of numbers.

Exp.
if TRUE Switch these if
▼ ▼▼ is > ▼▼
▼ ▼ ▼
▼ ▼ ▼
TRUE FALSE 10.5 1.75 5.5
FALSE TRUE 6.5 1.75 2.5
TRUE FALSE 10.5 8.25 1.75
TRUE TRUE 4.85 5.55 5.5
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
22,776
The IF function will return ranges, I don't know your data lay-out but maybe something using this kind of principle will work for you

=SUM(IF(A1=1,B1:B3,C1:C3))
 

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,880
Assumming your sample is columns E:I starting at row 5:

Code:
Sub test()
Dim t As Double, c As Range
For Each c In Range("F5:F" & Range("E" & Rows.Count).End(xlUp).Row)
    If c Then
        If c.Offset(, 1) > c.Offset(, 3) Then
            t = c.Offset(, 3)
            c.Offset(, 3) = c.Offset(, 1)
            c.Offset(, 1) = t
        End If
    End If
Next
End Sub
 

Forum statistics

Threads
1,081,563
Messages
5,359,618
Members
400,540
Latest member
JimUSMC

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top