Free Microsoft Excel 2013 Quick Reference

A fast way to fill cells in Excel

Hello there,

I have some questions regarding this Excel Automation (I'm not an expert on
this so please "excuse" if the questions are too trivial)...

I try to build a small application that will generate a very large number of
columns and rows into Excel sheets (lets say about 200 sheets each one with
50 000 rows and 100 columns). There is a way to store "Cell" objects in
arrays so I could lets say "create" them into this array and than add the
array to a specific range (similar to the "Text", double, short storage into
an array...)? Why I'm asking this is because my Cells must have a specific
format (they are somehow structured as "BLOCKS" and this block will repeat
itself for many times) and I need a fast way to "copy" this "block"... I
already did a sample where I'm generatig arround 10 000 "blocks" on a sheet,
each block containing 3 rows and 15 colums, having each cell with a different
color and a random border. The time until it was finished was arround 31
seconds... Can I do this faster? Any ideas are more than wellcome.

Thanks,
Tas


Post your answer or comment

comments powered by Disqus
IS THERE A WAY TO HAVE CELLS IN AN EXCEL SPREADSHEET REQUIRE DATA?

I am trying to fill in a series so that only the cell beside the filled yellow cells will be numbered.

So initially i have in a.jpg

and i want it to become b.jpg

What is a fast way to do that in excel? I have 1500 of these yellow cells, Manually go thru them will take me >2 hours

thx thx

I use conditional formatting in some of my spreadsheets and I noticed that if
the conditions are not in the right order, it doesnt work as well as I would
like. However, I don't know of a way to change the order without deleting
many of the conditions. This is quite troublesome since many of my
conditions have formulas and I have many conditions for the cells. Is there
a fast way to change the order of conditions, without deleating and re-typing
them?

Is there a way to format cells in Excell to automatically capitalize the
first letter of text in a cell?

Ways To Restrict Loops in Excel VBA
Restricting Excel VBA LoopsEXCEL VBA: Restricting LoopsI would like to show you 2 ways to restrict looping by using the COUNTIF Function with the Find Method. The 1st code uses a whole cell match, while the 2nd uses a part cell match.The key thing to note in both codes is our use of the range variable rFound in the Find Method parameter for After: That is, After:=rFound. By using this we can move down the Column and find all matches. If we didn't use this, we always find the 1st match over and over again.
Sub RestrictLoop1WholeCellMatch()
Dim rFound As Range
Dim lLoop As Long

With Range("A:A")
'Set our range variable to the 1st cell in Column A
Set rFound = .Cells(1, 1)

'Use COUNTIF to restrict our looping
For lLoop = 1 To WorksheetFunction.CountIf(.Cells, "Dave")
'Use the Find Method and set each parameter to suit whole cell match
Set rFound = .Find(What:="Dave", After:=rFound, LookIn:=xlValues, LookAt _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)

With rFound
'Your .With code here
End With
Next lLoop
End With
End Sub

Dim rFound As Range
Dim lLoop As Long

With Range("A:A")
'Set our range variable to the 1st cell in Column A
Set rFound = .Cells(1, 1)

'Use COUNTIF to restrict our looping
For lLoop = 1 To WorksheetFunction.CountIf(.Cells, "*Dave*",)
'Use the Find Method and set each parameter to suit part cell match
Set rFound = .Find(What:="Dave", After:=rFound, LookIn:=xlValues, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)

With rFound
'Your .With code here
End With
Next lLoop
End With
End Sub


Is there a way to average if in excel when looking at a range of data. For
example, I want average a subset of numbers in a list based on those numbers
in that list that are above a certain threshold as well as are in a subset of
another criteria in another column. All of this without adding an additional
column.

I want to add a sound file to any cell in excel so that when the data is
entered into the cell by a user or when a calculation is performed by say an
IF statement, that expression is TRUE then a sound is made.

Hello

What is the fastest way to paste special in excel?

I do Ctrl C, Alt E, then I roll down to Paste Special…

I believe there is a faster way because I saw someone do it

Thnks

Hey everyone - thanks in advance for reading and helping

I'm trying to find a quicker way to do something with Excel 2007.

I have a Column G and a Column H

Under G I have a large list of Colleges listed alphabetically some of the colleges are listed several times (for example Alabama has 5 listings) in column H there is a numerical value that corresponds to that college. I'm trying to find out if I can write something that will look for every record of a particular college and SUM the corresponding numerical values.

This is what the data looks like.

G H
Alabama 2200
Alabama 1600
Alabama 720
Alabama 660
Alabama 9.2
Alabama A&M 2.3
Appalachian St. 30
Appalachian St. 27.2
Appalachian St. 18
Arizona 480
Arizona 13.6
Arizona 6.4

