Free Microsoft Excel 2013 Quick Reference

Code to create a Histogram

I am trying to use some VB code to generate a histogram of data. The input
range for the histogram can vary in the number of rows only. I am trying to
have the code be "generic" enough to know how many rows were filled with data
and use that info to populate the "Input Range" field for the histogram. The
code is as follows:

Application.Run "ATPVBAEN.xla!histogram", ActiveSheet.Range("B2:B3035"),
"Histo Chart", ActiveSheet.Range("$a$2:$a$58"), False, True, True, False

I would like to replace the first Activesheet.range statement with a
variable name. However, when I build the string value, I cannot place the
double quotes in the string (the macro thinks it is at the end of the string
value and won't insert the double quote).

The string I am trying to build is similar to :
InputRange = "Activesheet.range("b2:B" & ActualCount

I would then insert the InputRange variable for the first Activesheet.range
statement in the call to the Histogram. The macro gets lost after the second
double-quote.

Short of completely coding the histogram generation, does anyone have any
suggestions or ideas?

Please let me know.

Thanks
Don


Is there code to create a new WorkSheet in a WorkBook and have it a
duplicate of another WorkSheet ?
Is there a limit to how many WorkSheets can be in a WorkBook ? Over time
there may be 10,000 sheets needed.

I have a costing sheet that i want to set up, but want to be able to search
through all records to find specific values in a costing sheet.
Is there a formula to search through ALL WorkSheets in the selected cell for
values that match, and to ONLY display those WorkSheets ?
I want to place a Button on the 1st WorkSheet and IF clicked, it creates a
New WorkSheet COPY of the Costing Sheet template.

How would i go about this?

Corey....

Hi,

I would like to know if it is possible to create a histogram from data
in excel. I have approx. 30000 values in a column that varies from
0,001030093 to 276,9992477.

It would be great if I could get a bar chart with like 5 bars where
each bar shows the number of values within a particular range for
example 0-2, 2-4,4-10, 10-100,100-300.

I've tried to use the program Minitab but I cant seam to get it
right.

Hi,

I need some help with VBA code.

I have a sheet of data and need to create a chart for each row- ideally on seperate sheets.

I have attached an example of the data and the type of chart I need.

Any help would be greatly appreciated as I have been tearing my hair out over this!

Cheers

Hi,

I need to create a chart for each row in an excel document.

Ideally, each chart would go into a new sheet, there is about 300 rows and this is an example of the data:

Doctorate Msc/MA Postgraduate diploma Postgraduate Certificate Other diploma Other Certificate Other 4.01 0.2 0.2 0.5 0 0.2 0.2 0.2 4.02 0.57 0 0.14 0 0 0 0.43 4.03 0.25 0.5 0.25 0.25 0 0.25 0 4.04 0 0 0 0 0.5 0 0.5 4.05 0.14 0.43 0.29 0 0 0.14 0 4.06 0.18 0.09 0.64 0.09 0 0 0.23 4.07 0.67 0 0.17 0 0.17 0 0 4.08 0.14 0.29 0.86 0.04 0.04 0.04 0 4.09 0.17 0.67 0.83 0 0.17 0.17 0.33 4.1 0.57 0.29 0.14 0.14 0 0 0.14 4.11 0.33 0.21 0.67 0.08 0 0.08 0.13 4.12 0.22 0.17 0.94 0.06 0.06 0.06 0.06 4.13 0.17 0.33 0.33 0.17 0.33 0.67 0

I need the numbers on the left (e.g. 4.01 etc to be the title and doctorate etc to be the column headings. Can somebody help me with the VB code to do this?

I guess I need to create a chart and then loop through all the data but I have no idea how to do so.

Help is much appreciated,

Cheers!

Can someone please help be decode this and explain to me how this works exactly. I have tried a few different things and I still cant figure it out. I have even tried to look at the function and am still not sure.I believe it makes a time stamp but I am not sure how it is doing it

My goal is to figure out how to create a timestamp in one cell when something is inserted or changed in another cell. I have a formula that works, but I am looking for a VBA code to do the same thing.


	VB:
	
 
     
    If makeSound = "On" Then 
        Call sndPlaySound32("C:Program Filestrigger.wav", 1) 
    End If 
     
    SetTrigger = Now 
     
End Function 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Hi Folks

I am trying to do the following but having a little difficulty so would really appreciate some help with the code.

I want to create a box by highlighting some cells in a worksheet, but place it after/underneath the rows that already have text in them.

Once created i feel confident enough to place further code in for what i want to do with it, however despite attempts i am failing to place the box where i want and create it how i want.

I have described it below but if anyone needs more information then let me know.

In column A of the active sheet find the last row with any text in it and then move down and select the second empty row below that. Then in that selected row do the following.

Select the next 11 empty rows (downwards) and across 4 columns to and including column D.
In that selection place a border.
then In the first row merge columns A, B and C.
Repeat this for all selected rows.
Next, change the colour of the rows and previoulsy merged columns to yellow.

For example if row 23 column A had text in then the selected cells for the above process would be A25 to D25.

I would really appreciate some help with this.

Thanks

I want to be able to run an advance filter on an input range and have a
histogram that automatically updates to reflect the filtered range. This
works on regular Excel charts. I am using the Frequency function to create
the histogram.
Help?

I am trying to create a macro which does an iterative process

I have a spreadsheet which performs a complicated series of calculations
based on a generic formula.

the 2 inputs that I have i would like to be changed are based in 2 cells and
I would like the value of the input (at the touch of a buttton) changed in an
iterative process ie a + 2; a+4 .... a+40, date+1day;
date+2day...date+20days. and then the result foudn in cell (x,y) I would
like paste into a matrix. a x b.

I presume that there is some standard coding which contains a looop function
which can perform this task,
Can anyone help?

is their any code that create a list of sperate words in individual fields based on the data in one entire field, for example
if i have the words "dog 1 cat 2" in one cell (A1) is there a way to create code to take those words and in cell a2 place dog, a3 place 1, a4 place cat and a5 place 2?
any help is much apprechiated thanks

Hello everyone,

I am using VBA code to create a command button on a sheet:

ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1", Link:=False, _
DisplayAsIcon:=False, Left:=190.5, Top:=38.25, Width:=97.5, Height:=20.25).Select
Does anybody know how to assign code to OnClick event to the button using VBA?

Any help appreciated.
surotkin

Hi,

I am trying to write the code to create a custom macro depending on the input from a userform.

What i want is for the code in this form (which asks for a cell number to place an object in the cell to call the macro from) to create an individual program depending on the inputted cell (e.g. input "A1" in the forms location field and a macro called linkA1 is created).

Assuming this can be done (i have tried to create a code that adds a macro as follows:

Dim xlmodule As Object 'VBAComponent
Dim strCode As String
strCode = _
"sub MyMacro()" & vbCr & _
" msgbox ""Inside generated macro!!!"" " & vbCr & _
"end sub"
xlmodule.CodeModule.AddFromString strCode

which i cant get to work) then i would like to run this code (idealy) by clicking some text in a cell - like a hyperlink is run

Hope someone can help!

Best Regards,

Robert

Hi,

I am using the following code to create a custom command menu.


	VB:
	
 AddMenus() 
    Dim cMenu1 As CommandBarControl 
    Dim cbMainMenuBar As CommandBar 
    Dim iHelpMenu As Integer 
    Dim cbcCustomMenu As CommandBarControl 
     
     '****************************************************************
     'Delete exisiting toolbar
     '****************************************************************
     
    On Error Resume Next 
    Application.CommandBars("Worksheet Menu Bar").Controls("&New Menu").Delete 
    On Error Goto 0 
     
     '****************************************************************
     'Set a command variable to worksheet menu bar
     '****************************************************************
     
    Set cbMainMenuBar = Application.CommandBars("Worksheet Menu Bar") 
     
     '****************************************************************
     'Return the Index number of the Help menu. We can then use this
     'to place a custom menu before.
     '****************************************************************
     
    iHelpMenu = cbMainMenuBar.Controls("Help").Index 
     
     '****************************************************************
     'Add a Control to the "Worksheet Menu Bar" before Help.
     'Set a CommandBarControl variable to it.
     '****************************************************************
     
    Set cbcCustomMenu = cbMainMenuBar.Controls.Add(Type:=msoControlPopup, _ 
    Before:=iHelpMenu) 
     
     '****************************************************************
     'Give the control a caption
     '****************************************************************
     
    cbcCustomMenu.Caption = "&Logistics Attendance Tracker" 
     
     '****************************************************************
     'Add another sub control and give it a Caption and tell it which
     'macro to run
     '****************************************************************
     
    With cbcCustomMenu.Controls.Add(Type:=msoControlButton) 
        .Caption = "Open Net 2 Access" 
        .FaceId = 33 
        .OnAction = "Open_Net2" 
    End With 
     
     '****************************************************************
     'Add a sub control and give it a Caption and tell it which macro
     'to run.
     '****************************************************************
     
    With cbcCustomMenu.Controls.Add(Type:=msoControlButton) 
        .Caption = "Add New Employee" 
        .FaceId = 607 
        .BeginGroup = True 
        .OnAction = "Run_Addrecord" 
    End With 
     
     '****************************************************************
     'Add another sub control and give it a Caption and tell it which
     'macro to run
     '****************************************************************
     
    With cbcCustomMenu.Controls.Add(Type:=msoControlButton) 
        .Caption = "Edit / View Employee" 
        .FaceId = 607 
        .OnAction = "Run_viewrecord" 
    End With 
     
     '****************************************************************
     'Add another sub control and give it a Caption and tell it which
     'macro to run
     '****************************************************************
     
    With cbcCustomMenu.Controls.Add(Type:=msoControlButton) 
        .Caption = "Delete Employee" 
        .FaceId = 607 
        .OnAction = "Run_deleterecord" 
    End With 
     
     
     '****************************************************************
     'Add another menu that will lead off to another menu
     'Set a CommandBarControl variable to it
     '****************************************************************
     
    Set cbcCustomMenu = cbcCustomMenu.Controls.Add(Type:=msoControlPopup) 
     
    cbcCustomMenu.Caption = "Holidays" 
     
     
     '****************************************************************
     'Add a control to the sub menu, just created above
     '****************************************************************
     
    With cbcCustomMenu.Controls.Add(Type:=msoControlButton) 
        .Caption = "Add Employee Holiday" 
        .FaceId = 126 
        .OnAction = "Run_addholiday" 
    End With 
     
    With cbcCustomMenu.Controls.Add(Type:=msoControlButton) 
        .Caption = "Edit Employee Holiday" 
        .FaceId = 126 
         
    End With 
     
    With cbcCustomMenu.Controls.Add(Type:=msoControlButton) 
        .Caption = "Delete Employee Holiday" 
        .FaceId = 126 
         
    End With 
     
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I want to add a new button control at the bottom of the menu underneath the holiday control but it keeps adding it on the sub menu, which leads off the holiday button control.

I want the menu to look like this.

Open Net 2 Access
Add Employee
Edit Employee
Delete Employee
Holidays (3 options on sub menu)
*New Control

Thanks,

Hi

i am using the following code to transfer a line of data from each workbook within a folder

this is working well, i need to do this for every subfolder within a folder.

the folder contains 12 monthly folders each containing 4 weekly folders,

i need to modify the the code so that it pulls the same line of data out of each of workbooks in each of the folders
code:

Sub Pull_data()
Dim a, c, e As Integer
Dim f, b, d As String
Cells(2, 1).Select
f = Dir("C:TEST" & "*.xls")
Do While Len(f) > 0
ActiveCell.Formula = f
ActiveCell.Offset(1, 0).Select
f = Dir()
Loop
x = Cells(Rows.Count, 1).End(xlUp).Row
For a = 2 To x
b = "HB "
For c = 2 To 9
d = Choose(c - 1, "A", "C", "D", "E", "F", "G", "H", "I")
e = 6
Cells(1, 1) = "='C:TEST[" & Cells(a, 1) & "]" & b & "'!" & d & e
Cells(a, c) = Cells(1, 1)
Next c
Next a
End Sub

Hello

I am trying to create a combo / List box from some data on a sheet. The problem is that the sheet itself will not be sorted. But i need the form list to be sorted.

I currently have the list filled using

With CreateObject("scripting.dictionary")
.comparemode = vbTextCompare
For Each cCand In ws.Range("B4:B25000")
If cCand.Value "" Then
If Not .exists(cCand.Value) Then
With Me.cboCandidateRoles
.AddItem cCand.Value
.List(.ListCount - 1, 1) = cCand.Offset(0, 1).Value
cCandCount = cCandCount + 1
End With
.Add cCand.Value, Nothing
End If
End If
Next cCand
End With

But dont know how to add / change this code to create a sorted list.

I tried (copied from microsoft site)
Dim strArray
Set ListBox1 = Me.cboCandidateRoles
For i = 0 To cCandCount
strArray(i) = ListBox1.List(i)
Next
Dim intRet
Dim intCompare
Dim intLoopTimes
Dim strTemp

For intLoopTimes = 1 To UBound(inpArray)
For intCompare = LBound(inpArray) To UBound(inpArray) - 1
intRet = StrComp(inpArray(intCompare), _
inpArray(intCompare + 1), vbTextCompare)
If intRet = 1 Then
' String1 is greater than String2
strTemp = inpArray(intCompare)
inpArray(intCompare) = inpArray(intCompare + 1)
inpArray(intCompare + 1) = strTemp
End If
Next
Next

Me.cboCandidateRoles.Clear

For intCompare = 1 To UBound(inpArray)
Me.cboCandidateRoles.AddItem inpArray(intCompare)
Next

But it doesnt work..

Can someone help please

Good morning, everyone,

I am back for help:

I have a current open file named DailyReport. From DailyReport, I want to write a sub to create a new file. The new file will be named after DailReport file’s sheet Sum!A3 value.

In the new file, Sheet1 will be named Sum. Then copy range A:C and F:G value only and formats from DailReport’s Sum sheet and paste to new file’s Sum sheet A: E.

Sheet2 will be named Month. Then copy range A:D value only and formats from DailReport’s Month sheet and paste to new file’s Month sheet A: D.

New file’s Sum and Month should keep same format as DailyReport’s.

New file’s Sheet3 will be deleted.

Any ideas will be welcome.
Have a good day.
Dennis

I have vb code that creates a Userform with checkboxes for each worksheet. Each user selected checkbox can then be printed. It works great. How can I add a checkbox in my vb code to create a checkbox that selects "All" sheets (checkboxes) to print?

Here is a portion of my code:
' Add the checkboxes
TopPos = 40
For i = 1 To ActiveWorkbook.Worksheets.Count - 4
Set CurrentSheet = ActiveWorkbook.Worksheets(i)
' Skip empty sheets and hidden sheets
If Application.CountA(CurrentSheet.Cells) 0 And _
CurrentSheet.Visible Then
SheetCount = SheetCount + 1
PrintDlg.CheckBoxes.Add 78, TopPos, 150, 16.5
PrintDlg.CheckBoxes(SheetCount).Text = _
CurrentSheet.Name
TopPos = TopPos + 13
End If
Next i
'PrintDlg.CheckBoxes.Add
' Move the OK and Cancel buttons
PrintDlg.Buttons.Left = 240

' Set dialog height, width, and caption
With PrintDlg.DialogFrame
.Height = Application.Max _
(68, PrintDlg.DialogFrame.Top + TopPos - 34)
.Width = 230
.Caption = "Select sheets to print"
End With

' Display the dialog box
' Sheets("Index").Activate
Application.ScreenUpdating = False
If SheetCount 0 Then
If PrintDlg.Show Then
For Each cb In PrintDlg.CheckBoxes
If cb.Value = xlOn Then
Worksheets(cb.Caption).Select Replace:=False
End If
Next cb

Application.Dialogs(xlDialogPrinterSetup).Show
ActiveWindow.SelectedSheets.PrintOut copies:=1

Hi folks! I've been trawling the internet looking for some help to create a macro to do the following.
I have 2 tables in one spreadsheet that I need to combine to produce another table and run a large amount of calculations on. Before I had been using the INDEX function on my report sheet and referenced the two indexes on each table to bring back the data but this is produced a very complicated bunch of statements on the report sheet that I didnt want users to access. The tables will vary in size as the first table index is the each plot number in a development and the second table is the various interest rates that can be applied against each plot. The numbers will vary from each spreadsheet but I want to create a master spreadsheet that users can reference and change according to their data while the other sheets will be locked once the macro is run and the "master" table calculated. The first table has 24 columns and can vary up to 100 rows while the second table has 11 columns and up to 20 rows. The "master" table can potentially have 2000 (100x20) rows and (including calculations that need to be performed) 70 columns. The calculations are the same for each row but I need to be able to search the "master" table to reference the right information.
I have been trying to work on it but my VB for macros is as rusty as it comes! Basically I think its a nested loop statement to copy all the data from table 1 against the first row in table 2 and perform calculations needed in the master table, then run through through the process again for all the data in table 1 against row 2 in table 2. I'm on a tight deadline so any help would be greatly appreciated

Thank you to whoever has the answers, you boss should double your salary!

James (a non coder but how life changes that!)

I have seen code to create a work book from a filtered sheet.
How would you create a 10 Sheet WB (with the same Sheet Names) from a 10 sheet book that is filtered?

Thanks

Hi I wonder if anyone can assist me I am new to VBA programming. I have a risk log and want to create a report from a button. The selections are; I need to first select only Open risks and then only Red risks and pull these together as a report, I know I could produce this by using the auto filter but really want to produce this view as a macro as it will be repetedly produced. I also then need to take it a step further copying all this information across into another workbook from a click of a button.

Please please can anyone assist?

I NEED HELP ON HOW TO CREATE A HISTOGRAM GRAPH FOR MY DATA?

I am using the histogram with the data anylsis tool from microsoft office. I
am trying to create a histogram without space between the bars.

Using a formula,
How can i get excel to create a custom unique number using the first three (or two) characters from another field and then adding four unique numbers to the characters.

basically heres the info

Field H2 contains the characters "ACS" so i would want another field to create a unique code to include something like "ACS1234" and to continue to the next row would be the first three characters of field H3 then 1235.

Thanks ahead of time

Hi all,

First of all, I would like to apologize in advance for my English. I’m Brazilian, so I ask you all a little patience with mistakes that will certainly appear on this text…

Second, I’ve tried to find help all over the web on different forums already, but this is a bit different than what I’ve found, so I think this is a brand new question.

I’m trying to create a spreadsheet that represents a gantt chart. An example very similar to what I’ve done you can find in here: http://www.mrexcel.com/tip058.shtml

In Column A I have 5 conditions: “S” for Scheduled; “P” for In Progress; “A” for Attention; L for “LATE” and “C” for completed;
In Column B I have the initial date
In Column C I have the final date
And in the 1st row I have the starting days of each week: 02/04; 02/11; 02/18 etc…

What I want to do is to draw the line, like the example above, but I want the line to have a different color for each of the conditions. I’m current using conditional formatting, but the problem is that I can only have 3 colors. An example of the logic is in here:

=IF($A9="";"";IF($A9="S";AND(D$3>=$B9;D$3

I am trying to create a spreadsheet for businesses within zip codes. What I
would like to do is enter a zip code ie: 90620, and have the business number
523 returned to be part of, and create a total as shown below.

Buena Park Zip 90620
La Palma Zip 90623
Cypress Zip 90630
Los Alamitos Zip 90720
Costa Mesa Zip 92626
Costa Mesa Zip 92627
TOPP Prospects 5,805

I am using Excel 2003