Replicating rows and putting in data based on criteria

baggarwal

Well-known Member
Joined
Jul 10, 2002
Messages
591
Hi All:

Here is the file I need your help on:
Book2
ABCDEF
2Current
37000275133000COS:contractclean270
41,510
5
67000285133000COS:contractclean540
70
8
97003565133000COS:contractclean2,050
10417
11Desired
127000275133000COS:contractcleanBudget270
137000275133000COS:contractcleanActual1,510
14
157000285133000COS:contractcleanBudget540
167000285133000COS:contractcleanActual0
17
187003565133000COS:contractcleanBudget2,050
197003565133000COS:contractcleanActual417
Sheet1


I have setup the spreadsheet so that the current section contains the a block of data I need a macro to convert into the desired outcome.

Can you please help me build a macro that will do the following for me:

1. In the current section, if A3 contains an order number (i.e. 700027). I need A3:C3 to be replicated in A4:C4. But I need the macro to do this wherever an internal order number is found. So A6 contains an order number so replicate A6:C6 in A7:C7.

2. Now the next thing I need the macro to do is put in the word "Budget" in cell D12 and the word "actual" in cell D13 if the an internal order number is located in cell A12. I also need the macro to repeat the process in the same way if an order number is found in column.

Thanks so much in advance for your help on this,

Bharat
 

Some videos you may like

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

Tom Urtis

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,225
Here is one way to do that, and there are probably other (more efficient) ways.

Your screen shot shows Budget and Actual in column E but your text says it should go in column D. The following code will put those words in column E to coincide with your screen shot.

The following macro assumes that, according to your example, in column A there is an order number or nothing at all. Therefore, the code searches for anything and does the text changes you indicated.

This worked when I tested it:

Sub CurrentToDesired()
Application.ScreenUpdating = False
Dim NumCol As Range, cell As Range, rToChange As Range
Set NumCol = Range([A3], [A65536].End(xlUp))
For Each cell In NumCol
If cell.Value <> "" Then
If rToChange Is Nothing Then
Set rToChange = cell
Else
Set rToChange = Application.Union(rToChange, cell)
End If
End If
Next
With rToChange
.Offset(1, 0).Value = .Value
.Offset(1, 1).Value = .Offset(0, 1).Value
.Offset(1, 2).Value = .Offset(0, 2).Value
.Offset(0, 4).Value = "Budget"
.Offset(1, 4).Value = "Actual"
End With
Application.ScreenUpdating = True
End Sub
 

baggarwal

Well-known Member
Joined
Jul 10, 2002
Messages
591
Hi Tom:

It looks like the A3:B3 combination is being replicated all the way down. So this means that A7 = A3 and B7 =B3; I want to set up the macro so that A7 = A6 and B7 = B6; A10 = A9 and B10 =B9.

Can you help me fix this?

Bharat
 

bolo

Active Member
Joined
Mar 23, 2002
Messages
423
Hi ihave just modified TU's code. Hope you don't mind!

Sub CurrentToDesired()
Application.ScreenUpdating = False
Dim NumCol As Range, cell As Range, rToChange As Range
Set NumCol = Range([A3], [A65536].End(xlUp))
For Each cell In NumCol
If cell.Value<> "" Then
If rToChange Is Nothing Then
With cell
.Offset(1, 0).Value = .Value
.Offset(1, 1).Value = .Offset(0, 1).Value
.Offset(1, 2).Value = .Offset(0, 2).Value
.Offset(0, 4).Value = "Budget"
.Offset(1, 4).Value = "Actual"
End With
Set rToChange = cell
ElseIf rToChange = cell Then
Set rToChange = Nothing
End If
End If
Next
Application.ScreenUpdating = True
End Sub

This should work. HTH

For me Toms 'Union' code bit wasn't working properly.
This message was edited by bolo on 2002-10-13 11:23
 

baggarwal

Well-known Member
Joined
Jul 10, 2002
Messages
591
Tom, you are the magic man thankyou.

Bolo, thanks so much for your help too.

This issue has been resolved.

BA
 

Watch MrExcel Video

Forum statistics

Threads
1,123,396
Messages
5,601,428
Members
414,450
Latest member
Cassy_sn

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top