ANmarS - Search results

Excel/VBA expert since Sep 1997 (21 years 2 months)

We are moving ...

We are moving to another location, better, faster and more resources .... check it out ANmar.Systems

49 Q & A posts tagged with macro

Mon
Feb 6
2012

Copy pictures from sheet to another

i have two workbooks, lets say, workbook A and Library Workbook
Workbook A is where all the code has been going, Library has no code in it. My project in Workbook A needs to look up and pull a worksheet out of Library Workbook - just a direct copy. Simple enough in general, only one problem. Most of the sheets in Library Workbook have images embedded in them that dont get copied when doing a direct Workbooks(Library)Cells.Copy
i can get all of the formatting, column widths, and values/formulas, but not the images
now, there is one thing i havent tried that I think will work, but it has drawbacks...i could copy the entire sheet, not just the cells, but the sheet itself and put it in Workbook A. I think that this would work in theory, but Im reluctant to try it for one reason
I have embedded sheet code in Workbook A in the sheet that I want to be the destination for the copied library contents. The sheet code is event-based, and Id like it to stay in the code. So I was trying to paste the library contents into this sheet to retain the sheet code, where if I paste the entire sheet directly from the library, I dont get to keep the sheet code, because the library, by definition, is supposed to be (vba free)

so...Ive been chasing two different possible solutions, neither one with much luck yet.
(1) Learn how to copy cells from a worksheet with the images included
i know you can use vba to copy images, but the sheets have varying numbers of images, so theres no good way to programatically handle all sheets
shapes(1).copy, ...
might be more than one image


Answer

you still can use shapes.count and start a loop, did you tried that?
however, there might be a downside, when you copy and paste, they will all be over each other, but you can override it by using:
Sheet1.shapes(x).left = Sheet2.shapes(x).left
sheet1.shapes(x).top = sheet2.shapes(x).top
assuming copying shape from Sheet2 to Sheet1
does that make sense?

Monday 2/6/2012
Wed
Nov 16
2011

Correct VBA Code on microsoft visual basic?

please help, it is not working. Thanks.

VBA CODE:

Sub StemAndLeaf()
dataColumn = 1

'Clean everything out of the Stem worksheet.
Worksheets("Stem").Cells.Clear

'Look at the Data worksheet.
Worksheets("Data").Activate

'Find the maximum value.
rowPointer = 2
Do Until Cells(rowPointer, 1).Value = ""
rowPointer = rowPointer + 1
Loop
Maximum = Cells(rowPointer - 1, dataColumn).Value

'Set the divisor to strip off leaves.
divisor = 1
Do Until Maximum / divisor <= 10
divisor = divisor * 10
Loop

'If the first digit of the largest value is less than 5, then
'use a smaller divisor.
'Otherwise you could end up with four or fewer rows in the plot.
If Fix(Maximum / divisor) < 5 Then divisor = divisor * 10

'Calculate the top stem's value.
topStem = Fix(Maximum / divisor)

'Set up the Stem worksheet.
Worksheets("Stem").Activate
Cells(1, 1).Value = "Count"
Cells(1, 2).Value = "Stem"
Cells(1, 3).Value = "Leaves"
For rowPointer = 2 To topStem + 2
Cells(rowPointer, 2).Value = rowPointer - 2
Cells(rowPointer, 3).Value = "|"
Next rowPointer

'Calculate the counts.
'The following code is slower than it needs to be,
'but a faster code would be harder to read and understand.
Worksheets("Data").Activate
rowPointer = 2
Do Until Cells(rowPointer, dataColumn).Value = ""
measurement = Cells(rowPointer, dataColumn).Value
Stem = Fix(measurement / divisor)
Worksheets("Stem").Cells(Stem + 2, 1).Value = Worksheets("Stem").Cells(Stem + 2, 1).Value + 1
rowPointer = rowPointer + 1
Loop

'Calculate the shrink factor.
Worksheets("Stem").Activate
maximumCount = 0
For rowPointer = 2 To topStem + 2
If Cells(rowPointer, 1).Value > maximumCount Then
maximumCount = Cells(rowPointer, 1).Value
End If
Next rowPointer

shrinkFactor = Fix(maximumCount / 50)
If shrinkFactor < 1 Then shrinkFactor = 1
Cells(1, 4).Value = "Each digit represents" + Str(shrinkFactor) + " cases."

'Return to the data, and fill the leaves in light of the values in the data.
Worksheets("Data").Activate
rowPointer = 2
Do Until Cells(rowPointer, dataColumn).Value = ""
measurement = Cells(rowPointer, dataColumn).Value
Stem = Fix(measurement / divisor)
leaf = measurement - Stem * divisor
leaf = Fix(leaf * 10 / divisor)

Worksheets("Stem").Cells(Stem + 2, 3).Value = Worksheets("Stem").Cells(Stem + 2, 3).Value + Trim(Str(leaf))
rowPointer = rowPointer + shrinkFactor
Loop

'Get to the Stem worksheet.
Worksheets("Stem").Activate
End Sub



Read more: How to Make a Stem & Leaf Plot in Excel 2007 | eHow.com http://www.ehow.com/how_6408182_make-leaf-plot-excel-2007.html#ixzz1dsvrvNfw


Answer

What is the problem? what error code generated?

as a start, you should have two sheets named "Data" and "Stem"

let me know, I will be glad to help, contact me here

Wednesday 11/16/2011 4:07:39 PM
Wed
Nov 16
2011

Beginner Programming Task Questions?

Hi all,

I have next to no programming knowledge but keen to learn.

