Library tutorials & articles
VBA in Excel
James first started this website when learning Visual Basic back in 1999 whilst studying his GCSEs. The site grew steadily over the years while being run as a hobby - to a regular monthly audience ...
Running Macros
After having a quick to see what code Excel recorded, I'll show you how to run the code, and then move on to the basic commands for formatting and adding formulae.
In Excel, you have a number of different ways to run some code. Either, you can press the Run Macro (Play) button in the Visual Basic editor, just like you would in VB. Alternatively, you can go to Tools|Macro|Macros... You can then select the Macro you want to run, and click Run!
To see what our recorded Macro does, add a new sheet to the Excel workbook by clicking Insert | Workbook. Then, run the macro using one of the methods above. You will now see the header we created when recording the Macro mysteriously appear, without you having to type anything! Hopefully now you can get some idea as to how much work Macros can actually save you, especially if you are performing similar operations regularly.
Now that you know how to record a Macro, and how to run it, we can take a look at the different objects and properties that let you apply formatting in Excel.
Comments
Related articles
Related discussion
-
Problems with VBA User Forms on Excel
by Veritant (1 replies)
-
Problems with User Forms
by Uncle (1 replies)
-
Outlook VBA query
by James Crowley (1 replies)
-
problem with a progressbar : it dispears
by Uncle (1 replies)
-
Access, Calender dates, Strings and SQL
by Uncle (1 replies)
Events coming up
-
Jun
16
Code Generation 2009
Cambridge, United Kingdom
A developer event with a practical focus on helping people get to grips with code generation tools and technologies.
Hi
What i am trying to do is use a formula to grap cells a specific distance apart, and then use autofill to copy these cells. Currently, I am doing it by hand as I cannot work out a formula.
So I need to take something like this (shown as formula view) :
=cellB1 =cellF1 =cellJ1
=cellC1 =cellG1 =cellK1
I then want to be able to drag this across using autofill.
This might not be the place to ask this question, but here goes...
I'd like to put a macro in an excel worksheet that limits the time someone can use the sheet. that is, I have a "demo" of a product that I want to send to some folk, but i don't want them to have the product for too long, or rather i don't want them to be able to use it indefinitely.
How can I write a macro to set an expiration date? And will that do what i want anyway?
You need to first write a code to select the entire range, then
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.EntireRow.Delete
Regards,
Charu
I am trying to use VB in excel 2003 to check if a cell has a border. I tried the following but it does not work. Can anyone explain how I should be doing this?
ActiveCell.Borders(xlEdgeLeft).Color = RGB(0, 0, 0)
If ActiveCell.Borders(xlEdgeLeft).Color = RGB(0, 0, 0) Then ActiveCell.Borders(xlEdgeRight).Color = RGB(0, 0, 0)
Thanks,
Andy
Why dont you try using a For loop instead. Something like:
x = o
For x to 100
// What ever you want to do
Next x
Makes it very easy.
Hi, how do I increment the row value so that I check rows continously?
My code looks something like:
Do While Count <= 1000
Count = Count + 1
If Range("B2").Text = ".com/xSer" Then
Rows("2:2").Select
Selection.delete Shift:=x1Up
But instead of having "B2", I'd like to have the 2 increment to B3, B4, B5 etc.
Can anyone help me? Thanks!
I am using excel to see if two large spreadsheets have changed. I am setting up an =if(exact.... statemenet to do lookups. The problems is that when one spreadsheet that has an #N/A as one the values returns an #N/A and then it interrupts my lookup.
I want to write a VB macro that will scan the document for values of #N/A and delete the contents of the cell if there is a #N/A.
Thanks in advance.
This should be simple to do...without a macro. Just setup a nested IF statement to ignore #N/A values. For instance: IF(ISNA(EXACT(A1))=TRUE,"",EXACT(A1)). This may or may not work for what you are trying to do but you didn't explain what you are looking for in depth so if this doesn't work, give more detail about your problem
hi, you could try below code which delete the entire row for empty in cells, you can chane the value for 50 and cell D1.
Dim x As Integer
'move to the upper most cell of last columne of data
Range("D1").Select
For x = 1 To 50
If ActiveCell.Value = "" Then
'Delete entire row
ActiveCell.EntireRow.Delete
x = x + 1
Else
ActiveCell.Offset(1, 0).Select
End If
Next x
End Sub
Hi, I am trying to delete all the empty lines in my spreadsheet by creating a do while loop macro. I want the macro to find the last filled row and delete all other rows which are empty. Pls help. Thanks
I'm having a similar problem, but this doesn't seem to help. I'm importing a sheet of data which contains dates. Some of the dates, appear in the left hand side of the cell, and are therefore not recognised as dates. I need a loop formula in VBA, which presses F2 and then enter, until it has completed the range.
Any ideas? Anyone?
hey, think i done it by = int(numberstart) seems to be ok now thank everyone
hey, i'm using Vba with excel i'm very new to this. i have created a Userform that insert information into the spreadsheet, some off the information is number. Excel is saving the number has text ? can n e 1 help? thank you
I am using excel to see if two large spreadsheets have changed. I am setting up an =if(exact.... statemenet to do lookups. The problems is that when one spreadsheet that has an #N/A as one the values returns an #N/A and then it interrupts my lookup.
I want to write a VB macro that will scan the document for values of #N/A and delete the contents of the cell if there is a #N/A.
Thanks in advance.
hey, think i done it by = int(numberstart) seems to be ok now thank everyone
hey, if you highlight where you want the user to type in the password, right click properties and scroll down to "PasswordChar" then input what you want to be displayed
My password coding for excel
Private Sub OK_Click()
Dim m As String
If UCase(TextPassword) = "SPROUT" Then
Backdoor.Hide
interface.Hide
m = "Click the logo to restart system" & vbCrLf
m = m & "please do not edit sheet1"
MsgBox m, vbInformation, "Password screen"
menuon
Else
Backdoor.Hide
MsgBox "Incorrect Password", vbExclamation, "Access Denied"
End If
End Sub
Hope this helped (not very good at explain stuff sorry
hey, i'm using Vba with excel i'm very new to this. i have created a Userform that insert information into the spreadsheet, some off the information is number. Excel is saving the number has text ? can n e 1 help? thank you
i'm realy sorry i can't help u but i think u can help me.
i need to make a graph in excel that will be a line graph, the graph sould "take" it's data from a matrix that have 2 lines
the first line will be the axis X and the second line will be the axis Y.
if u already wrote a graph from v.b to excel i will happy to get any help u can give me
you can write me to yuval_lisner@hotmail.com
tank's any way lisner
Hello Miriam,
this solution is not the fastest but it should the work:
(data is the column that contains the x's ...)
Sub DeleteRows()
Dim myRange As Range
Dim i As Single
Application.ScreenUpdating = False
Set myRange = Sheet1.Range("data")
For i = myRange.Rows.Count To 1 Step -1
If myRange.Cells(i, 1).Value = "x" Or myRange.Cells(i, 1).Value = "" Then
myRange.Rows(i).EntireRow.Delete
End If
Next i
Application.ScreenUpdating = True
End Sub
I need help with writting a code that looks for cells (in the same colomn) that contian "x" and if the cell
does not contain "x" or is blank, I would like to delete the entire row. Have been looking on the internet for ideas
but am getting nowhere.
I would be very grateful for all your help!!
I have a spreadsheet that I pull new data into everyday. The data is then sorted by date. I want to delete all of the rows where the date variable (which is contained in column n) is < today's date (i.e. the date is yesterday or before). I am trying to write a macro to this, but it is not working and I am going crazy. Here are a few that I have tried and what is wrong with them:
(This works, but only deletes 1 row at a time, doesn't continualy do it...I want it to delete all rows with a date <today)
Sub PurgeDate
Range("n2:n200").Select
Do While ActiveCell.Value <> Empty
If ActiveCell.Value < Date Then ActiveCell.EntireRow.Delete
ActiveCell.Offset(1, 0).Select
Loop
End Sub
(This is deleting all of the rows below the active cell, but not the ones with the date < today)
Sub PurgeData()
Dim Cell As Range
For Each Cell In Range("elimdate")
If Cell.Value < Date Then ActiveCell.EntireRow.Delete
Next Cell
End Sub
Please HELP!!!!
-CHRIS
I am also interesting in knowing the answer
Thank u
Turns out I was trying to write a function, but I was using the code window, not the module window.
Once in excel, I clicked on VB editor. I then brought up the code box by pressing F7. I typed in a stupid little command
Function Addit (a, b)
Addit = (a+b)
End Function
I am trying to get that back into excel, where I can click in a cell and run it like
it was an equation out of the equation bar, ie. clicking in the bar, right clicking, and going to "Pick From List".
I when I pick the program I just made, it should ask me what 2 cells I would like to be a & b.
I have seen this done before and was told to replicate it, but the problem is, when i click on
"Pick From List", it stalls and never pulls up the list. Does anyone know if I'm doing something wrong?
hi all,
I'm using an input box method as an easy way to set up a password. My question: Is there a way to hide the text you type into the input box or convert your key strokes into *'s?
Dim strPass As String
strPass = inputBox("Please Enter Password", "TEST")
If strPass <> "password" Then
MsgBox "Access denied - Please Contact Me"
End
End If
Tia
Chris
A ha!
You found the deliberate mistake!!!
That's what happends when you try an edit code in a web browser!!!
Cheers!
I'll try that, thanks for the help I appreciate it.
KM
I think the reason this isn't working is that the second to last line in the Function reads:
Set FindNextEmpty Cell = Result ,
but the Sub procedure uses: Set Target = FindNextBlank Cell(Range("A1")),
so if you change the second to last line in the Function to read as follows it should work:
Set FindNextBlank Cell = Result
-Chris
Hi! I am trying to write a macro to automatically create a graph. Since the data in the sheet will be updated daily, I want the graph to only contain the last 20 (or 30 or whatever) data points. I can't figure out how to get the macro to use only the last 20 points when those points are changing every day.
Please Help....!!!
if i want import the data from text file into excel, what the method i can use?? by using earlier version of excel
strarr = Split(str1, " ")
i would like to ask, how we know, how many cols of strarr had splited?? what the method i can get the total n of strarr?? <<strarr(n)>>
Basically I have raw data on a tab named "data" starting in row 2, column A I have toll-free numbers, column B I have billing codes (in some cases the toll-free is repeated in two rows because it resides on two billing codes). On my Master output tab, (in this case named October for the month i'm working with) I have Column A again as toll-free numbers (although it is the master list, not every toll-free had volume on the data tab, many are not on the data tab because they are inactive or had no calls, therefore don't show up in the data tab) Column B on "October" is the same as the data tab, it has the Bill codes that corespond with the toll-free in column A of the same row.
I need to write something that will scan each row in 'data' and if column A and B in that row match column A and B of "October" then it will take data contained in the same row, 4 seperate cells, and paste that data into the corresponding row of "October"
1 other problem i forsee that i may need to use seperate macros. The cells that will be copied from the row in 'data' if A and B match, from 'october' and 'data' are in different locations:
what i mean is: if A3 and B3 of 'Oct' find a match in A and B of 'data' the next step is to copy the cell of that row in column D of "data" to column G of 'October'
the others are 'data' column C to 'October' column J; 'data' column E to 'october' column K; 'data' column F to 'october' column L
is it better to keep taking data from that row that we've matched in 'data' and pasting it in the corresponding cell of 'October' or just use a seperate macro for to match A and B of the 2 tabs and take the info from the column in 'data' to paste in the corresponding cell of 'October'
Sorry this is so long, i really have very minimal skills in VB... this is way over my head and looking for anything to get this solved...
Basically I have raw data on a tab named "data" starting in row 2, column A I have toll-free numbers, column B I have billing codes (in some cases the toll-free is repeated in two rows because it resides on two billing codes). On my Master output tab, (in this case named October for the month i'm working with) I have Column A again as toll-free numbers (although it is the master list, not every toll-free had volume on the data tab, many are not on the data tab because they are inactive or had no calls, therefore don't show up in the data tab) Column B on "October" is the same as the data tab, it has the Bill codes that corespond with the toll-free in column A of the same row.
I need to write something that will scan each row in 'data' and if column A and B in that row match column A and B of "October" then it will take data contained in the same row, 4 seperate cells, and paste that data into the corresponding row of "October"
1 other problem i forsee that i may need to use seperate macros. The cells that will be copied from the row in 'data' if A and B match, from 'october' and 'data' are in different locations:
what i mean is: if A3 and B3 of 'Oct' find a match in A and B of 'data' the next step is to copy the cell of that row in column D of "data" to column G of 'October'
the others are 'data' column C to 'October' column J; 'data' column E to 'october' column K; 'data' column F to 'october' column L
is it better to keep taking data from that row that we've matched in 'data' and pasting it in the corresponding cell of 'October' or just use a seperate macro for to match A and B of the 2 tabs and take the info from the column in 'data' to paste in the corresponding cell of 'October'
Sorry this is so long, i really have very minimal skills in VB... this is way over my head and looking for anything to get this solved...
how can the vb recogrite the excel parameter? i found a solution that is to attach the excel library into the current project, but it's need the mircosoft office 2003, so that if 2002 it's doesn't work.... so what the method else i can apply to my program without update office version??
This Will Help
Sub Data_Import()
Sheets("DataSheet").Visible = True
Sheets("StoredData").Visible = True
Sheets("DataSheet").Select
Columns("A:H").Select
Selection.ClearContents
ActiveCell.Columns("A:H").EntireColumn.Select
Selection.Copy
Sheets("DataSheet").Select
ActiveWindow.ScrollWorkbookTabs Position:=xlLast
ActiveCell.Columns("A:H").EntireColumn.Select
ActiveSheet.Paste
End Sub
KM,
Sorry I've been a bit slow replying, I was on holiday; anyway, when you say that you can't get it to work, are you getting any error messages? Is it pasting the data in the wrong place?
Havling looked at the code, I would guess that the problem is in the 'DidCellsChange' procedure. It doesn't look like it will detect whether the cells have changed properly, but I could be wrong.
I have read many articles on how to search for empty excel cells. but none really help.
what i need is my company recieves a data every week. i want to be able to grab the data from another sheet called "DATASHEET" and past them into our stored data sheet called the "STOREDDATA"
the amount of data though is not on cell it is a numerous amount of cells which have spaces inbetween and is all over the place.
what i tried doing was running an autofilter macro and pasting into the next blank cell. all i get is 1 cell being pasted in the blank and the rest repasted on each other.
i have data in "STOREDDATA" that i want stored for all time. the dat is from E2 to V7. The next blanks are just below in E8 to V12.
so just to recap i want it to
1. grab the "SELECTED" info i need from DATASHEET
2. Paste it into the next balnk cell which is current E8.
This will be an ongiong thing so if i can get it to always store in the next blank cell would be appreicated
PLEASE SOMEONE HELP.
I NEED THIS ASAP OR IM IN TROUBLE FROM THE BOSS
I have a question regarding your code. This is what I’m trying to do: I have an excel spreadsheet pulling public financial information form the internet via a web query. The problem is that when the website is updated, once every quarter, my excel sheet changes without allowing me to automatically capture and save this data. I would like to save the data into graph historic changes. I have tried to piece together a program that will copy the data every time that the website updates my spreadsheet, and then paste special that data into another sheet- thus allowing me to save the data and build a database. As you can see, I have tried to use your program below to place copied data in the first available column of a new spread sheet. The problem, however, is that I can’t get it to work for the life of me. I am very new to programming and would greatly appreciate any help.
KM
Sub auto_open()
' Run the macro DidCellsChange any time a entry is made in a
' cell in Sheet1.
ThisWorkbook.Worksheets("Sheet2").OnEntry = "DidCellsChange"
End Sub
Sub DidCellsChange()
Dim KeyCells As String
' Define which cells should trigger the KeyCellsChanged macro.
KeyCells = ("B1")
' If the Activecell is one of the key cells, call the
' KeyCellsChanged macro.
If Not Application.Intersect(ActiveCell, Range(KeyCells)) _
Is Nothing Then CopyAndPasteSpecial2
End Sub
' This has been tested and you should just be able to copy and paste it into vba
Function FindNextBlankCell(FirstCell As Range)
Dim Result As Range
Set Result = FirstCell
If Result.Value = "" Then
' Our starting cell is empty. We can just use it
Else
Set Result = FirstCell.End(xlDown) ' Find the last non-blank cell
If Result.Row = Result.Parent.Rows.Count Then
' If we end up at the bottom of the worksheet, this means that only
' the first cell was populated. Go back to it
Set Result = FirstCell
Else
' We are now on the last used cell
End If
' Goto the next row down
Set Result = Result.Cells(2, 1)
End If
Set FindNextEmptyCell = Result
End Function
Sub CopyAndPasteSpecial2()
Dim Target As Range
Sheets("Sheet2").Range("b1:B5").Copy
Set Target = FindNextBlankCell(Range("A1"))
Target.PasteSpecial xlPasteValues
End Sub
hi adamsgi
You could also use this function, it might be slight overkill, but basically you can give it a cell reference and it will return the next blank cell:
' This has been tested and you should just be able to copy and paste it into vba
Function FindNextBlankCell(FirstCell As Range)
Dim Result As Range
Set Result = FirstCell
If Result.Value = "" Then
' Our starting cell is empty. We can just use it
Else
Set Result = FirstCell.End(xlDown) ' Find the last non-blank cell
If Result.Row = Result.Parent.Rows.Count Then
' If we end up at the bottom of the worksheet, this means that only
' the first cell was populated. Go back to it
Set Result = FirstCell
Else
' We are now on the last used cell
End If
' Goto the next row down
Set Result = Result.Cells(2, 1)
End If
Set FindNextEmptyCell = Result
End Function
Example:
Dim Target As Range
Selection.Copy
Set Target = FindNextBlankCell(Range("A1"))
Target.PasteSpecial xlPasteValues
End Sub
Hope this helps,
Any problems, just reply to my post
cheers!
This is all you have to do:
sht=Range("B4")
Sheets(sht).select
actually come to think of it, this code may not work, as excel is not good at finding nothing. Instead perhaps place a dummy value or "###" at the end of your series of cells.
Then get excel to find this, paste on top of it, then place ### in the following cell for next time the command is issued
try this:
Cells.Find(What:="", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False) _
.Activate
and then paste it
Hey,
I have a large workbook in excel with around 60 sheets... Shock Horror I know;
Is there a way I can use a macro to check what value is in Sheet 1, Cell B4... (which contains the name of a sheet to view) and then activate this sheet?
I know it is possible to do this with several long statements such as:
If Range("B4") = "Sheet 2" Then Sheets("Sheet 6").Select
If Range("B4") = "Sheet 3" Then Sheets("Sheet 7").Select
If Range("B4") = "Sheet 4" Then Sheets("Sheet 8").Select
If Range("B4") = "Sheet 5" Then ....etc
However as I have such a large quantity this would be impractical. What is the solution tho this problem?
Thx,
-Barkerj
I need to write a piece of code that checks to see if (for example) cell B1 is empty. If it isn't, it checks cell C1, and so on until it finds a blank cell, and then pastes some data into it. I have tried various bits of source code found on the web, but nothing seems to work properly (I am copying a predefined area, whether there is data in it or not and need to minimise space on the worksheet, so I don't have 10 rows of blank space between records.)
Can anyone help?
Adam
HI,
I have created an interface which pastes data into an excel spreadsheet.
ONce the code has run, I want to pass the focus back to excel whilst keeping the form open to manipulate again.
How do I do this???
Thanks
HI,
This has now been solved thanks
I have created an interface which takes in data from the user and pastes it into an excel spreadsheet. The code checks if there is data in the cell to be pasted to and concatenates it if there is.
My problem is that if the cell contains the following
=40+50+60
and I want an additional 100 in there, so that it appears like this
=40+50+60+100
Instead I get this
=150+100
WHen the value is being taking from the cell in order to concatenate, my code is taking the total volume istead of the string equivalent "=40+50+60"
How can I avoid this??
I tried using the .formula code but I still get the total volume
Thanks
Ger
Using an Ansi report to be imported and final tallies MUST be done using only
Excel (licensing and system restrictions). Problem in the file is REAL ugly.
It is comprised of several different sections with different widths, not tab
or comma delimitated and need some information from at least two different
sections. Really need a hand with ideas I can use to capture this data.
Information needed:
Date From and To
Agent ID (5 digit ID starting in this case with the 57xxx)
Full Name (would be nice but willing to cross reference off of other tabs)
...and all the information under Taken Days
Having additional information such as:
Total Earned
Max Partial Hours
Total Taken
Total Debited
Remaining to Select
Would be ideal, but I am willing to forgo these if necessary.
Unfortunatly the report can not be modified in any way (otherwise I might
have an easier time) and no the import can not set to Export to Excel.
Can send the file to whomever request and have included a sample that would
be best to copy/paste into notepad before you look at it. The information has
been changed to protect the innocent.
---Report Start---
From: 12/28/03 Application
10/29/04
To: 12/31/04 Company Name
09:06
Agent
Vacations Vacation Group
Page: 1
Vacation Group: LV Skills 1,2,4,7 Vacation Year:
2004 Master From Date: 11/07/04
Sorted by: Agent Name
Master To Date: 12/31/04
Show: Cancelled Selections - No, Comments - No, Detail and Summary, Summary
Times in HH:MM
Report Agent Moves: No
57071 - Last Name, First Name Initial
Bidding Date: 09/11/00- Accrual Date: 01/01/04
====================|=========|=========|=========|=========||=====|=========|=========||=========|=========|=========|=========|=========
| | | |Carryover|| Min|
Max| Partial|| | | |Carryover|Remaining
| | | Total| From|| Full|
Partial| Hours|| Total| Total| Total| To| to
Vacation Type | From| To| Earned|Last Year||Weeks|
Hours| Selected|| Selected| Taken| Debited|Next Year| Select
====================|=========|=========|=========|=========||=====|=========|=========||=========|=========|=========|=========|=========
Vacation Day Paid | 01/01/04| 12/31/04| 66:40| 04:00|| 0|
80:00| 00:00|| 00:00| 49:07| 15:59| 00:00| 05:34
Discretionary Day Pd| 01/01/04| 12/31/04| 52:00| 00:00|| 0|
56:00| 00:00|| 00:00| 19:55| 32:00| 00:00| 00:05
--------------------+---------+---------+---------+---------++-----+---------+---------++---------+---------+---------+---------+---------
Totals | | | 118:40| 04:00|| 0|
136:00| 00:00|| 00:00| 69:02| 47:59| 00:00| 05:39
--------------------+---------+---------+---------+---------++-----+---------+---------++---------+---------+---------+---------+---------
Wait List | 01/01/04| 12/31/04| 40:00| 00:00|| 0|
00:00| 00:00|| 00:00| | | 00:00| 40:00
====================|=========|=========|=========|=========||=====|=========|=========||=========|=========|=========|=========|=========
Taken Days
Taken
---------------------------------Selection------------------
Day Date Range Type Length Year Time Length
Last Changed Selected By
=== ================= ==================== ====== ==== =========== ======
============== ====================
Wed 06/02/04 Vacation Day Paid 08:00
Fri 06/04/04 Vacation Day Paid 08:00
Wed 06/16/04 Vacation Day Paid 08:00
Thu 07/08/04 Discretionary Day Pd 01:00
Tue 07/13/04 Discretionary Day Pd 02:00
Fri 07/23/04 Discretionary Day Pd 00:55
Fri 07/30/04 Discretionary Day Pd 02:15
Tue 08/17/04 Vacation Day Paid &nbs
I am working on a vba program in excel with some others and wondered how we could incorporate a version control system , doesnt seem to work with the ordinary version control systems, any ideas?????
Hi,
does anyone know a way to start the execution of a macro through command line arguments, e.g. when starting Excel from within another application? The idea is to launch excel by having the user click a button in the application and instantly generate a diagram of the values previously computed by the application.
Thanks for your help.
does anyone know how i can make it so i can have some buttons always on top of all other windows and have excel minimized either to task bar or preferably the tray area?
Thanks
i want my xcel macro to be converted into vb code for standalone execution.
Hello there!
I don't know if I get it, but I'll try my best:
Excel can do part of the work for you.
Excel treat the dates like days using integer numbers, and the date its formatted just for display purpouses, so you can handle them with no much trouble (especially if you want to do some calculations with them) especifiying in the Excel menu "Format | Cells ... | Number" the "General" option.
Example:
Use a pair of cells, one with the initial date, A1, and other with the final date, A2, and in a third one, B1, calculate the diference between the dates (=A2-A1). Now especify the format of the cell B1 like "General" (like I told you before). This result will be in days, so now you can calculate the hours multiplying it by 24. ( =(A2-A1)*24 ).
Hope it helps
Kaneda
Probably something simple, but I can't find it. When creating a function add in, how do you deit the comments on the formula palette. Specifically the comments that describe each field.
Hemdog
Hello,
I have difficulties at present with Visual basic and their programming for Excel! I need a code with which I in a certain year by the selection certainly second days in one month, by which selection of these two days along-considers the distance annually in hours those time adjustment to the summer or to the winter. Example: My selection is 5.April.2004 and 9.April.2004. The calculation gives with the hours, in this case by the 4 days 4*24=94h, between these day after the 9.April.2004 is me the days given will along from the month April to 31.April and no more the hours the 1.Mai.2004 to become me all months returned in the year still remaining to remain. I do not have the following code unfortunately white I where I to begin must. If me someone to support I could do much would be pleased.
Best regards,
Munzur
How do I format cells for a given column on a new worksheet created using VBA. My code creates charts that have datetimes on the x-axis. Excel doesnt display the datetimes correctly unless the column is formatted to text (normally by right-clicking -> format cells). When my code creates a new worksheet, how can i format the cells to text for the column that contains the datetimes, so that the chart displys correctly?
I'm trying to make a macro that does the following:
- Asks how many new excel files you'd like to create
- Counts all of the records from the main file and divides them up evenly between the new files
For instance, my main file will likely have 500 records. If I say I want 5 files, I want it to count how many records the main file contains (500), and create 5 new files with 100 records each.
I'm very new to VBA and have no idea on the syntax to use.
Thanks,
Wire
i believe this is what you are asking
Private Sub CommandButton1_Click()
Sheets("sheet1").Activate
ActiveSheet.Range("A1").Select 'select initial cell position
count = active cell.value 'set $a1 to = 5000
coladdr1 = ActiveCell.Offset(0, 1).Address 'set coladdr1 = cell address $b1
coladdr2 = ActiveCell.Offset(0, 3).Address 'set coladdr2 = cell address $d1
filldwn1 = ActiveCell.Offset(count, 1).Address
filldwn2 = ActiveCell.Offset(count, 3).Address
Set matrix = ActiveSheet.Range(coladdr1, coladdr2) 'select $b1 through $d1
Set matrix2 = ActiveSheet.Range(filldwn1, filldwn2) 'select $b5000 through $d5000
ActiveSheet.Range(matrix, matrix2).FillDown 'fills down based on contents in $b1:$d1
End Sub
or you can also select matrix to equal :
matrix = Sheets("sheet1").Range("c1:e1")
Can excell and if so, how can I have a number in let's say cell b2 which is a count(spreadsheet! d:d) which equals 5000
then I want do copy cells c1 through e1 down to c5000 e5000
how can this be down I think I use the filldown command in the marco but don't know how\
THanks
Dave
i read this article and it helps me but i have two questions:
i am doing the following: i have a dataset and i create an excel file from this dataset(vb.net application)
i have 2 questions:
1-i want to put the text in the cell in the center (center alignement), can any property do it, in your article you explain how to change the font, the color.
2-i want to change the width of the cell: i used this property: StandardWidth. so all the columns took the value of this cell, this is not what i want. i want for example the first column, width=15; the second column width=20
can i do that?
I have written a simple program that charts multiple charts from a specified source data range.
The range, and chart objects are expressed depending on the users input by date.
From here a chart, form, and report is made up.
The code will work fine for everything until I allow the file to be "shared"
When Shared, everything works except for specific chart objects (legend and axis names)
I can change the titles, source data, type of chart, location of titles and legend, font....)
Im only using excel 97 and i realize certain limitations are given to procedures (macros) when sharing a file for multi use. However i feel if i can modify the other chart objects, there should be a way to modify the legend and axis names when shared.
This is the code im using....Any suggestions Please Help
Sheets("U1RHR").Activate
Sheets("U1RHR").Range("B3").Activate ' first date address
Sheets("U1RHR").Range(ActiveCell, ActiveCell.End(xlDown)).Select
Dim valuemin As Date ' minimal date allowed (first date - B3)
valuemin = ActiveCell.value
Dim value As Date
Do Until value > valuemin
On Error Resume Next
value = InputBox(Prompt:="Enter the PI Date mm/yyyy OR Text (MONTH YYYY).", Default:=Format(Date, "mmm yyyy"))
If value = False Then
Sheets("Update").Select
Exit Sub
End If
If value < valuemin Then
MsgBox "you have entered an incorrect date format"
Else
Selection.FIND(what:=value).Activate
End If
Loop
'Defines address Range of Xaxis
rowaddr1 = ActiveCell.Address
rowaddr2 = ActiveCell.Offset(-12, 0).Address
'Defines address Range of Matrix Series (5 columns)
coladdr1 = ActiveCell.Offset(0, 16).Address
coladdr2 = ActiveCell.Offset(-12, 4).Address
'update chart 1
Set xaxis = Sheets("U1RHR").Range(rowaddr1, rowaddr2)
Set Matrix = Sheets("U1RHR").Range(coladdr1, coladdr2)
Sheets("Mchart_U1RHR").Select 'select chart
ActiveChart.Axes(xlCategory).CategoryNames = xaxis
ActiveChart.SetSourceData (Matrix)
ActiveChart.HasLegend = True
ActiveChart.Legend.Position = xlLegendPositionBottom
ActiveChart.HasDataTable = False
ActiveChart.Axes(xlCategory).CategoryType = xlCategoryScale
ActiveChart.Axes(xlCategory).HasMajorGridlines = False
ActiveChart.Axes(xlValue).HasMajorGridlines = False
ActiveChart.Axes(xlValue).HasTitle = True
ActiveChart.Axes(xlValue).AxisTitle.Caption = "% Unavailable 12-Month Rolling Average"
ActiveChart.Axes(xlValue).AxisTitle.Font.Size = 12
ActiveChart.Axes(xlValue).AxisTitle.Font.FontStyle = "bold"
ActiveChart.SizeWithWindow = True
ActiveChart.HasTitle = True
ActiveChart.ChartTitle.Font.Size = "18"
ActiveChart.ChartTitle.Font.FontStyle = "Bold"
ActiveChart.ChartTitle.Text = "NMP1 PROGRESSIVE Residual Heat Removal System Performance"
ActiveChart.PlotArea.Fill.PresetTextured (msoTextureRecycledPaper)
' ----------------------------------------------------------------------------------------------
' the next steps will not modify the axis and legend names
' all prior info is erased, relabeled by msexcel as series1,2.... and 1,2,3....
ActiveChart.SeriesCollection(1).XValues = xaxis
ActiveChart.Axes(xlCategory).CategoryNames = xaxis
ActiveChart.SeriesCollection(1).Name = "WANO"
ActiveChart.SeriesCollection(2).Name = "NRC(G/W) < 1.5%"
ActiveChart.SeriesCollection(3).Name = "BP GOAL"
ActiveChart.SeriesCollection(4).Name = "TQ"
ActiveChart.SeriesCollection(5).Name = "RHR Avg (NRC)"
ActiveChart.SeriesCollection(6).Name = "Train 111"
ActiveChart.SeriesCollection(7).Name = "Train 112"
ActiveChart.SeriesCollection(8).Name = "Train 121"
ActiveChart.SeriesCollection(9).Name = "Train 122"
ActiveChart.SeriesCollection(10).Name = "Train 11"
ActiveChart.SeriesCollection(11).Name = "Train 12"
ActiveChart.SeriesCollection(12).Name = "Train 13"
ActiveChart.SeriesCollection(13).Name = "RHR Avg (WANO)"
' -----------------------------------
' it continues...but i have ended the sub here until i can figure out a way to modify the axis and legend names within a shared file
Any suggestions, please help.
You can use conditional formating.
Click on Tools --> Conditional formating
Hope this helps.
Chao --
Could someone please help me with my problem? Its about an IF function in excel...
well this is what i want to achieve with an IF function:
I have 3 columns with ad random the letters R, S and I in them. I need a code that would change the background of the cells. If there is a S in the cell, the cell must turn green, if there is an I then Orange and if R then red.
thx!
It appears that you cannot link cell formats as easily as values (with a formula).
One way to do what you want manually is by copying and pasting formats only.
Make sure you backup and try this out on a test copy of your file.
Select the range of cells you want to keep the format of and click Edit -> Copy.
Then Select the range of cells where you want to "Paste" these formats.
Click Edit -> Paste Special -> Formats -> OK
(note: the size of your copied selection must match the size of the pasted selection)
Now all the format attributes have been copied over.
You could do this every time you made a change, or record this procedure in a macro.
Click record and do the above procedure, click stop.
The record macro button, and the play macro button are on the Visual Basic Toolbar.
You can create a shortcut (e.g. CTRL + M) in the play macro screen under options.
The recording of a macro creates visual basic code to accomplish the task, so you can see what the macro is doing.
I know this seems like overkill, but it should work.
Good luck.
hi
i want to add menu to word with some shortcut key
and then i want to show some dialog box which will list all the fonts used in that word document which is opened
pls help me
I guess you already have your required data(which is to be displayed) ready with you in certain variables or contants.
Then you may try this:
With Worksheets("sheet1").Cells(a,b)
.Value = s
End With
this peice of code will write the value of s in cell(a,b),i.e row a column b
you can also specify a range of cells by .Range property.
Now you can access the Required cells and do the required manipulation:
With Worksheets("sheet1").Cells(a,b)
if .Value = blahblah then
whatever u want
EndIf
End With
what exactly do u want to do???
insert some fields into a text file??
and you are taking your Input from excel or another text file??
If this is exactly what you want to do then I can dare suggest something.
You can take your input easily from any source....as far as writing in a text file is concerned....just select microsoft scripting runtime from References menu of Tools ...then declare a variable as TextStream
e.g: Dim ts as TextStream
now open the file to be written into in a suitable mode by Set command
e.g:
Dim fs As New FileSystemObject
outputlocation = "D:/111.txt"
Set ts = fs.OpenTextFile(outputlocation, ForAppending, False, TristateUseDefault)
now capture the Input stream in a variable,say,
inputname = "blahblah"
now do this
ts.write(inputname)
this would write the name "blahblah" in your file 111.txt
....
bye
Hi for all
This is very hard.
I want to insert into a text file create by Excel some information like name, address and so on.
The text file is saved and opened for data inserts.
The file is opened, excel´s macro waits 'till I save the file.
Then, it copy each line of the text file and put in a excel´s row.
Please HELP MEEEEEEE!!
Hughes for all
Alex
Try inserting code to go to the sheet before the call of the dataform.
Example:
Range("A4").Select
ActiveSheet.ShowDataForm
I had the same error after putting in a button to goto the dataform. After inserting the line, bingo it worked.
I think the problem in Excel is that it didn't know where the call was from, but after just going to any cell on the sheet it knew where it was.
Hi !
How can I open a sheet by VB with Openoffice ?? with Excel, I know..
Thanks
Hi !
How can I open a sheet by VB with Openoffice ?? with Excel, I know..
Thanks
Hope you can help with this one. I have done the following and it works, but I need some code to make the cell background change. Here is the background of my problem:
I have a workbook with 2 worksheets. One is called "DataEntry" and the other is called "Invoice". I have a column on both with the heading title of "Name Last, First".
On the Invoice worksheet I have the following formula for the cells: =DataEntry!A5. This would take the value from the DataEntry worksheet and place it in the Invoice worksheet, therefore it is linked.
That is working fine, but if I change the text color in the DataEntry worksheet, it doesn't change the text color in the Invoice worksheet.
This is what I am using and it works:
Steps:
1) In your DataEntry sheet, assign the following range name to the input cell: inp1
2) In your Invoice sheet, assign the following range name to the corresponding (linked) cell: inv1
One method of assigning a range name:
a) Highlight the cell (or range of cells)
b) Hold down <Control> and hit <F3>
c) Type the name
d) Hit <Enter>
3) Enter the following "Worksheet_Activate" event for your Invoice sheet.
Private Sub Worksheet_Activate()
[inv1].Font.ColorIndex = [inp1].Font.ColorIndex
End Sub
In case you're not familiar with VBA, here are further steps...
3a) To enter the VBA Editor, hold down <Alt> and hit <F11>
3b) If your sheets are not shown on the left, use the VBA menu - choose: View - Project Explorer.
3c) Under "Microsoft Excel Objects", click on the "Invoice" sheet.
3d) On the right side of the screen, copy and paste the above routine from here in Tek-Tips to this location in the VBA Editor.
I need to also make the cell background color change as well. This is what I tried, but it didn't work:
Private Sub Worksheet_Activate()
[inv1].ActiveCell.Font.ColorIndex = [inp1].ActiveCell.Font.ColorIndex
End Sub
Can someone help me get the cells to change automatically as well?
Any help would be surely appreciated.
Thanks in advance,
Hi, everyone. I'm a new member and need help.
Please help!!! I've always used Excel. Recently, I've taken a new position and I've been given the task of converting a Lotus macro (see below) used on several worksheets to Excel.
It appears to be a simple macro even when I let it run step by step; however, it appears very difficult to write a program in Excel to duplicate its function.
Thank you!
Mike ("desparately seeking macro")
+{LEFT 5}-{LEFT 9}~
{IF @CELLPOINTER("CONTENTS")>0}{END}{LEFT}/M{R 4}{END}{DOWN}~{DOWN}~{R 5}{D}{BRANCH RECON}
{IF @CELLPOINTER("CONTENTS")=0}{DOWN}{BRANCH RECON}
{LEFT 9}/M.{END}{DOWN}{RIGHT 2}~{DOWN}~{RIGHT 9}{DOWN}
{BRANCH RECON}
Hi,
I'm using OLE Automation for reading the contents of an Excel File. I have to read the cell color (either text color, fillcolor). I'm using Worksheet object for reading the contents. How can I do this?
While using Activesheet.Interior.Color, I got an error "Object doesn't set". How to use this?
The code which i'm using to read the content is
sheetobj.rows.cells(i,j)
Thanks in advance
Hi Jonj,
I would like to assign the following code to a button in excel.
Sub DataForm()
activesheet.ShowDataForm
End sub
I have done this however I keep getting the same error? Run Time Error 1004. I can't seem to make head nor tale of this can anyone help?
There is no syntax error, U need not worry, the only problem because of what run time error 1004 is occuring is that u have not created a data form. first create the data for the active sheet. Then, click on the button , it will work..
Hi! i'm sorry i have no answer to your problem, in fact i got interested in your message because you said you were able to use OpenOffice from VB. how?we are currently exploring alternatives to MS Office, and one of our difficulties is Excel automation from VB6. thanks in advance.
No, the dataform on Excel is under the Data menu and is called Form. This code allows you to bring it up without going to the menu's
Don't you mean:
Sub button name_click()
Userform name.show
End sub
Or are you trying to do something diffrent?
I would like to assign the following code to a button in excel.
Sub DataForm()
activesheet.ShowDataForm
End sub
I have done this however I keep getting the same error? Run Time Error 1004. I can't seem to make head nor tale of this can anyone help?
Hello,
I want to apply VBA features in Excel to perform the following task,but I did'nt use VBA in Excel before.
Actually I linked Oracle database with excel and query the database ,in this way the output from database display in Excel Sheet.
I am also using The another work sheet which have graphics which is
triggered by the output of the SQL Scripts.
Now I want to perform the following,How can I do this?
Every time If buffer hit ratio(Oracle) has a low value
then A cell in EXCEL sheet get red ,where the output stored.
Or
and after the query ,If buffer cache hit rate(Oracle) is not good a red bar appears
automatically in the graph sheet and if the buffer cache hit rate (Oracle) is satisfactory the green bar will appear in the graph.How will it possible.
My friend told me that it is possible only in VBA.
I will be very thankful if some one write the code in VBA to perform the above activity.
Bilal Ahmed
Easy, you should use the following code :
Range("yourcell").Value=yourvar
i am working on an application where i am opening openoffice through visual basic. i am able to open
openoffice and format cells accrodingly through visual basic. but i am not able to add sheets
through visual basic.
Please help me in adding sheets through visual basic in openoffice
Reagrds,
Mukul Malhotra
Does anyone know how to read a pattern in an Excel macro? I do not want to change the pattern just count how many of each pattern is in a range.
Does anyone know how to read a pattern in an Excel macro? I do not want to change the pattern just count how many of each pattern is in a range.
I am trying to calculate some data in a macro in excell, and I have no problem with that, but I do not know how to put that calculation back into the cell I want in the workbook I want.
If anyone has any idea or suggestion I would appreciate it
Hi, what I am trying to do is that when I click a cell in Excell, I want to see files list in a specified folder and then select the file
Thank alot
Hi,
my question is, in excel sheet i am having data where in one coloumn consists of dates, what i want is that to extract the row corresponding to the oldest date. hope my question is clear please help if you get a solution.
A friend helped me write a vba which adds together all the different combinations of an array of data. I use this on two data sets and then look for equal sums. Once I have found equal sums, I find in the adjacent columns which components make up those sums. I am trying to write a vba to do this for me, but am having great trouble since I am new to this. Can anybody help me? Here is what I have so far:
Public Sub test5()
Dim y As Integer
Dim z As Integer
Dim myResultRowNumber As Integer
Dim myResultCol1 As Integer
Dim myResultCol2 As Integer
a = Range("A:A")
b = Range("B:B")
c = Range("C:C")
d = Range("D
myResultCol1 = 5
myResultCol2 = 6
For z = 0 To d.Length - 1
For y = 0 To b.Length - 1
If b(y).Value = d(z).Value Then
Sheet6.Cells(myResultRowNumber, myResultCol1) = a(y)
Sheet6.Cells(myResultRowNumber, myResultCol2) = c(z)
End If
Next y
Next z
End Sub
Hi
I have written a macro which would ask the user a name, the name is then entered in a particular cell of the sheet and saved as '<name>.xls'. But the new xls file created also asks for a name when opened. Kindly help me out to prevent this macro getting run in the file created.
Thanx
Kanamu
You will probably want to post this question in our VBA forum ( http://www.developerfusion.com/forums/forum.aspx?id=6226 ) rather than here.
Hi
I am new to this area and would appreciate if anyone could help me with my problem. I have a created a form in excel which includes a name box and 4 buttons (in which I got some check boxes). I need to know how to write a code that each time I enter a name in the form, with its related checked or unchecked boxes that I call from each button, it save it individually in the desktop.
Thanks
Bruce
This thread is for discussions of VBA in Excel.
Leave a comment
Sign in or Join us (it's free).