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

29 Q & A posts found in December

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
Wed
Dec 20
2006

What is the best mobile phone to buy supporting word docs, excel and email?

I've been told that Qtek 9000 is the best but after 2 or 3 months it starts failling. Can anyone give me advise/suggestions?
Cheers


Answer

If you want a cell phone wiht big screen, qwerty keyboard, wireless network, bluetooth.
Then The best one I found until now is Siemens SX66
I hav'nt seen such a big screen like this ever

VBAXLMan

Wednesday 12/20/2006 1:17:03 PM
Wed
Dec 20
2006

I have a problem in writing Excel formula?

Hi guys,

I have a problem in writing an excel formula .. IT looks very simple.. I wanna say that if the value in a specific cell is less than 1000, add 30 ..

I have tried =if(K5<1000,"30").. it never work


Answer

Yes do it like this

=IF( A1<1000, A1 + 30, A1)
This will add 30 to the cell in A1 if it is less than 1000 and bring the origin cell value if not

Enjoy my profile, I am the VBAXLMan

Wednesday 12/20/2006 10:11:54 AM
Wed
Dec 20
2006

Is ther any way to install a .wba file which is a windows blind file.It is a neet desktop theme?

I downloaded it not knowing it was a .wba file.I want to install it but apparently this is a windows blinds extension.I dont plan on buying windows blind to install the theme is there any free way. Other wise I will try to find a free one somewhere.Its reaaly neet though.
Thanks for all help


Answer

I don't think there is away to see the file only by installing the software
check the website, they can give you a try version

VBAXLMan

Wednesday 12/20/2006 9:39:46 AM
Wed
Dec 20
2006

I have data in a column in an Excel spreadsheet and I need to use it in formulae in a row on another sheet.?

I don't want to have to type the formula over and over for each cell! Is this possible? Using the '$' sign doesn't work...


Answer

You can use
1- The Copy + Paste Special (Transpose)
2- Use the COLUMN(), ADDRESS functions like this:
Your source column is in D in Sheet1, you want it in row 5 in sheet2, so in cell A5 in sheet2 paste this
=INDIRECT( "Sheet1!D"& COLUMN())
the function in cell A5 will call the value in cell D1 in sheet1 and so on.
You can change the sheet name of the source column by changing Sheet1 in the formula.
If you are not starting from D1, or you want the row to start from other than A5 then add the -1, +1 after the COLUMN() to modify it

3- Use the TRANSPOSE function

I think this will answer you

Enjoy my profile, I am the VBAXLMan

Wednesday 12/20/2006 4:36:07 AM
Tue
Dec 19
2006

Excel Spreadsheet opens up blank?

Coworker wanted me to double check her work and when I open the spreadsheet it comes up blank (just gray) not even showing the columns and rows. But looking at full screen I can see it. All other excel spreadsheets are fine.


Answer

There are alot of tricks in Excel to do this, I just need you to check these one after another
1- Go to (Window > Unhide) and see if there are items shown in the dialog that appears
2- Go to (Tools > Options > Window options) and make sure you select (Row and column headers, gridlines, horizontal scroll bar, vertical scroll bar)
3- got to (Format > Sheet > Unhide) and check the hidden sheets

PS: If excel asked for password, then the file is protected, you will need it

Enjoy my profile, I am the VBAXLMan

Tuesday 12/19/2006 2:56:20 PM
Tue
Dec 19
2006

Is there software that helps me edit out sound from movies?

I want to find software that will let me edit sound clips from movies or television shows on DVD. And can I get the clip in 5.1 if the movie HAS the sound in 5.1???


Answer

Search it in
http://www.download.com
I am sure you will find something good and free

Try to search for "VCDCutter", "DVDCutter"

VBAXLMan

Tuesday 12/19/2006 9:44:06 AM
Mon
Dec 18
2006

I have added new users to Windows XP. In each of these new accounts the Firefox addons have disappeared.?

Recently I have added new user accounts to Windows XP. Since then I have been experiencing numerous problems regarding the use of Mozilla Firefox. All bookmarks, add-ons and customizations have disappeared completely. I've solved the bookmarks problem by importing my old bookmarks list into my new account. In addition customizing Firefox to back my likings was a breeze. However the problem of the missing add-ons remains. I've painstakingly been searching Google for the missing add-ons are re-downloading them one by one.

This brings me to my question. Is there a way to copy Firefox add-ons from one XP account to another? Can I somehow copy add-on files from their location in one users account to another? If such a feat were possible it would save immense quantity of time and reduce my download usage. Help is much appreciated.


Answer

Who said copying addons files will copy the addons itself.
If you are sure of that, you can do this:
1- Open the Documents and settings, you will see a folder for each of the users
2- Open the main user (The one that was active when you installed the addons), then go to Application Data folder
3- search in there for the firefox addons files (It should be there in the folder named "firefox" or something like that)
4- If you found nothing there, try the folder "Local Settings" then "Application Data"
5- Copy it into the exact place in all of the users folders
6- restart computer and try again

I still can not think that copying these files will enable them into firefox, any way, you can try

VBAXLMan

