ANmarS - Search results

Excel/VBA expert since Sep 1997 (20 years 9 months)

We are moving ...

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

55 Q & A posts found in November

Tue
Nov 22
2011

Im trying to repair my excel and word starter 2010?

When i go to programs they aren't on the list although the icon is right there on the desktop, how can i get them on the list?


Answer

You need to use the installation CD, to re install Office Starter, or use another option called "Repair"

Otherwise, you may need to re install the whole system (Windows and everything) if Office starter came with Laptop itself

Tuesday 11/22/2011 9:30:21 PM
Tue
Nov 22
2011

What is the best simple address book application?

I am looking for a simple Windows based "address book" application. Just a simple application that lets me input people's names, addresses, and phone numbers and lets me search and print out the book on 8 x 11 paper. It doesn't even have to be freeware. Please don't suggest Excel or Word or any other word processor/spreadsheet...also, don't suggest Outlook. I want a.simple stand alone application


Answer

Try this one, it looks promising
http://download.cnet.com/Easy-Address-Bo…

Otherwise
http://download.cnet.com/1770-20_4-0-2.h…

Tuesday 11/22/2011 9:17:50 PM
Mon
Nov 21
2011

How do i get the total of one column based off of another column in microsoft excel 2010?

I do not want to "add" the numbers. Basically i have one column of numbers lets call it column (B) and another column (C) with one character it might be an "x" and it might be a "1" anyway. I need a formula that will tell me the total number of info in column B if there is a character in column C. Is this enough info to get help?


Answer

Yes sure
Paste this in cell in column D

=SUMIF(B:B, C:C, "X")

This one will get the total of column B, if its equivalent cell in column C is "X"

You can change "X" to be "1", or 2 , etc

Let me know

Monday 11/21/2011 5:58:10 PM
Mon
Nov 21
2011

Excel - How do I stop cell overlap?

How do stop text from overlapping onto the next cell WITHOUT using the wrap text feature? I don't want the row width to adjust like it does when it wraps text, nor do I want to manually adjust cell size. I just want to everything I type or enter to be hidden when it exceeds the standard cell size.


Answer

You can also put an empty string (space or " ") in the cell next to it, this way, Excel will assume it has a value and stop the overlap

This is usually how I do it

Monday 11/21/2011 5:29:51 PM
Mon
Nov 21
2011

Can someone please help with this piece of visual basic excel code? 10 Points!?

Please go here for the code: http://pastebin.com/Hr4rhd3P
The reason I couldnt put it here was becasue for some reason it went past the character limit. Please could you check out my visual basic code in excel. I keep getting mainly Else without If Errors and Sin errors. Please could you go through my coding and check for the mistakes. Ty in advanced. Ten Points to best answer...


Answer

You need to give more details on what is exactly you need to do to have your code corrected

When I went through it trying to correct it, I got lost since I don't know what you want to do

It is in sub FFactorial indeed

plus Private Sub Factorial_Click()

Both have mistakes in IF statement

email me here with more details to go through it and correct your IFs

Monday 11/21/2011 5:07:16 PM
Mon
Nov 21
2011

Please help. Microsoft Office Pro. 2010?

My Mom installed MO2010 on my computrer. I was re-arranging my room and ALL I did was unplug the tower and monitor. I didn't even diconnect the two from each other. I plugged the tower and monitor back in and went to do a Research Paper and all my Office 2010 things (Excel, PowerPoint, Word, etc.) say this when you click on them to open:
The operating system is not presently configured to run this application.
Please help me. I need Word NOW for my RP. I am not computer-smart so i don't read Computer language. SO please put it into english. Please help. You will be my lifesaver. I know it can be fixed, because all we did was unplug it long enough to move it. and it was working before that.


Answer

I am afraid you need to reinstall office 2010
Use the same cd/source your mom used and run the setup. Again

It should ask you to repair or reinstall all

Try each and good luck

Monday 11/21/2011 3:28:21 AM
Sat
Nov 19
2011

What Kind Of Excel Function Do I Need?

Q: In cell B6, insert a function to calculate the total number of units in the apartment complex in A6. Be sure to enter the criteria range as an absolute reference. Copy the function down through B10.

In column A there is a list of different types of apartments and I need to find total units of each apartment column B. what kind of function would I use to count and recognize each number of units for each different apartment?


Answer