My goal is to create a simple program ~ where it goes to a website, gets certain current information, puts it into MS excel and crunches some numbers for me (few further things i'd like after).

Assuming i'm starting with nothing what should I be looking at to make this happen? Programming language (im guessing C?+?) and what do i need to create a program using C+? anything else i need to know?

Many thanks.


Answer

Microsoft Excel itself has a programming language called (Visual Basic for Application) aka VBA or macros
I would suggest that you start with that
In addition you may need to learn some HTML top read website directly

That been said, there is a code that enables you to log in certain website and do whatever you need to do with it, like search, register, sign in, etc

Get it here as a start
http://www.access-programmers.co.uk/foru…
here
http://vba-corner.livejournal.com/4623.h…
and here
http://www.excely.com/excel-vba/ie-autom…

If you stuck somewhere you may always contact me, most of my help to others are free.

Wednesday 11/16/2011 9:21:06 AM
Sun
Nov 13
2011

How to exchange text into random number and then exchange back?

I need a code so that when I run a macro in excel it will exchange items enclosed in a parenthesis to a random number. For example the text in cell A1 is: {I have a (cat) and a (dog)}. {I have (two) cats and (three) dogs}.
What I need the macro to do is that:
1. Look at the cell and search for curly braces
2. Look for text enclosed in a parenthesis
3. Replace the text with a random number
4. Replace the number with the original text
5. Then move on to the next curly braces and repeat the process

So it would look like this:
1. {I have a (cat) and a (dog)}. {I have (two) cats and (three) dogs}.
2. {I have a (1.8) and a (3.4)}. {I have (two) cats and (three) dogs}.
3. {I have a (cat) and a (dog)}. {I have (two) cats and (three) dogs}.
4. {I have a (cat) and a (dog)}. {I have (.9) cats and (1) dogs}.
5. {I have a (cat) and a (dog)}. {I have (two) cats and (three) dogs}.

All your help will be greatly appreciated.


Answer

Try doing something like this

Range("A1").value = Replace(Range("A1").value, "(dog)", "(" & Int( Rand()*100) & ") dogs")

This is the main command that you will be needing
You can actually repeat it to cover all options like these:

Range("A1").value = Replace(Range("A1").value, "(dog)", "(" & Int( Rand()*100) & ") dogs")
Range("A1").value = Replace(Range("A1").value, "(mouse)", "(" & Int( Rand()*100) & ") mice")
Range("A1").value = Replace(Range("A1").value, "(cat)", "(" & Int( Rand()*100) & ") cats")


And then put it inside a loop to check for them, like this

X1=1
Max1 = range("A1" ).currentregion.rows.count
do until x1>max1
Range("A1").offset(X1 ).value = Replace(Range("A1").offset(X1 ).value, "(dog)", "(" & Int( Rand()*100) & ") dogs")
x1=x1+1
loop





let me know if you need more help

Sunday 11/13/2011 6:58:22 PM
Sun
Nov 13
2011

How to assign a random number as an id to a marked text in a cell in excel?

What code can I use so that I can assign a random number as a the ID for a text in a cell. For example cell A1 has these sentences:
{I have (three) dogs and (two) cats}. My pets always fight. {My (dog) is afraid of my (cat) and they are both afraid of (mice)}.
I need an excel macro code so that it will assign a random number as an ID for the items enclosed in the parenthesis. I need this macro to assign a random number ID one at a time. In other words what I want the macro to do is to:
1. Search for curly braces in the cell
2. Get all the items enclosed in the curly braces that are enclosed in parenthesis
3. Assign a random number as an ID to the items in the parenthesis without touching the other text
4. Then move on to the next text enclosed in curly braces and continue until there are no more curly braces in the cell

So what the macro will do is that it will look at the contents of the selected cell. Then look for curly braces then look for words that are enclosed in parenthesis and then assign a random number as an ID for each then move on to the next curly braces.

The curly braces and the parenthesis may be changed to any marker.

All help is greatly appreciated.


Answer

The main command you might need is:
Range("A1").value = Replace(Range("A1").value, "(dog)", "(" & Int( Rand()*100) & ") dogs")

Doing it repeatedly is the trick that you will be using to replace all options once found, note that "Replace" works only if that text is found

And then put it inside a loop to check for them, like this

X1=1
Max1 = range("A1" ).currentregion.rows.count
do until x1>max1
Range("A1").offset(X1 ).value = Replace(Range("A1").offset(X1 ).value, "(dog)", "(" & Int( Rand()*100) & ") dogs")
Range("A1").offset(X1 ).value = Replace(Range("A1").offset(X1 ).value, "(cat)", "(" & Int( Rand()*100) & ") cats")
Range("A1").offset(X1 ).value = Replace(Range("A1").offset(X1 ).value, "(mouse)", "(" & Int( Rand()*100) & ") mice")
x1=x1+1
loop


let me know if you need more help

Sunday 11/13/2011 6:47:20 PM
Sat
Nov 12
2011

How do you insert external data into excel STARTER?

This question is specifically for Microsoft Excel STARTER. It's not like on other versions of excel where you have a data tab and can follow the steps from there.
I have already checked google also so please don't suggest pages on their or searching it on google. I have also checked the microsoft website.

BASICALLY, if you personally know how you insert data into Microsoft Excel STARTER I would really appreciate the steps on how to do it :) And will rate best answer for whoever tells me. Thanks


Answer

There are actually several ways, basically using VBA or macros
If you know how to do that there are several macros out there

I would suggest one of these
http://www.anmars.com/Work/?P=598866300
to read directly from MySQL DB into Excel sheet

or use
http://social.msdn.microsoft.com/Forums/…
to read from sql or Access

Also
http://www.bygsoftware.com/Excel/SQL/Usi…

Again, those are macros (Using the back door for Excel) to insert these data

If none of them helped, let me know from where are you inserting the data and I would be glad to show you the way.

let me know if that helped

Saturday 11/12/2011 2:17:09 AM
Mon
May 3
2010

Need help putting quotes around my text in excel spreadsheet?

Hi,

I have an excel spreadsheet with different words in all the cells. I need to put quotes around each word. I have tried using a macro but I am new to it and so far have not been able to get it to not just paste the exact same word I used to record the macro. Every cell is a different word. I need the macro to ignore the word in the cell and just put quotes around each individual word. Can anyone help with this??? I appreciate it!


Answer

The char code for the quotes is 34
Meaning, if you use Chr(34) in VBA code
or
=CHAR(34) in Excel formulas, you will get the quote

Maybe something like this, in formula bar
=CHAR(34)&C1&CHAR(34)

or

Range("A1").value= Chr(34) & Range("A1").value & Chr(34)

Hope this will help

VBAXLMan

Monday 5/3/2010 1:00:36 PM
Mon
Apr 26
2010

How to add check boxes to Excel 2007 to a column if a the row has data?

I want to have check boxes in column D, but I dont want the box boxes to appear unless data is intered into column A, that way I can keep the spread sheet clean because I will be adding data to this spread sheet daily. I have no idea how to even start this process. Any help would be great! Thanks!


Answer

The only ay to add checkbox automatically is using VBA or macros