Monday 12/18/2006 10:56:14 PM
Mon
Dec 18
2006

The little icons beside the inbox etc. in yahoo mail are missing?

again after formating c drive and reinstalling xp.. when I log onto my mail in yahoo and my homepage in yahoo the small icons beside the text do not display.
what is the problem?
installed java


Answer

Make sure of these:
1- Enabling graphics in Tools > Internet Options > Advanced
2- Clear History
3- cookies are enabled
4- Clear cookies


VBAXLMan

Monday 12/18/2006 9:17:44 PM
Mon
Dec 18
2006

Where do i download the Vista Theme for my computer..?


Answer

Get it here
http://www.astahost.com/vista-theme-t955…

good luck

Monday 12/18/2006 5:55:20 PM
Mon
Dec 18
2006

Firefox 2.0 wine installation problem?

I am trying to upgrade to Firefox 2.0 on my openSUSE 10.2 and when I launch the installer, it comes to the page where you select where you want to install the files and it doesnt matter where I select, it always gives me "You don't have sufficient disk space to install in this location, Please click ok to select another"


Answer

This message comes up when you are trying to install in a full hard drive.
Make sure that your C drive (Or wherever you are installing in) is not full and have the space in the program recommandation
Try install in another hard drive.
If you have only one, then clear some data from it then try again

Enjoy my profile, I am the VBAXLMan

Monday 12/18/2006 12:14:17 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
Sun
Dec 17
2006

How can I compress a MPEG video clip? The current clip is too large to attach and send by e-mail.?


Answer

MPEGs files are already compressed, even if you zip them
You can only control the resolution (bit rate) by reducing it to fit the size you want.
Try one of editing video applications on download.com

VBAXLMan

Sunday 12/17/2006 4:30:20 PM
Sun
Dec 17
2006

MS Excel fomular for link sht?

=SUMIF(INDRECT(C$4&"!$A:$A"),$6,INDIRECT…
An expert told me to use the above fomular to link 3 work sht into one main sht for summary purpose. I can't contact him now.
Can anyone tell me :
1) How to COPY the data in the 3 work sht and use the above fomular to pPASTE it on the main sht.
2) please explain the meaning of the fomular in layman term.

Thank you very much for help.

WT Tan


Answer

Hello my friend, it is me who send you the formula.
Now what is the problem?

mail me

VBAXLMan

Sunday 12/17/2006 10:04:44 AM
Sun
Dec 17
2006

How Can I Do This, And Were Can I download The Program To DO IT.?

I want to make this figure looke like if it were really fighting. Its from a cartoon, so i just want it too look like if two characters from diffrent cartoons were really fighting each other, also i need to know how to erase a background so only the figure from the cartoon character will be left. PLEASE HELP..


Answer

There are alot of video editing software out there, try Premiere from Adobe, I think it is good

VBAXLMan

Sunday 12/17/2006 9:07:28 AM
Sun
Dec 17
2006

How to convert web files in excel?

or how to covert txt file from dos to excel


Answer

Excel can read *.csv files
So just make your list a comma seperated list, like
V1,V2,F4,450
and so on, then
open the file in Excel, and it is all here in a sible sheet

Enjoy my profile, I am the VBAXLMan

Sunday 12/17/2006 5:12:24 AM
Sat
Dec 16
2006

Hey, Excel users?

What has happened to my Excel file?
I suddenly can't enter any data. The damned cursor moves where ever it wants to and won't accept an entry in the selected cell.
No idea what question to ask in Excel Help.

Thanks.


Answer

Mske sure that
1- Your sheet is not protected against changing (See Tools > Protection > Protect worksheet)
2- You are not openning a shared file that is already opened by someone else
3- You are not openening a file that is accedently closed
4- Copy the file in another folder with another name and try again
5- You don't have a virus in that particular file

mail me for any details here

Enjoy my profile, I am the VBAXLMan

Saturday 12/16/2006 10:01:52 PM
Sat
Dec 16
2006

Where can I find an excel spreadsheet of question and answers?

I am looking for question and answers for students for basic tests and exams for things like chemistry, geology, science. Just basic stuff like:
What is oxygen? An element that makes up our atmosphere.

I have many text files of info but they are taking for ever to manually copy/ paste into Excel (which is what my test creation program imports from).


Answer

I made somthing like this (Based on text files database) that will read these files and make the 10 questions with the 3 random ordered choices in VBA of Powerpoint, check this link
http://shiekhali.com/anmar/projects.html
and go down until the "Ayad Project" in 2002 and see the screenshots for it.

It is VBA in PowerPoint that will calculate the result of points, I can mail it to you and show you how to customize it if you are interesting, mail me here in Y! Answers


Enjoy my profile, I am the VBAXLMan

Saturday 12/16/2006 11:47:30 AM
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
Tue
Dec 12
2006

This is regarding Concatenate function in Excel:?

I am trying to concatenate a Currency value ( $ 233,456,389) with a

percentage ( 74 %) in Excel using this function :


=CONCATENATE("$ ", TRUNC(B313)," @ ",TRUNC(C314)," % ")



I would like to have it as $ 233,456,389 @ 74%