I am not sure what do you have exactly in A6, however, I am assuming only
paste this in B6
=COUNTIF( A:A, A6)

Then fill down

Saturday 11/19/2011 8:42:40 PM
Sat
Nov 19
2011

Microsoft excel selection movement behavior?

in excel, there is an option to choose which direction the selection will move after pressing enter on the current cell (i.e. down, right, etc). i frequently have to change this option because i require different behaviors depending on which worksheet i am working on. is this only a global option or is it possible to set this behavior specific to a particular worksheet?


Answer

Yes, it is global

Now there is a trick that you can use

- Select a range of cells, say B5:F12
- Now in your selection, you can edit first cell
- Then move using Enter, Shift+Enter, Tab, Shift+Tab
- Enter, moves cursor down (assuming default settings in Options)
- Shift+Enter, moves it up
- Tab, moves it to the right
- Shift+Tab, moves it to the left
- Notice that you cursor will not go out of your selection earlier
- As long as you don't use Arrow keys, this will be valid

Again , these assuming you have default settings

Bottom line, using these keys will help you decide where to go after every cell edit

I think that is the only way around your situation

let me know if it helped

Saturday 11/19/2011 5:18:39 PM
Sat
Nov 19
2011

What function to use in excel?

I have a range of the first five digits of telephone numbers and I need to replace them into country name. so i need to compare very single digit of these telephone numbers with the range of prefixes i have before deciding the country name. is there any function to do that ?

I used lookup function but it compare the number exactly
example if i have the number 27800 and in my prefixes i only have 27 it will not recognize it.


Answer

You can also still using VLOOKUP but with some modifications

Modify your list of prefixes to have the entire number with zeros
so 27 becomes 27000, 15 to become 15000, etc

here you can use the VLOOKUP with Range lookup to be TRUE, like this

=VLOOKUP( A1, D:L, 2, TRUE)

The "TRUE" will let you search for the nearest item to the searched one before it

Assuming your list of prefixes is sorted in ascendant order by prefix number

Excel help has more details about the "TRUE" in VLOOKUP

Saturday 11/19/2011 11:42:40 AM
Sat
Nov 19
2011

Excel formula to convert gram to kilo?

Hi everybody ,

I would like to know if there is a formula to convert grams (example 375 gr) to kilos (0.375)
What is the easiest way to achieve that ?

Thank you very much for your help


Answer

Say grams is in A1, in B1, paste this
=A1/1000

That is all

That would be the easiest way

Saturday 11/19/2011 10:46:13 AM
Sat
Nov 19
2011

What function to use in excel?

I have a range of the first five digits of telephone numbers and I need to categorize them into country name. so i need to compare very single digit of the telephone numbers before deciding the country name. is there any function to do that ?


Answer

Use VLOOKUP with TRUE in "range lookup" as in this question
http://answers.yahoo.com/question/index?…

You can also use other suggestions there

Saturday 11/19/2011 9:29:16 AM
Sat
Nov 19
2011

Sorting in excel ROW to COLUMN?

Hi,
I have some data in a column in which i want to sort as first row in first column, second row in second column third row in first column forth row in second column fifth row in first column sixth row in second column, and so on.

How can I do this?

Can anybody help?


Answer

I think I see what you are looking for
There is an Excel file called "2Cols to ColRow" that will convert your table

Check it out here
http://www.anmars.com/Work/?P=596630201

Note that this uses formulas

If this didn't help, please let me know, I can sure do if you send me more details

Saturday 11/19/2011 7:32:54 AM
Sat
Nov 19
2011

How can I convert from pipe delimited txt file to excel file?

Then convert that excel file to rss feed?

Or am I making this more difficult than it needs to be?


Answer

Excel already have Text two Columns wizard, however, you can use this one
http://www.anmars.com/Work/?P=589970000

It is in pure formulas and can be used to extract unlimited columns with certain delimiter

Once you copy and paste the content into column A fill the formulas down to extract them

Saturday 11/19/2011 3:58:51 AM
Sat
Nov 19
2011

How do I make a input Text field in Excel 2010 and how do I extract the value of it into other formula?

I'm trying to place a text field in my excel document that would be visible and editable when saving as .PDF or .CSV

So that when users enter a Value, my tables would adjust according to what they filled in.
When I go to the Developers tab, I can't seem to select the Text field option :/

Any ideas on how to do this?


Answer

