PDA

View Full Version : new to VBA scripting. need help.



Cranium
02-23-2007, 10:32 AM
hey folks,

my boss wants to see if i can figure out how to automate a process in excel. basically i have one colum containing product numbers. i need the script to search for 64 specific values and then if it finds any of them, to replace them with corresponding set values.

basically i need a script that says "if any of the cells in column 'F' equal "3025" then change it to "2101" and if it equals "4046" to change it to "3108", etc etc. there is no set difference between the 2 values so i cant just do one if/then statement. i have to have 64 different cases (64 values i need to look for and change them to their corrsponding values).

can anyone clue me as how to do this? i can email you the excel file if you need it. THANK YOU!

Cranium
02-23-2007, 11:15 AM
i'm thinking i need to have a loop start at cell F1, then move down to the next cell in column F until it gets to the last filled row.

something like this example i found...

Sub Loop2()
' This loop runs as long as there is something in the next column
Range("F1").select

Do

ActiveCell.FormulaR1C1 = "=IF(ActiveCell.Value = 3025, 2101, ActiveCell.Value)"
ActiveCell.Offset(1, 0).Select
Loop Until IsEmpty(ActiveCell.Offset(2, 0))
End Sub

is that code right for the loop? can u help me figure out how to format the if/then formula to put in the middle? basically i want it to test what the active cell's content is. if it's one of the 64 values, change it to its corresponding value, if not, leave it alone. PLEASE HELP! :)

mvidas
02-23-2007, 11:16 AM
Cranium,

There are a number of ways to do this, however since what you're doing is pretty cut and dry, I think the easiest would just be to use the Replace method:Sub CraniumReplace()
With Columns("F")
.Replace "3025", "2021", xlWhole 'only need xlWhole on first replace
.Replace "4046", "3108"
'etc
End With
End SubMatt

Cranium
02-23-2007, 11:20 AM
wow. thats way easier. i am TOTALLY new to VBA. i didnt even know it existed til yesterday. THANK YOU! ill let u know if i need anything else.

mvidas
02-23-2007, 11:40 AM
No worries :) You'll find VBA to be fun and powerful, so please feel free to ask anything you'd like
Matt