I need to add something to my macro to delete a sheet if it doesn't have more than 40 rows of data. If it has more than that, it needs to do a whole bunch of stuff to it. I've already got the whole bunch of stuff written, I'm just not sure how to do the if function. I took a stab at it, but it wants the count to be an object. I am not very good at writing code.
Code:
Dim count As Integer
Set count = "COUNT('33M & 33RUL'!A:A)"
If waffle Is "
i have a workbook iam using as a template.(workbook "template") the user opens which then requests a job number which will then saves the workbook as the enterd job number. this workbook also contains all the material data. eg: price and type which is on sheet "data". I am hopin to get to the sheet "data" from another workbooks command button by bypassing the job enter request by the workbook_open sub. basically i need some code that disables a workbook_open sub
A post about skipping rows is very similar to my situation, but I'm stuck on how to translate that for columns. (Filling formulas but need to skip cells)
I'd like to transfer information from a worksheet called "Raw Data" to another sheet, skipping every 5th column. I'd like to go from "Raw Data" B12 to a new sheet C6, then from "Raw Data" G12 to D6 in the new sheet, and so forth.
I figured out that I'll need to use COLUMN()*5-13 as a component of the function, but I can't seem to figure out how to format the name of the worksheet correctly, with the ampersand and quotation marks.
This doesn't work at all, but I think it's in the ballpark...
I'm trying to create a mini-table that will give me the 3 highest and lowest values in a range (I know how to do this using LARGE and SMALL functions), but I also want to get the corresponding name (in column A) for each number. This last part I don't know how to do.
I've attached an example sheet that I hope gives a basic idea of what I'm trying to do. I'm looking for a method to fill in the data for cells A13:A15 and C13:C15 in the attached sheet.
I could modify my worksheet a bit and probably get the result I'm looking for, but I'm hoping to come up with a nicer solution, and hopefully learn Excel a bit better in the process.
I was doing this task using sorting then adding column today morning one of my friend told i can do it with formula only, no need to using sort and adding new column
Excel vlookup function returns #VALUE when the lookup value exceeds 256 characters. I need a hash function to transform large strings into a value that does not exceed the 256 character limit.
I use a large function when ranking numbers in an array each month. I'm only interested in the top 5 numbers. However, there are occasions when the top 5 numbers contain a tie. How do you build into a large function, logic to handle a tie. Here's my function, which is very simple:
What I'm trying to do is take this one step further and not only find the nth largest numbers in a set of data based on a particular criteria, but also sum those numbers because they repeat in a table: for example a sample table is below:
Account Accout # Store # channel $ sales A 1000 10001 green $100 B 1001 10011 green $230 A 1000 10002 green $120 C 1002 10021 brown $145 A 1000 10003 green $100 D 1003 10031 red $20 B 1001 10012 green $50
So what I'm basically attempting to do is bring in the nth largest accounts within the "green" channel. Now if these were the top 5 stores I was looking for, the formula from above would suffice. However since this deals with accounts and the account # repeat I need to bring in the total sum of those repeating accounts instead of just one of the unique stores. So if I was seeking the largest account (NOT store) within the "green" channel the correct values this formula would be:
Account A $320
I would imagine we would need to combine a sumif with a large function or maybe involve sumproduct somehow.
I have a large amount of data, in columnA a list of cars, columnB a list of engines, in columnC the city built, in columnD the country to ship, in columnE distance, in columnF the time.
Now some makes, city's and destinations match and sometimes the engine, but i would like to be able to say cellX = (time to ship) where cellA = Ford AND cellB = 1.8 AND cellC = London and CellD = Ireland
The key data is the city built and shipping destination, so i'd like to say if cell A3 = London AND Cell B3 = Ireland AND Cell A5 = Ford AND CellA7 = 1.8 then CellA10 = (distance) & CellA11 = (time)
Vlookup's are of no use due to the large amount of data. But is there a way of writting this command in VB?? or am i missing something in Excel?
I am trying to use the 'large' function to input data from multiple spreadsheets, but am uncertain how to do so other than to use the same rows/columns in each and to use only continuous worksheets (that is, I want to enter data from worksheet 1,3,5 and different columns in each, for example).
I read the thread Converting from Hexadecimal to Binary and what I need to do is convert the following hex number to binary; 9E6799CF. The function in that thread doesn't seem to handle that large of a hex value. Is there anyway of doing that with a HexToBin function, short of chopping it up converting the smaller numbers and recombining it in binary?
I have a spreadsheet. it's not a big spreadsheet, it's not a complicated spreadsheet.
It links to another which is stuffed full of pivot tables and offset/match/index's.
For whatever reason the simple summary sheet regularly corrupts itself, i.e. when you close it Excel crashes (using 2003 on XP), it recovers but links are corrupted and when you open the last saved original that's corrupted too (it says data corruption has happened).
So I've added a piece of code which can recreate the corrupted spreadsheet from the corrupted version (it changes each month so a standard template won't work).
I'd like to not install an add-in on peoples PCs with the recovery code, but I can only do that if I can replicate the VBA in the new file.
can a piece of code copy a module of VBA from one workbook to another?
I have been using various bits of information from all sorts of threads and made a somewhat nifty little Macro program for the company i work for.
The following threads may have my answer in them, but I can't see it or figure out how it could be restructured to my needs.
Disable Read-only Message Box
looks as if the file is set as readonly then to close it without saving
[url] [url]
contains a msgbox I would like to add in, specifying that the file is read only therefore to abort current macro for particular file
concerns very much the problem I am having.
Basically what it boils down to is,
My Program Manager runs a daily report, then uses my program to update up to 7 different workbooks. Each having their own Sub program.
Some of the Supervisors or leads gets in to early and thus makes the file as a Read-Only, which of course stops my program and can not continue.
What I would like to learn is.. (from the countless threads I have looked through, I can't seem to find the answer)
Bring up a msgbox, informing the PM that a specified file is already open and therefore this particular process has been aborted.
Then it will move on to the next Sub, and so forth...
I want to thank everyone for their help directly or indirectly, (been reading threads for about 9 months now) and any future help as well.
And if it's not to difficult, could it be close to layman terms? I try to understand alot of these codes and I get lost with some of the wording. I am just pratically piecing pieces together with my "eyes closed" and somehow come out with a working VB codes.
Sub BlankGcolumn() Dim cl As Range For Each cl In Range("B8", Range("B15").End(xlUp)) If cl.Value = "" Then cl.Offset(0, 0) = cl.Offset(0, -1) & " " & cl.Offset(1, -1) End If Next cl End Sub
in the "Next cl" part, i want it to skip the next cell and do the one after it. Ive tired: ....
I want to create a user input for a "LARGE" function so that I can then return the specified # of "large" items. i.e. user wants to get the top X number of users, user then enters number either into a dialog box or cell, code then looks at the number and populates a range of cells based on the input value. The current code that I am using simply refers to an existing table (r9:r30) that I then used the auto fill to copy to cells below it.
I really want to be able to just get a user dialog box going that will automatically populate both the range r9:rx (where X is the user input) and then also copy the above formula into the x number of cells below it.
I have 3 columns of data: col. A = name (random order), col. B = Net #, Col. C = Gross #.
I am using =LARGE(C$1:C$4466,ROWS($D$1:D1) to Automatically sort col C in decending order.
I would Like to do another decending sort but only the values in Col C that corespond to a particular name in Col A. Can I imbed a index match function combination within the large function to do this?
I'm looking for a formula that shows one piece of data but only if another criteria is met. Eg Column A1:A:100 show a piece of paperwork has been issued and column E1:E100 show the area that it's been issued to (North, South, East, Central)
I have a blank work sheet showing various % of things being late as a whole but also want to break this down into the four areas.
How do I go about assigning a number to a particular piece of data? To give a simple example of what I'm trying to do, and what I envision, I'll pretend I'm building a sandwich.
So, someone could come along and build their sandwich, ham/turkey/tomato/mayo on white bread. Excel would then recognize that 1+3+4+7+8=23 23=The American (the name of the sandwich, which I've already assigned a variable to)
I'm using the sandwich model because its a lot simpler than what I'm attempting to do.
I want the buttons to run a seperate piece of code to start, but then all buttons run a similar piece of code at the end. I am hoping to be able to pass a variable from the first piece of code each button runs into the section of code that all share (which I have done as a seperate macro)
I have create excel VBA to put data from excel sheet to Server database through network. I create the VBA on the "Workbook_beforeprint" so every time the user print out the sheet, the code will run.
Problem occur when there is no network and excel application become not responding.
I want to make VBA code so that every time excel application become not responding because no network, this code will bypass the existing VBA and resume to print.
