VBA Programming help

Hello,

I have an Excel file with 2 worksheets. One containing Order Info called 
"Orders" and another named "Analysis" which contact some fixed data and two 
columns for automatic fill in from the "Orders" sheet.

I would like to create a code or macro when execute, transfer the Order# and 
Prod Start columns from the "Orders" sheet to "Analysis" sheet taken the Qty 
into account. so, if the order# has a quantity of 2, then it will fill in two 
rows for one Order. The sample data is below. Thanks for any help.

Analysis sheet:
Line	Plan Date	  Actual Date       Order#
100	12/9/09		
101	12/10/09		
102	12/10/09		
103	12/11/09		
104	12/14/09		
105	12/14/09		
106	12/15/09		
107	12/18/09		
108	12/29/09		

Orders Sheet: 
Order#	Prod Start	  Qty
167834	1/22/10	  2
172916	2/1/10	  2
169001	2/1/10	  2
169003	2/2/10	  2

RESULT of Analysis sheet:
Line	Plan Date	Actual Date    Order#
100	12/9/09	1/22/10	    167834
101	12/10/09	1/22/10	    167834
102	12/10/09	2/1/10	    172916
103	12/11/09	2/1/10	    172916
104	12/14/09	2/1/10	    169001
105	12/14/09	2/1/10	    169001
106	12/15/09	2/2/10	    169003
107	12/18/09	2/2/10	    169003
108	12/29/09		

0
Utf
2/24/2010 3:31:01 PM
excel.programming 6508 articles. 2 followers. Follow

2 Replies
637 Views

Similar Articles

[PageSpeed] 23

Programming exercise, not tested

Sub t()
    Dim wsa As Worksheet
    Dim wso As Worksheet
    Dim rng As Range
    Dim c As Range
    Dim lrow As Long

    Set wso = Worksheets("orders")
    Set wsa = Worksheets("analysis")

    lrow = wso.Cells(Rows.Count, 3).End(xlUp).Row
    Set rng = Range(wso.Cells(2, 3), wso.Cells(lrow, 3))
    With wsa
        For Each c In rng
            lrow = .Cells(Rows.Count, 3).End(xlUp).Row + 1
            .Cells(lrow, 3).Resize(c, 1) = c.Offset(-1, 0)
            .Cells(lrow, 4).Resize(c, 1) = c.Offset(-2, 0)
        Next c
    End With
End Sub

"Cam" <Cam@discussions.microsoft.com> wrote in message 
news:7E1CA819-4F82-4E94-9CCD-DF84260B6973@microsoft.com...
> Hello,
>
> I have an Excel file with 2 worksheets. One containing Order Info called
> "Orders" and another named "Analysis" which contact some fixed data and 
> two
> columns for automatic fill in from the "Orders" sheet.
>
> I would like to create a code or macro when execute, transfer the Order# 
> and
> Prod Start columns from the "Orders" sheet to "Analysis" sheet taken the 
> Qty
> into account. so, if the order# has a quantity of 2, then it will fill in 
> two
> rows for one Order. The sample data is below. Thanks for any help.
>
> Analysis sheet:
> Line Plan Date   Actual Date       Order#
> 100 12/9/09
> 101 12/10/09
> 102 12/10/09
> 103 12/11/09
> 104 12/14/09
> 105 12/14/09
> 106 12/15/09
> 107 12/18/09
> 108 12/29/09
>
> Orders Sheet:
> Order# Prod Start   Qty
> 167834 1/22/10   2
> 172916 2/1/10   2
> 169001 2/1/10   2
> 169003 2/2/10   2
>
> RESULT of Analysis sheet:
> Line Plan Date Actual Date    Order#
> 100 12/9/09 1/22/10     167834
> 101 12/10/09 1/22/10     167834
> 102 12/10/09 2/1/10     172916
> 103 12/11/09 2/1/10     172916
> 104 12/14/09 2/1/10     169001
> 105 12/14/09 2/1/10     169001
> 106 12/15/09 2/2/10     169003
> 107 12/18/09 2/2/10     169003
> 108 12/29/09
> 


0
KC
2/25/2010 3:06:54 AM
Hi KC,

I tried your code and got this error when I ran the macro.
"Run-time error '1004':
Application-defined or object-defined error"