Developers toolbar, doen't have textbox control, because you have millions of them already in the sheet
each cell can be a textbox control
You can modify the layout/format of that sheet to make certain cell looks like a textbox

Or, you can use Control Toolbox textbox
http://folder8.com/View/?7U5WAWLP8K72

Let me know if that helped or not

Saturday 11/19/2011 12:40:05 AM
Fri
Nov 18
2011

How to save excel file to text file without spaces?

I have tried everything. I have coordinates in an excel file like this: x1y3 all together with no spaces and in one column. Every time I save it as a text file it comes out "x 1 y 3" I cannot have spaces or " in the file. How do I fix this other than manually? I have 800 coordinates and I'm not about to fix this by hand.


Answer

Save it as CSV file
CSV file = Comma Separated Values

Which will put , as separator

Then you just need to drag that csv file into a notepad window and save it as txt

good luck

Friday 11/18/2011 4:57:48 PM
Fri
Nov 18
2011

WORD, POWERPOINT, EXCEL CLASSES WANTED IN BOSTON OR NEARBY!!?

Hi,

Do you know whom teaches computer classes in Boston or near by?

If you teach or if you know someone, please let me know how much you charge for your time and where you are located.

I do not have computer, only a net book, if this could be used, fine!

Please let me know if you provide computer and space or not.

Please write me back because I need to take classes ASAP.

Thank you so much!!


Answer

I actually do these classes here in my town, and also I do them online

If you can contact me here, or through Man@VBAXLMan.com

We can discuss the online option in this case

Friday 11/18/2011 12:24:38 AM
Thu
Nov 17
2011

Refresh a read-only spreadsheet?

I have a large spreadsheet that I use for construction bids.

On bid day, my partner opens up a read-only copy so that he can follow along and help compare bid prices with what we are currently using.

Is there a way for him to refresh his read-only copy of the excel worksheet without having to save the original and then close and re-open the read-only copy?


Answer

When you say "Refresh" you mean "Calculate", correct?
If so, then F9 will do an entire calculation for all opened workbooks

If by "Refresh" you mean "PivotTable" refresh, then each PivotTable has its own "Refresh" button

Otherwise, please advise on what "Refresh" means

Thursday 11/17/2011 11:14:16 PM
Thu
Nov 17
2011

Thinner bars than default settings in excel?

I have selected the thinnest bar available in Excel. Are there ways to download a custom width that is slightly thinner that would be noticeable on a print out? I have to do some graphing and the thinnest bars are still slightly too thick.. I dont want to use dashed lines either


Answer

As far as I understood you
You can modify the width of these bars by going Right click then selecting Format
These options can do that for you

If not, then try to change the type into 3D, then you can modify gaps too

good luck

Thursday 11/17/2011 9:28:23 PM
Thu
Nov 17
2011

Please Help! How would i draw this graph on excel 2010?

http://imgur.com/IMLLE

Sorry the pic isn't rotated, and it's kinda small...
I can't figure out how to graph it:S I don't know how to insert negative x values in the graph, and i don't know how to change the default x values of 1,2,3,4, etc. to different numbers...
Please help! best answer will get 10 points today cause i'm in a ruuuush!
Thanks!


Answer

This one looks like a SIN(x) graph for me
just do 1, 2, 3, 4, etc in col A, then do
=Sin(A1)
in column B

Then graph it

hope this is not too late

Thursday 11/17/2011 4:27:33 AM
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
Wed
Nov 16
2011

How to protect MS Publisher 2003 file with password?

I want to protect my MS Publisher file with a password so that no one except me could open it. Pl answer to ques with steps.


Answer

You can always use any Zip app to compress it with password

or, create a protected drive using TrueCrypt that holds these files

Get a free zip app from http://www.7-zip.org/
and TrueCrypt from http://www.truecrypt.org/

Wednesday 11/16/2011 9:09:44 AM
Wed
Nov 16
2011

Can someone give me a way to boot a disc?

Okay, I don't have a F-key I can press at the startup of my computer to select boot options.

Can I boot the disc in a different way?
I don't want to install a program (*coughcoughbecauseicantcoughcough*)
I also have antivirus I can't change it's settings, and I can't choose to accept the threat. (Microsoft Forefront Endpoint Protection)

Please help! Really need to boot!!!


Answer

