Clean Up Code - consolidate steps

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

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


"Frantic Excel-er" <FranticExceler@discussions.microsoft.com> wrote in
message news:5F27D57A-DDF5-46F1-A9E4-CE2187D09679@microsoft.com...
> 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
>
>
>
>
>

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

"Anne Troy" wrote:

> 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
>
>
> "Frantic Excel-er" <FranticExceler@discussions.microsoft.com> wrote in
> message news:5F27D57A-DDF5-46F1-A9E4-CE2187D09679@microsoft.com...
> > 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
> >
> >
> >
> >
> >
>
>
>

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


"Frantic Excel-er" <FranticExceler@discussions.microsoft.com> wrote in
message news:E00AA9C7-3433-45EE-BC74-37052B8AC445@microsoft.com...
> 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....
>
> "Anne Troy" wrote:
>
> > 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
> >
> >
> > "Frantic Excel-er" <FranticExceler@discussions.microsoft.com> wrote in
> > message news:5F27D57A-DDF5-46F1-A9E4-CE2187D09679@microsoft.com...
> > > 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
> > >
> > >
> > >
> > >
> > >
> >
> >
> >

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


--
HTH

Bob Phillips

"Frantic Excel-er" <FranticExceler@discussions.microsoft.com> wrote in
message news:5F27D57A-DDF5-46F1-A9E4-CE2187D09679@microsoft.com...
> 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
>
>
>
>
>

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

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



"Bob Phillips" wrote:

> 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
>
>
> --
> HTH
>
> Bob Phillips
>
> "Frantic Excel-er" <FranticExceler@discussions.microsoft.com> wrote in
> message news:5F27D57A-DDF5-46F1-A9E4-CE2187D09679@microsoft.com...
> > 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
> >
> >
> >
> >
> >
>
>
>

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





"Bob Phillips" wrote:

> 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
>
>
> --
> HTH
>
> Bob Phillips
>
> "Frantic Excel-er" <FranticExceler@discussions.microsoft.com> wrote in
> message news:5F27D57A-DDF5-46F1-A9E4-CE2187D09679@microsoft.com...
> > 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
> >
> >
> >
> >
> >
>
>
>