New to VB macros/functions....need some help

[tb_1234]

Hi,

I have a spreadsheet that I am using to generate a string of data (all numbers) on one sheet depending on data that is located in another sheet. I have successfully made it automatically generate two lines of code (taking up two seperate rows) for every one row that is referenced on the other sheet. The problem is I have a population if hundreds of thousands of rows (most of which will not be returning a line of code as a result of conditions I established) that need to be checked and I cannot use autofill. Here are the formulas:

Line 1 should be
=IF(Sheet1!$S2="","",("776"&TEXT(Sheet1!$A2,"000000000")&"32"&TEXT(Sheet1!$C2,"YYMMDD")&" 41 "&TEXT(Sheet1!$E2,"00")&TEXT(Sheet1!$G2,"00")&"00"&REPT("0",9-LEN(Sheet1!$S2))&Sheet1!$S2&REPT("0",9-LEN(Sheet1!$S2))&Sheet1!$S2&" & 1"))

Line 1 should be
=IF(Sheet1!$T2="","",("776"&TEXT(Sheet1!$A2,"000000000")&"32"&TEXT(Sheet1!$C2,"YYMMDD")&" 41 "&TEXT(Sheet1!$E2,"00")&"07"&"00"&REPT("0",9-LEN(Sheet1!$T2))&Sheet1!$T2&REPT("0",9-LEN(Sheet1!$T2))&Sheet1!$T2&" & 1"))

I'd like those two lines to replicate down column A, with the values S2,T2,A2,C2... to change to S3,T3,A3,C3....., and S4,T4,A4,C4.....etc. I'd also like to have this work automatically without having to run a macro. I'm assuming VB will have to be used and have even written a loop that might take care of this but do not know how to apply it to VB as i've never used it before.

I know it seems complicated but I would appreciate any input or ideas that anyone may have about how to accomplish this.

Thanks!

[swatsp0p]

I have a several questions regarding your post. First you say you have "hundreds of thousands of rows". This, of course, is not possible, as Excel has a limit of just over 65,000 rows (not even one hundred thousand!).

Second, you list two codes for Line 1. Do you mean Line 1 and Line 2? When you say Line 1 do you mean Row 1 or Column 1 (or something else)?

Where on your sheet do you have these formulas (e.g. A1 and B1)?

If you copy these formulas, the imbedded cell references will change relative to the row location they are copied to because the rows are not made to be Fixed by use of $ in the reference. That is, the reference to $S2 will become $S3 if you copy from cell A2 to A3. $S2 remains $S2 if you copy from A2 to B2. Whereas a reference $S$2 will remain $S$2 no matter where the formula is copied to.

If you care to give more details, someone here will happily try to solve your problem.

[tb_1234]

oops...sorry for leaving out those details I was sort of in a rush when posting.

First, when I stated 'hundreds of thousands of rows' I should of clarified that I have this data in text files from which I am copying, formating and pasting into excel in groups of 20-30 thousand rows.

Second, line 1 is located in Column A Row 1 and line 2 in Column A Row 2. All the data on that sheet (Sheet2) will be in Column A.

All the other data in the formula (S2,T2,A2,B2,C2,etc) are values being pulled from Sheet1.

I made those embedded cell refererences because, as it is now, each row in Sheet1 (the sheet where all the data being pulled is located) is going to be checked for data. If there is no data in S2 for instance then no string of code is needed however if there is data then the IF function is executed yielding data.

I hope that clears this up somewhat. Thanks for your help!

[swatsp0p]

Here is how I understand your question:

Sheet 1 is populated with data from a text file, 20,000+ rows of data.
Sheet 2 is used to pull specific items from corresponding rows on Sheet 1

I'd like those two lines to replicate down column A, with the values S2,T2,A2,C2... to change to S3,T3,A3,C3....., and S4,T4,A4,C4.....etc. I'd also like to have this work automatically without having to run a macro.

Unless I'm missing something, you need only place the formulas in A2 and B2 on Sheet 2 and copy them down 20-30k+ rows.

To copy them quickly, Press CTRL+G and enter A30000 in the Reference line of the "GoTo" box and click OK. In Cell A30000 enter 'x'. Press End then the UP arrow (or CTRL+HOME). Highlight both A1 & B1, hold the Shift key down, and press END then the Down Arrow Key, which will highlight to row 30000. Release the shift key and press CTRL+D to copy the formulas down this range.

With the Relative References you have in your formulas, they will automatically adjust to return the data from the desired rows. (the formula in row 30000 will be:

=IF(Sheet1!$S30000="","",("776"&TEXT(Sheet1!$A30000,"000000000")&"32"&TEXT(Sheet1!$C30000,"YYMMDD")&" 41 "&TEXT(Sheet1!$E30000,"00")&TEXT(Sheet1!$G30000,"00")&"00"&REPT("0",9-LEN(Sheet1!$S30000))&Sheet1!$S30000&REPT("0",9-LEN(Sheet1!$S30000))&Sheet1!$S30000&" & 1"))


Is this what you are looking for?

Good Luck.