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.

Sub DepartmentChanger()
Dim userinput As String
Dim rangecheck As String
Dim NewX As Integer '<-- you never specified where to get the NewX data from?
NewX = 1 '<-- setting the value to 1 just to have a value

userinput = InputBox(Prompt:="Name of prompt here.", Title:="Title Here", Default:="defaultvalue")
If userinput = "defaultvalue" Or userinput = vbNullString Then
MsgBox ("You need to enter something")
Exit Sub
End If

Columns("H:H").Select ' range to search


Dim foundRange As Range
Set foundRange = Selection.Find(What:=userinput)
If foundRange Is Nothing Then
MsgBox ("No such value exists")
Else
Selection.Find(What:=userinput).Value = NewX
End If
End Sub

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

Sub DepartmentChanger()
Dim userfind As String
Dim userchange As String
Set R = Range("H1:H200") '<--set your range here

userfind = InputBox(Prompt:="Name of prompt here.", Title:="Search for something", Default:="defaultvalue")
If userfind = "defaultvalue" Or userfind = vbNullString Then
MsgBox ("You need to enter something")
Exit Sub
End If

R.Select

Dim foundRange As Range
Set foundRange = Selection.Find(What:=userfind)
If foundRange Is Nothing Then
MsgBox ("No such value exists")
Exit Sub
End If


userchange = InputBox(Prompt:="Found your value!", Title:="What to replace with?", Default:=userfind)
If userchange = "defaultvalue" Or userchange = vbNullString Then
MsgBox ("You need to enter something")
Exit Sub
Else

For Each cell In R
 If cell.Value = userfind Then
 cell.Value = userchange
End If
Next
End If
End Sub

**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

IF <yourstatement here> = false  <you don't get the info your code want/need> THEN
<execute this code only if your statement is false> e.g. "exit sub"

"exit sub" will then stop executing more code. everything from here and below is aborted.

end if
<continiue with more code here if yourstatement = true (or correct or whatever you wanted it to be)>

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 )