Need help writing a macro involving user input!

**Alkatraz** · 04-22-2010, 02:57 AM

Hi everyone, I'm brand spanking new and am seeking help from sages. Basically I have a dilemma, I want to create a form in which users can:

1. Select a Column (Column H in my case)
2. Enter a value as input that will be matched up with the values in that column
3.With these selected values (integers), replace all of them with the NEW value.

I've been trying to stitch together a frankenstein macro of other programs. Hopefull you guys can help.

Heres my logical flow:
Sub DepartmentChanger

X, NewX; integer

User: Input Value X
PC: Does Value X exist in the system?
IF Value X = TRUE
THEN Input NEW value X,
X = NewX
ELSE "No such value exists".

end.

Thanks.

**wamp** · 04-22-2010, 03:35 AM

Hi.
Try this code in a macro:

Note: you never specified where to get the "NewX" value from.
you could for example substitute the line saying "NewX = 1" with "NewX = Range("A1").value if you want to insert the data there.

Please Login or Register  to view this content.

Hope this helps

If you need any further information, please feel free to ask further

However

If this takes care of your needs, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED. It helps everybody!
Also
If you are satisfied by any members response to your problem please consider using the scales icon top right of their post to show your appreciation.

**Alkatraz** · 04-22-2010, 03:48 AM

Hi Wamp,
You are a genious

Essentially I want to replace an existing number with a new number. Lets say you have H column with numbers ranging from 1 to 30. I want to be able to replace all the number 2's to 5's for example. The newX was is the 5's.
Basically excel needs to check if the 2's are present in the column, ask the user what he wants to change the 2's to, allow the user to input an integer and then change all the 2's to that integer. NewX was that new integer - im unfortunately not a guru at this

Sorry if i wasn't clear enough, Ive never done excel programming, but I'm slowly breaking apart your code to and trying to interpret it.

If you could help me on nailing that, it would be great!

Thank you so much!

**wamp** · 04-22-2010, 04:24 AM

Ahh.. I see..
This is the code you want then, I assume

Please Login or Register  to view this content.

**Alkatraz** · 04-22-2010, 04:40 AM

Thanks a TON wamp, you've cut down my workload immensely. Im definitely giving you good feedback.

I have a couple of questions though if you could humor me.

Here are my assumptions about your code:
1. Dim i assume is the name of a new expression or variable,
2. Set is predefining a variable with a string or integer
3. range is from column a to b, where the : ties the two together as a range.
4. MsgBox generates a message box that is pre-coded into excel to be maniuplated.
5. vbNullstring is another way of saying = 0

Heres we're my knowledge gets fuzzy:
What is the R.select?
How come you have Dim's starting half way into the program?
You exit as Sub up top, but then exit it again half way without declaring a new sub.

Cheers Wamp, have a good one!
Alkatraz

**wamp** · 04-22-2010, 05:38 AM

1: Dim <--if you are to set a variable, you have to specify what kind of variable it is.
dim <variablename> as <variabletype> , e.g. "dim test as string" tells the program that this variable will contain text
2: Set <-- set <object> = <somevalue> you can set variables as objects that you don't know what is.
a range for example is neither a string or an integer, but an array. you could probably "dim r as range, r = range(a1:a4)", but setting it to an object is imho safer when it comes to runtime errors.

3:that is correct. a range is a selection of cells between the points you specify. for examle range("A1:B3") = all cells in this area in your sheet.

4: a messagebox is simply a box that pops up with information to the user. great for programming, as you could do a msgbox(yourvariable) <--to see that it is correct.
you have another option called inputbox where the user can insert information that your program can store in a variable for later use etc.

5: vbnullstring is not the same as "0". "0" is a value, whereas vbnullstring is nothing.
there is a difference. you could alternatively type "if <variable> is nothing then" or 'if <variable> = ""' for strings, but vbnullstring is the safest bet.
--------------
for your other questions
early in my code, I specify a range in R (set R = range(xxx))
R.select simply invokes that range. I could alternatively type Range(xxx).select <--but it's easiert to specify the range in a variable
--------------
setting variables later on in the macro.. why?
in short: trial and error.. I set the variables as I go.

From a programatically point of view, you could think of it like this: every variable you declare reserves a lil-bit of memory on your computer.
the 'foundrange' variable is declared only IF the user inputs a valid item to search for.
Why declare a variable and reserve memory-space if it's not needed?

however to be consistent with this rule, the "userchange" variable should also be set later on in the macro.
-------------
Exit sub.
Exit sub is not the same as End sub.
The code runs linear, and goes through each line of code from top to bottom. if an error comes along (like the user input is wrong) then it should exit the whole subroutine. or else it would continue trying to execute code with bad parameters.

as you can see in my code, the 'exit sub's are always in IF statements. what does that mean? well

Please Login or Register  to view this content.

Oh..
I assume your original problem is solved, so please click EDIT in your original (first) post, click GO ADVANCED and set the PREFIX box to SOLVED. It helps everybody!

(note: your thread will not be closed, so you can continue to post here and ask questions )