adding data from two colunms

TheMSsForum.com: The Microsoft Software Forum

The MSS Forum ‹ Excel Programming
- Archive
  - Biz
  - MCSE
  - CRM
  - Drivers
  - Framework
  - ADO
  - ASP
  - Compact
  - Forms
  - Dotnet
  - C#
  - VB
  - FontpageGen
  - Excel
  - WorkSheet
  - Exchange
  - Setup
  - Fox
  - Fontpage
  - ASP
  - IIS
  - Entourage
  - Money
  - Messanger
  - PocketPC
  - Powerpoint
  - Project
  - Publisher
  - Excel
  - VB
  - Security
  - Portal
  - Services
  - SQLServerDev
  - SVCS
  - SQLServer
  - VB
  - VC
  - MFC
  - ExcelGen

- Previous
  - 1
    - Need help to read data from a text file Hi, I have a data which is poorly organized on a text file (it was taken out from a very old computer when we did lab experiments). However, I can you this. There is a 2 conseqent rows of data (2nd row of data is not important) in which I only want the code to read the 5th number of 1st row and insert that value in excel. The value consists of about 2 digits and 12 decimal places. I would appreciate if anyone can make a code to read from the text file and copy the 5th number from this file and insert it in excel vertically. I don't have a very good VB experience but i have come across some good VB programmers and I would like to give them the opportunity to solve this problem. I can send this text file to you if you want to try. Please give me your email address and I'll send it to you. Mahadevan Swamy Tag: adding data from two colunms Tag: 997574
  - 2
    - How to Display UserForm on Full Screen. Hi, I wanto to make a function to display UserForm on Full Screen, when loads. What would be the size of the form, if any one have the different screen resolution then how we can handle this..... 1024X768 or 800x600 I want to open the same size of UserForm on the above resolution. is it possible. pls tell me the 1024 x 768 first. what would be the size of the Form. Regards. Shahzad Tag: adding data from two colunms Tag: 997569
  - 3
    - Range.find performance my project involves parsing large number of excel files. and looking for keywords in each file, I am wondering if this could be a performace issue, to call the Find function requently. the other approach i've seen is to get the contents into an array ans work off that array to gain efficiency. any experience to share? thanks. -- epr Tag: adding data from two colunms Tag: 997568
  - 4
    - Find and replace cell values Macro Hello, I am looking for some help creating a macro for find/replace. What I am looking to do is look at a cell say A1, take the values in the cell, and use it to replace "X" in a vlookup. For example A1 = 5 replace =vlookup(c1,"Sheet X",A:D,2,false) Thank you in advance. Tag: adding data from two colunms Tag: 997558
  - 5
    - Stupid array question Can someone please point out what is wrong with the following: Sub Test() Dim asdf() As Integer, i As Integer For i = 0 To 5 asdf(i) = i Next i End Sub doesn't work even if Option Base 0 were to be included, though obviously isn't relevant to my problem Whereas it likes: Sub Test_() Dim asdf(0 To 5) As Integer, i As Integer For i = 0 To 5 asdf(i) = i Next i End Sub what i'd actually like to do is loop through a named range, and assign to the array only cells that meet a certain criteria, and join those values to output to another cell i.e. Sub Test() 'Two worksheets on workbook, as in default 'Sheet1 contains has a named range, "MyNamedRange", 3 by 1 in size 'cell A1 has text "apple" 'cell A2 is blank 'cell A3 has text "banana" Dim asdf() As String, i As Integer, arr_size As Integer arr_size = 0 For i = 0 To 2 'the named is range is 5x1, say a1:a5 'If Len(Workbooks("Book1").Sheet1.Range("mynamedrange").Cells(i, 1).Value) > 1 Then If Len(Sheet1.Range("mynamedrange").Cells(i + 1).Value) > 0 Then asdf(arr_size) = Sheet1.Range("MyNamedRange").Cells(i + 1).Value arr_size = arr_size + 1 End If Next i ' would like array to be ("apple", "banana", "") at this point ' and at this point arr_size = 2 ReDim Preserve asdf(0 To arr_size - 1) 'resize Worksheets("Sheet2").Cells(5, 5).Value = Join(asdf, ", ") & "." End Sub note this sub doesn't actually work, to run it, replace: "Dim asdf() As String" with "Dim asdf(0 to 2) As String" and comment the Redim Preserve line as array has been dimensioned. This is really bugging me, and i know it's something stupid but just can't see it! Thanks in advance Tag: adding data from two colunms Tag: 997555
  - 6
    - ComboBox displays last selection I have a combo box on a user form. The first time I run it, it works fine. The second time I run it. It has the previous selection in it. How can I have it come up blank? Thank You, PW Tag: adding data from two colunms Tag: 997554
  - 7
    - Formulas into VBA I have a spreadsheet with approximately 90 spreadsheets that my boss would like me to take the formulas out of the cells and put them into VBA code (I have no idea why). I am somewhat at a loss. Does anybody know of a way to take a worksheet and move it into VBA without doing it one thing at a time? -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.aspx/excel-programming/200807/1 Tag: adding data from two colunms Tag: 997545
  - 8
    - Togglebutton Tag: adding data from two colunms Tag: 997542
  - 9
    - Togglebutton Is there a code that turns a toggle button on and off? Thanks Tag: adding data from two colunms Tag: 997541
  - 10
    - Deploying custom toolbar and custom icons I'm working on a custom toolbar for a workbook I'm building (plenty of buttons, including custom icons). We've decided to stick the workbook on the network and have other folks access it as well. What is the preferred method for ensuring the toolbar is available for all users when they open the workbook? It's been years since I had to deploy a custom toolbar, and that was in Word97 via a template- whereas this is in Excel2003, and it is an active workbook (not a template). I googled and found a lot about building a custom toolbar via code, but there were references to issues with custom icons, and I'm hoping there is a way to just keep the toolbar connected to the workbook without having to generate it via code for each user. Thanks for any advice, Keith Tag: adding data from two colunms Tag: 997534
  - 11
    - Populate Deposit Record from another worksheet I'm way over my head here! I have about 50 worksheets. Part of every sheet is a simple payment record. Every worksheet is the set up exactly alike. In all worksheets D6 = FName, N6=LName, D54:D95=DatePaid,G54:G95=Tuition, J54:J95=Fee, K54:K95=Gift. I would like to populate a deposit record on another worksheet (DepositRecord) with the information above. Each payment would go on the next empty row in the deposit record. On deposit day a total amount would be calculated on the DepositRecord (this would be done manually). After the deposit is made, new records would be added below for the next deposit day. Hope this is clear enough to get me started Tag: adding data from two colunms Tag: 997533
  - 12
    - Reading field values selected by a Page variable I have a pivottable with row, column (District) and page (Region) variables. There are 80 districts available when Region is set to ALL, but only a few for each Region. I need Basic code that can read the District values displayed to the user depending on the Region selected. The District field is already set to ascending sorting. The code: Set fld = AllDistrictsCalc.PivotTables("PivotTable5").VisibleFields("District1") NumDistricts = fld.PivotItems.Count always returns "80". The code: For iItem = 1 To NumDistricts flag = fld.PivotItems(iItem).Visible Next iItem tells me all values are visible. Can anyone point me in the right direction? Am I simply using the wrong Property? Tag: adding data from two colunms Tag: 997520
  - 13
    - Run-time error 1004 since upgrading from Excel 2003 to 2007. I have a workbook with many, many macros. Since I upgraded to Excel 2007, I am receiving the following error message with one of my macros: "Run-time error 1004. The number must be between 1 and 32767. Try again by entering a number in this range" Here is a sample of the code. The statement "Application.MaxIterations = olditer" is the line that is being highliged by Visual Basic. skipcalc4: If Worksheets("Workarea").Cells(43, 7) < 0 Then Worksheets("NPV Model Workarea").Cells(43, 7) = 0 End If Application.MaxIterations = olditer Application.MaxChange = oldchange ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True Worksheets("Workarea").Visible = False End Sub Tag: adding data from two colunms Tag: 997514
  - 14
    - Radio Button Size and Print Features Is there a way to resize the 'Control' radio button? It is rather small (as compared with the 'Form' radio button). Also, the 'Control' radio button - when printed - is surrounded by a dark box ... ugly ... is there any way to change or remove this feature? I like the functionality of the 'Control' button, but the 'Forms' button is much more 'GUI and printer friendly' ... in my opinion. Thanks. Tag: adding data from two colunms Tag: 997513
  - 15
    - Fill down two columns...C & D The following code works, but need it to work for not column C&D. Any clues Sub Fill_down_C() Application.DisplayAlerts = False Dim wks As Worksheet Dim Rng As Range Dim LastRow As Long Dim Col As Long Set wks = ActiveSheet With wks Col = .Range("C1").Column Set Rng = .UsedRange LastRow = .Cells.SpecialCells(xlCellTypeLastCell).Row Set Rng = Nothing On Error Resume Next Set Rng = .Range(.Cells(2, Col), .Cells(LastRow, Col)) _ .Cells.SpecialCells(xlCellTypeBlanks) On Error GoTo 0 If Rng Is Nothing Then Exit Sub Else Rng.FormulaR1C1 = "=R[-1]C" End If With .Cells(1, Col).EntireColumn .Value = .Value End With End With Application.DisplayAlerts = True End Sub Tag: adding data from two colunms Tag: 997508
  - 16
    - Distinguishing 0 formatted as "na" and as 0 With this standard format, a zero will appear as as 0: #,##0;-#,##0 However, you can set zero to appear as "na" using the following format: #,##0;-#,##0;"na" My question is whether it is possible to test whether the cell is showing a 0 as "na", even though the underlying value is still a 0. For example, I cannot use IF(A10="na",... because the cell A10 actually still contains a 0; it just looks like "na" because of the formatting. Tag: adding data from two colunms Tag: 997502
  - 17
    - Set StartRange and End Range I am trying to get Excel to select all the cells between Cell A3 and the last used cell in Column A. Code below: Dim StartCell, EndCell As Range Set StartCell = Range("A3") Set EndCell = Cells(Rows.Count, "A").End(xlUp) Range("StartCell:EndCell").Select What am I doing wrong? Thanks, Ryan--- -- RyGuy Tag: adding data from two colunms Tag: 997497
  - 18
    - Error using..... .Value = .Value Running the following two codes... About halfway running through each page in my workbook, it stops and gives me an error message on the following line. Any clues as to why it completes this function on about 15 sheets, then stops halfway with an error? Is there a better solution? (Should I combine both columns to fill simultaneously, change the line to another code, ???) .Value = .Value Sub Fill_down_C() Application.DisplayAlerts = False Dim wks As Worksheet Dim Rng As Range Dim LastRow As Long Dim Col As Long Set wks = ActiveSheet With wks Col = .Range("C1").Column Set Rng = .UsedRange LastRow = .Cells.SpecialCells(xlCellTypeLastCell).Row Set Rng = Nothing On Error Resume Next Set Rng = .Range(.Cells(2, Col), .Cells(LastRow, Col)) _ .Cells.SpecialCells(xlCellTypeBlanks) On Error GoTo 0 If Rng Is Nothing Then Exit Sub Else Rng.FormulaR1C1 = "=R[-1]C" End If With .Cells(1, Col).EntireColumn .Value = .Value End With End With Application.DisplayAlerts = True End Sub Sub Fill_down_D() Application.DisplayAlerts = False Dim wks As Worksheet Dim Rng As Range Dim LastRow As Long Dim Col As Long Set wks = ActiveSheet With wks Col = .Range("D1").Column Set Rng = .UsedRange LastRow = .Cells.SpecialCells(xlCellTypeLastCell).Row Set Rng = Nothing On Error Resume Next Set Rng = .Range(.Cells(2, Col), .Cells(LastRow, Col)) _ .Cells.SpecialCells(xlCellTypeBlanks) On Error GoTo 0 If Rng Is Nothing Then Exit Sub Else Rng.FormulaR1C1 = "=R[-1]C" End If With .Cells(1, Col).EntireColumn .Value = .Value End With End With Application.DisplayAlerts = True End Sub Tag: adding data from two colunms Tag: 997495
  - 19
    - How to return a column number? I have a large database that continues to grow on a weekly basis. Within this database, I have formula references using vlookup, but I keep having to adjust the col_index_num for these cells because the database keeps expanding. Is there a function I can use to specify the right-most column in the database instead of using a fixed column for vlookup? Ie, "farthest-right" instead of "M", so if the database extends through columns AK or LAA or beyond it always returns the column label of that right-most data column? Tag: adding data from two colunms Tag: 997489
  - 20
    - Setting Range = Array of User-Defined Types I have a large array ['P()'] of Public Types ['PORecord'] from which I would like to write some of the sub-types [eg. 'PO_OutputDate'] to a spreadsheet at the end of a set of calculations. Currently I am using a "For...Next" loop, but this is slooww. Currently my Code goes something like this: 'Start Code For q = UBound(P, 1) To LBound(P, 1) Sheet1. Range(Info.PO_Output_Col & q).Value = P(q).PO_OutputDate Next q 'End Code What I would like to do is write all the OutputDates in the array at once to a range rather than stepping through it bit by bit. This is what I had in mind... 'start code Sheet1.Range("Z:Z") = P().PO_OutputDate 'end code ... however I get an "invalid qualifier" error. What am I doing wrong? How can I fix this? Thanks in advance, Steve Tag: adding data from two colunms Tag: 997487
  - 21
    - Use VBA to Enter Break-Mode? I run a lot of code on a remote computer. The problem is that sometimes the code hangs up, or the job runs longer than anticipated and I need to break the code. unfortunately, sometimes I can't even remote into the machine or Ctrl-Break isn't recognized. I played with setting up a watch that breaks the script if a file named pause.excel appears at a specific location - this did allow me to force the code to enter break mode on a remote computer by creating the file pause.excel from my laptop. I haven't played with the watch feature until now so to test it I savd the file, launched from another computer and looked at the watch, but it was empty. Is there VBA that I can put in the On-open statement that would Add my critera for a Watch? Or is there another way to force Excel into break-mode from a remote computer? -- "Trying to make reports so easy... even a monkey could run ''em!" Tag: adding data from two colunms Tag: 997486
  - 22
    - Userform input question Hi, Is there a way to 'force' a user to input the time on my userform in the format xx:xx, ie 24hrs with ':' between the hours and minutes for example...... 12:23 is good 09:12 is good 8:15 is good 1223 is bad 0912 is bad 815 is bad the txtbox for this on the userform are txttime and txtATA if the wrong format is used I would ideally like a msgbox to appear to advise wrong format used. hope you can help thanks Tag: adding data from two colunms Tag: 997481
  - 23
    - Change "With Worksheets" to "Activesheet" Code works perfectly.... But plan on using this on various sheets. How do i change this line to "active sheet"? With Worksheets("sheet 1") Sub GetFirstFourLetters() Dim Col As Variant Dim X As Long Dim Z As Long Dim LastRow As Long With Worksheets("s.07") For Each Col In Array("A", "B") LastRow = .Cells(Rows.Count, Col).End(xlUp).Row For X = 1 To LastRow For Z = 1 To Len(.Cells(X, Col).Value) With .Cells(X, Col) If Mid$(.Value, Z, 1) Like "[A-Za-z]" Then .Offset(0, 2).Value = Mid$(.Value, Z, 4) Exit For End If End With Next Next Next End With End Sub Thanx Tag: adding data from two colunms Tag: 997479
  - 24
    - VBA Email Cell Contents When Filled In Good morning. I have created the following spreadsheet to log requests> A (email address) B ("yes") C (Date of Request) D (Time of Request) E (Description of Request) F (Request Due) This will be on ongoing log. I would like for when a new row is completed, that a "confirmation of request" email go out to the email address listed. I tried some things by looking at different posts and I am still getting errors. Any help would be greatly appreciated. This is the code that I currently have that is not working. Please let me know if I am way off. .To = cell.Value .Subject = "Request Confirmation :" & " " & _ Cells(cell.Row, "E").Value & " " & _ Body = " Dear " & cell.Offset(0, -1).Value & vbNewLine & vbNewLine & _ " Please confirm the following report request." & vbNewLine & vbNewLine & _ " Detail are : " & vbNewLine & vbNewLine & _ " Date of Request : " & Cells(cell.Row, "C").Value & vbNewLine & _ " Time of Request : " & Cells(cell.Row, "D").Value & vbNewLine & _ " Description : " & Cells(cell.Row, "E").Value & vbNewLine & _ " Deadline : " & Cells(cell.Row, "F").Value Tag: adding data from two colunms Tag: 997475
  - 25
    - printing same tab from multiple workbooks I currently have numerous workbooks (one for each employee) containing thier timesheets for the current year (file name: "[NAME] TimeSheet's 2008.xls"). the tabs in these workbooks are simply named by the week ending date (every saterday). I would like to have a special workbook that's only perpose is to batch print all or certain timesheets. In this wkb, i would have a cell in which i type the week ending date that i want to print. I am thinking it could search all files in a folder for that tab name and print them OR furthurmore, I would also like to have a list of employees names that I could check off, or something, if i want the timesheet for that person printed. would any of this be possible? Thanks Tag: adding data from two colunms Tag: 997472