And the Analysis sheet returns like this:
Line	Plan Date	Actual Date	Order#
100	12/9/09	Qty	
101	12/10/09	Qty	
102	12/10/09		
103	12/11/09		
104	12/14/09		
105	12/14/09		
106	12/15/09		
107	12/18/09		
108	12/29/09		

What is causing this? Thanks

"KC" wrote:

> Programming exercise, not tested
> 
> Sub t()
>     Dim wsa As Worksheet
>     Dim wso As Worksheet
>     Dim rng As Range
>     Dim c As Range
>     Dim lrow As Long
> 
>     Set wso = Worksheets("orders")
>     Set wsa = Worksheets("analysis")
> 
>     lrow = wso.Cells(Rows.Count, 3).End(xlUp).Row
>     Set rng = Range(wso.Cells(2, 3), wso.Cells(lrow, 3))
>     With wsa
>         For Each c In rng
>             lrow = .Cells(Rows.Count, 3).End(xlUp).Row + 1
>             .Cells(lrow, 3).Resize(c, 1) = c.Offset(-1, 0)
>             .Cells(lrow, 4).Resize(c, 1) = c.Offset(-2, 0)
>         Next c
>     End With
> End Sub
> 
> "Cam" <Cam@discussions.microsoft.com> wrote in message 
> news:7E1CA819-4F82-4E94-9CCD-DF84260B6973@microsoft.com...
> > Hello,
> >
> > I have an Excel file with 2 worksheets. One containing Order Info called
> > "Orders" and another named "Analysis" which contact some fixed data and 
> > two
> > columns for automatic fill in from the "Orders" sheet.
> >
> > I would like to create a code or macro when execute, transfer the Order# 
> > and
> > Prod Start columns from the "Orders" sheet to "Analysis" sheet taken the 
> > Qty
> > into account. so, if the order# has a quantity of 2, then it will fill in 
> > two
> > rows for one Order. The sample data is below. Thanks for any help.
> >
> > Analysis sheet:
> > Line Plan Date   Actual Date       Order#
> > 100 12/9/09
> > 101 12/10/09
> > 102 12/10/09
> > 103 12/11/09
> > 104 12/14/09
> > 105 12/14/09
> > 106 12/15/09
> > 107 12/18/09
> > 108 12/29/09
> >
> > Orders Sheet:
> > Order# Prod Start   Qty
> > 167834 1/22/10   2
> > 172916 2/1/10   2
> > 169001 2/1/10   2
> > 169003 2/2/10   2
> >
> > RESULT of Analysis sheet:
> > Line Plan Date Actual Date    Order#
> > 100 12/9/09 1/22/10     167834
> > 101 12/10/09 1/22/10     167834
> > 102 12/10/09 2/1/10     172916
> > 103 12/11/09 2/1/10     172916
> > 104 12/14/09 2/1/10     169001
> > 105 12/14/09 2/1/10     169001
> > 106 12/15/09 2/2/10     169003
> > 107 12/18/09 2/2/10     169003
> > 108 12/29/09
> > 
> 
> 
> .
> 
0
Utf
2/26/2010 5:38:05 PM
Reply:

Similar Artilces:

help with problem formula
this formula worked up to yesterday. i had a power surg and now it onl give me one color. Private Sub LCase(Target.Val Target As Range) Dim myColor As Long If Target.Cells.Count > 1 Then Exit Sub If Intersect ( Target, Me.Range("d:d")) Is Nothing Then Exit Sub Select Case LCase(Target.Value) Case Is = "a" : myColor = 33 Case Is = "b" : myColor = 38 Case Is = "c" : myColor = 20 Case Is = "e" : myColor = 35 Case Is = "f" : myColor = 40 Case Is = "g" : myColor = 8 Case Else myColor =x1None End selection Target.Interior.Col...

Please help #7
I have 2 front-end servers (OWA 2003 servers) and 3 backend servers (Exchange 2003 clustering). -- users have no problem login to Outlook 2003 to access email. -- users using OWA 2003, after keyin the user id & password on the pop-up credential windows. It shows an error page below, this happened even using internal LAN workstations. Thus, nothing to do with firewall. -- the last thing i did was upgraded all Windows 2003 servers to SP1 and all Exchange 2003 to SP1. Pls HELP..... The page cannot be displayed........... ........................................ HTTP 500 - Internal serv...

