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 Total PG+ SALLY 50 1 50 PG Gunther 100 1 100In 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 Total PG+ SALLY 50 1 50 PG Gunther 100 1 100I *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.

=IF(OR(LEFT(A2,2)="AB",(LEFT(A2,2)="PG"),(LEFT(A2,5)="TOTAL")),"Leave_prod_total_in_DCOL","Move_value_in_Dcol_to_Ecol")

(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).RowBut 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.

-=Ed

Let's start with an explanation of this: lastARow = Range("A" & Rows.Count).End(xlUp).Row

lastARowis 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).Rowtells 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 50I'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:

=IF(OR(LEFT(A2,2)="AB",LEFT(A2,2)="PG",A2="Total"),B2*C2,"")

In the Alert column:

=IF(AND(LEFT(A2,2)<>"AB",LEFT(A2,2)<>"PG",A2<>"Total"),B2*C2,"")

Does that get you what you want?

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.

-=Ed

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 Else 'If not found, Move D to E then Clear D Cells(thing.Row, 5) = Cells(thing.Row, 4) Cells(thing.Row, 4) = "" End If LeaveItAlone: Next End Sub

DerbyDad03

- Thing of beauty.-=Ed

Ask Your Question

Weekly Poll

How long do you think until flying cars or taxis are common?

Discuss in The Lounge

Poll History