ect...ect....

I'm hoping there is a function that I can write that will find all instinces of different schools in Column G and SUM the corresponding numerical values in Column H.

Thanks again - let me know if this is confusing

is there a quick way to put spaces in data? I have a Excel Spreadsheet
that has data in it which was inported there. There is info in columns
a b c d and in rows 1 through to the end (thousands). I need to get a
space inserted after every 3 lines of data. So it would be A,1-2-3-4
then B, 1-2-3-4 then c, 1-2-3-4 then blank 1,2,3,4, then 3 rows of info
followed by a blank, and so on. I posted a sample at the address below:

http://lmr7.homestead.com/index.html

thanks in advance for any help offered!!!!!!!!

How do I show the address of hyperlink to another cell in Excel worksheet?

How do I quickly add a fixed value to every cell in a column?

Thanks

I'd like to change the standard fill patterns found under format > cells >
patterns. I do not have Visio. Is there a way to do this in excel or with a
free add in or something?

I'd like to change the standard fill patterns found under format > cells >
patterns. I do not have Visio. Is there a way to do this in excel or with a
free add in or something?

When I insert a bullet or number in Word, type something and enter, a second
bullet or number appears automatically on the next line enabling me to make a
bulleted/numbered list.

However, in excel when I do the same thing and enter, it takes me to a new
cell and hence creating a bulleted/numbered list is not possible for me
within the same cell. Kindly tell me how to create a bulleted list within
one cell in excel.

I am using MS Office Professional Edition Excel 2003

Thank you.

I need to create a maco to clear input cells in excel. Please help!

I need to make a script where I can add a single quote to the number in each
cell in Excel and after the second quote a comma. Hence the cell would start
like

08558952

and afterwards would look like,

'08558952',

I can't quite seem to get it done, any ideas?

TIA,

Rod

I would like to apply a combo drop down box in Excel. However I only want
the box to appear when the cursor is over the cell can anyone offer me some
help on this matter?

Thank you,
Dwain

Hi All

How does one refer to a previous row cell in excel, I want to use a IF stament to say if the precious cell is "C" then change current cell to "C"

I have tried touse the following but the get the error

=if (Rc[-11]="C", "C", "")

The "C" is not a static valus it will move across the row depending on user input on another oart of the sheet.

Hope this makes sense

I've created a UDF that allows me to calculate bills for a quantity of widgets. Below is the cost structure:

******** ******************** ************************************************************************>Microsoft Excel - billcalc_udf.xls___Running: xl2002 XP : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutI8=
BCDEFGHI3WidgetsCost*WidgetsCost*WidgetsCost45*$****1.00**4*$****1.50**12*$****3.00*510*$****2.00**12*$****2.25****$****7.50*615*$****3.00**25*$****3.50****720*$****4.00****$****4.00****8**$****5.00*******Sheet1*
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

Here's the way it works. In columns B & C, you can see the number of widgets and the cost for each one. So if the customer purchases 5 widgets, he would pay $1.00 for each one. If the customer purchases 6 widgets, he pays $1.00 for the first five and then $2.00 for the sixth widget. (That's the tricky part.) And so on down the line.

I'd like the function to be able to handle every single pricing structure (B & C, as well as E & F and H &I). I don't mind if I have to change the function arguments. I just don't want to have a different function for each pricing structure.

I've created a UDF, but I can't help but think there's an easier way to do this. There might even be an easy way to do this in Excel that I'm probably missing. Anyway, here's what I have so far:

Code:
Function BillCalc2(widgets As Double, costs As Variant, prices As Variant)

Select Case costs.Count

Case 1
    Select Case widgets
        Case 0 To costs.Cells(1)
            revenue1 = widgets * prices.Cells(1)
        Case Is > costs.Cells(1)
            revenue1 = costs.Cells(1) * prices.Cells(1)
            revenue2 = (widgets - costs.Cells(1)) * prices.Cells(2)
    End Select
 
Case 2
    Select Case widgets
        Case 0 To costs.Cells(1)
            revenue1 = widgets * prices.Cells(1)
        Case costs.Cells(1) To costs.Cells(2)
            revenue1 = costs.Cells(1) * prices.Cells(1)
            revenue2 = (widgets - costs.Cells(1)) * prices.Cells(2)
        Case Is > costs.Cells(2)
            revenue1 = costs.Cells(1) * prices.Cells(1)
            revenue2 = (costs.Cells(2) - costs.Cells(1)) * prices.Cells(2)
            revenue3 = (widgets - costs.Cells(2)) * prices.Cells(3)
    End Select
    
