Conditionally Move cell values with VBA

Excel Excel 2007
May 21, 2010 at 05:38:55
Specs: Windows XP
I can't seem to bust through this.

I have a range of data, I would like to search through one column of data, and move the contents of the cells in one column to another column conditionally, if a third column's contents meets certain criteria. e.g.

Description	Happy	Times	Total	Alert
AB+ HOGAN	400	4	1600	
PG+ SAM	50	2	100	
Lucite Counter	1500	1	1500	
PG+ SALLY     	50	1	50	
PG Gunther	100	1	100	

In the sample data above, if the cells in the 'Description' Column - (Column A say) do not start with AB or PG, or the word 'Total' then move the Contents (a formula) of column 'Total' ( Column D ) to Column 'Alert' ( Column E )

As you can see in the sample data, there are multiple ranges, so the significant data is Non-Contiguous. The result would be:

Description	Happy	Times	Total	Alert
AB+ HOGAN	400	4	1600	
PG+ SAM	50	2	100	
Lucite Counter	1500	1		1500
PG+ SALLY     	50	1	50	
PG Gunther	100	1	100	

I *think* I should be using the OR function to determine AB, PG or Total, nested in an IF statement, as in my struggles at a formula below, but I dont know how to move the contents, which is a formula, or how to loop through the range... and then do it in VBA.


(the text strings "Leave_prod... " and "Move_valu... " are just placeholders)

For the Looping - I have seen examples from Humar and DerbyDad03 that show me how to determine the end of a column's significant data

Dim lastARow, lastDRow As Integer
lastARow = Range("A" & Rows.Count).End(xlUp).Row
lastDRow = Range("D" & Rows.Count).End(xlUp).Row

But my head started hurting...I am not there yet... I need to do this a few more times to get it. BTW: Any good books to read on this DerbyDad03? Humar?

Right Track? Wrong Track? Give up and open a fish & chips store in Orillia?

Any help is appreciated.


See More: Conditionally Move cell values with VBA

Report •

May 21, 2010 at 06:38:23
Let's start with an explanation of this:

lastARow = Range("A" & Rows.Count).End(xlUp).Row

lastARow is just a variable name. It could be X or Fred or intRowLength or any other non-reserved word.

Range("A" & Rows.Count) is equivalent to Range("A65536") in Excel versions earlier than 2007 and Range("A1048576") in 2007.

Using Rows.Count makes the syntax compatible with all versions of Excel since it lets VBA determine how many rows are in the spreadsheet.

.End(xlUp).Row tells VBA to go to the end of the range and look up until it finds data in a cell in that column. When it finds data in a cell, it returns that Row number.

That said, I'm slightly confused by your data table. Is there a reason that you don't have 200 in the Total column for this value:

PG+ SAM	50

I'll assume it's a oversight.

OK, now back to what you are trying to do.

I'm not sure why you want to mix VBA with formulas. I think you can do it without any VBA.

I can reproduce your table above (with the addition of the 200 mentioned earlier) with these 2 formulas:

In the Total column:


In the Alert column:


Does that get you what you want?

Report •

May 21, 2010 at 07:11:08
Hi DerbyDad03 !

Yes the missing 200 was an oversight...

I included the formula to clearly identify what I am trying to do, and yes your formulas work perfectly.

The solution I am working on is pretty large. Daily, a new spreadsheet is generated from a sql stored proceedure. It contains 8 or 9 spreadsheets and is very , well... 'raw'.

Since she needs to present and share the 'reports' with others, she has been spending 30 to 40 minutes each day, with each new 'data dump', formating the spreadsheet. I have been able to do all kinds of easy stuff in VBA, such as autowidth columns, highlight items that are overdue, bolding etc. Even subtotal varying length columns of data, and most recently ( With your Help! ) replace 'raw data' with a formula, so the spreadsheet can be more functional.

So - long winded way of saying - that I've come so far with VBA, that I would be disappointed to have to say - oh and these last two things, just 'do this'. I would love to be able to finish this off in VBA.


Report •

May 21, 2010 at 10:36:59
OK, if you want to use VBA (which is fine) perhaps the Like operator would work for you.

In the VBA editor, put your cursor on Like and hit F1 for an explanation of how to use it.

Option Explicit
Sub MoveValues()
Dim thing As Range
Dim lastARow As Integer
'Determine last Cell In Column A
 lastARow = Range("A" & Rows.Count).End(xlUp).Row
'Loop through Column A
  For Each thing In Sheets(1).Range("A2:A" & lastARow)
'Look for AB, PG and Total
   If thing.Value Like "*AB*" Or _
      thing.Value Like "*PG*" Or _
      thing.Value Like "*Total*" Then
'If found, Do Nothing
       GoTo LeaveItAlone
'If not found, Move D to E then Clear D
      Cells(thing.Row, 5) = Cells(thing.Row, 4)
      Cells(thing.Row, 4) = ""
   End If
End Sub

Report •

Related Solutions

May 21, 2010 at 13:50:28
- Thing of beauty.


Report •

Ask Question