I am a woodworker. I have a CAD program that gives me a cut list of all the parts I need. I then take this list into Excel and do some stuff with the data.
all the dimensions are in fractional inches where ' is used to designate feet and " designates inches. I use the find and replace command to get rid of the ' and ". if the dimension is just a fraction like 3/4 Excel thinks this is a date and displays march 4th. I have tried formatting the cells to numbers before i do the find replace with no luck.
I design all my spreadsheets on a 19inch LCD however when I push them out to the network drive and our assoiciates open them on there 17 inch CRT'S the sheets dont fit correctly. What can I do to correct this on my end so when they open them they look and fit correctly on there screens?
I have a worksheet compiled with text and decimal numbers all the numbers are filled in via. a data validation drop down list. This fills in many cells in certain columns and rows with data from others sheets depending on what item is choosen from the list.
Is it possible to have a macro or two macro buttons that will convert the decimal numbers from inch to metric or vice versa. When clicked. Note that there are decimal numbers in 3 decimal places up to five decimal places.
I know to convert Decimal to Metric is .0000/.03937
copied down to a minimum of 100 rows (names) The AGG is the sum(a:e/5) When u use RANK(agg,agg1:agg100,1) It ties Names AAAA and BBBB as they both have AGG of 0.142. Problem is the rule book states ties are split by the smallest of the results for each competitor in columns A to E. In this example BBBB is 2nd as he has smallest result in Column C being 0.08.
I have tried the AGG+1/min(a:e) and still end up with incorrect results. The problem is the numbers are so small and you have to split ties all the way to the 100th person involved. I cant use sort either as the data has to be printed in alphabeticall name order all the way to 100 names with their ranks recorded against the names.
Is there a way in excel to say if i select Item1 and it is 100mm x 30mm x 100mm, excel will say this will fit into a compartment called A1 and may also fit into a compartment called B6.
I have a list of all the compartments and what the internal dimensions are. So is it just a matter of saying if these dimensions are within those of the compartment then that item will fit in.
I have a list of some what 6000 Items that have dimensions. I may pick 200 or so of these items to fit into special compartments of a machine. Because of the varying sizes i need to know what compartment each item will fit in so i can build a machine big enough and get the right size compartments.
My macro prduces some text ang a graph on the screen and I can print the result. Ok so far. But when I run this macro in a different screen resolution, the layout of the page is disturbed: the graph is on the wrong place and the dimensions of the graph are different.
Of course I can drag and resize the graph and include this proces also in the macro but I think this is not the right way. My question is: are in VBA commands which can fix the graph to a location on the sheet/screen and adjust the graph to the requested heighth and width?
I am working with a dynamically declared array. Just wondering if anyone knows of a way to get the dimensions of the array so that I can work with them? e.g. to loop through the array.
I am trying to graph dimension measurements that are automatically added as a new worksheet upon completion of a CMM program. Do I need to move all of my data to one work sheet to be able to graph it or is it possible to call the same cell on multiple sheets? Also is there a way to set the graph up so that it updates the information every time a new sheet is added? I have a sample workbook attached that only list one dimension the actual workbook will have many dimensions listed. Sheet 1 is blank and sheets 2-4 contain the information from the CMM.
I have a worksheet with many dimensions and I would like to have a formula or macro to do the following: I need to check 4 different columns that have dimensions and calculate the best combinations in order to fit within the least amount of 96 inch boards.
I'm a bit uncertain if my named range has turned out the way i want it to.. Is their anyway of finding the dimensions of my namned range, i.e. # of rows and columns?
I am trying to automate the cleaning and compiling of some data, in fact its quite a lot of data, there will be about 200,000 lines over several spreadsheets.
Determine the width of the data on spreadsheet1 Determine the rows with empty cells at the bottom of spreadsheet1 "now that I know the dimensions of this rectangle" Copy a rectangle (with the same dimensions as the one on sheet1) of data from sheet2 And paste this rectangle into sheet1...
"This is the point of the excercise, I cannot have any gaps in the data (this includes at the end of a spreadsheet) as it needs to be a continous flow from one shreadsheet to another"
This is what I have done so far... I understand if it is laughable, I've only been doing this a couple of days.
Sub HowMuchSpace()
' No. of columns Dim bWidthOfColumns Range(Worksheets("Sheet1").Range(":*", ":*").Cells.SpecialCells _(xlCellTypeConstants).Count).Value = bWidthOfColumns
'No. of rows Dim lNumOfRows Range("*:*").Value = lNumOfRows
I have a lot of carton dimensions that are always presented in the same manner and would like to be able to split the individual dimensions into seperate columns. The format is always: Length Width Height. Presentation of data is always ???x???x??? or ??x??x??? (ie two or three chrs seperated by the letter X). If the full string is stored in column A I would like B to display Length, C the Width and D the Height. I find it easy to use LEFT for Length but struggling with Width and Height which I'm sure are easy, just can't get my head around it.
I am working now on macros changing the datas in a pivot table created from a OLAP cube. I have there some hierarchic dimensions in the format of Category.Group.Item etc. When creating a macro with recorder i get something like this...
Is somewhere out there any tutorial to that [All Dim_Item] thing? ... cause i feel there IS what i need, that with some cunning command i could show an item without knowing his group and category, but i cant get the macro working.
I am trying to create a spreadsheet containing a table of dimensions that are multiples of up to six loadable variables. I work for a stainless steel tank manufacturer. The body of tanks are formed from coil stock of varying widths. Typically these might be 1200mm, 1500mm, 2000mm and possibly 1800mm as well as the imperial equivalents. This could mean up to eight variables but in reality all are unlikely at any one given time.
We are told the volume that the tank is required to contain and a target preferred diameter. The theoretical height is therefore derived from this information. But now comes the tricky bit. It is desirable, to minimise cost of welding by minimising the number of strakes or wrappers. Can anyone give me a formula or whatever that can be used to do this and hopefully generate a table of close heights up to say 40 meters. Thus allowing an operator to select from say five presented options.
I have a table in a spreadsheet that the three principal widths of 1200mm 1500mm and 2000mm generate. It was created for me a while ago by a gentleman working at the Amsterdam Museum that I’m happy to send for perusal. The trouble is he never told me how he did it and no matter how hard I try, I cannot discover the method or formula. The first column gives the total combined height, the second the number of 1200mm wrappers, the third the number of 1500mm and the last the number of 2000mm. It is that simple
In my business I deal with a ton of different dimensions for my products. A lot of times the dimension will be within the cell as follows, "Product A 17x27 Brown", is in a single cell.
Is it even possible to write a code or formula that will find the "17x27" in the description, recognize it as a "dimension" and then run the formula that I need which is
I have a worksheet that contains a number of ActiveX image controls, which have been scaled to a uniform height to serve as thumbnails.
I have also created a class, where each instance is initialized with one of these image controls, and events (specifically the on click event) are captured. When the user clicks the picture, it opens a userform containg the same picture. However, I'd like that picture to be at its original size, not the scaled-down size.
how can I determine the original dimensions of a picture used in an image control? I can tell that the original resolution is there (by changing the PictureSizeMode to clip), but I can't find any properties that show me how big that image actually is, only ones that return or set the size of the object itself.
So my problem is that i have a table that is imported from sql and everyday i manually type in comments in a column that i made in excel next to the table that is imported. evreytime i refresh the data and get new data the comments are gone, how do i keep the comments with the row that i type it in?
I am importing data from a csv file into another program. For some reason this particular cell is causing everything past it in the same row to be thrown off. It is moving bits and pieces to the next column, then the next, etc....all in the same row though.
Can you see anything in this data that would cause issues in a csv file when importing. The data in this cell is html code for an ecommerce item description.
i have a script to import a workbook which works fine, but when the data is imported i would like to have all the data in the cells aligned to the left of the cells, as im new to vba i can work this out.
Below is the script i'm using to import.
I would like this to aligned all cells to the left and centered when imported.
Sub tst() With workbooks.open("C:example.xls") with .sheets(1).usedrange thisworkbook.sheets(1).cells(rows.count,1).end(xlup).offset(1).resize(.rows.count,columns.count)=.value end with .close False End with End Sub
I am trying to import from Excel Worksheet 1 into Excel Worksheet 2 in a user-friendly manner. I've created a form in Excel Worksheet 2 that allows the user to browse for a file and select it.
All I want to do is copy columns A through I from Worksheet 1 into Worksheet 2. How can I do this very smoothly without needing to open up Worksheet 1?
How can I limit the number of lines when importing a file? Limit the import to the first 53 lines of a file that has 50,000+ lines.
Want to import the first "XX" set of lines, then I want to skip a set of lines, then continue to import the rest of the lines. The second import needs to be inserted 2 or 3 lines down from the first import.
There is the function:
Workbooks.OpenText Filename:=
But it only has a StartRow and can not specify the "EndRow". Also can not specify where to start in the worksheet, i.e. Cell A5
I have a Excel workbook (wb1) where the columns are showing the months and the rows are showing different actions/activities. If an action takes place in a specified month the cell corresponding to both the action and the month is marked yellow.
In another workbook (wb2) I want to be able to retrive data from wb1. What I want is that when wb2 is opened it should do a search in wb1 to find the first action/activity that takes place in the current month. The activity/action text should be imported to a textbox or cell in wb2.
I want to import data from the web into Excel, but the data I want come from the result of a query - I have to enter a few parameters and get the results. I would like to import these results into Excel but although I can enter the website for Excel to look at I cannot figure out how I can enter the parameters to generate the results.
I am having a little trouble with this piece of simple code that just seems to be not working the way i want to. I can't figure why it won't work.
Private Sub CommandButton1_Click() Sheets("Global").Select Range("B5:F19").Copy Range("B25").PasteSpecial (xlPasteAll) Range("B5:E5").ClearContents Range("B7:E7").ClearContents Range("B11:E11").ClearContents Range("B13:F13").ClearContents Range("B17:D17").ClearContents Range("B19:D19").ClearContents End Sub
for some reason if i try to do Range("B5:F19").Select and then do Selection.Copy, I get an application error message. This has never happened before and for some reason it just started to act up. If you want to know what it does when I run this macro.. it actually doesn't go to Sheet "Global" it stays in the same sheet and does all the copying/pasting/clearing in the same sheet as the button is. I made sure that the tab is "Global" and i have confirmed caps. spaces everything.
I have made a macro to import the a excel file into a new file after truncating few initial rows. Here is the code for it:
Sub Import() Dim Prompt1, Prompt2, Prompt3, myDir, myFile, mySheet As String Dim ClCnt As Long 'column counter Prompt1 = "Enter the location of input file:" myDir = InputBox(Prompt1) Prompt2 = "Enter filename:" myFile = InputBox(Prompt2) Prompt3 = "Enter sheetname:" mySheet = InputBox(Prompt3) With Workbooks.Open(myDir & myFile).Sheets(mySheet) ClCnt = .Range("A33:CB33").SpecialCells(xlConstants).Count ' Count the number of columns present..................
I am building a service for a company using software provided. The software provided will export the information into an excel sheet.
I am creating an A/B testing for 2 different areas of the site. I will be doing a daily/weekly and monthly figure reading. There will be 2 excel sheets, daily/weekly and monthly. I will need to put them into 1.
I want to somehow, import these 2 excel sheets so it automatically pulls the information I require into the relevant cells.
From there, I will create a percentage of what. Is this possible to do via excel or will I require other software to happen?
