Move a cell based on value

racerx03

New Member
Joined
Mar 27, 2008
Messages
5
I have a list of data in a column the includes numbers and text, I want to move the numbers greater than 22000 to the left column (A)

Example Data in Column B
<table style="width: 65pt; border-collapse: collapse;" x:str="" border="0" cellpadding="0" cellspacing="0" width="87"><colgroup><col style="width: 65pt;" width="87"></colgroup><tbody><tr style="height: 12.75pt;" height="17"><td style="border: medium none rgb(212, 208, 200); width: 65pt; height: 12.75pt; background-color: transparent;" x:num="" align="right" height="17" width="87">240010</td></tr><tr style="height: 12.75pt;" height="17"><td style="border: medium none rgb(212, 208, 200); height: 12.75pt; background-color: transparent;" height="17">FS</td></tr><tr style="height: 12.75pt;" height="17"><td style="border: medium none rgb(212, 208, 200); height: 12.75pt; background-color: transparent;" height="17">
</td></tr><tr style="height: 12.75pt;" height="17"><td style="border: medium none rgb(212, 208, 200); height: 12.75pt; background-color: transparent;" x:num="" align="right" height="17">220014</td></tr><tr style="height: 12.75pt;" height="17"><td style="border: medium none rgb(212, 208, 200); height: 12.75pt; background-color: transparent;" height="17">2005 FTRS</td></tr><tr style="height: 12.75pt;" height="17"><td style="border: medium none rgb(212, 208, 200); height: 12.75pt; background-color: transparent;" height="17">
</td></tr><tr style="height: 12.75pt;" height="17"><td style="border: medium none rgb(212, 208, 200); height: 12.75pt; background-color: transparent;" x:num="" align="right" height="17">240014</td></tr><tr style="height: 12.75pt;" height="17"><td style="border: medium none rgb(212, 208, 200); height: 12.75pt; background-color: transparent;" height="17">2005 FTRS</td></tr><tr style="height: 12.75pt;" height="17"><td style="border: medium none rgb(212, 208, 200); height: 12.75pt; background-color: transparent;" height="17">
</td></tr><tr style="height: 12.75pt;" height="17"><td style="border: medium none rgb(212, 208, 200); height: 12.75pt; background-color: transparent;" x:num="" align="right" height="17">220015</td></tr><tr style="height: 12.75pt;" height="17"><td style="border: medium none rgb(212, 208, 200); height: 12.75pt; background-color: transparent;" height="17">F/S- R S</td></tr><tr style="height: 12.75pt;" height="17"><td style="border: medium none rgb(212, 208, 200); height: 12.75pt; background-color: transparent;" height="17">
</td></tr><tr style="height: 12.75pt;" height="17"><td style="border: medium none rgb(212, 208, 200); height: 12.75pt; background-color: transparent;" height="17">006 210</td></tr><tr style="height: 12.75pt;" height="17"><td style="border: medium none rgb(212, 208, 200); height: 12.75pt; background-color: transparent;" height="17">F/S- M S, JR.</td></tr><tr style="height: 12.75pt;" height="17"><td style="border: medium none rgb(212, 208, 200); height: 12.75pt; background-color: transparent;" height="17">
</td></tr><tr style="height: 12.75pt;" height="17"><td style="border: medium none rgb(212, 208, 200); height: 12.75pt; background-color: transparent;" x:num="" align="right" height="17">220013</td></tr><tr style="height: 12.75pt;" height="17"><td style="border: medium none rgb(212, 208, 200); height: 12.75pt; background-color: transparent;" height="17">F/S- D S</td></tr></tbody></table>

OK, I have some simple code I reused from MS examples, this will change each of the cells (greater then 220000) yellow, but, how do I loop through the selection (about 400 rows) and move the resulting highlighted cells 1 column left?

Code:
Sub Check_Values_1()
   Dim CurCell As Object
   For Each CurCell In Selection
      If Val(CurCell.Text) > 220000 Then CurCell.Interior.ColorIndex = 6
      Next
End Sub
 
Last edited by a moderator:

Smitty

MrExcel MVP
Joined
May 15, 2003
Messages
29,536
Welcome to the Board!

How about:

Code:
If Val(CurCell.Text) > 220000 Then CurCell.Cut CurCell.Offset.(,1)
Hope that helps,
 

sous2817

Well-known Member
Joined
Feb 22, 2008
Messages
2,276
Couldn't you use an "If" formula

=IF(B1>22000,B1,"")

Just put it in the cell where you want B1 to show up and drag it down to the end.
 

schielrn

Well-known Member
Joined
Apr 4, 2007
Messages
6,934
If you want to move them and clear the contents of the original spot try this:

Code:
Sub Check_Values_1()
   Dim CurCell As Range
   For Each CurCell In Selection
      If CurCell.value > 220000 Then
         CurCell.offset(0,-1).value = curcell.value
         curcell.clearcontents
      End If
   Next
End Sub
Hope that helps.
 

racerx03

New Member
Joined
Mar 27, 2008
Messages
5
schielrn, sous2817, Smitty - thanks! that did it with a minor change

Sub Check_Values_1()
Dim CurCell As Range
For Each CurCell In Selection
If Val(CurCell.Text) > 220000 Then
CurCell.Offset(0, -1).Value = CurCell.Value
CurCell.ClearContents
End If
Next
End Sub

When I used,
CurCell.value > 220000

It moves some of the text (unless it is proceeded by a number like, 2005 FTR) as well as the intended numbers? I'm not sure why?
thanks for the help
 

Forum statistics

Threads
1,081,793
Messages
5,361,316
Members
400,625
Latest member
Asraful Alam

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top