You can do one of the options:
1- Every computer have F-keys, sometimes they are actually combined with other keys, like my friends, he needs to press Fn + Shift + 1 to do the job of F1, you may have the same structure
2- If that didn't help, connect an external keyboard to the computer (may be a USB) and use its combinations
3- Otherwise, if you can go to BIOS settings, most computers allow you to select boot order from there.

I hope that helped

Wednesday 11/16/2011 7:27:59 AM
Wed
Nov 16
2011

Recently uninstalled Winzip and I can't download stuff anymore?

Well I recently uninstalled Winzip because I whenever I downloaded something, it would show up as winzip and when I clicked on it, I needed to get the pro version of Winzip. I deleted it and now whenever I try download something it just shows up as if its an Internet Explorer file and then when I click on it in uncontrollably duplicates itself and keeps popping out so I have to manually reboot my computer. So how can I download stuff normally again. By the way I have a PC Windows Vista and I'm trying to download Paint.NET. Thank you.


Answer

You just need a program that register .zip files with
When you first installed WinZip, it overwrites the default registration of .zip (Which was with Windows extract app)
And after uninstall, the .zip files are not registered with any app

So installing a program that register .zip files with, will solve the problem

I would suggest installing 7z, it is a free and light, and does what WinZip does mostly
http://www.7-zip.org/

Wednesday 11/16/2011 7:26:45 AM
Wed
Nov 16
2011

How do you graph data on Excel using two Y axes?

I have the values for the data on the left side of the graph but I want them on the right side. When I right the axis there isn't any 'properties' but Format Axis instead. I'm using Excel 2010 by the way.. Any idea on how to get the Y axis to the right side?


Answer

You need to do like this

http://folder8.com/View/?W0V

Wednesday 11/16/2011 5:07:56 AM
Wed
Nov 16
2011

Microsoft Excel Certification Question?

Is it possible to just take a certification exam for excel (offered by microsoft) and nothing else?


Answer

Yes
There is a certificate called MOS on Excel
Microsoft Office Specialist on Excel
http://www.certification-crazy.net/mous_…

Wednesday 11/16/2011 4:47:59 AM
Mon
Nov 14
2011

Question about MS Excel?

How come sometimes when I double-click on a cell in sends me all the way up to the top of the spreadsheet?


Answer

Scawny is correct, I just need to add that double click on the black border will take you to the next empty/full cell found on that direction
if you double click on the right black border of the cell, it will take you the last full cell or blank

Exactly as you do CTRL + Arrow right
CTRL + Arrow Top equals double click on the top border of a cell
CTRL + Down arrow equals to double click on the bottom border
and so on

Monday 11/14/2011 10:19:09 PM
Mon
Nov 14
2011

I can't open an Excel file in any computer, is there a way to force it open?

I worked a couple of budgets in two separate Excel spreadsheets and save them in my laptop. Now that I want to go back to them, I can't open them. Doesn't show any window or anything. The program starts but the spreadsheets do not open. Any advice please? I need that information!


Answer

I faced that before since I was using special characters (not English) in the name
Try rename that file (and any its parents folders)

Some times an app failed to open a file because the path provided has some unexpected chars

good luck

Monday 11/14/2011 3:45:05 PM
Mon
Nov 14
2011

How do I write a formula in Excel to figure out age and then if older?

Here is my question:
How would I a write a formula that figures out someone's age and then checks if that age is greater than 55? I know the birth date but am not sure on how to formulate. Nested function, if so how?


Answer

One if should do the trick
Assuming the birth date is on cell A1, paste this in B1
=IF( TODAY()-A1>55*356, "Older than 55", "Younger than 55")

let me know if this helped

Monday 11/14/2011 6:06:28 AM
Mon
Nov 14
2011

How to make a double line graph on Excel?

I have the temperature of the days of the week high and low how do I put that into a double bar graph?


Answer

I have a file that does that, have it here
http://Folder8.com/F/?Yan

file called "High_Low_Temperature Chart.xls"

let me know if that helped

Monday 11/14/2011 3:59:55 AM
Mon
Nov 14
2011

How to get a HP Compaq Presario CQ60 out of hibernation mode? and how to disable the hibernation mode?

I have a HP Compaq Presario CQ60 and it's been accidentally put into hibernation mode about three times now. It never wants to come out of hibernation mode, I was just wondering if anyone knows how to get it out of hibernation mode? and if anyone knows how to manually disable the hibernation mode option. If you could help me out by giving my the directions for the first, second one, or both it would be greatly appreciate. Thank You, very much!


