Solved: Reduce x number of 'with' loops using code to update ranges from userform entries?

[cchris_uk]

Hi again!

I have 4 variables,

dataRowStart (First row of wanted data - manually entered into a userform)

dataRowEnd (This is obtained by finding the last row containing data)

dataColumnStart (First column, usually 'C' but may change, so this is a manual entry via userform)

dataColumnEnd (Operator selects how many columns there are, so for example, if they enter '4', the script needs to make the calculation to determine the last column, eg, dataColumnStart = C, therefore 4 columns starting from 'C' means last column is 'F')

These are setup by the operator using a userform.
An example of userfom data is:

dataRowStart = 7
dataColumnStart = C
dataColumnCount = 4
dataEndRow = 135


I currently have the following script (4 'with'/'end with' loops that import individual columns before a 'Do While' loop loads up the next spreadsheet):

[vba]Do While mpFile <> ""

Set mpNewWB = Workbooks.Open(reportloc & "\" & mpFile)
mpNextCol = 1

With mpThisWB.Worksheets(1)

For Each mpCell In mpNewWB.Worksheets(1).Range("C7:C135")

.Cells(mpNextLine, mpNextCol).Value = mpCell.Value
mpNextCol = mpNextCol + 1

Next mpCell
End With

mpNextCol = 1
mpNextLine = mpNextLine + 2

With mpThisWB.Worksheets(1)

For Each mpCell In mpNewWB.Worksheets(1).Range("D7135")

.Cells(mpNextLine, mpNextCol).Value = mpCell.Value
mpNextCol = mpNextCol + 1

Next mpCell
End With

mpNextCol = 1
mpNextLine = mpNextLine + 2

With mpThisWB.Worksheets(1)

For Each mpCell In mpNewWB.Worksheets(1).Range("E7:E135")

.Cells(mpNextLine, mpNextCol).Value = mpCell.Value
mpNextCol = mpNextCol + 1

Next mpCell
End With

mpNextCol = 1
mpNextLine = mpNextLine + 2

With mpThisWB.Worksheets(1)

For Each mpCell In mpNewWB.Worksheets(1).Range("F7:F135")

.Cells(mpNextLine, mpNextCol).Value = mpCell.Value
mpNextCol = mpNextCol + 1

Next mpCell
End With

mpNewWB.Close

mpNextLine = mpNextLine + 2

mpFile = Dir
Loop[/vba]
What I need to happen is:

1. Where the ranges for the data are currently set via hardcode, they should be calculated from the entries made in the userform.
2. The 'with' loops could also be reduced to just one loop that updates itself within another loop dependant on how many 'columns' of data are required.

Regards,

Chris

[Bob Phillips]

I think this does it

[vba]

Dim mpCol As Long

Do While mpFile <> ""

Set mpNewWB = Workbooks.Open(reportloc & "\" & mpFile)
mpNextCol = 1

With mpThisWB.Worksheets(1)

For mpCol = dataColumnStart To dataColumnCount - dataColumnStart + 1

For Each mpCell In mpNewWB.Worksheets(1).Cells(dataRowStart, mpCol).Resize(dataEndRow - dataRowStart + 1)

.Cells(mpNextLine, mpCol).Value = mpCell.Value

Next mpCell

mpNextLine = mpNextLine + 2
End With

mpNewWB.Close

mpFile = Dir
Loop
[/vba]

[cchris_uk]

Cheers, I will make the changes and give that a try

Chris

[cchris_uk]

Thanks xld!

I had to modify your code a little to correct the formatting of the imported data (this took me a few hours to figure out, but thanks to the help here, I managed it )

The changes I made to your suggested code was as follows.

[vba]
mpNextLine = 1
Do While mpFile <> ""
Set mpNewWB = Workbooks.Open(dataLocation & "\" & mpFile)
With mpThisWB.Worksheets(1)

For mpCol = dataColumnStart To dataColumnCount + dataColumnStart - 1
mpNextCol = 1
For Each mpCell In mpNewWB.Worksheets(1).Cells(dataRowStart, mpCol).Resize(dataEndRow - dataRowStart + 1)

.Cells(mpNextLine, mpNextCol).Value = mpCell.Value
mpNextCol = mpNextCol + 1
Next mpCell
mpNextLine = mpNextLine + 1
Next mpCol

End With
mpNewWB.Close
mpFile = Dir

Loop[/vba]
Thanks again, the support here is awesome

Chris