Instread I am getting $ 233456389 @ 74% (currency value without commas)

Can someone help me in getting the commas in the currency display ?


Answer

Add this to the TRUNC function
TEXT(B313 ,"###,###")
Means the TRUNC would be
TRUNC(TEXT( B313, "###,###"))
and your final function will be

=CONCATENATE("$ ", TRUNC(TEXT( B313, "###,###"))," @ ",TRUNC(C314)," % ")

Enjoy my profile, I am the VBAXLMan

Tuesday 12/12/2006 11:03:16 PM
Tue
Dec 12
2006

In the Excel Sheet(Microsoft Excel),I am not getting the 'FILE'option in the menu bar.How can i restore it?.

The excel sheet is starting with 'EDIT' from the left side.


Answer

The easy way is as follow:
- Go to View > Toolbars > Customize
- in that window make sure you are in the tab that named "Toolbars"
- Select "Worksheet Menu Bar" which is the last one in that list of toolbars
- Now press the "Reset" at the right
- Enjoy it

Enjoy my profile, I am the VBAXLMan

Tuesday 12/12/2006 1:10:19 PM
Tue
Dec 12
2006

I am not able to tick the 'chart menu bar' in ('tools'-'customise')Excel sheet.How it is possible?

This is to bring back the 'file' option in menu in Excel Sheet.Presently it is not displayed.Display starts from 'EDIT'


Answer

I answered you,
Check the answer for this question in this link
http://answers.yahoo.com/question/index?…

Enjot mt profile, I am the VBAXLMan

Tuesday 12/12/2006 5:47:50 AM
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

Hey! is there any Excel expert who can solve this.....? are u here Mr VBAXLMAN..?.?

i would like to create a line chart in ms excel....where "runs" will be ploted in y axis, or vertical axis and "overs" in horizontal axis....
runs will ploted in 50 runs interval and overs will be in 10 overs interval....u might have seen this in cricket...
wait .....it's not so easy one....i've a few conditions
1) the resultant line will be displayed in a single line,,,,i repeat ..in a single line....as u would have noticed in cricket score analysis.
2)it will start from the (0,0) point...that means 0 run at 0 over...
and will travel with the increasing runs and respected overs...(as it is made in graph papers.)
3)i'm using msexcel 2002....so pls solve it in 2002 or older....
i think i've expressed it enough....
if u 've done it then try this for two teams for there score analysis .....obviously there will two seperate line for two teams , say India & Pakistan or Australia & NewZealand...
every good answer will get a thumbs up and the best answer ......u know better


Answer

I got your e-mail.
The solution is all in the table that you will create for it
Wherever hard you think the chart is, the solution is in the table
Excel charts are created from tables, means that you can not (Never) create a chart without its data.
So we are (You and me) have to see how to build the table that will represent the chart

I can't e-mail you through Yahoo, you have not confirmed your e-mail there...
send me your e-mail address, I can not e-mail you

Enjoy my profile, I am the VBAXLMan

Tuesday 12/5/2006 2:10:13 PM
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
Mon
Dec 4
2006

An Excel search question?

I am trying to make an excel worksheet simply count the number of each persons names in a, say 10x10 grid randomly filled with names. Can it be done? And how!? I just want a list underneath of, for example "John - 10; Dave - 3; Kim - 28" depending on how many boxes each person's name appears in. I have tried DCOUNT but probably incorrectly, as it brought back totally random figures that dont make sense. Please help!!


Answer

You will need COUNTIF
it goes like this
=COUNTIF( A1:F10, "*John*")
I recommand using * to allow finding the cell with John, and cell with John - 10

Enjoy my profile, I am the VBAXLMan

Monday 12/4/2006 12:50:00 PM
Sun
Dec 3
2006

I have an excel file and I have forgotten the password. Is there a way to recover the password.?

I have checked the crack softwares on the internet. I am not ready to pay $40 for just 1 file. But this file contains some important tax info and I need to make some changes urgently. Is there any way I get recover the password.


Answer

I have one and ready to share it
I just want to ask, which password you are talking about
Excel do have at least three password levels

mail me here for it

Enjoy my profile, I am the VBAXLMan

Sunday 12/3/2006 11:05:59 PM
Sun
Dec 3
2006

How to find the largest non-empty row of an Excel column with VBA code?


Answer

I guess you mean the largest value in a range of cells, right?

If so, then you can do one of the following:
1- Use another cell with the function of
=Max( B:B)
then call that cell using the object [Range]
MaximumValue = Range("A1").Value

2- Use the powerfull feature WorksheetFunction that will enables you to use some of Excel built-in functions in VBA, example:
MaximumValue = WorksheetFunction.Max( "Sheet1$B:$B")

Or...
You may want to find which row have the largest no of non-empty cells.
If so, then you will need som fomrula combination, I will need you to mail me here if you are looking for that in particular.
I also will need you to answer some questions like (Do these rows are one after one? means they don't have empty rows in between?)



mail me here for any further info

Enjoy my profile, I am the VBAXLMan

Sunday 12/3/2006 3:19:22 AM

Tags

History