Answer

Q1) If the computer goes to hibernation by itself, it means there were no power.
I can tell that the power cable is not firmly connected, or a worse scenario, you battery is broken
Try the following:
- Remove the battery, plug in power cable only and run the laptop
- if it runs, that means you need a new battery to replace
- If not, then mostly there is a problem inside the laptop (main board, cpu, etc) It looks like time for a new laptop.
Sorry

Q2) Once you have the laptop run, go to
Control Panel > Power Options > Change when your computer sleeps
Then remove the hibernation from all drop downs
That was if you have Win7, older versions of Windows should be something like that.

Let me know if that helped

Monday 11/14/2011 3:30:11 AM
Mon
Nov 14
2011

Is it possible to replace a char in a string of chars in java?

I want to replace a char in a string of chars by another char .Is it possible?if it is , how to do that? Thanks in advance


Answer

Here is how
http://www.w3schools.com/jsref/jsref_rep…

VBAXLMan

Monday 11/14/2011 2:33:15 AM
Sun
Nov 13
2011

Why does my computer freeze for a second when i try to play videos?

It's like it has to think about it for a second before it wants to play it. everything just stops, except the mouse. so i minimize and bring the window back and then it does it, but why does it do it in the first place? I just got my computer reformated or whatever recently because my windows crashed. could that be a reason?


Answer

That is a tough one
Could be the driver setup? (Since youjust reformated it)
Could be the video itself, by the way where is the video that you are playing?
If online (like youtube or others) try to change the browser (recommend Google Chrome)
If the video is local, then how big is that video file?

more details = answer more specific

Sunday 11/13/2011 10:27:53 PM
Sun
Nov 13
2011

What device do I get if I need to split an ethernet connection from an LAN line from the wall?

An ethernet cable comes out of the wall into a desktop, I need to share that port from the wall, what do I get if I want to split the ethernet connection to that desktop and my computer


Answer

Called router
There are a lot out there, wired, wireless or both wired and wireless at the same time
something like this
http://www.buy.com/sr/searchresults.aspx…

usually the wired only are much cheaper
http://www.buy.com/sr/searchresults.aspx…

Sunday 11/13/2011 9:55:16 PM
Sun
Nov 13
2011

Whats a website where i cant get new themes for tumblr for free?

not just the background part of the theme but like but like a whole different layout of where things are placed is what im looking for.


Answer

These guys have a very good library of tumblr templates
http://scriptmafia.org

Just do a search and browse through it

Sunday 11/13/2011 9:53:47 PM
Sun
Nov 13
2011

Is there a program that allows all downloads for windows to a mac pc?

Isn't there like a program that makes all downloads for windows work on a mac? Because there are some programs that don't work on mac.


Answer

There is something called (Virtual Machine)
http://www.vmware.com/products/fusion/ov…

That should enables you to run Windows on Mac, at that time, you can download and install just like you are having a PC

good luck

Sunday 11/13/2011 9:12:42 PM
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
Sun
Nov 13
2011

How do I delete a video from my YouTube account?

So I have a video i uploaded to youtube a while ago, and its a really stupid, short video that has no point in it, so I want to delete it, but i dont know how.


Answer

If you own that video, you can go to "My Videos" and select the desired video, and click on the small arrow next to "Action"
Something like this the picture in this folder
http://folder8.com/F/?Yan
that called "YouTube_Delete"

or access image directly here
http://folder8.com/Folders/B7P/YouTube_d…

Let me know

Sunday 11/13/2011 6:02:32 PM
Sun
Nov 13
2011

How come when I post a comment on Youtube and then google it it appears on a bunch of different sites?


Answer

Because Google bought YouTube few years ago and merge it with its search results
Also because some websites have RSS readers to include new data from famous websites to drive visitors to them.

Does that make sense?

Sunday 11/13/2011 5:33:33 PM
Sun
Nov 13
2011

How to scan a passport size photo?

I use an HP printer/scanner and want to scan a passport size photo 600(width)X450(height) but when i scan the picture it doesn't come passport size what to do.


Answer

Sizes in digital world (on screen) are not measured with regular units
we have pixels instead of inches
meaning, you can scan in any size then adjust that size in any printer app (like word) to make it ready to print in the size you want.

Sunday 11/13/2011 2:18:35 PM
Sun
Nov 13
2011

Low disk space when unrar?