- Next
  - 1
    - SQL Query to Excel I am in the initial throws of attempting to populate an Excel Worksheet, with table rows selected from a SQL Server DB, using the following code: Sub WSPopulate() Dim con As ADODB.Connection Dim rs As ADODB.Recordset Dim strWhere As String Set con = New ADODB.Connection con.Open "Driver={SQL Server};Server=GVS00534\node1;Database=mydb;" strWhere = "SELECT * FROM dbo.application WHERE Owner_L2 = 'sbcorg'" Set rs = con.Execute(strWhere, , 1) Range("a1").CopyFromRecordset rs rs.Close con.Close End Sub This worked VERY nicely the first time...and ALL my data rows/columns populated the page. However, on my second attempt, only 3 columns from the sql db passed to the Worksheet, and on my 3rd, nothing. On the third try, the hour glass hardly flickered...it's as if I have consumed all of some resource along the way. Do I have a "hygiene" issue here, where I am not releasing something I should be? Any other ideas? Also, does anyone have a pointer to tips on how to do this? I am pretty new to DB access from excel VBA, and some "examples" might help me a lot. Thanks, Patk Tag: adding data from two colunms Tag: 997471
  - 2
    - Redimming (preserve) a 3D array This is a repost of an earlier question that didn't recieve responses (my question may have been too obscure). I have a 3D array which I'll call myarray(1 to 10, 1 to 100, 1 to 3) I would like to reduce the number of parameters to turn it into a 2D array which I'll call mynewarray(1 to 10, 1 to100) while keeping the data that is already in the array. Redim/preserve only allows you to mess with the last dimension (1 to 3), which is fine- but from help: "Similarly, when you use Preserve, you can change the size of the array only by changing the upper bound; changing the lower bound causes an error" The data I'd like to preserve is in the middle, e.g. mynewarray(x, y) = myarray(x,y,2) Is there a better method, or will I be forced to loop through and build mynewarray one value at a time? The real problem is that in my larger loop of code, myarray is dynamic and is updated frequently, and I'll have to pull current copies of mynewarray fairly often, and that seems like a lot of processing overhead. If there is a way to redim the array directly, that would save me all kinds of time. Thanks! Keith Tag: adding data from two colunms Tag: 997470
  - 3
    - Change NumberFormat and Cell Fill Colour in SELECT CASE Construct Hi, I'm using the Select Case construct for conditional formatting on my spreadsheet. The data is live linked to a SharePoint table, so when updated, is pulled through in Text format. Within this data there may be some dates, which are shown as Text in the format "dd/mm/yyyy" when pulled through. I need to turn these into Excel dates (ie 39652, formatted as 'Date') and format these cells according to whether or not they are prior to TODAY(). I'm not a VB coder - most of the Case constructs have been adapted from online example. Here's what i've written for this particular Case: For Each Cell In Rng1 Select Case Cell.Value Case "##/##/####" Cell.NumberFormat = "dd/mm/yyyy" Cell.Interior.ColorIndex = xlNone Cell.Font.Bold = False If Cell.Value < TODAY() Then Cell.Interior.ColorIndex = 4 'Green End If End Select Next I think there are several things that could be wrong here, mainly '##/##/####' and Cell.Value < TODAY(), but no idea how to correct. Any advice, or an easier way to do this automatically would be appreciated. Tag: adding data from two colunms Tag: 997469
  - 4
    - Gathering data from multiple, non-contiguous ranges Hello, Everyone! I know this question has been asked many times and dealt with in many ways, but I don't know how to modify it for my personal dilemma. I found this code in another forum for copying a single range from all sheets of all books in a folder to a single new master sheet. [CODE]Sub Open_All_Files2() Dim oWbk As Workbook Dim w As Worksheet Dim sFil As String Dim sPath As String Dim k As Long, n As Long sPath = "C:\Documents and Settings\gl1b\Desktop\Tacoma 2007\" ChDir sPath sFil = Dir("*.xls") 'change or add formats Application.DisplayAlerts = False k = ThisWorkbook.Sheets(1).Range("A65536").End(xlUp).Row n = k + 1 Do While sFil <> "" k = ThisWorkbook.Sheets(1).Range("A65536").End(xlUp).Row n = k + 1 Set oWbk = Workbooks.Open(sPath & "\" & sFil) Range("A13:J27").Copy Set w = ThisWorkbook.Sheets(1) ThisWorkbook.Sheets(1).Range("A" & n).PasteSpecial oWbk.Close True sFil = Dir Loop Application.DisplayAlerts = True End Sub[/CODE] I ran it and it seems to open, copy and close everything in the folder I specified. I just don't see the pasted data anywhere. My specifics are these: I need to copy everything from B4 to B?? <-- wherever the data in col B runs out, C4 to C?? and N4 to N??, paste it continuously down Cols A, B and C of a new master sheet. I have puzzled over this code for 2 days, changing things and changing them back when it didn't work. If anyone could make the necessary tweaks, I would appreciate it tremendously. Thanks for your time. Arlen Tag: adding data from two colunms Tag: 997467
  - 5
    - Like operator in SQL with Excel Driver Hi, I am using adodb recorset and excel driver to query a worksheet in excel file. When I try to use like poerator it gives me an error. Can someone please help me. Below is my code When I remove % and replace it with correct part number it shows the correct results. But my part number column has more part number and I just need to count records that contains part number I supplied. Sub PartNumberUsage() Dim condb As New ADODB.Connection wk_selected = ActiveSheet.Name wk_xtab = "MSO_Xtab" dbpath = ActiveWorkbook.FullName conn = "DRIVER={Microsoft Excel Driver (*.xls)}; ReadOnly=1;DBQ=" & dbpath condb.Open conn Set rst = New ADODB.Recordset Sql = "select count(" & Worksheets(wk_xtab).Range("A1").Value & ") from [MSO_Xtab$A:iv] where Part_no = '301971%' " Call rst.Open(Sql, condb, CursorTypeEnum.adOpenForwardOnly, LockTypeEnum.adLockReadOnly, CommandTypeEnum.adCmdText) Cells(ActiveCell.Row, ActiveCell.Column + 1).Value = rst(0) Application.StatusBar = False rst.Close Set rst = Nothing Set condb = Nothing Tag: adding data from two colunms Tag: 997465
  - 6
    - Problem with Userform the second time I have a macro in my personal.xls attached to a toolbar that opens a userform and displays various files in a listbox along with a date in a textbox. I can select a file and date and then run a command (code below) button to open that file. It works the first time but when I open the userform the next time and select a file I get the following error "Could not complete the operation due to error 800a01a8". I close the userform and it works the next time. Basically it does not work after each time I press the command button - i get error above, close the userform, open it again, it them works next time and we start the loop again What I can see is when it does not work the command button looks like it has focus but also the listbox has the file highlighted I opened the previous time. When it does works the command button has no focus and nothing is highlighted in the listbox and the cursor is flashing in the text box any ideas what problem is and how to fix Private Sub CommandButton1_Click() Dim wbOpenFile As Workbook ActiveSheet.Calculate Set wbOpenFile = ActiveWorkbook Dim PLpath As String PLpath = Range("PLpath") Workbooks.Open Filename:=PLpath With UserForm1 .Hide End With wbOpenFile.Close savechanges = False -- Kevin Tag: adding data from two colunms Tag: 997464
  - 7
    - Copy the value to the next open cell when the original cell changes I have a Excel spreadsheet that is linked to another program and displaying a value from the linked db in the first cell of the spreadsheet. What I would like to do is capture the value of the first cell as it changes and copy to the next available empty cell in the row. Something like this example below. Initial value shows: Col1 Col2 Col3 53 when the value changes the first time do this: Col1 Col2 Col3 54 53 when the value changes again do this: Col1 Col2 Col3 55 53 54 And so on..... Any help would be greatly appreciated. Tag: adding data from two colunms Tag: 997461
  - 8
    - move data from rows to columns Is there a way to combine rows of data where the company, address, name, and other columns are the same with a product description, product code and qty that contains different data. Some companies have numerous entries for each product ordered, other companies may only have one row. THe size of the spead sheet also varies. DAGRT PROGRAM DESCRIPTION ECP 10 DIG CORP COMPANY NAME ADD1 STATE_ ZIP FIRST_NAME LAST_NAME ITEM # ITEM DESCRIPTION ORDER UOM SHIPPED QTY 1410631 ALLIED BUSINESS 74121594 350081 W G LEFFELMAN & SONS INC 340 N METCALF AVE IL 61310-1119 Doug O'Rourke BLW2 W-2 BLANK FACE & BACK LASER 1=1PK EA 11 1410433 AMERINET 72004581 334523 SPECIALIZED MEDICAL MGMT 4900 BARRANCA PKWY STE 103 CA 92604-8603 Melinda S LMISCRECST 1099 MISC ST COPY LASER 1=1PK PK 2 1410433 AMERINET 72004581 334523 SPECIALIZED MEDICAL MGMT 4900 BARRANCA PKWY STE 103 CA 92604-8603 Melinda S 7956E 7956E/DW ENV FOR 1099 R,MISC,DIV,B/MW396 EA 1400 1410433 AMERINET 72004581 334523 SPECIALIZED MEDICAL MGMT 4900 BARRANCA PKWY STE 103 CA 92604-8603 Melinda S L1096NC FREE LASER 1096 TRANSMITTAL 1EA EA 4 1410433 AMERINET 72004581 334523 SPECIALIZED MEDICAL MGMT 4900 BARRANCA PKWY STE 103 CA 92604-8603 Melinda S LMISCFED 1099 MISC COPY A LASER 1=1PK PK 2 1410433 AMERINET 72004581 334523 SPECIALIZED MEDICAL MGMT 4900 BARRANCA PKWY STE 103 CA 92604-8603 Melinda S LMISCREC 1099 MISC COPY B LASER 1=1PK PK 2 1410433 AMERINET 72004581 334523 SPECIALIZED MEDICAL MGMT 4900 BARRANCA PKWY STE 103 CA 92604-8603 Melinda S LRFEDA 1099 R COPY A LASER 1=1 PK PK 1 1410433 AMERINET 72004581 334523 SPECIALIZED MEDICAL MGMT 4900 BARRANCA PKWY STE 103 CA 92604-8603 Melinda S LRPAY 1099 R COPY 1/D LASER 1= 1 PK PK 1 1410433 AMERINET 72004581 334523 SPECIALIZED MEDICAL MGMT 4900 BARRANCA PKWY STE 103 CA 92604-8603 Melinda S LRREC2 1099 R COPY 2 LASER 1=1 PK PK 1 1410433 AMERINET 72004581 334523 SPECIALIZED MEDICAL MGMT 4900 BARRANCA PKWY STE 103 CA 92604-8603 Melinda S LRRECB 1099 R COPY B LASER 1=1PK PK 1 1410433 AMERINET 72004581 334523 SPECIALIZED MEDICAL MGMT 4900 BARRANCA PKWY STE 103 CA 92604-8603 Melinda S LRRECC 1099 R COPY C LASER 1=1 PK PK 1 1410433 AMERINET 72004581 334523 SPECIALIZED MEDICAL MGMT 4900 BARRANCA PKWY STE 103 CA 92604-8603 Melinda S LMISCPAY 1099 MISC COPY C LASER 1=1PK PK 2 1410634 AUTO/TRUCK PRIDE 71645177 261678 REPUBLIC DIESEL 305 E COLLEGE ST KY 40203-2395 Priscilla Fields TT3B W-2 EMPLOYEE SET 3 PART NCR 2/PG EA 276 1410634 AUTO/TRUCK PRIDE 71645177 261678 REPUBLIC DIESEL 305 E COLLEGE ST KY 40203-2395 Priscilla Fields TT4A W-2 EMPLOYER SET 4 PART NCR 2/PG EA 276 1410634 AUTO/TRUCK PRIDE 74139630 499947 PRECISION BRAKE & WHEEL 15 E BELLEVIEW AVE CA 93257-2441 Joy Kanthack LW24 W-2 4 PART KIT LASER 50/PK EA 1 The above is a sample of the data in question. I need each company to have one row that contains all the items ordered. Tag: adding data from two colunms Tag: 997459
  - 9
    - Build Pivot Table with VBA; Keeps Throwing an Error I keep getting Run-Time Error 5 Invalid Procedure call or argument The following is hilighted yellow: ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _ Sheets("C2_UnionQuery").Range("A1").CurrentRegion).CreatePivotTable _ TableDestination:="[C2_UnionQuery.xls]Summary!R3C1", TableName:="PivotTable2", _ DefaultVersion:=xlPivotTableVersion10 I can't figure out what is causing the error... All data resides on this sheet: C2_UnionQuery I am trying to put the Pivot Table on this sheet: Summary My code is below: Sub BuildPT() Application.ScreenUpdating = False Dim wks As Worksheet For Each wks In ActiveWorkbook.Worksheets If wks.Name = "Summary" Then Sheets("Summary").Delete Set ws = Worksheets.Add 'ws.Move After:=Sheets(Sheets.Count) ws.Name = "Summary" End If Next wks Application.ScreenUpdating = False Sheets("C2_UnionQuery").Select Range("A1").Select 'Cells.Select ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _ Sheets("C2_UnionQuery").Range("A1").CurrentRegion).CreatePivotTable _ TableDestination:="[C2_UnionQuery.xls]Summary!R3C1", TableName:="PivotTable2", _ DefaultVersion:=xlPivotTableVersion10 Sheets("Summary").Select With ActiveSheet.PivotTables("PivotTable2").PivotFields("RVP") .Orientation = xlRowField .Position = 1 End With '...some more Pivot Fields here... End Sub Please help...... Thanks, Ryan--- -- RyGuy Tag: adding data from two colunms Tag: 997455
  - 10
    - Copy Current Month into YTD Spreadsheet I would like to create a macro to copy the current month information from one spreadsheet into a Montly YTD spreadsheet. I want to be able to create a macro so that for example if it's June, the macro would know how many columns over it needs to "copy to" on the YTD spreadsheet. I will be doing this each month. Hope that makes sense. Thanks! -- AJ Tag: adding data from two colunms Tag: 997443
  - 11
    - SheetCalculate Event Hi all, The Macro1 below was not evoke upon the calculation of active worksheet. I put these codes under ThisWorkBook but saved as addin (.xla) file. The auto-filter was set on the newly created worksheet. Private Sub Workbook_SheetCalculate(ByVal ob As Objects) If ob.AutoFilterMode Then Macro1 End Sub Should these event code must be placed under ThisWorkBook of newly created worksheet in order to call Macro1? Thanks in advance! Tag: adding data from two colunms Tag: 997439
  - 12
    - Search for strings does anybody know simple and correct way search for key word (or strings, like AKL-35, etc) in Excel workbook that have multiple worksheets. Key word can be in any place inside spreadheets. Without messing with macro ar smth else, just search, fast and simple. Tag: adding data from two colunms Tag: 997438
  - 13
    - submit web form hi, i have the following. i was able to fill the fields in, but i just cant get the code to simulate clicking the "OK" button to submit the form... 'This project includes references to "Microsoft Internet Controls" and '"Microsoft HTML Object Library" 'Variable declarations Dim myIE As New InternetExplorer 'New ' Dim myURL As String Dim myDoc As HTMLDocument Dim strSearch As String 'Set starting URL and search string myURL = "https://cantsay.com/Transactions/Logon.asp" strSearch = "me" 'Make IE navigate to the URL and make browser visible myIE.navigate myURL myIE.Visible = True 'Wait for the page to load Do While myIE.Busy Or myIE.readyState <> READYSTATE_COMPLETE DoEvents Loop 'Set IE document into object Set myDoc = myIE.document 'Enter search string on form Dim strpassword As String strpassword = "mine" With myDoc.getElementsByName("UserName")(0) .Value = strSearch End With With myDoc.getElementsByName("Password")(0) .Value = strpassword End With With myDoc.getElementsByName("Client")(0) .Value = "TEST" End With 'Submit form 'myDoc.getElementsByName("Client")(0).OK.submit 'myDoc.forms(0).submit 'myDoc.forms("Logon").submit 'Set sform = myDoc.forms(0) 'sform.submit 'Set formbutton = myDoc.getElementsByName("btnSubmit") 'formbutton.Click Windows.Application.SendKeys "{ENTER}" SendKeys (strIn) SendKeys "{ENTER}" ... any help would great be appreciated!!! thanks in advance, geebee Tag: adding data from two colunms Tag: 997436
  - 14
    - multiple Insert Shift:=xlDown hi all !!! i just need to write down my code so to make 300 times the next command.. or to insert 300 times a void line ( dimension e80:l80 ) in e 82 : aWS.Range("E80:L80").Copy aWS.Range("E82").Insert Shift:=xlDown let me know pls if you have any idea !! byy! Tag: adding data from two colunms Tag: 997431
  - 15
    - Code help Hi I have this bit of code that is part of a macro to search for and copy paste data when a match is found. wks1.Cells(iRow, 1).EntireRow.Copy Destination _ I don't want the ENTIRE ROW to be copied just the range A:O can anybody adapt the code for me please to carry this out thanks Tag: adding data from two colunms Tag: 997428
  - 16
    - Trimming worksheet name from an imported data source I'm bringing in data from an external source that changes each month, and to make the macro run correctly it needs to have the same name each month on the tab (I think. I'm still a novice at VBA). It's name is always the same length except for the month's name at the beginning. I'm having a little trouble getting the Trim function to work in VBA though. Can somebody help me with this? I'm sure I'm missing something easy. If it helps here's the name for the July/August worksheet: JA_20082008_CHRYSLER_300_SERIES Next month the JA will be replaced with AS for August/September. I really would appreciate your help. -- Message posted via http://www.officekb.com Tag: adding data from two colunms Tag: 997427
  - 17
    - Countif and I can't seem to come up with the right code to say the following: Count if $H&1:$H199 = "5th" and if B2 = "Active" H1:199 will always be the same reference. B2 will change to c3, etc. Help please Hank Tag: adding data from two colunms Tag: 997416
  - 18
    - Detect UPPERCASE in cell value? Fellow programmers... below is some code I tried to create to detect if the contents of the ActiveCell.Value was all UPPERCASE. The spreadsheet is importing data and if a cell's value is all updercase it means something specific with the data that being imported. Any ideas as to how to make a TRUE & FALSE statement based on the characters being all uppdercase? Declare Function IsCharUpper Lib "user32" Alias "IsCharUpperA" _ (ByVal cChar As Byte) As Long Sub Button22221_Click() If IsCharUpper(ActiveCell.Value) = True Then MsgBox "It is bitch.", vbOKOnly, "-Test-" End If If IsCharUpper(ActiveCell.Value) = False Then MsgBox "It's not uppercase.", vbOKOnly, "-Test 2-" End If End Sub Thanks in advance! Tag: adding data from two colunms Tag: 997411
  - 19
    - Last KeyStroke Is there a way VBA can tell the last keystroke that a user entered? Here is what I want to do: If the user hits enter, instead of going down one row, I wish to go across one column. But if they press the arrow keys, then I don't want to "alter" their choice. So if they press the down arrow, they go down one cell, but if they press the enter key they go right one cell. Ideas? Tag: adding data from two colunms Tag: 997408
  - 20
    - Send e-mail from Excel macro problem I'm receiving the following error: Compile error: user-defined type not defined It's not recognizing this: Dim objOutlk As New Outlook.Application 'Outlook I've compared it to others online, and it looks the same, but I must be missing something? The code is below. Any help would be much appreicated! Thanks! Sub SendEmails() Application.ScreenUpdating = False Dim Row, Col As Integer Dim ConfDate, UName, UNumber As String Row = 2 ConfDate = Application.InputBox("Enter the date attestation should be received: eg (Friday August 18, 2006") UName = Application.InputBox("Enter your name as it will appear at the bottom of the email") UNumber = Application.InputBox("Enter your phone number as it will appear at the bottom of the email") Workbooks.Open Filename:="[server path]\Business Leaders.xls" Range("A2").Activate Do If Cells(Row, 1).Value = "Investigate" Then MsgBox ("One file found where investigation is required- Name Investigate.xls") Else MsgBox "Click to Send Email" Dim objOutlk As New Outlook.Application 'Outlook Dim objMail As MailItem 'Email item Dim strMsg Const olMailItem = 0 'Create a new message Set objOutlk = New Outlook.Application Set objMail = objOutlk.CreateItem(olMailItem) objMail.To = Cells(Row, 1).Text objMail.cc = Cells(Row, 2).Text objMail.Subject = "User Verification " & " - " & Cells(Row, 1).Text & " " & Date$ 'Add the body strMsg = Null strMsg = "Hello " & Cells(Row, 2).Text & vbCrLf & vbCrLf [etc...] Tag: adding data from two colunms Tag: 997402
  - 21
    - adding automations to Excel (doesn't seem to work properly) ! successfully created an Add-in set called "ExcelAddIn2.MyFunctions"; and added it. This was following the book "Visual Studio Tools for Office Using C# with Excel, Word, Outlook, and InfoPath" by Eric Carter & Eric Lippert (sometimes called Eric2). See especially pp. 88-89 in the 2006 Pearson Education edition. However, when I created a new project, e.g. "ExcelAddIn3.TheseFunctions", I use the Automation function, scroll down the list, and find it. I check the appropriate box in the "Add-Ins" dialog box (or list box or whatever form it is). However, when I use insert->function -> Or Select a category and scroll down, it is absent. I also have found the same true of add-ins I've downloaded from the web. For these, I store in a known sub-directory, then use Tools->AddIns ("Browse" button), find the appropriate file in the subdirectory. Once again, I check the appropriate box in the "Add-Ins" dialog box, but similary when I go to insert->function -> Or Select a category and scroll down, it also is absent. I've tried removing the "ExcelAddIn2.MyFunctions" -- thankfully it comes back when I restore it ; I've tried re-booting and so forth. Does anyonoe have any idea what the problem is? Also, how does one remove "Add-Ins available" from the aformentioned Add-Ins dialog box (obtained by using Tools--> AddIns)? NOTE: I am using VSTO, Microsoft Visual Studio 2008, and Microsoft Office (Proffesional Edition 2003) including the Microsoft Excel 2003. Does anyone know of a link (URL) that would help? Thread discussion: To do anything at all with VSTO, one needs to be able to use an add-in in the appropriate office program, e.g. Excel. However, if this thread belongs elsewhere please let me know where I should put it. Thanks in advance, (-: -- Joseph a/k/a joseph_doggie Tag: adding data from two colunms Tag: 997401
  - 22
    - macro = Can't perform requested requested operation I was editing a macro. Suddenly said the "Can't perform etc." when tried to run it in the editor. Backed out without saving and closed everything. Opened... and it won't run or step into without the same message. If I click the button on the spreadsheet to run it it says it can't find the macro. The macro is listed in the macro section along with other macros. When I choose this particular macro the run is blanked out. Baffling... what do I do now? Thanks John Tag: adding data from two colunms Tag: 997398
  - 23
    - For Next Loop Function for Creating New Spread sheets Dear All, I am having a Master Workbook from which I've to create a new book having "n" number of sheets. Let me explain clearly, In my master workbook ,there are 2 sheets , one is a Spread sheet which is a standard format. I am also having one more sheet in which totally 5 columns are there. My users will enter the data in those 5 columns (A1:A30, B1:B30.....E1:E30). I'm creating a command button in the program. Now I'm creating a VB Coding for the button in such a way that it should copy the Standard format sheet five times. Data in First column of sheet has to be copied/pasted to First copy of standard format sheet , Second column data sheets should be copied/ pasted to second copy of Sandard format sheet and it will be repaeted upto 5 sheets. I enterd coding as follow, For I = 1 to 5 Worksheets("Std Format").Activate Cells("A1").value = Worksheets("Tech sheets").Cells(1,(I+2)) Cells("A2").value = Worksheets("Tech.sheets").Cells(2,(I+2)) .... ... ... Cells("A30").value = Worksheets("Tech.sheets").Cells(30,(I+2)) Activesheet.Cells.Select Selection.Copy Windows (newfile).Activate Worksheets.add Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats Windows(myfile).Activate Next I Is it the correct way of coding for the above procedure I mentioned? Please correct me if I am wrong. Also Please suggest me if there is any other better options for sdoing the above activity. Hope it is clear. Tag: adding data from two colunms Tag: 997396
  - 24
    - Use of a range as an email address in a .SendMail array Rather then hard wiring the email addresses into the code I wish to place it in the worksheet then define it as a Range and input the range in the array as shown below. How can I get it to work, I think I need something other than .Value Dim MyPath5 As String, MyRange5 As Range Set MyRange5 = MyWBConvertor.ActiveSheet.Range("D1") MyPath5 = MyWBConvertor.Path '''''''''''''''''''''''''''''''''''''' MyWBLnkZn.SendMail Recipients:=Array("MyRange5") Tag: adding data from two colunms Tag: 997388
  - 25
    - How could I have saved memory? I threw together a macro in Word 2007 yesterday, but it kept interrupting. There was no error, but I'd get the annoying message box that says "Code execution has been interrupted." and the CONTINUE, END, and DEBUG buttons. I've learned this is usually an indication that I'm stretching the capacity of my memory - all I have to do is hit CONTINUE and it runs along just fine - until it interuupts again!! I had tables in a Word doc with reprot numbers in one column, and several more blank columns. The data to fill in those blanks was mostly in an Excel spreadsheet. One blank, though, could only be filled in by finding the report document and grabbing one line of text. So the macro was designed to set an object to the table I had selected and iterate down the report numbers, open the spreadsheet and find the needed values for that report, then open the report, grab the one line of text, and close the report. Lather, rinse, repeat until the end of the table. Can someone see where I could have saved memory overhead and made this run a bit smoother? Ed Sub EnterMyInfo() Dim doc As Document Dim tbl As Table Dim str As String Dim cll As Word.Cell Dim tir As Document Dim this As Range Dim oXL As Excel.Application Dim wkb As Excel.Workbook Dim wks As Excel.Worksheet Dim xlcll As Excel.Range Dim x As Long, y As Long Dim a As Long, b As Long Dim vlu Set doc = ActiveDocument Set tbl = Selection.Tables(1) Set oXL = New Excel.Application Set wkb = oXL.Workbooks.Open("C:\MyFile.xls") oXL.Visible = True Set wks = wkb.Worksheets("Sheet1") a = wks.Range("A20000").End(xlUp).Row x = tbl.Rows.Count For y = 1 To x Application.StatusBar = "Row " & y & " of " & x Set cll = tbl.Cell(y, 1) If Left(cll.Range.Text, 5) = "L5-BB" Then str = Left(cll.Range.Text, 10) For b = 2 To a If wks.Range("B" & b).Value = str Then Exit For Next b 'Stop '********** 'vlu was deliberately left undefined 'so I could adjust it depending on the table. '********** vlu = Format(wks.Cells(b, 3).Value, "mm/dd/yyyy") tbl.Cell(y, 2).Range.Text = vlu vlu = Format(wks.Cells(b, 5).Value, "####0.0") tbl.Cell(y, 3).Range.Text = vlu 'tbl.Cell(y, 4).Range.Text = wks.Cells(b, 7).Value Set tir = Word.Application.Documents.Open(FileName:="\\Server1\ & str & ".doc") tir.PageSetup.LeftMargin = InchesToPoints(0.75) tir.PageSetup.RightMargin = InchesToPoints(0.75) Set this = tir.Content With this.Find .Text = "|90. " .Execute this.Collapse wdCollapseEnd this.MoveEndUntil "|", wdForward tbl.Cell(y, 4).Range.Text = Trim(this.Text) End With 'Stop tir.Close wdDoNotSaveChanges Set tir = Nothing End If Next y EndMeNow: On Error Resume Next wkb.Close oXL.Quit Set oXL = Nothing On Error GoTo 0 MsgBox "I'm done!" End Sub Tag: adding data from two colunms Tag: 997387

I hope you can help me with this.
I have two columns one estimated cost the other actual cost

est cost actual
100 50
300
400
500

t 1300 total $$$
I would like the total under the actual colunm to use the est cost value if
the actual cost is not there, so that i have a running total of what i have
estimated and what has been already aquired.
thank you in advance

Top

Re: adding data from two colunms by Gord

Gord
Wed Jul 23 19:12:02 CDT 2008

=SUM(A1:A10) entered in A11

=A11-SUM(B2:B10) entered in B11

Gord Dibben MS Excel MVP

On Wed, 23 Jul 2008 17:01:00 -0700, maria2008
<maria2008@discussions.microsoft.com> wrote:

>I hope you can help me with this.
>I have two columns one estimated cost the other actual cost
>
>est cost actual
> 100 50
> 300
> 400
> 500
>
>t 1300 total $$$
>I would like the total under the actual colunm to use the est cost value if
>the actual cost is not there, so that i have a running total of what i have
>estimated and what has been already aquired.
>thank you in advance

Top

Re: adding data from two colunms by Skinman

Skinman
Thu Jul 24 07:29:25 CDT 2008

I could be wrong but the formula I think you require is
=SUMIF(actual,"",estcost)+SUM(actual)...
Enter in cell below the named range actual. You have to name the range
estcost and the range actual
Skinman if

"maria2008" <maria2008@discussions.microsoft.com> wrote in message
news:D3E01D8A-746A-4A93-A881-C35256A9DC12@microsoft.com...
>I hope you can help me with this.
> I have two columns one estimated cost the other actual cost
>
> est cost actual
> 100 50
> 300
> 400
> 500
>
> t 1300 total $$$
> I would like the total under the actual colunm to use the est cost value
> if
> the actual cost is not there, so that i have a running total of what i
> have
> estimated and what has been already aquired.
> thank you in advance
>

Top