Using Custom Objects With Existing Data Structures
Mar 16, 2008
I have an Excel worksheet that contains many data structures, each one consisting of an array of cells, say a3:z13, a14:z24, a15:z25 etc. I can code a custom object to easily access the data in the structure but only after I load the structure into the custom object in memory. What I'm looking for is a way to assign the structure to a custom object for accessing without having to load the custom object. Again I maybe headed in the wrong direction, it may be easier just to hard code it.
View 9 Replies
ADVERTISEMENT
Aug 26, 2012
I am trying to automate drawing a flowchart based on a moel which the user can add or remove nodes and links from at will.
I can add shapes and position them but what I want to do is to insert connectors between existing shapes (I can draw two shapes and a connector at the same time but what I want to do is add a new node and then work out which existing nodes it links to and draw the lines)
Code:
Function AddConnectorBetweenShapes(ConnectorType As MsoConnectorType, _
oBeginShape As Shape, oEndShape As Shape) As Shape
'The ConnectorType can be one of three constants - msoConnectorCurve, msoConnectorElbow, or msoConnectorStraight.
[Code].....
View 2 Replies
View Related
Sep 19, 2008
I an newbie at VBA but I took some short programing classes back at my college days. I am trying to declare an array with different data types and since that seems to be imposible for what I gather then my other option is to declare what I remember as a structure.
View 9 Replies
View Related
Dec 27, 2013
So I'm creating a grid worksheet for engineering calculations and I have a couple questions about the best way to do it. I've been messing with excel for my calcs for about a decade now, and I every once in a while I try and improve them.
First: I will have several input areas that will either be colored text or shaded background (either works for me). I don't want these 'input required' objects to print as color, just black. But I want my logo at the sheet top to print as color. I've only found ways to not print any color. Can I print the logo as color and the 'input required' stuff as black?
Second: When I do calcs by hand, I write them out on 10x10 grid paper. Each 10x10 grid is one inch. In the past I've created this grid out of the cells, which works. I frequently need to change formulas around though, and each time I do this, I end up needing to mess with the grid cells also. Is there a way to create the grid and have it in the background so it doesn't need to be adjusted each time I change formulas? I wan't the grids to print, and also want to see them on the screen, as I sometimes draw simple objects along with the formulas.
View 9 Replies
View Related
Feb 15, 2014
I'm trying to make a converter between about 8 various types of values. These are not units like Km or miles or something like that, but rather numbers that represent a specific "hardness value" on a variety of scales (to name a few: HRC, HRA, K)
What I've been doing so far is plotting the two types against eachother and then getting the best trendline I can so that I can use that formula to convert between the two with relative certainty. (for example, when plotting HV vs HRC my fourth order polynomial trendline with an Rsquared of 1 is y=0.0001x4 - 0.0188x3 + 1.0768x2 - 20.709x + 350.69)
My questions comes up where I was hoping to make a window or box of some sort allowing the user to input a numeric value, then selecting the Input units and the hopeful output calculated units, and have the box spit back to the user the conversion.
View 9 Replies
View Related
Aug 26, 2009
I have 2 pivot tables in an Excel Sheet (Pivot A and Pivot B). They have different structures i.e. the table headings are different. I need to sum the "Grand Total" values from each of these pivots. Can anyone explain if the following is possibe:
1. Can you combine 2 pivots with different structures in order to acheive a single Grant Total value?
2. Can I create a formula which adds the 2 separate Grand Total Values?
View 2 Replies
View Related
Jun 29, 2012
I have this VBA code which I use for transferring data from my data sheet to slides in PowerPoint - it works fine.
However in PowerPoint I'm currently forced to transfer data to "text-objects" which is a bit of a drag because I'm used to working in a table in Powerpoint when organizing data.
Therefore my question is how to edit my VBA code so I can transfer data to a table in PowerPoint instead of an object.
Here's the VBA code:
Code:
Sub TDPTest()
Dim shtStudent As Worksheet
Dim strMedarbejder As String
[Code].....
View 9 Replies
View Related
Jan 29, 2013
I am trying to create a user form that will allow the user to type int values in boxes. Then when the user clicks the submit button the code needs to add the values from each user input box to the existing values in specific cells accross multiple worksheets. Then the form needs to be cleared after the cell values are updated. I can create the form it is the code on the submit button i am lost on. Also it is important that some boxes may be left blank.
If you click the Grey "Qty Form" button on sheet1 the form will open. User data numbers can be entered in the blank boxes. When the submit button is pressed the form needs to add the user entered numbers to the numbers in the corresponding cells in sheet 1 and sheet 2. How to code the submit button to do this properly. Also after the data on the spreadsheet is updated the form needs to be cleared and start the cursor back in the Item 1 box on the form.
View 1 Replies
View Related
Oct 18, 2013
I have been working on a macro that compares a existing list of data to an updated list of data and then either moves any data not on the new list over to a completed tab (followed by deleting the record on the existing sheet), and then adds any items not on the existing sheet, but which appear on the new list, to the existing list.
I have come across a stumbling block, i have managed to identify on the existing list the rows of data that have been removed from the new list and therefore need to be moved over to the completed tab, but when i select the data it selects the header row aswell (which will always remain the same row). Obviously this then pastes the header row aswell, and also i can't seem to get it to paste in the new sheet to the next available row (i.e this will be used daily and i don't won't to overwrite the infor already in the completed tab). the next issue i have is then when i go back to existing sheet to delete the data i just copied across, as the header was initially select this also gets deleted.
The code below, is the complete code, including filtering, copying some forumals etc. The area i am getting stuck on is highlighted in red:
Sub Update()
Dim bottomrow As Long
Dim My_Range As Range
bottomrow = Cells(Rows.Count, "C").End(xlUp).Row
Set My_Range = Range("A1:Y" & bottomrow)
[Code] .....
View 6 Replies
View Related
Mar 21, 2014
I have a range of weekly data that I need to copy into another workbook, paste it below the data that already exists there and then delete the data from the original workbook. I would love to do this with vba but after hours of searching on how to do this my brain is frazzled.
I have 50 workbooks that I need to import weekly into one master sheet (Master.xlsm) but they need to be done individually after the weekly data has been checked. The master sheet will therefore have existing data and the new data needs to be appended at the bottom. Also column A will be blank in both workbooks so to find the last used row it will need to look in column B.
The number of rows in the weekly sheet will also vary rather than be a fixed range so I guess the last used row will also need to be found there too.
The attached file is a cut down version of my working file showing where the data starts on Row 14, I won't need to copy the headings.
Example file 21.03.xlsm
View 4 Replies
View Related
Feb 12, 2014
getting data externally from web and also keeping the previous data prior to the refresh.
My external table/data has 10 rows reporting values per day (10 days data table).
I can link the table to my sheet, however what I can not do is to create an offlinek, year to date table in the sheet which captures the rolling 10 days (everytime it is refreshed) without losing the prior days.
For ex:
External bank Data
Day Ratio
02/05/2014 0.15%
02/04/2014 0.13%
02/03/2014 0.14%
01-31-2014 0.23%
01-30-2014 0.16%
01-29-2014 0.16%
01-28-2014 0.18%
01-27-2014 0.19%
01-24-2014 0.19%
01-23-2014 0.21%
Above table changes on a daily basis, only reporting the last ten days.
How can I capture this data on a rolling basis, i.e. in a year to date format, so that everytime I refresh, the internal table gets updated with the new data.
View 3 Replies
View Related
Jan 3, 2008
I'm trying to move data from a primitive user form to another sheet acting as a DB. I will further pivot the data in a third sheet to boil up results.
Here is the primitive user form - or desired data from the user form: ...
View 9 Replies
View Related
Aug 1, 2014
I have many rows of data 6 columns wide. I want to be able to enter data into a specific section, then run a macro to "cut-and-paste" that data onto the bottom of my existing data (with one empty spacer row between the new and existing data)
Here's what I have so far:
[Code] ....
Basically the part I need working on is changing [ Range("A101:F130").Value ] to be dynamic. For the code to determine the last row of data, move 2 rows down, and paste the block there.
View 6 Replies
View Related
Sep 11, 2012
Each day I copy paste data onto a sheet (below the prior days copy paste). What's a simple code to automate this? I want to keep all prior day data and just paste into the first blank row
View 3 Replies
View Related
Aug 5, 2013
I am trying to add data to an existing chart with VBA. I have defined and populated my arrays, but I m not sure how to add them to graph. I am getting a Invalid proedure or call argument on the code below.
Code:
Sub Plotting()
Xvalues(1) = 50
Xvalues(2) = 48
Xvalues(3) = 46
Xvalues(4) = 44
Xvalues(5) = 42
Xvalues(6) = 40
Xvalues(7) = 38
Xvalues(8) = 36
[code]....
View 1 Replies
View Related
Dec 11, 2006
I have the following macro which:
- opens a database checks each sheet in the database for a contract number,
- highlights the row in which it finds the contract and updates the database with claim number etc (not important)
- returns information to another sheet from the database
The macro works fine BUT I have recently realised that if the contract has already been paid out it will simple overwrite the information and I will not be aware that the contract had already been paid out.
I need the macro to return a value of "Already claimed" instead of "Found in Database" only if the contract number has already been updated.
Sub DataBaseCheck()
' Designed to check Database for claims issued each month
' Open Data Base file to facilitate matching process.
' Check for matching data
' Return checking data to spreadsheet & Update Database
Dim currentfile As String
Dim WS As Worksheet
Dim R As Range
Dim Myvalue As String
Dim Myrange As Range
Dim Tcell As Range
View 9 Replies
View Related
Jul 2, 2012
Clicking the Add to DB button will add new items but not existing one.
now, what I want to do is to first, populate the existing TIN and then do the necessary update.
my basis for update is txtTIN.value
Here are the codes anyway:
VB:
'This adds the data on the WorkSheet named DBPIT"
Private Sub cmdAdd_Click()
Dim iRow As Long
[Code].....
View 5 Replies
View Related
Dec 4, 2013
I need a formula to check if anyone from the Old employee col(B) exist in current employee column(A).
A B
Current employees Old employees
A C
B G
C T
D
E
F
G
View 2 Replies
View Related
Oct 9, 2008
I have a worksheet containing employee information (i.e. name, manager, department, etc.). I also have a userform that will be used to make changes to the employee information. The userform has three comboboxes and, when activated, the userform is populated with the existing data in the worksheet.
Here is the problem - I need to have the existing worksheet data populate (as it does now) but also have the combobox choices available in the event that one of the 'combo' fields need to be changed. How do I keep the existing worksheet data AND keep the functionality of the combobox?
View 3 Replies
View Related
Jun 1, 2013
I have the below code that copies and pastes from one part of a spreadsheet to another. I need to modify it so that it does not over-write the information where it pastes to, but rather adds the new information to the lines below the data that is already there.
Private Sub PlaceData_Click()
Range("A7:C23").Select
Selection.Copy
Range("G4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
[code]....
View 3 Replies
View Related
May 2, 2007
need to add same data to every other existing cell in the column, but not replace the data already in it, but to add to it. I've tried to google the answer and look here, but I probably use bad search terms.
For example, I need to add "QW" after each of these lines:
data1432
data9292
data3933
data3939
so it would look like this:
data1432QW
data9292QW
data3933QW
data3939QW
I have a few thousand rows of data, so wouldn't rather not do it manually cell by cell by typing :-)
View 9 Replies
View Related
Sep 28, 2009
I don't know how else to ask this so I will just tell you what I'm trying to do.
-I have a list of name on one column say "A"
and this list of names goes through "A1" through "A145"
-I am taking those names and pasting them via "right click" "paste special"
"Values" onto another spreadsheet.
-BUT I have to take each name one by one and paste them evenly
in every 8th row for example ( "A1" A8" "A16" "A24" ...)
-Is there a way to take the existing data on that row and just spread it evenly throughout the same row? So I don't have to take names one by one by one...
View 9 Replies
View Related
Mar 9, 2009
I have a column with blank rows inbetween rows with data.
Column Heading
1000
Blank
Blank
1001
Blank
Blank
Blank
1002
Is it possible to autofill the blank rows with the row that has a value above them, have it skip the next row with a value, recognize the next blank cells and fill them in with the previously skipped row, and so on down the line? How might I go about doing that?
View 4 Replies
View Related
Apr 26, 2013
While making my first foray into defining external connections (to an Oracle database) I ended up with an "Existing Connection" that reports "File could not be opened. It is either damaged or the file format is not valid. Try opening the application that created it and saving to a new file." Followed by "Unable to open or read this connection file. Either the file has been damaged or the file format is not valid."
Ok, fine. So how do I delete it? There seems to be no option for that in the "Existing Connections" dialog. A search for a *.odc or *.udcx file doesn't turn up anything with the name of this connection. A search of the registry doesn't turn up anything.
extdata.jpg
View 1 Replies
View Related
Feb 17, 2009
I am working on this estimating template. To be used on Excel 97, 2000 and XP systems.
I have a few issues i am unable to resolve.
There are 2 of the worksheets (Short Quote and Stores Req) which individually need to have a button on them so i can create a new workbook as an exact coy of that sheet. but with just the values and not the formulas.
Also i am unsure on the macro on the workbook currently( as i dont know how to do macros) i dont know what it does?
View 7 Replies
View Related
May 2, 2009
I have created a form in VBA using txt boxes and cmd's. I want the abitility to edit the existing data on my excel worksheet using the form. Currently, I can add data, but not search through the existing data on the form.
I have been using the excel tips and it has been great, but I am stumped at this point. This is my first time to every post a message.
View 4 Replies
View Related
Nov 28, 2011
I have 2 opened workbooks. Call them BookCopy.xls and BookPaste.xls.
In BookCopy.xls I have filtered data which I want to copy ("shtCopy") and insert into a particular row in sheet ("shtPaste") in BookPaste.xls.
"shtPaste" already has data in it. Hence when I paste the data in, I don't want the existing data to be overwritten.
View 9 Replies
View Related
Dec 26, 2011
I have an excel list of My Existing Customers and have recently purchased an excel list of all possible customers in my market that happens to have my existing customers listed in it as well. How can I remove my existing customers out of this purchased list so that I can import it into my Database as Prospects? I'm using Excel 2010. Deleting duplicates doesnt work for this. I want a function that looks at data in one spreadsheetA and if it finds it in the second spreadsheetB, it deletes the row out of SpreadsheetB.
View 9 Replies
View Related
Mar 7, 2014
I have a named range "ABC", range is ("A1", "A3", "A5"). Now I need to use Macro to extend the range to ("A1", "A3", "A5", "A7"), that is, adding "A7" to the existing named range "ABC".
View 1 Replies
View Related
Mar 1, 2007
How color the cell autmatically when the existing data is altered. the same needs to be done with the entired column
View 9 Replies
View Related