I've CCcleaned all junk files in my PC, and my C driver can only has 4gb left. Except progams, i store all games,media files, doccuments in a 1,5tb HDD . But whenever i extract a >4gb rar file in the HDD, the disk space in the C driver goes down until the "low disk space" warning appear.If i stop the extraction, the disk space rise up again. How do i solve this problem ?


Answer

You can tell WinRaR to use the other drive as temporary location

Here is a screenshot for that setting
http://folder8.com/View/?XDCV07RTB

Sunday 11/13/2011 7:41:27 AM
Sun
Nov 13
2011

How to make a record cd?

how to record a cd
how to make a cd


Answer

In Win7, you can open the CD after inserting a blank CD then copy and paste MP3s to it and burn an "Audio CD"

Or most of CD burners come with an application that do that

Sunday 11/13/2011 7:16:38 AM
Sun
Nov 13
2011

How many tables needed? urgent plz?

A Manager of CNG station is interested to design a database to accommodate all CNG relevant information. He wants to keep the record of all vehicles, regular customers and all transaction relevant to customer and suppliers. Moreover manager is interested to generate different report relevant to see information about daily weekly or monthly sale and profit record. On some occasion manager of CNG station offer different sale discounts, so he is also interested to keep the record of all discounts. Similarly, manager also using monthly billing system for some type of regular customers.
Q1. How many tables needed.
Q2. design tables.(optional for you viewer :P )


Answer

I would say at least 5 tables
Customers
Vehicles (one customer can have more than one vehicle)
Supplies
Services (To have actual service provided with Customer id, supply id, vehicle id, billing id, etc)
Discounts

And you can add
Billings (to have bills, with Costumer Id, amount, service id, discount id, etc)
Employees



let me know

Sunday 11/13/2011 7:12:14 AM
Sun
Nov 13
2011

Can I make A Log-In Box For a website With Just One Password for everyone to use?

I Recently Saw that www.wixlab.net has a log in like the one im asking for. you enter the password ilovewixlab It Unlocks The Downloading Feature. BUT all I Want Is For It To Take you to another Assigned Page. But if you know how to do Anything with this or what it is or where can i get it. i welcome you to answer please. Thanks!


Answer

Yes you can
You need to use cookies, based on your technology that used to build the site (php, asp, .net, cms, etc)
You can set the password entered by user to save in a cookie, then match it against the password you like.

let me know if that makes sense, I will be happy to do it if you want and if the technology is something I know

Sunday 11/13/2011 6:45:12 AM
Sun
Nov 13
2011

Does 4g connect to the internet even without an access point?

such as in a car.
in a restaurant with no hotspots.
outside.
etc.


Answer

Yes

Sunday 11/13/2011 6:13:11 AM
Sun
Nov 13
2011

How do you order a recovery disc for a Altec laptop for windows 7?

anyone know the phone number and how much should expect to pay for this?

also, if can actually get a Windows 7 installation disc..what is the difference? (Btw, this Windows Installation disc is for 1 computer only..but can it be used again and again like a recover disc?)

WHAT IS DIFFERENCE BETWEEN A "RECOVERY DISC " AND AN "OPERATION DISC"?
PLEASE EXPLAIN.


Answer

Recovery disc is a Windows disc made especially for that particular PC
years ago, people used to be able to do a recovery disc on machines that not came for, but not anymore.
In another word, recovery disc is a special version of that OS with all additional drivers and software.

Here is the answer to your question:
Some companies lately have the option to save your recovery disc on your hard drive instead, and have a special software that enables user to create those recovery disc(s) for you. If you have that you can create recovery discs for free. Search something called "Recovery" on your start menu.

While Operation disc, or so called OS disc, is the OEM version of the system that should be installed on only one machine (unless they authorize you to do more)
IT IS ILLEGAL TO INSTALL ONE OPERATION DISC ON MORE THAN ONE MACHINE if it was not made for that (Some Windows7 versions allows you to install on 3 machines like Windows7 family pack)

Otherwise, it is illegal.

Let me know if that makes sense

Sunday 11/13/2011 3:23:36 AM
Sun
Nov 13
2011

Toshiba C660 cam video recording?

i have a toshiba C660 laptop it has a built in cam but i don't think any software came with it to actually record and store vid files on my laptop i am interested in making vlogs for youtube .. help <3


Answer

