Excel Macro

[flyfish]

I need to take columns and create a list with some extra words in them, basically I want to do this:

John Doe 65454 46465
Jane Doe 65465 12358
Tom....

to become:

John Doe
word
word
word
65454
word
word
46465
Jane Doe
word
word
word
65465
word
word
12358
Tom...

I have used copy and paste to try and create the list, but the macro uses the range number (A1), so if I try a loop it just does the same. Any help would be appreciated.

 

[Smitty]

Welcome to the Board!

You'll need to give some more specifics.

Which columns are the data in?

What determines the number of "word"s that get placed between each name? I see a 3-2 pattern, but will it change between names? What and where are the "word"s?

Etc.

And finally, what are you hoping to achieve? A dataset in your proposed structure won't likely be all that useful.

 

[flyfish]

Thanks for the reply. I know it doesn't make sense, but the list will become a macro for a data load onto an as400. So it will strictly be a list. The Name column is c
the second column is d
and the third colum is f

The "words" are static. The brackets and names are correct and static below. The cells shown are the rows and are the only things that would change.the pattern is:

a1 b1 c1 d1 e1 f1
a2 b2 c2 d2 e2 f2

I want a list like this.

c1
[enter]
[down]
[down]
[down]
[down]
[down]
[down]
[down]
[down]
[down]
[down]
[down]
[down]
d1
f1
c2
[enter]
[down]
[down]
[down]
[down]
[down]
[down]
[down]
[down]
[down]
[down]
[down]
[down]
d2
f2
.......and on and on till row 287.

 

[flyfish]

*bump*

 

[ChrisOswald]

Assuming the sheet your table is on in Sheet1, this formula should work to generate your list. (copy it down to your last need row)

=IF(MOD(ROW(),16)=1,INDEX(Sheet1!C:F,INT((ROW()-1)/16)+1,1),IF(MOD(ROW(),16)=2,"[enter]",IF(MOD(ROW(),16)=15,INDEX(Sheet1!C:F,INT((ROW()-1)/16)+1,2),IF(MOD(ROW(),16)=0,INDEX(Sheet1!C:F,INT(ROW()-1)/16+1,4),"[down]"))))

Just out of curiousity, how are you doing your data load?

 

[flyfish]

Thanks for the formula....seems to be working. I will be doing the upload with a macro on Client Access for iSeries. This is basically the body of the macro.

 

[flyfish]

How would I adjust this formula to add an enter after the field name? So it would go fom 16 to 17 and the 17th slot would be [enter]

 

[flyfish]

=IF(MOD(ROW(),18)=1,INDEX(C:F,INT((ROW()-1)/18)+1,1),IF(MOD(ROW(),18)=2,"[enter]",IF(MOD(ROW(),18)=17,"[enter]",IF(MOD(ROW(),18)=0,"[enter]",IF(MOD(ROW(),18)=15,INDEX(C:F,INT((ROW()-1)/18)+1,2),IF(MOD(ROW(),18)=16,INDEX(C:F,INT(ROW()-1)/18+1,4),"[down]"))))))

Have the final version.....Thanks immensely foy your help.

 

[ChrisOswald]

Just a word of warning if you're using a macro recorded by the as400 system -- the list you want makes me suspect you're concatenating them into a bunch of sendkey statements in VBScript.

It's very likely for a script to "Get ahead" of the system and lose a screen. A quick fix, if you don't feel up to dealing the the Auteclps wait thingys (not sure what these are offhand, it's been over a year since I've messed with AS400), is to just put a msgbox(" ") statement after each enter statement

it think autECLSession.autECLOIA.WaitForInputReady(10000) will work too, but this is something I just quickly copied from something I wrote a while back -- I can't remember if there's any additional declarations or anything needed.