Clean Up Code - consolidate steps

[Guest]

Hi All...

I have code that contains the following:

ActiveCell.FormulaR1C1 = "USB"
Range("A2").Select
ActiveCell.FormulaR1C1 = "04165"
Range("A3").Select
ActiveCell.FormulaR1C1 = "14709"
Range("A4").Select
ActiveCell.FormulaR1C1 = "14716"
Range("A5").Select
ActiveCell.FormulaR1C1 = "24704"
Range("A6").Select
ActiveCell.FormulaR1C1 = "44705"
Range("A7").Select
ActiveCell.FormulaR1C1 = "44710"

and it goes on to enter the associated text on the next line...all the way
down to 60 rows. Is there a way that I can consolidate these steps where the
macro will automatically know to put the text in the next rows......for
example....
MACRO CODE = Enter Text, start A1...FAS,12354, 23456

 

[Anne Troy]

If this is always the same, wouldn't it be simpler for you to copy some
cells from a hidden sheet instead?
*******************
~Anne Troy

www.OfficeArticles.com
www.MyExpertsOnline.com

 

[Guest]

I think so, but my boss wants them in every worksheet that I validate, so it
would be there just in case it was ever referenced....

 

[Bob Phillips]

There is no pattern to the values so the simplest thing is to simplify the
code

Range("A1").Value = "USB"
Range("A2").Value = "04165"
Range("A3").Value = "14709"
Range("A4").Value = "14716"
Range("A5").Value = "24704"
Range("A6").Value = "44705"
Range("A7").Value = "44710"

You could also load an array

aryValues = Array("USB", "04165", "14709", "14716", "24704", "44705",
"44710")
For i = 0 To UBound(aryValues)
Range("A" & i + 1).Value = aryValues(i)
Next i

 

[Anne Troy]

I'm sorry... I meant that you could record a macro that copies a named range
from a hidden worksheet, right? Why step through entering each cell with the
macro is what I mean. Why not just have the macro copy the cells from the
hidden worksheet?
*******************
~Anne Troy

www.OfficeArticles.com
www.MyExpertsOnline.com

 

[Guest]

Thanks a bunch Bob!!!!.....

Even cleaning the code up that much is better than nothing, and it makes my
macro much easier to read.

 

[Guest]

Bob --- one more question about the array...

for these values I am entering..I actually am making 2 lookup tables...the
first is values for "USB" - 1,2,3,4..Etc, and that is in column A, Col. B has
"Ok, down to B9, then "India" only in B9, then okay again from B10 to B34 ,
then in Column D i have values for "FAS" - 1,2,3,4,..etc, and then I have in
column E "OK" from E2 to E61...

so when the macro is executed, it looks something like this:
ColA ColB ColC ColD ColE
USB FAS
04165 OK 04165 OK
14709 OK 14014 OK
14716 OK 14016 OK
24704 OK 24013 OK
44705 OK 24017 OK
44710 OK 24019 OK

Also, the code for making the macro list account numbers is in my first
thread...

Once all the accounts are "typed" by the macro, I then name the two blocks
of items so that I can do vlookups with them...Code looks like this for the
naming:

Range("A2:B34").Select
Range("B34").Activate
ActiveWorkbook.Names.Add Name:="USB", RefersToR1C1:= _
"='Lookup Tables'!R2C1:R34C2"
ActiveWindow.SmallScroll Down:=28
Range("D2:E61").Select
Range("E61").Activate
ActiveWorkbook.Names.Add Name:="FAS", RefersToR1C1:= _
"='Lookup Tables'!R2C4:R61C5"

Would making the lists of accounts I have above into an array and naming
them so that vlookup would work be possible???? If so, how would it look?????

Thanks a bunch for all your help....I am really trying hard to learn this
stuff!!!!!!!

Sara