There are a lot of software that do that
try this
http://download.cnet.com/1770-20_4-0.htm…

Sunday 11/13/2011 2:44:24 AM
Sun
Nov 13
2011

How do I kick an Xbox 360 from my WiFi network remotely?

I'm trying to torrent and download here but my brothers are hogging all the bandwidth playing on Xbox Live! The Xbox 360 is connected wirelessly to the network so is there a way where I can quietly kick it off the network, secretly allocate all of the bandwidth to myself, or just control my home wireless network without them knowing?


Answer

Yes, sure
Most wireless routers enable you to control that using its IP (like 192.168.0.0)
You should have that IP in the router manual
Once there, you can control who gets what
You can create password, refresh IP addresses, restrict access to only certain devices, etc

Sunday 11/13/2011 2:28:12 AM
Sun
Nov 13
2011

What is a good user to user chat to add to a website?

Like the user to user chat in facebook not like a chatroom.


Answer

I am personally using Chatango (http://www.chatango.com/)
It is kind of making its purpose for me

Sunday 11/13/2011 2:06:24 AM
Sat
Nov 12
2011

Simple financing in Excel for personal business?

Hey,


I've looked online for templates and such but didn't really come up with much.

My small business is in Repairs. So all I really need is an Excel spreadsheet (I have windows excel home and student 2010)

Could someone explain how i could make one with some basic formula.

Basically, I need a column for costs such as parts, and then the final invoice bill. And hopefully i can have a box to the side that will work out the final balance for that month/year.

Thanks,
~D


Answer

I have a very simple sheet that should do exactly what you are looking for

You can download it for free here
http://folder8.com/F/?Yan


Called "SimpleANBox" in 2007/2010 format

Saturday 11/12/2011 5:36:22 PM
Sat
Nov 12
2011

Ofiice Word and Excel documents icons are blank in Windows 7?

I had MS Office Starter(with only Word and Excel in it), I uninstalled it, and installed MS Office 2010 Professional Plus. The documents are opening good, but icons are blank... how to fix this, its annoying... Please help... Deleting Icon Cache didn't help... And EVEN System Restore won't help...


Answer

I would suggest that you run Office installation setup again and select "Fix installation" or may be called "Repair"
This might generally fix it.

Otherwise, you can go a head and clear the temporary files and internet cache, these sometimes do some interesting errors like yours

good luck

Saturday 11/12/2011 4:11:18 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
Thu
Nov 10
2011

How to get an ms- excel formula to get the nos in the months col?

There are 100 apples. i need to give it to 2 ppl, every month. Person 1 is the 1st pref. he has to exhaust his quota of 80 apples, only then i can give the rest to person 2, whose quota is 20 apples. Pls help me put a formula in excel. Thanks in advancce



Total apples 100
Quota of Apples for Person 1 80
Quota of Apples for Person 2 20

Sub1 Sub 2
Apples given in Month 1 15 0
Apples given in Month 2 15 0
Apples given in Month 3 15 0
Apples given in Month 4 15 0
Apples given in Month 5 15 0
Apples given in Month 6 5 10
Apples given in Month 7 0 10


Answer

You just need to have the table set up correctly
first: put the total number of apples in certain column (Say E starting from E4)
Then in F4 paste this to get apples for person 1
=IF(E4 > 80, 80, E4)

Then in G4 to get apples for person 2
=IF(E4>80, E4-F4, 0)

Then fill those down to cover other months


let me know if that make sense or not

Thursday 11/10/2011 9:32:50 AM
Thu
Nov 10
2011

Is there a way to change the auto-assigned value of empty cells in excel?

In excel, empty cells are treated as though their numeric value was 0, and as though they had no text value.
I'm using a custom function that runs through a range of data (which contains 0 values) and gives me a result based on the range's findings.
Currently, there are no blank cells before the end the range. Therefore, I can use CountA(range) to tell my function how many times to iterate (i.e. to continue until it reaches the end of the range).
I wish to instead specify to the program that it must continue to the end of the range (another problem) and ignore blank spaces.
Rather than re-writing all of my functions to ignore these empty cells, is there an excel option that will allow me to force excel to treat empty cells as a value other than 0?


Answer

If I got you correctly you can use the formula
ISBLANK()
When you put it inside IF, you will will get if the cell is blank

CountA will not count blank cells either

let me know if that solves it or not

Thursday 11/10/2011 3:34:45 AM

Tags

History