However, there is another way, but using Validation instead
You can use Validation based on Validation, or Validation based on your value in column A
Make the validation list of Yes, No and you will get something similar to check box

Check out Multiple validations here
http://www.file1.net/lesson.asp?id=6y0x6…

VBAXLMan

Monday 4/26/2010 7:24:31 PM
Fri
Apr 16
2010

How to work with open Excel workbooks where filenames are given in cells?

I have the user input the file name into a cell. There are several open Excel files so the user will call out the name of one. What is the syntax to reference the named file so I can copy/paste?

The files come from emails so want to bypass having to save them and use the
...Workbooks.Open(Range("A5").Value)
command.
I've tried lots of variations with syntax but can't get it.


Answer

You need to create a loop through all open files, like this
Now, if you have the cell that holds the file name in A12 sheet1, then do this

MyFileName = Sheet1.Range( "A12").Value
For I=1 to Workbooks.Count
If UCase(Workbooks(i).Name) = UCase( MyFileName) then
Workbooks(i).Activate
Exit For
End If
Next

I used Activate because doping Open while the workbook is already open will generate an error

Try it and let me know (using my profile) if that solved it or not

VBAXLMan

Friday 4/16/2010 8:40:37 AM
Tue
Apr 6
2010

How to change the author of excel macros?

how to change the author of excel macros?

i've gone into visual basic and replaced the original author's name with my name, but it still appears with the original authors name when i use the macros in excel.

Thanks!


Answer

