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

3 Q & A posts tagged with random

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
Mon
Apr 6
2009

In Microsoft Excel, the auto filter does not show all my items that are listed.?

There are a lot of drop downs, but it randomly does not have all come up in the list. If I go to them in the spreadsheet, they are in there correctly but do not appear in the filter drop down. Does anyone know why this is? thanks


Answer

Autofilter requires no empty spaces between rows
That could be the reason
Or

Make sure that you disable / enable the Auto filter

VBAXLMan

Monday 4/6/2009 7:58:05 AM

Tags

History