loop that keeps producing a next without for error

Several issues:

1. Your nesting of your For... Next and Do... Loop overlap illogically. Do you want to perform the Do... Loop for each x value? (in which case swap lines "Next i" and "Loop" in your code)
2. You stated For x = 1 to 10, then close that with Next i. Which variable are you trying to loop through - x or i? If it is x then this line should be "Next x".
3. Your code has an undimensioned variable: CellRefx

You can't append a variable as a value in the way you are trying it.

CellRefx doesn't become CellRef1 when x is 1. You'd be better declaring CellRef(10) as an array, then referring to CellRef(x).

Similarly with your ranges: "Gi:Ii" will not be recognised as "G4:I4" when i is 4. You could refer to that range as

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

for example.

It's hard to properly unpick your code without an attached workbook, but look at something like this:

Please Login or Register  to view this content.

See the different ways we can use variables to refer to ranges?

If you want more help with this, I'll really need you to attach a workbook so I can test the code against your actual workbook structure. Don't think you're far off though.

Two questions, before I look at your workbook:

Which version of Excel are you using? Your profile says 2003, but your attachment is >2007 format?

What do you actually need help with now - from the code I posted above, which bit doesn't work, or don't you understand well enough to tweak to work? There's no point me spending time covering stuff you are already capable of doing yourself, let's identify and solve the problem that you need help with.

Originally Posted by Sc0tt1e

OK the module is called "Functions" and the button running code is "Costs & Closures"

There is a module called Documents_History which is currently set up to generate the header on the first tab, this code is about producing the info to create a grid below it of all fields in database that has a value.

Nope. Not seeing any of that in your attachment.

Sorry, had to go do some real work.

Just had a look at your workbook now.

Your Do ... Loop will never end, as nothing is changing in either the reference or the value of CellRef(x).

What is it you are trying to achieve? As I understand it: you enter a search string, find the matching row in Sheet2, and for each document that exists in columns BA:CG, copy those values into Sheet1, with some formatting. Right?

Try this:

Please Login or Register  to view this content.

See the code I just posted. It loops through all ten x values, checks if a matching value is present in the associated column of Sheet2, and if there is a value, copies the data across to Sheet1.

Okay. This chunk of my code:

Please Login or Register  to view this content.

Replaces this chunk of your code:

Please Login or Register  to view this content.

Instead of 10 variables, we use one array variable. Values 3 to 10 follow a regular pattern to increment the column numbers, so we don't need to write a separate line for each variable - we can loop through them. So, when i is 3, we set CellRef(3) to be S2LI.cells(RowNumba, 62). When i is 4, we set CellRef(4) to be S2LI.Cells(RowNumba,65). And so on. It's just a way of reducing / simplifying the code.


Now, as for looping through x from 1 to 10:

Please Login or Register  to view this content.

I have highlighted in red all instances of the x variable in this loop. We are essentially using the x variable to loop through all the (potential) source data, and using the i variable to format and write the output range.

I hope that helps clarify what the code is doing. Now, time for ME to ask you another question: does it achieve what you expected? And if not, what needs changed?