Excel saves these info usually in the few lines of the macro (the ones that start with ' )

So editing that will do it for most cases

However, you might also looking for the Author of the file itself (The file that has the macro), change that also in Right click in Explorer > Properties

Hope that is what you are looking for

VBAXLMan

Tuesday 4/6/2010 4:38:23 AM
Mon
Apr 5
2010

How do you run a macro within a formula in microsoft excel 2007?

.... for example If(A2=A3, "Run Macro", "")

thats what i want to do, i just dont know how to do it


Answer

A formula is ACTUALLY a macro
It is FUNCTION macro and not SUB macro

So, to do that, you need to replace the lines of your macro
from
Sub WhatEverName()
....
End Sub

TO

Function WhatEverName()
...
End Function

AND, putting that function in a module (and not a form module) will enables you to run it as formula
However, you need to go to "User-Defined Functions" in the Insert Function dialog and you should see it there

Keep in mind that this is a macro, means that you need to have the macros enabled for that file to make it run

Also, you can run that formula from any opened file, as long as the file that has the function (the function macro) is open

You can see it in action in this free file
http://www.file1.net/project.asp?id=b2vi…


Does that make sense? let me know if not

VBAXLMan http://www.file1.net/project.asp?id=b2vi…

Monday 4/5/2010 4:33:00 PM
Sun
Apr 4
2010

Create an automatic table based on the past three months?

hi, i have an excel sheet with the past five years data on it. Each row represents a month so each month it gets one row longer.

I already have a defined range that selects the most recent 3 months, but i need to know how to display the last 3 rows and the field headings on another sheet in a specific position by the click of a button(macro).

I can make a line graph using the last three rows of data with just a click of a button and now i want to show the data used underneath the chart.

Thanks for all answers


Answer

You don't need a macro to do that
You can make it in Formulas which means faster, auto updated, etc

Let's start with some assumptions:
- You have two sheets (Sheet1 has the full data and Sheet2 to have the last three months)
- In Sheet1, your data starts from cell A1 along to V1, with headers in first row and the data below it.
- In Sheet2, you want the last three months to be in A2 to V2, A1 to V1 has the row headers

Now, in Cells A1:V1, just copy the headers (you need to do that once)
In cell A2, paste this
=OFFSET( Sheet1!$A$1,COUNTA( Sheet1!$A:$A)-ABS(ROW()-1),COLUMN( )-1)

Then copy A2 along to V2, then copy Row2 into Row3 and Row4

These three rows will bring you the last three rows from your table in Sheet1 assuming you don't have any empty cells in that table in column A
And when you add new values at the end of that table in Sheet1, these will automatically updated

Does that make sense?

let me know

VBAXLMan

Sunday 4/4/2010 11:13:43 AM
Sun
Apr 4
2010

How do I copy particular images from one excel file to another in a automatic manner?

I have an excel file with more than ten thousand of product details and its images. Most of the times I am getting the requirement for images of 100 or 200 random items. Each time I have to search manually and copy those image to the new file. It would be great if there is any 'program' or 'shortcut' to paste those images from the original file to the newly requested file.
Thanks in advance


Answer

Excel has the most powerful macros (VBA), that we can use
I recommend saving the images out in a seperate folder, naming them with certain mask that has the product ID
Then, when you need a product image, you can easily let the macros to call its image

I use this technique all the times, however, you might need some VBA skills

VBAXLMan www.samotech.net

Sunday 4/4/2010 12:16:30 AM
Fri
Apr 2
2010

How can I put a private sub within a private sub?

I am working with visual basic 2008 and I want to create a timelog within excel but have the clock in and clock out within the same worksheet. I got it to work but the times are in different worksheets or should i say different books. I just need to know how to put them together here is the code:
Thanks

Public Class Form1


Private Sub Timer1_Tick(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Timer1.Tick
Label1.Text = TimeOfDay

End Sub


Private Sub ClockIn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ClockIn.Click

Dim Time As String = Now()

Dim Name As String = TextBox1.Text()


'writes time to label on form

Label2.Text = Time

Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object

'Start a new workbook in Excel
oExcel = CreateObject("Excel.Application")
oBook = oExcel.worksheet.Add


'Add data to cells of the first worksheet in the new workbook
oSheet = oBook.Worksheets(1)
oSheet.Range("A1").Value = Name
oSheet.Range("A1").Font.Bold = True
oSheet.Range("B1").Value = "Clock In"
oSheet.Range("B1").Font.Bold = True

'Adding the time
oSheet.Range("B2").Value = Time


End Sub


Private Sub ClockOut_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ClockOut.Click
Dim Time As String = Label2.Text

Dim Time2 As String = Now()

Dim Name As String = TextBox1.Text()

'writes time to label on form

Label3.Text = Time2


Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object

'Start a new workbook in Excel
oExcel = CreateObject("Excel.Application")
oBook = oExcel.Worksheet.Add


'Add data to cells of the first worksheet in the new workbook
oSheet = oBook.Worksheets(1)

oSheet.Range("C1").Value = "Clock Out"
oSheet.Range("C1").Font.Bold = True

'Adding the time
oSheet.Range("C2").Value = Time2
'Save the Workbook and Quit Excel
oBook.SaveAs("C:\Book1.xls")
oExcel.Quit()
End Sub


End Class


Answer

First of all, your question is confusing

You can not put sub inside another
Yes, you can call it from the other one, but not put it there

As far as I understood your question, here is my suggested answer

You need to Open that Excel file first, then modify it, then save it
What you are doing now is creating new workbook every ClockIn/ClockOut

I recommend putting these three in one workbook, call it main.xls for example
And open one workbook (already created) , add new rows for timein/timeout, save it and close, instead of creating new one every time

VBAXLMan

Friday 4/2/2010 5:44:51 PM
Fri
Apr 2
2010

How to rename worksheets (tab) in a locked workbook in MS Excel?

Hello, am working on a spreadsheet that is locked - But I need to rename the tabs. I know there is a way to open a new spreadsheet and create a macro and go back to the locked spreadsheet and run the macro. Can someone please help with the coding? Thanks


Answer

This is what you are talking about, right?

http://www.xl-logic.com/modules.php?name…

Let me know

VBAXLMan

Friday 4/2/2010 1:34:37 PM
Sat
Apr 25
2009

Code in Sheets vs. Code in Modules; Excel?

I transferred all my module code into specific worksheets modifying Subs to Private Subs and replacing Form Controls with ActiveX controls. Am I running into any disadvantages doing so? Is it helping me any, besides the fact that pressing ActiveX buttons feel more like clicking buttons than Form Control buttons do?


Answer

I can think of one...
If you want to do a USER-DEFINED-FUNCTION, then you can not do it in a worksheet module, you would have to do it in a Module

Other than this, You still can call any sub/function in a worksheet form other worksheets, using the "Public" key before the sub/function name


One more thing, selecting ActiveX or Form controls are not affected by Module or sheet module


VBAXLMan

Saturday 4/25/2009 1:48:16 PM
Sat
Apr 25
2009

I am using Microsoft Excel 2003.?

I am wanting to make a list of words so that by typing in the first letter it will automatically fill in the whole word. I do not want to have to type the list of words first (there are alot of them!!), but for this to be a setting which i can apply to different sheets. I do not want the validation option of a drop down list (there are just too many words i need to auto fill) Is this possible?
Please help! thank you!


Answer

Options 1:
If you have these words in one column, once you try to enter a word, Excel will automatically list the words that starts with the same letter

Option 2:
If you have all these words listed in a column also, right click on the empty cell (below it) and select "Pick from list"

Option 3:
Data Validate (which you already refused to do) but, you can make it Auto-updated, so when you add a word to your list, it will automatically be in the populated list
http://www.file1.net/lesson.asp?id=a1vh8…

Option 4:
Use some macros to do that

Option 4 requires some work and sample files, reply me here if you are interested



VBAXLMan http://www.file1.net/search.asp?query=va…

Saturday 4/25/2009 7:05:09 AM
Fri
Apr 24
2009

How do I get Excel to do this?

Let's say I type a numerical value into cell A1. How do I set it to auto-calculate 56% of that value and plop it back into that same cell? In other words, set a cell to display a calculated value of a number a user inputs into the same cell.


Answer

You would need a macro to do that, try this

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then
Target.Value = Val(Target.Value) * 65%
End If
End Sub

The 1 is for column A, change it to 2 if you have B, 3 if you have C, etc
Also change the 65% to whatever percentage you may have


To use that code, press ALT+F11, double click on the sheet name in the "Project Excplorer" window (Up left) and paste this code


Mail me here if you have problems


VBAXLMan

Friday 4/24/2009 7:08:35 PM
Thu
Apr 23
2009

[Excel/VBA] How to prevent excel to scroll up after typing ?

Hi

Each time I enter something at the bottom of a sheet, excel scrolls up the page (half of window height).
Is there a way to prevent (or reduce) this ?

Or some VBA to place a given row (last one) at the bottom of the window ?

Thanks in advance


Answer

First part of your question, no is the answer

For the second part, yes there is, it will depend on the Zoom percentage you are having and the height of your Excel screen

Try doing a macro to...
1- Scroll to the end of the end of your table (last row on your table)
2- Scroll back up to 10/20/30 rows
3- Activate any cell in the last row of your table

Something like this

Sub ShowLast()
CC=Worksheetfunction.Counta( Range("A:A"))
Range("A1").Offset( CC).Activate
Range("A1").Offset(CC-20).Activate
Range("A1").Offset( CC).Activate
End Sub


You need to try it and change the 20 to what ever suitable for you



Good luck



VBAXLMan

Thursday 4/23/2009 11:24:16 PM
Sat
Apr 18
2009

Macro to check character count & give error if exceeds 500, and display d cell with error in msgbox.plz help?

Macro to check the character count in excel worksheet and give error if exceeds charatcers exceeds 500 including spaces and other special characters. Also display the cell number with more than the specified limit in a msgbox. Pls help


Answer

You can not read the number of chars in a specified cell until the user hits Enter or selects another cell

That including VBA or Validation

If that is OK with you, you can use the Data > Validation method to achieve that
Here is a lesson on how to do that in Data > Validation in Excel2007
http://www.file1.net/lesson.asp?id=FLn3D…

VBAXLMan

Saturday 4/18/2009 5:48:23 AM
Sat
Apr 18
2009

Can you format an Excel cell with a formula and without using a macro or the Conditional Format menu?

Can you enter a formula directly into a cell to change the format of the cell, mainly the font color or the background shade color?


Answer

No
No formula there to change the format of another cell (If not macros nor Validation)

Forget it

Sorry!

VBAXLMan

Saturday 4/18/2009 12:52:04 AM
Thu
Apr 16
2009

It there a way(possibly write a macro) to automatically sort and delete data in Excel?

I am handling data which is a mix of numbers and letters, I need to sort by some value, delete, then re-sort and delete and so on, about 4-5 times.

Thanks,
Sharad


Answer

Here is an auto Sort free video lesson
http://file1.net/lesson.asp?id=XJTFWqI1c…
And this one
http://file1.net/lesson.asp?id=a17h833WW…

And this one one how to delete rows
http://file1.net/lesson.asp?id=b2wia4l8X…

VBAXLMan

Thursday 4/16/2009 3:28:55 PM
Tue
Apr 14
2009

How can we use macros in excel. how we can have advantage from macros?


Answer

Macros are automating Excel processes
So that you can Automate the daily Excel process you are doing for example
Copy certain region, save the file, open another one and paste the copied there
Or anything like that

There are a lot of Excel macro free files out there, check some of them here
http://www.file1.net/Projects.asp?id=18

There are 42 now

VBAXLMan

Tuesday 4/14/2009 1:34:05 PM
Mon
Apr 13
2009

Separate English and Traditional Chinese (excel file) into 2 columns without special characters (; or : or *)?

Hello there:

Hope u can help me...

I have a huge list of Buddhist terms (20,000) in Traditional Chinese, together with the English definition for each of the 20,000 entries. Problem: all of the terms are listed in a single column, with the english definition coming before every Traditional Chinese concept: what I try to mean with this is that some english definitions involve more than one single word (some definitions have 23 words as a whole), and the same concept, but in Chinese, might involve more than just one character (up to 7).

I cannot use a "space" to separate the text into different columns, because of the amentioned reason, there are not special characters that separate the English of the Chinese (such as ; or : or *), its all like the following sample:


9-11 am ??
a (walled) city ?
a bed ??
a day without work is a day without food ????????
a heap ?
a ko?i of nayutas ?????
a man ??
a thread, a butt ????
a wild fellow ???

like this, all the way up to 20,000

So, do u know of any way I can separate the whole text into different columns, based on the used script (Chinese and English)?

I asked some Computer science teachers at a local university: they suggested to create a macro that differentiates the script in use according to ASCII (english letters come with very low numbers, while Traditional Chinese must involve really big numbers, or so they said), but, I confess that my knowledge of computing is extremely low (main reason Im contacting u, btw...)

Hope u read this message, understand my broken english, and give me an answer...

Thanks a lot

Cheers


Answer

You don't need a macro to do that
I just fixed something like this (in pure functions) few days ago, it was about changing CamelCase to Proper, here is the file in case you are interested

Modifying that file, you can do something like this

If you have the cell in B4, paste this in E4

=IF(OR( COLUMN()-4>LEN( $B4),SUM( $D4:AC4)>0),"",IF(CODE( MID( $B4,COLUMN( )-4,1))>150,COLUMN( )-4,""))
Then copy and paste up to column AD (to get the possibilities of 26 characters)
Now in cell AE4, paste this to get the left side of the text (Before the first Chinese char)
=LEFT(B4,MAX(E4:AD4)-1)

And this in nAF4 to get the right part (after the first Chinese char)
=MID(B4,MAX( E4:AD4)+1,500)


Good luck with that, let me know if you didn't understand it or you want to edit it

VBAXLMan is here to fill your Excel needs

Monday 4/13/2009 12:09:05 PM
Mon
Apr 13
2009

In an Excel Macro, how do I return a value that the user selects in a userform?

For instance, the user can select one of three options via an option (radio) button (i.e. apple, orange, banana). I need code that will return this selection to module1 as a variable.


Answer

Paste this in your userform assuming:
1- You have the names of the radio buttons as Option1, Option2, etc
2- The user clicks Ok (named as "CmdOk")

sub CmdOk_Click()
If Option1.Value then Option_Sel=1
If Option2.Value then Option_Sel=2
' Do as many lines as you have in your Options
end sub

Then paste this in Module1

Public Option_Sel as integer


This will make the Option_Sel as global variable and then you can use it in any sub in your entire project


Let me know if that is fine or not


VBAXLMan

Monday 4/13/2009 8:58:02 AM
Mon
Apr 13
2009

How to add Counter to an excel workbook?

How to add Counter to an excel workbook, such that we can track how many times the excel had been viewed. The countability can be tracked?


Answer

- Press ALT + F11 to open VB Editor
- Double click on the "Workbook" object in the Project Explorer window
- Paste this in the white code area
Private Sub Workbook_Open()
Sheets( "Sheet1").Range("A1") = val(Sheets( "Sheet1").Range("A1")) + 1
End Sub

- You need to make sure that your sheet is named "Sheet1", if not, just put the name of your sheet instead of Sheet1 in this line
Sheets( "Sheet1").Range("A1") = val(Sheets( "Sheet1").Range("A1")) + 1


Good luck

VBAXLMan

Monday 4/13/2009 7:45:11 AM
Thu
Apr 9
2009

Can I count number of rows based on 2 critera that are inputted by the user?

I have many sheets of raw data that I am trying to Roll up in a simple Dashboard using excel. So far I have managed fine but have come stuck on this part.
I am trying to count a number of rows based on 2 criteria. The problem is these criteria are inputted via validation list from the user!

The user selects a name of a program and what week number they would want to view a button is selected to run a macro that calculates this information.

The macro is no problem just the input of the search criteria via validation list.
I have tried everything I know but cant figure it out.
Any help is much appreciated


Answer

I would rather you do DSUM
Here is a video lesson on how to do it
http://file1.net/lesson.asp?id=6d2CvCTrt…

You can make the criteria range as the same as the validation cell (Excel will not mind)

Let me know if everything goes well or not

VBAXLMan

Thursday 4/9/2009 12:12:14 PM
Tue
Apr 7
2009

Filtering data in Excel 2007 based on formatting?

I have two columns I need to filter. Some have text and some have text with strike-through (a line going through the text). Does anyone know how I can filter based on the strike-through - or if that's not an option can I filter based on anything that is not formatted like the regular text so it will separate the cells with the strike-through? Thank you.


Answer

There have been a lot of requests to read format through formulas

Since it is nit supported yet, here is a user definec function to do that

Function IsStrickeThrough(CellAddress As Range) As Boolean
If CellAddress.Rows.Count = 1 And CellAddress.Columns.Count = 1 Then
IsStrickeThrough = CellAddress.Font.Strikethrough
End If
End Function

To use it...
- Press ALT + F11
- Insert > Module
- Paste this
- Now go to any cell and use it like this
=IsStrickeThrough( A1)
Assuming the cell you want to check is A1



Enjoy it



VBAXLMan

Tuesday 4/7/2009 9:01:21 AM
Tue
Mar 31
2009

How do you turn macros on in microsoft excel 2007?


Answer

First, you will need to save your file as Excel macro-enabled worksheet with XLSM as extension rather the default XLSX.

If you are doing ans XLS 2003 file type, ignore that

Then once you open the macro file, Excel will show you a new bar above the Formula bar with "Security warning"
like this screen
http://www.file1.net/img.asp?prj=-8-_-2_…

Click on "Options" and enable the macro content

OR
Show the "Developer" tab, here is a lesson on how to show it
http://www.file1.net/lesson.asp?id=6VM8P…

Then in the Developer tab, click on "Macro Security" in the "Code" section


VBAXLMan

Tuesday 3/31/2009 6:18:07 PM
Tue
Mar 31
2009

How can I create a Macro with Excel 2000 and Visual Basic.NET?

When I start Excel 2000 and click tools > marcro > visual basic editor, it starts Visual Basic 6. I also have Visual Basic.NET and would like to program Macros in a more up to date version of VB. Is there a way to do this?


Answer

No, Cozmosis you are wrong

Sorry Nathan
All Excel versions after Excel97, (Including Excel2007) are having VBA version that is based on VB6
Excel97 has the VB5 version of VBA

You can not program VBA code in Excel using other than the built-in VBA

Which is for now all in VB6

You would may be wait until they might put .NET in the coming version of Office

VBAXLMan

Tuesday 3/31/2009 3:42:01 PM
Wed
Feb 4
2009

Excel 2003 - VB - Accessing abilities between modules for public functions?

I have one module with:
"Public Function Code128B(inpara As String) As String"
and then the function (i'm pretty sure it's copyrighted etc; it said Shareware on a few sites i was at and it's for barcodes)

I have another module with:
----------
...
Dim foot As String
foot = ActiveWorkbook.Name + " " + ActiveSheet.Name
ActiveSheet.PageSetup.CenterFooter = "&""MRV Code128bMA,Regular""&14 " & Code128B(foot)
...
----------

And the error is returning is that it is an "Ambiguous Code Name"

Is there something fishy going between modules with public functions? I'd think that a public function means it's always available to every module but I'm not sure.

If this is not enough data to solve the problem say so and I'll just let the whole thing drop (it's not my code or job to fix it :P )


Answer

You are right
"Public" will set this function public to every other module inside that workbook
And I quote "That Workbook"
If you are running this from another workbook, you need to call the function in another way
Workbook1.xls!Code128B(Foot)

If you are using the same workbook, then you may need to check two stuff:
1- You have a reference to the library that is used by that function
Some function requires you to add a reference (Toos > References) to a specified library to make it rub
2- You don't have the "Option Explicit" in any of your modules

If you still face the problem again, mail me some sample files, I can do it for you (May be for free)

VBAXLMan is here to feed your Excel needs

Wednesday 2/4/2009 9:05:51 AM
Wed
Jan 21
2009

Urgent! MS Excel Macro help needed!?

Hello

For worksheet A, I need to loop through the values in column A, and if AX="cow", then I need to copy cells CX, FX and HX into worksheet B.

Any tips or code on how I can accomplish this?

Thank you very much.


Answer

Sure, Advanced Filter is your solution
- While you are in Worksheet B, type the name of the AX field in Worksheet A and type "cow" below it
- Go to Data > Filter > Advanced Filter
- Check the option "Copy to another location"
- And enter the three values, knowing that, the Criteria range is your two cells in Worksheet B

Enjoy it, it is a powerful feature

VBAXLMan is back

Wednesday 1/21/2009 3:21:07 PM
Wed
Jan 21
2009

Multiple pivot tables controlled by a single set of page fields to automatically update all tables?

Hi,

Is it possible in Excel to update multiple pivot tables, all using the same page fields, by changing the page fields just once instead of having to change them for each table?

Thanks


Answer

Sure
When you build the pivot tables, make them reading the values from that source table

Once you update your fields, you may need to refresh each pivot table, or use the macro to make updation for all you pivot tables like this

ActiveSheet.PivotTables( "PivotTable1").PivotCache.Refresh

All you need to add is a loop through all your sheets with pivot tables and refresh each one

For i=1 to ThisWorkbook.Worksheets.Count
Worksheets( i).PivotTables( "PivotTable1").PivotCache.Refresh
Next i

Also you may need to make sure that each Pivot is named "PivotTable1" in each sheet

VBAXLMan is back to feed your Excel needs

Wednesday 1/21/2009 5:28:24 AM
Tue
Jan 20
2009

Debug error 3001 on VBA when I try a sequential search of the database.?

I am trying to subtract the quantity ordered by a customer by the Number In Stock of a particular product. To do this, I needed a sequential search, and I used a code which I thought of. However, it brings up a "Run-time error '3001': Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another."
The Debug error is this:
rst.Find "ProductID = " & Me!ProductID, 0, adSearchForward, 1
Here is the code:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.NewRecord Then
Dim con As New ADODB.Connection
Dim rst As ADODB.RecordSet

Set con = CurrentProject.Connection
Set rst = New ADODB.RecordSet

rst.Open "tblProduct", con, adOpenKeyset, adLockOptimistic

rst.Find "ProductID = " & Me!ProductID, 0, adSearchForward, 1
rst("NumberInStock") = rst("NumberInStock") - Me!QuantityOrdered
rst.Update
rst.Close

Set rst = Nothing
Set con = Nothing

End If
End Sub


Answer

- Is the name of field you want to search in named "ProductID" too?
- Did you gave all arguments of "Find"?
- What about the result of Find, shouldn't you assign the result to a variable?
- Are ProductID columns from both sides in the same type?

VBAXLMan

Tuesday 1/20/2009 2:05:12 PM
Sat
Jan 17
2009

I want to fix a pic in MS Excel 2007 cell and to use in an another sheet with Vlook up formula?

For each cell of employee I want to fix a pic and to use in forms with Vlookup formula


Answer

Excel functions are working with text and numbers
You can not do this with functions, instead you will need macros to do that

I would suggest to do a folder with employee pictures named with employee ids
Then insert a picturebox in that Excel file and do some macros to make it read the selected employee picture.

Something like this:
Sub ReadPic()
EmployeeID =Range("A1").value
PictureBox1.picture=LoadPicture( "D:\Pix\" & EmployeeID & ".jpg")
End Sub

This will read the picture in the picturebox assuming cell A1 had the employee id

I will be glad to help if you just send me the file you are talking about and more details to build the macro for you

VBAXLMan

Saturday 1/17/2009 10:34:54 AM
Fri
Jan 16
2009

Excel e-mails me via a macro. Can Excel e-mail me as per my macro even when the workbook is closed?

I just added a macro to Excel that will e-mail me when a cell value changes from '1' to '0' from live data. Will it e-mail me even when the workbook is closed or does the spreadsheet need to be open in order for the macro to take effect?


Answer

If the workbook is not opened, the value of cell will not be changed
Meaning the email will not be send

Yes, the Excel file have to be opened in order to execute the macro and email to be send

VBAXLMan

Friday 1/16/2009 1:08:00 PM
Fri
Jan 16
2009

Is it possible to add a calendar alert to Excel spreadsheet?

I've created a Excel '07 spreadsheet as a calendar. Is it possible to insert pop-up alerts/reminders in the spreadsheet?


Answer

Yes, using macros, for example:

Put this code in the Workbook module in the VBA (ALT+F11)
Private Sub Workbook_Open()
Application.OnTime Worksheets( "Sheet1").Range( "A1").Value, AlertSub
End Sub

And put this in a new Module after you insert it

Sub AlertSub()
MSGBOX "Alert", VBOkOnly
End Sub

Assuming you have the cell A1 in Sheet1 having the time that you want to alert in

Close the Excel file, and reopen it again with macros enabled, then it is done

VBAXLMan is back to feed your Excel needs

Friday 1/16/2009 9:14:43 AM
Fri
Jan 16
2009

Excel, Update sheets automatically?

Hi,

I have this sheet which i am currently working on - what i would like to do is every time a new record is added to Sheet1, it automatically updates Sheet2.

Sheet1 has 6 categories but every record is just added to the list. Sheet2 has records by categories. It has to be able to find the correct category in Sheet2 and update it with the new record.

Thanks.


Answer

This can not be done in Excel formulas
You will need to do one of the following:
1- Do a Pivot Table in Sheet2 to get the result of each category in certain columns (Might need to do more than pivot table)
2- Do a macro that is refreshed every time you change a cell in sheet1

I can not show you how to do the PivotTable here since it is different between Excel2007 and Excel2003

Also the macro thing cannot be done here, since I need more details from you, but it can be something like this:

- In Excel, Press ALT+F11
- Double click the sheet name (Sheet1 in your case) in the left tree list
- Paste this

Private Sub Worksheet_Change(ByVal Target As Range)
' Macro should be here
End Sub

The macro you need to put here is the macro that I will do it for you for free, if you send me your file to
XL@VBAXLMan.com
with some additional details

VBAXLMan

Friday 1/16/2009 8:13:22 AM
Fri
Jan 16
2009

How can retrieve the InstallDate for Windows and MS Office with an Excel Macro ?


Answer

The date of Windows installation is the date that Administrator account was created (or your User account)
And the date of Office istalled is the date of Microsoft Office folder was created
So
Depend on your Windows version (VISTA or XP) use this code to get the installation date/time of Windows

Sub WinDate()
Dim fs, fo
Set fs = Server.CreateObject("Scripting.FileSyste…
Set fo = fs.GetFolder("c:\Users\Administrator")
MsgBox fo.DateCreated
End Sub

Change the c:\Users\Administrator to c:\Documents and Settings\Administrator if you have WinXP
(Although the second one will run in VISTA as well)

Do the same for c:\Program Files\Microsoft Office

good luck

VBAXLMan is back

Friday 1/16/2009 3:59:00 AM
Thu
Jan 15
2009

Ms Excel macros problem ??? ?

it give the error

Macros in this workbook are disabled bcase the security leavel is high, and the macros have not been digitally signed or verified as safe. to run the macros , you can either have them signed or change your security level.

i reduce security level high to low , i also crate one password for that file also than after it give this error ?????

what can i do ???

give full information about this problem and how to solves ??


Answer

Excel does that even if your security level is low, you will need to enable macros

I can smell that you have Excel2003 or XP, then do enable when you open the file

IF you not enable the macros, you can not run them, whatever is your level of security

It should show you a window asking you if you want to enable macros

Click "Enable macros"

VBAXLMan

Thursday 1/15/2009 7:13:43 AM
Wed
Jan 24
2007

How to run my macros in excel?

how to run my macros in excel
before allowing users to view the worksheet ? as in I want the macro for "inputbox Username" to pop up as soon as someone opens the workbook


Answer

Yes, it is Workbook_Open() that you need to put your code in to be started as the user opens the file
BUT
The only thing that you may face, is that user might have his macros are disabled, then all your work will be gone.
I faced this issue 7 years ago, and I came out with a good solution.
I will recommand you to use my tool, that will show the user a window asking him to enable his macros to start running.

It is here if you are interesting (It is called "Always Visible Sheet", first project in 2006 projects here)
http://www.projects.file1.net

I am thinking of making it free to download...

If you like it? I can send you a free copy, mail me for it

Enjoy my profile, I am the VBAXLMan

Wednesday 1/24/2007 4:12:51 AM
Thu
Jan 18
2007

How to create excel macro to fill in empty cells based on information in abolve cell?

I am looking to write macro that fills in the empty cells in certain range based on info of above info. Idea is to populate cells that have been left empty after pivot table results are copied as value in other worksheet.
It should execute starting from top and going down row by row in selected range.
Example: Row 1 has text "Formula", row two has empty cell. If macro detects empty cell it should populate it with info from cell above (e.g. Formula). It is kind of auto copy function


Answer

It is amacro, right?
So, you will need to create two loops and check if a cell is empty, if so, fill the cell from the above cell, then:

Sub FillFromAbove()
For i=1 to 4 ' column A, B, C and D
For j=2 to 7 ' rows 2, 3, 4, 5, 6 and 7 , so the range is A2:D7
If Worksheets( "Sheet1").Range( "A1").Offset( j-1, i-1).Value ="" then Worksheets( "Sheet1").Range( "A1").Offset( j-1, i-1).Value = Worksheets( "Sheet1").Range( "A1").Offset( j-2, i-1).Value ' to fill from the above cell
Next j
Next i
End Sub

Wow, I am realy the VBAXLMan

Thursday 1/18/2007 8:52:04 AM
Sun
Jan 7
2007

Excel formula to make input number its inverse?

I want to be able to input a number in a cell and automatically have it convert to its inverse (in that same cell). Is there a forumla for that?


Answer

No formula can do this.
Formulas are have to be entered in a cell first to work.
So You will need a VBA to do this
put this VBA macro in the code area of any sheet you want to do this:

Private Sub Worksheet_Change(ByVal Target As Range)
ActiveCell.Value = 0 - Val(ActiveCell.Value)
End Sub

need more info? mail me here in Y! Answers

Enjoy my profile, I am the VBAXLMan

Sunday 1/7/2007 9:07:50 PM
Wed
Dec 20
2006

Matching with Ms Excel?

I have a spreadsheet which contains 5-6 columns of info, with about 200-300 rows. Column D contains totals. Some of the totals in column D will match with one another, just with a minus sign. Say if there is a 54300, then there is another -54300, just its with a minus sign (this is actually a ballance of a clearing account).
What I want is to write a formula or macro that would take column D value from the first row, search through all column D, find matching value, cut those two rows and paste in another sheet within the same file. Then repeat this with all the rows, cutting rows with matching value in column D, so that all the matching values are pasted one below another in another sheet. This way the original sheet will only contain unmatched items.
I work as an accountant so have to go through account statements like that very frequently. Having a formula or a macro like that would make my life a lot easier :)

Thanks for any responses!

Happy Xmas!


Answer

You can solve it in one of these ways
1- Formula
2- Macro
I can post here the formula solution, because it is easier to understand.
Now, leave column E as seperator, go to F2 and paste this
=ABS( D2)
and fill it down to all the other cells. In G2, paste this
=LARGE( F:F, ROW()-1)
And fill the G2 down to all the cells below
Hay, it is your exact list, but sorted in Descending order

Yes
That is because VBAXLMan show you how to do it

You can cut and paste this G column cells to what ever you want

Enjoy it and..

Enjoy my profile, I am the VBAXLMan

Wednesday 12/20/2006 5:36:13 PM
Sun
Dec 17
2006

Is there any formula to convert numbers to words in "Excel Program" other than Add-ins. Pls answer me.


Answer

Microsoft do have a function like this, it is a macro, so you may need to copy it first to your VBE module window

Check this out
http://www.ozgrid.com/VBA/CurrencyToWord…

Enjoy my profile, I am the VBAXLMan

Sunday 12/17/2006 9:43:26 PM
Thu
Dec 14
2006

How do I get file attributes such as picture size type etc into an excel database using Visual Basic 6.0?

I'm making and index of my CD's in Excel and Everytime I get a new CD I need to right click and get the properties and copy them in excel. Thats why I want to make an application in VB which will do the job for me.


Answer

I would rather do the program in VBA of Excel, it is close to plain VB, besides you don't need a setup, just open the Excel file and run the macro (VBA subroutine)
Now the answer is using a special DLL to get the picture properties, there are alot of libraries sites out there in the net like:
vbaccelerator.com
a1vbcode.com
and much much more, i don't remeber all now.
If you got the declerations part, you can easily use it in the VBA of Excel

mail me for more info, i would be glad to help

Enjoy my profile, I am the VBAXLMan

Thursday 12/14/2006 4:45:18 PM
Wed
Dec 6
2006

Excel Macros help!!?

If you have the following macro:

Sub CleanupGridReport_1()
'
' CleanupGridReport_1 Macro
' Macro recorded 12/6/2006 by me
'
'
Sheets("GridReport_1").Select
Range("V22:V29,Y22:Y29,a33:d33D32").Sele…
Selection.ClearContents
Range("I32").Select

End Sub

Can someone tell me what are the steps I have to follow, starting from >>

Sheets("Grid_Report_1")

So, I start the macro, then what do I do?


Answer

Line 1: Sheets("GridReport_1").Select
Is going to select the sheet names GridReport_1
Line 2: Range("V22:V29,Y22:Y29,a33:d33...
Is to select these ranges of cells at the same time (Highlight them)
Line 3: Selection.ClearContents
Will clears the content of the selection (Contents means only whats inside the cell, not Formats or commints
Line 4: Range("I32").Select
Selects the cell I32

Thats it

good luck

Enjoy my profile, I am the VBAXLMan

Wednesday 12/6/2006 11:43:29 AM
Tue
Dec 5
2006

How do you generate a PO number in an excel spreadsheet?

I have a purchase order form in excel that I want to automatically generate a purchase order number every time it is opened. Is this at all possible and if so how would I do it?


Answer

It is in macro (Did you heared of VBA?)
You will need to create the macro that will run at each start and adding new value to the old one.
It is something like this

Paste these lines in the ThisWorkbook object in your VBA window (Open excel, then press ALT+F11)

Private Sub Workbook_Open()
Starting
End Sub
Sub Starting()
Sheet1.Range("B1").Value = VAL(Sheet1.Range("B1").Value) +1
End Sub

Assuming that the cell B1 in sheet "Sheet1 is the cell with PO number

mail me for more info

Enjoy my profile, I am the VBAXLMan

Tuesday 12/5/2006 1:56:20 PM
Mon
Dec 4
2006

Excel Query: Closing Down the file you are in using a macro?

Hi,

Can anyone help?
I have an excel spreadsheet with various macro buttons attached. What I need is a piece of additional code where, once entered straight into vb, and one of the macro buttons is pressed, the file that they are in closes.

Thanks in advance


Answer

Closing Excel file has some details
1- Closing the file (the file that have the macro) is in this line
ThisWorkbook.Close TRUE
TRUE for saving file before close
2- Closing the Active file, the one that is selected in the Window menu is
ActiveWorkbook.Close TRUE
3- Closing Active window will not all times closes the file (You may have two windows for the same file) it is like this
ActiveWindow.Close
4- Closing Excel is another issue, you will need to save all opened file to let Excel close without asking for saving files:
- If you have one file opened, save it then exit Excel, like this
ThisWorkbook.Save
Application.Quit
- It you have more than one file opened, then you can make a loop to save all opened files, like this
For i= Workbooks.Count to 1 step -1
Workbooks( i ).save
Next i
Application.Quit

I think this will solve your issue

Enjoy my profile, I am the VBAXLMan

Monday 12/4/2006 1:03:15 PM

Tags

History