Solved If/Then macro to for conditional copy/paste action needed

June 18, 2013 at 07:33:08
Specs: Windows XP
Hi,
I'm looking for a macro to check a column for a value "<D>", if it's found then I want it to copy the text from one cell on the same row to another cell on the same row. I need it to repeat 'til it hits a row with no data in any columns.

I think it should be an IF/Then but I'm that green I could be miles off.

Thanks in anticipation.

DroG


See More: If/Then macro to for conditional copy/paste action needed

Report •

✔ Best Answer
June 18, 2013 at 13:25:58
Try this in a backup copy of your workbook since it's going to delete data and can not be undone:

Sub CreateSubHeadings()
'Determine last Row with data in Column A
 lastRow = Range("A" & Rows.Count).End(xlUp).Row
'Loop through rows, Copy Column D to G at each <D>
   For rw = 1 To lastRow
     If Cells(rw, 1) = "<D>" Then
       Cells(rw, 7) = Cells(rw, 4)
     End If
    Next
'Delete Column A:F
  Range("A:F").EntireColumn.Delete
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.



#1
June 18, 2013 at 08:40:02
Since VBA instructions need to be very specific about which cells it is checking, which cells it is copying and where it is pasted the values, it would help if you were a little more specific than "check a column", "one cell" and "another cell".

That said, why do you need a macro? Can't you just use a IF formula in the cell that you want the final result in?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
June 18, 2013 at 10:48:03
Ah, let me be a bit more specific. I'm working on turning a large multi-sheet spreadsheet of over 10,000 product lines into something our customers can use (losing costs, mark-ups etc., concatenating pack sizes, adding EAN13 barcodes and such-like). Column A has a series of "tags": <A>, <B >, etc., that I can use to identify the row containing the cell with the data I need to move to different column.

For example, if the macro finds <D> at row 25, then I want it to cut (or copy) the text in D25 to G25 (columns A-D are actually redundant after this operation completes and will be deleted)

I hope this makes sense, if not, I'll be glad to have another stab at explaining.

Thanks for taking the time to mull over my problem.

DroG


Report •

#3
June 18, 2013 at 11:15:56
You may need to provide more detail, but based solely on what you've described, put this formula in G1 and drag it down to the bottom of your data.

It will pull the value from Dx into Gx whenever Ax contains <D>. Otherwise Gx will remain empty.

=IF(A1="<D>",D1,"")

If you have more than one code to search for and perhaps a different cell to pull data from, you can use a Nested IF function:

=IF(A1="<D>",D1,IF(A1="<A>",C1,""))

If I am still misunderstanding your goal, perhaps assuming that it's more simple than it actually is, please try again.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
June 18, 2013 at 12:43:27
Perhaps if I give you snapshot of what I want to do; below we have A-G (C,E &F are empty, we can ignore them) I need to find the brand names (Aspall, Barleycup & Belvoir in the example) using the <D> tag then copy the brand names over to column G to be sub-heads in the final spreadsheet (columns A-F will then be deleted, so as to present the data in a more easily-used fashion)
A      B C      D                       E  F               G 
<E>		BRANDED GOODS ALPHABETICALLY BY SUPPLIER			
<D>		"ASPALL"			


	T	BALSAMIC VINEGAR			Aspall Org. Balsamic Vinegar
<A>	T	CYDER VINEGAR			        Aspall Org. Cyder Vinegar
<A>	T				                Aspall Org. Cyder Vinegar
<B>	T				                Aspall Org. Cyder Vinegar
<B>	T	RED WINE VINEGAR		        Aspall Org. Red Wine Vinegar
<B>	T	WHITE WINE VINEGAR			Aspall Org. White Wine Vinegar
<C>					
<D>		"BARLEYCUP"			


	T	BARLEYCUP NATURAL			Organic Barleycup
<C>					
<D>		"BELVOIR"			
<F>	T	CORDIALS			
<B>	T	BLACKCURRANT			         Belvoir Org. Blackcurrant Cordial
<B>	T	ELDERFLOWER			         Belvoir Org. Elderflower Cordial

Before I run the macro, the spread is a copy of a 'live' spreadsheet containing all our product details and pricing formulae, constantly being updated as there are frequent fluctations in market prices. I'm aiming for a simple "take the copy of the spread that we use for import into the database (4 times a year), Run the macro that turns it into a simpler, less data-dense spread that our customers can trim down to a stock and order sheet or import the EAN13 codes into their EPOS system or whatever they want.

Hope this is clearer, if not, just holler.

Thanks again for your help.

DroG


Report •

#5
June 18, 2013 at 13:25:58
✔ Best Answer
Try this in a backup copy of your workbook since it's going to delete data and can not be undone:

Sub CreateSubHeadings()
'Determine last Row with data in Column A
 lastRow = Range("A" & Rows.Count).End(xlUp).Row
'Loop through rows, Copy Column D to G at each <D>
   For rw = 1 To lastRow
     If Cells(rw, 1) = "<D>" Then
       Cells(rw, 7) = Cells(rw, 4)
     End If
    Next
'Delete Column A:F
  Range("A:F").EntireColumn.Delete
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#6
June 20, 2013 at 01:53:53
Thanks for that. Was AFK for a day, there. Just checked that out by adding relevant section into my ever-growing "one-click-does-all" macro, and all seems to be fine.

Thanks again, will come back if I hit any more problems.

DroG


Report •

#7
June 25, 2013 at 08:41:04
I presume that, whilst copying the contents of the tagged cell, we could also set attributes such as font/bold/italic? [just trying to get clever now]

DroG


Report •

#8
June 25, 2013 at 10:19:23
Yes, that could be done.

Try recording a macro while you set the attributes and then "clean up" the recorded code to just those attributes that are needed.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Ask Question