Case 3
    Select Case widgets
        Case 0 To costs.Cells(1)
            revenue1 = widgets * prices.Cells(1)
        Case costs.Cells(1) To costs.Cells(2)
            revenue1 = costs.Cells(1) * prices.Cells(1)
            revenue2 = (widgets - costs.Cells(1)) * prices.Cells(2)
        Case costs.Cells(2) To costs.Cells(3)
            revenue1 = costs.Cells(1) * prices.Cells(1)
            revenue2 = (costs.Cells(2) - costs.Cells(1)) * prices.Cells(2)
            revenue3 = (widgets - costs.Cells(2)) * prices.Cells(3)
        Case Is > costs.Cells(3)
            revenue1 = costs.Cells(1) * prices.Cells(1)
            revenue2 = (costs.Cells(2) - costs.Cells(1)) * prices.Cells(2)
            revenue3 = (costs.Cells(3) - costs.Cells(2)) * prices.Cells(3)
            revenue4 = (widgets - costs.Cells(3)) * prices.Cells(4)
    End Select
    
Case 4
    Select Case widgets
        Case 0 To costs.Cells(1)
            revenue1 = widgets * prices.Cells(1)
        Case costs.Cells(1) To costs.Cells(2)
            revenue1 = costs.Cells(1) * prices.Cells(1)
            revenue2 = (widgets - costs.Cells(1)) * prices.Cells(2)
        Case costs.Cells(2) To costs.Cells(3)
            revenue1 = costs.Cells(1) * prices.Cells(1)
            revenue2 = (costs.Cells(2) - costs.Cells(1)) * prices.Cells(2)
            revenue3 = (widgets - costs.Cells(2)) * prices.Cells(3)
        Case costs.Cells(3) To costs.Cells(4)
            revenue1 = costs.Cells(1) * prices.Cells(1)
            revenue2 = (costs.Cells(2) - costs.Cells(1)) * prices.Cells(2)
            revenue3 = (costs.Cells(3) - costs.Cells(2)) * prices.Cells(3)
            revenue4 = (widgets - costs.Cells(3)) * prices.Cells(4)
        Case Is > costs.Cells(4)
            revenue1 = costs.Cells(1) * prices.Cells(1)
            revenue2 = (costs.Cells(2) - costs.Cells(1)) * prices.Cells(2)
            revenue3 = (costs.Cells(3) - costs.Cells(2)) * prices.Cells(3)
            revenue4 = (costs.Cells(4) - costs.Cells(3)) * prices.Cells(4)
            revenue5 = (widgets - costs.Cells(4)) * prices.Cells(5)
    End Select
    
End Select
BillCalc2 = revenue1 + revenue2 + revenue3 + revenue4 + revenue5

End Function
So the first argument is the quantity purchased. The second argument is an array with the costs (or column B from above, for example). The third argument is the prices (column C). I originally tried getting this to work with a For/Next loop. I couldn't get that to work, but I still think that's possible.

Any suggestions are greatly appreciated.

I have a series of data values in non-adjacent columns in an excel spreadsheet.
In the following example, assume the | (vertical bar) refers to the start of
a new cell:

F|45|A|30|F|15|F|10

Using the SUMIF formula, I want to add all numbers which are preceded by a
cell containing the letter F.

SUMIF(A1:G1,"F",B1:H1)

What I need to do is specify a range of every second cell in the row
(starting with Cell A1) for validating they equal F, and a range of the
alternating cells (starting in column B1) for the range containing the data
to add. How can I specify these ranges (I can't name each cell individually
as I have more than 30 cells to add up in my real life situation and the IF
function allows selection of no more than 30 values)?

I'm trying to shade cells in an Excel sheet. However, there are only a few
colors to choose from and they're all quite dark/unattractive. How do I get
more customized colors? I see it is possible to "mix" by using patterns, but
that's quite clumsy I think. Are there any other methods of adding new
colors?

I need to peform a character count for certain cells in Excel. I found a
formula for counting words within a cell but not the number of specific
characters.

I have successfully created a query to pull data into an excel spreadsheet
from SQL. The problem is that I have about 10 select count(*) statements that
pull pretty much from the same tables with different criteria. Once I've
create the first query, I can't seem to figure out how to create another one
as the create new query is greyed out. In looking at the script that gets
created from using the wizard I can't seem to figure out how to duplicate it
and where the value will fill in. Can anyone shed some insight on the easiest
way to fill cells in based on data from one cell, but bring the data back
into multiple cells using multiple queries? Thanks Rick


No luck finding an answer? You could always try Google.