Help me in Iraq with Outlook on a USB pen-drive
Hello all, I'm in Iraq dealing with a lot of information sent via e- mail, and I have to remain mobile. I have a 1.5 gb USB mini-hard drive and I was hoping to configure Outlook 2002 (as I have my Office 2002 CD that I brought with me) on it and hot swap it from computer to computer as needed accessing my POP account. The problem is that I can't seem to get Outlook installed entirely on the USB Drive... it always seems to install a part onto the C: drive rendering it useless on another computer (and not very stable on this computer which has the full office xp suite on it....

Query Help : accounts which are not members of existing marketing list
Hey all, I have few marketing lists and having lot of accounts as members . Now i am interested to make a new marketing list with new members , which are not member of any of existing marketing list. Is there any way to see which accounts are not member of existing marketing list ? Thanks Aami Sure AAmi, Not within CRM but you can run this in the SQL Query Analyzer to find them: select * from accountbase A left join ListMemberBase L on A.accountid = L.entityID where L.entityID is null HTH, Dave Aami wrote: > Hey all, > > I have few marketing lists and having lot of accounts...

help with the syntax for using range name in a formula?
I have --------=3D+AVERAGE(SMALL(B7:B19,{1,2,3}))-------------. I want to replace B7:B19 with =93Myrange=94 but I can=92t seem to get the syntax right. excel says error in formula. Thks in advance for any help BRC Hi =AVERAGE(SMALL(Myrange,{1,2,3})) have you created Myrange? have you spelt it correctly? -- Regards Roger Govier "BRC" <brc1051-googrps@yahoo.com> wrote in message news:d0e09817-22dc-4c36-b60d-67a397067441@h9g2000yqa.googlegroups.com... > I have --------=+AVERAGE(SMALL(B7:B19,{1,2,3}))-------------. I want > to replace B7:B19 with �Myra...

HELP! I can't go to my desktop, much less get online
I kept losing my connection on my WiFi laptop, so I tried repairing the connection by right clicking the icon. Now when I start my pc it presents me with a place to type a password-and I never have set a password to get into my computer. How do I avoid this because it is my computer and I'd like to use it. My system is Windows XP, IE8.-Thank nYou for your help, I'm typing this on someone elses computer. James Moore Larry wrote: > I kept losing my connection on my WiFi laptop, so I tried repairing > the connection by right clicking the icon. Now when I start my pc it ...

Help with vlookup #3
using 2003. I am looking up a number (stored as a string) on another worksheet and returning another series of numbers (also stored as a string) - ie lookup return 411140 263791-411140E Problem - Excel formats the returned data as: 2.63791E+11. It apparently sees it as a mathmatical subtraction of an exponential number even tho it is a text. It doesn't seem to matter how I format the cell. When I format it as text, the formula does not work. (requires General to execute formula). Executing a "text to columns" does the same thing. I need the returned data to show - 263...

need help working in publisher 07
when i open a blank document it looks very squatty...the measurements are bigger across the top of the page and narrower along the sides of the page, even tho both are in inches. Do you mean your page is landscape instead of portrait? -- JoAnn Paules MVP Microsoft [Publisher] Tech Editor for "Microsoft Publisher 2007 For Dummies" "rebecca" <rebecca@discussions.microsoft.com> wrote in message news:D569716A-8BBE-4FBF-89B6-5DD7E519E1D3@microsoft.com... > when i open a blank document it looks very squatty...the measurements are > bigger across the top of ...

Need help with IF function
I need help writing an IF expression: IF("reference cell is empty", leave blank, otherwise statement) than -- Message posted from http://www.ExcelForum.com =IF(A1="","",Statement -- Message posted from http://www.ExcelForum.com =if(Cell Number="","",Statement) rmo5354 wrote: > *I need help writing an IF expression: > IF("reference cell is empty", leave blank, otherwise statement) > > thanx -- Message posted from http://www.ExcelForum.com To indicate a cell is empty, simply use quotes with nothing in between t...

Help with Calculation #2
Hi, I have the following XLS with 3 sheets: Credits, Inventory & Debits. Credits sheet (Stock sold) columns: 'Product Code', 'Product' (Text field) & 'Quantity' Inventory fields: 'Product' (Text field), 'Company', 'Supplier', 'Product Code', 'Quantity', 'Cost Price' & 'Retail Price'. Debits Sheet (Stock bought) columns: 'Product Code', 'Product' (Text field), 'Quantity' & 'Total cost'. New stock entered as 'Product Code', 'Product', 'Quantity&#...

Automatic Insert Rows VBA
A Very Good Evening All, I have a macro that runs: advanced filter/copy to another place/unique, between two worksheets (Column A in each worksheet being the identfier; worksheet1 is source data,with duplicates of identifiers; worksheet2 is formula driven from the sum of the unique identifiers in Worksheet1) However...I would like the macro to automatically extend the rows in worksheet2, to accommodate the ever increasing rows of worksheet1 Here is my specific code worksheet1 = "Invoice Record" worksheet2 = "General Report" Sub CommandButton1_Click() ' A...

help with If functoin
Hello!. I am a teacher and I have column formed of Roll nos from 1 to 250 of my students. I mark their presence in B column by marking p . I want to asertain in column C if the a particular cell B2 contains p or not. How do I proceed with If funcion? because when I use the following formula =if B1='p',1,0. then error is turned up saying #name? Please help. Also How do I calculate individual student's total presence ? Thank. -- hirendra7158 ------------------------------------------------------------------------ hirendra7158's Profile: http://www.excelforum.com/member.php?a...

Need Formula Help....
Hi: Is there a formula string for something like this.....?: I want a number in a cell when a specific word is typed in another cell, for example, if I wanted the number 50 to appear in C2 if I type the word, say, Airplane, in cell C1? Thanks, pinger You need to define what you want when cell C1 does not contain the word airplane. Enter this formula in C2: =IF(C1="airplane",50,"") If C1 does not contain airplane the formula will leave the cell blank. -- Biff Microsoft Excel MVP "pinger" <pinger@ec.rr.com> wrote in message news:47cf6287$0...

help to compare two list
I have a file with all the open orders for production and I have another file with orders which is already sent to production. I want to find out from the first list, which orders are not send for production. Thanks. ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ Take a look at http://cpearson.com/excel/duplicat.htm In article <itty.vneun@excelforum-nospam.com>, itty <itty.vneun@excelforum-nospam.com> wrote: > I have a file with all the open or...

interest/inflation formula help
Could someone please do this formula for me. �300,000 to work out its worth each year (including interest) Then deduct cost of living % (inflation) each year. So �300,000 (at 6% interest) = �318000 deduct (inflation at say 3%)= �308460 (this would be its net worth say after one year I would use rows A1 to J10 for the actual amounts and would use 2 cells in row 2 for the interest rate and for the inflation figure, this way I could change the rates without altering the formula. I hope this makes sense. thanks There are a heap of financial functions built into excel. Look up PV ...

Help! Exchange 5.5 logon failure
Hi all, We recently upgraded our NT domain to Windows Server 2003 Active Directory (Native Mode). While in the process of upgrading our Exchange 5.5 / SP4 server (running on NT4/SP6) to Exchange Server 2003 (Enterprise Edition, running on Windows Server 2003), we noticed that our users can no longer logon to the server using either: - Outlook Web Access - Microsoft Outlook clients (XP or 2003) Please note that if we supply the credentials of the Exchange 5.5 Service account during authentication (on the pop-up dialog box), the logon process succeeds! In addition, POP3/SMTP clients (e.g. Outlo...

Some macro help
Lets say I have selected the arbitray range of j31:ax44. I want to run some macro code that will hide every column in that selection where the entire selected column range has nothing in it (blank). Data validation may be assigned to the cells, but if nothing has been selected from the drop down list, then those cells are technically blank. There may be data entered elsewhere in the column, but it is outside of the selected range and thus shouldn't be considered for being hidden or not. Can anyone help me with some code to perform this action? TIA, AlanN How about: Option Explicit Sub...

Problems and Problems. Eperts please help
We are running GPS version 9.5 . I think its not supported by support any more so I amnot sure anyone can help me with this. We were running this of a Novell File server. It had Novell 5.0, GPS directory and Pervasive sql 2000i our server crashed. I build a new server and installed novell/ Perasvive sql 2000 and all the clients on the clients machine. Copied the GPS directory and made sure the clients path were pointing correctly to the GPS path. Now every client gets an error. Run betrieve first Nothing has changed the configration is the same and I have installed the betrieve clien...

Using VBA to Print PDF Files
Hi: My customer has PDF spec sheets for their inventory items. When a pick ticket is printed, they wish to print the PDF spec sheets for each item on the pick tickets. I am very comfortable with the report event structure and know exactly how to get to the PDF files using VBA, but I am having a problem getting the docs printed. I've taken two approaches: 1. Drop ACROPDF.dll onto a User Form in the VBA project. To do this, I created a procedure on the form as shown: Public Sub PrintPDF(FileName As String) AcroPDF1.LoadFile (FileName) AcroPDF1.printAll PDFPrinter.Hide End Sub T...

Series overlap/gap width with VBA
Does anyone know how to set the series overlap and gap width using VBA? Have looked through all the regular reference material (including KB search) and can't find any way to refer to these properties programatically. Thanks! K Dales Hi K, The macro recorder is a good way to discover these properties. It produced this code for me. With ActiveChart.ChartGroups(1) .Overlap = 20 .GapWidth = 50 End With Cheers Andy K Dales wrote: > Does anyone know how to set the series overlap and gap > width using VBA? Have looked through all the regular > ...

post to web forms from within a program
Does anybody know of a way to post data collected from within my vc++ app to a web from on my web site? It's just a basic html form that saves the data to a csv file. I've been looking all over the web for articles on this and haven't found anything. I may just not be looking in the right place. thanks in advance, ed You can WinInet to achieve it. MFC provides some relevant classes like CHTTPFile (CInternetFile based) classes. Another alternative might be to use IE/IWebBrowser2 object model to post the data. -- Ajay Kalra [MVP - VC++] ajaykalra@yahoo.com "ed"...

Help with Outlook Add in Manager
I just changed move to a new computer and used Windows Easy Transfer to migrate settings from my old compute to my new computer. Some old junk Outlook 2003 add-ins were migrated to outlook 2007. In 2003 the Add-in manager used to be in the tools-options-other dialog, I can't find it in 2007. Can someone help? Thanks. Tools | Trust Center | Add-ins. --=20 Sue Mosher, Outlook MVP Author of Configuring Microsoft Outlook 2003 http://www.turtleflock.com/olconfig/index.htm and Microsoft Outlook Programming - Jumpstart for=20 Administrators, Power Users, and Developer...

I need help setting up my database
I used Access in the past, so I have a general idea on how to use it, but now I have Microsoft 2007, so it's not as familiar. I currently have an excel document with staff names and drills they have performed. The drills include dates, times, an observer, and if they passed or failed. I had considered doing something where I had a table with all my staff, and then connecting tables for each person to list out the specifics for their drills. I don't know how to link the relationship with that though because the table name was the staff's name, so there wasn't a...

atipical UDT structures VC6 passed from VBA
Hi folks, maybe someone can help me on this item. I'm trying to pass a more complex structure from VBA to VC6 that should look like this: VBA part: Type C_str vv() as Double yy() as Double t as double End Type Type B_str zz() as Long cc() as C_Str End Type Type A_str a as Long b as B_str End Type VC part: struct C_str{ SAFEARRAY* psaVV; SAFEARRAY* psaYY; double t; }; struct B_str{ SAFEARRAY* psaVal; C_Str* c; // ??? ==>this should be a vector of C_str }; struct A_str{ int a; B_str* b; }; Questions: 1. How can I define in structure B that c is a...

VBA for comparing data within a range for Excel 2003
Hello, I have a column of data ( Column AD) where the cells will either be blank or have a numerical value in the cell. There is no pattern to which a cell will have a value or not. I am attempting to write code that will compare the data in the cells. In my example let D = the active cell B = the cell with a numerical value 2 above the active cell C = the cell with a numerical value 1 above the active cell A = the cell with a numerical value 3 above the active cell The logic is IF D >B, AND C>A Then AE = "Uptrend" or IF D<B, C<A, Then AE = "...