VERY Long Loop - Looping across "call" function?

J

Jack1381

New Member
Joined
Dec 13, 2018
Messages
1
Hi all,

Hoping for some assistance here. Attempting to create a macro which takes data from several points on one line and populates a different spreadsheet with the relevant data, then goes to the next line and repeats. I've managed to get it working as intended, the only problem being... it's too long for what I need.

There will be 17 properties data which needs populated (across 17 worksheets on one .xlsx file) - the macro I've pasted below is how I've got it to work, but I need to copy and paste this 17 times in one loop. I got 9 hotels in then it said Procedure Too Large.

Just for more clarification - there are different cells relating to different properties all on one line. The macro is taking cells from across that line, populating a different spreadsheet, then it would move to Worksheet 2 to do the exact same, and repeat until Worksheet 17, then it will end that first loop and move to the next line. The cell references are not sequential - i.e, Hotel 1 is not (cell one), (cell 18), (cell 35) etc. One hotel's data could be on A, then X, then AZ, or other random cells. That is why all the references on the macro below are a bit all over the place.

Appreciate any suggestions on how I can get the below macro to work 17 times then looping to the next row. I believe splitting it up and calling other procedures may be an option? But this doesn’t seem to work when I try and loop on a “Master” procedure.

Broadly, you can ignore exactly what the macro is doing, it’s more just getting it to do the exact same thing 17 times then

Macro below:

------
Code: 
[FONT=Calibri]'Main Variables for Workbooks/Worksheets[/FONT]
[FONT=Calibri]Dim wsm As Workbook[/FONT]
[FONT=Calibri]Dim MS1 As Worksheet[/FONT]
[FONT=Calibri]Dim GroupName As String, fileStart As String[/FONT]
[FONT=Calibri]Dim lr As Integer[/FONT]
[FONT=Calibri]Sub Main()[/FONT]


[FONT=Calibri]Sub hotels1to9()[/FONT]
[FONT=Calibri]Application.DisplayAlerts = False[/FONT]
[FONT=Calibri]Application.EnableEvents = False[/FONT]

[FONT=Calibri]Set MS1 = Sheets("Master Database")[/FONT]

[FONT=Calibri]For i = 3 To lr[/FONT]

[FONT=Calibri]Set wb = Workbooks.Open("[I]filepath to form to fill[/I]")[/FONT]
[FONT=Calibri]Set wbs = wb.Sheets("Hotel 1")[/FONT]


[FONT=Calibri]'-----------------------------<wbr>------------- Hotel 1 ------------------------------<wbr>------------[/FONT]

[FONT=Calibri]'Essential Hotel Information[/FONT]
[FONT=Calibri]    wbs.Range("F14").Value = MS1.Cells(i, 148).Value     'Hotel Name[/FONT]
[FONT=Calibri]    wbs.Range("F16").Value = MS1.Cells(i, 109).Value     'Physical Address[/FONT]
[FONT=Calibri]    wbs.Range("F18").Value = MS1.Cells(i, 152).Value     'Open Date[/FONT]
[FONT=Calibri]    wbs.Range("J18").Value = MS1.Cells(i, 150).Value     'Website[/FONT]
[FONT=Calibri]    wbs.Range("F20").Value = MS1.Cells(i, 149).Value     'Telephone[/FONT]
[FONT=Calibri]    wbs.Range("L20").Value = MS1.Cells(i, 126).Value     'Country[/FONT]
[FONT=Calibri]    wbs.Range("F22").Value = MS1.Cells(i, 24).Value      'Hotel ID[/FONT]
[FONT=Calibri]    wbs.Range("F24").Value = MS1.Cells(i, 151).Value     'Management Company[/FONT]

[FONT=Calibri]'Setting Number of Rooms[/FONT]
[FONT=Calibri]    If MS1.Cells(i, 153).Value = "Yes" Then[/FONT]
[FONT=Calibri]        wbs.Range("L22").Value = MS1.Cells(i, 92)[/FONT]
[FONT=Calibri]            Else[/FONT]
[FONT=Calibri]        wbs.Range("L22").Value = MS1.Cells(i, 154)[/FONT]
[FONT=Calibri]    End If[/FONT]

[FONT=Calibri]'Setting PMS Provider[/FONT]
[FONT=Calibri]       'Are All Hotels using Same PMS?[/FONT]
[FONT=Calibri]    If MS1.Cells(i, 318).Value = "Yes" And MS1.Cells(i, 319).Value = "Other" Then        'If Yes:[/FONT]
[FONT=Calibri]            wbs.Range("L26").Value = MS1.Cells(i, 320)[/FONT]
[FONT=Calibri]    ElseIf MS1.Cells(i, 318).Value = "Yes" And MS1.Cells(i, 319).Value <> "Other" Then[/FONT]
[FONT=Calibri]            wbs.Range("L26").Value = MS1.Cells(i, 319)[/FONT]
[FONT=Calibri]    ElseIf MS1.Cells(i, 318).Value = "No" And MS1.Cells(i, 325).Value = "Other" Then     'If No:[/FONT]
[FONT=Calibri]            wbs.Range("L26").Value = MS1.Cells(i, 376)[/FONT]
[FONT=Calibri]    ElseIf MS1.Cells(i, 318).Value = "No" And MS1.Cells(i, 325).Value <> "Other" Then[/FONT]
[FONT=Calibri]            wbs.Range("L26").Value = MS1.Cells(i, 325)[/FONT]
[FONT=Calibri]    End If[/FONT]

[FONT=Calibri]'Setting RMS Provider[/FONT]
[FONT=Calibri]        'Are All Hotels using Same RMS?[/FONT]
[FONT=Calibri]    If MS1.Cells(i, 318).Value = "Yes" And MS1.Cells(i, 321).Value = "Other" Then       'If Yes:[/FONT]
[FONT=Calibri]            wbs.Range("F26").<wbr>Value = MS1.Cells(i, 322)[/FONT]
[FONT=Calibri]    ElseIf MS1.Cells(i, 318).Value = "Yes" And MS1.Cells(i, 321).Value <> "Other" Then[/FONT]
[FONT=Calibri]            wbs.Range("F26").Value = MS1.Cells(i, 321)[/FONT]
[FONT=Calibri]    ElseIf MS1.Cells(i, 318).Value = "No" And MS1.Cells(i, 342).Value = "Other" Then    'If No:[/FONT]
[FONT=Calibri]            wbs.Range("F26").Value = MS1.Cells(i, 377)[/FONT]
[FONT=Calibri]    ElseIf MS1.Cells(i, 318).Value = "No" And MS1.Cells(i, 342).Value <> "Other" Then[/FONT]
[FONT=Calibri]            wbs.Range("F26").Value = MS1.Cells(i, 342)[/FONT]
[FONT=Calibri]    End If[/FONT]

[FONT=Calibri]'Setting Channel Manager[/FONT]
[FONT=Calibri]        'Are All Hotels using Same CMS?[/FONT]
[FONT=Calibri]    If MS1.Cells(i, 318).Value = "Yes" And MS1.Cells(i, 323).Value = "Other" Then       'If Yes:[/FONT]
[FONT=Calibri]            wbs.Range("L24").Value = MS1.Cells(i, 324)[/FONT]
[FONT=Calibri]    ElseIf MS1.Cells(i, 318).Value = "Yes" And MS1.Cells(i, 323).Value <> "Other" Then[/FONT]
[FONT=Calibri]            wbs.Range("L24").Value = MS1.Cells(i, 323)[/FONT]
[FONT=Calibri]    ElseIf MS1.Cells(i, 318).Value = "No" And MS1.Cells(i, 359).Value = "Other" Then    'If No:[/FONT]
[FONT=Calibri]            wbs.Range("L24").Value = MS1.Cells(i, 378)[/FONT]
[FONT=Calibri]    ElseIf MS1.Cells(i, 318).Value = "No" And MS1.Cells(i, 359).Value <> "Other" Then[/FONT]
[FONT=Calibri]            wbs.Range("L24").Value = MS1.Cells(i, 359)[/FONT]
[FONT=Calibri]    End If[/FONT]

[FONT=Calibri]'Subscription Selection[/FONT]
[FONT=Calibri]    'Free Subscription - All Hotels on Same Sub[/FONT]
[FONT=Calibri]    If MS1.Cells(i, 379).Value = "Yes" And MS1.Cells(i, 380).Value = "Free" Then[/FONT]
[FONT=Calibri]        wbs.Range("G30").Value = "Free Reporting"[/FONT]

[FONT=Calibri]    'Paid Subscription - All Hotels on Same Sub - EUR - Discounted Participant[/FONT]
[FONT=Calibri]    ElseIf MS1.Cells(i, 144).Value = "€" And MS1.Cells(i, 379) = "Yes" And MS1.Cells(i, 41).Value = "Y" Then[/FONT]
[FONT=Calibri]        wbs.Range("G30").Value = "Paid Reporting"[/FONT]
[FONT=Calibri]        wbs.Range("H30").Value = "- EUR xxx"[/FONT]

[FONT=Calibri]    'Paid Subscription - All Hotels on Same Sub - EUR – Non Discounted Participant[/FONT]
[FONT=Calibri]    ElseIf MS1.Cells(i, 144).Value = "€" And MS1.Cells(i, 379) = "Yes" And MS1.Cells(i, 41).Value = "N" Then[/FONT]
[FONT=Calibri]        wbs.Range("G30").Value = " Paid Reporting"[/FONT]
[FONT=Calibri]        wbs.Range("H30").Value = "- EUR xxx"[/FONT]

[FONT=Calibri]    'Paid Subscription - All Hotels on Same Sub - GBP - Discounted Participant[/FONT]
[FONT=Calibri]    ElseIf MS1.Cells(i, 144).Value = "£" And MS1.Cells(i, 379) = "Yes" And MS1.Cells(i, 41).Value = "Y" Then[/FONT]
[FONT=Calibri]        wbs.Range("G30").Value = " Paid Reporting”[/FONT]
[FONT=Calibri]        wbs.Range("H30").Value = "- GBP xxx"[/FONT]

[FONT=Calibri]    'Paid Subscription - All Hotels on Same Sub - GBP – Non Discounted Participant[/FONT]
[FONT=Calibri]    ElseIf MS1.Cells(i, 144).Value = "£" And MS1.Cells(i, 379) = "Yes" And MS1.Cells(i, 41).Value = "N" Then[/FONT]
[FONT=Calibri]        wbs.Range("G30").Value = " Paid Reporting”[/FONT]
[FONT=Calibri]        wbs.Range("H30").Value = "- GBP xxx"[/FONT]

[FONT=Calibri]    'Paid Subscription - All Hotels on Same Sub - USD - Discounted Participant[/FONT]
[FONT=Calibri]    ElseIf MS1.Cells(i, 144).Value = "$" And MS1.Cells(i, 379) = "Yes" And MS1.Cells(i, 41).Value = "Y" Then[/FONT]
[FONT=Calibri]        wbs.Range("G30").Value = " Paid Reporting”[/FONT]
[FONT=Calibri]        wbs.Range("H30").Value = "- USD xxx"[/FONT]

[FONT=Calibri]    'Paid Subscription - All Hotels on Same Sub - USD - Non Discounted Participant[/FONT]
[FONT=Calibri]    ElseIf MS1.Cells(i, 144).Value = "$" And MS1.Cells(i, 379) = "Yes" And MS1.Cells(i, 41).Value = "N" Then[/FONT]
[FONT=Calibri]        wbs.Range("G30").Value = " Paid Reporting”[/FONT]
[FONT=Calibri]        wbs.Range("H30").Value = "- USD xxx"[/FONT]

[FONT=Calibri]    'Free Subscription - All Hotels NOT On Same Sub[/FONT]
[FONT=Calibri]    ElseIf MS1.Cells(i, 379).Value = "No" And MS1.Cells(i, 381).Value = "Free" Then[/FONT]
[FONT=Calibri]        wbs.Range("G30").Value = " Free Reporting "[/FONT]

[FONT=Calibri]    'Paid Subscription - All Hotels NOT On Same Sub - EUR - Discounted Participant[/FONT]
[FONT=Calibri]    ElseIf MS1.Cells(i, 144).Value = "€" And MS1.Cells(i, 379) = "No" And MS1.Cells(i, 41).Value = "Y" Then[/FONT]
[FONT=Calibri]        wbs.Range("G30").Value = " Paid Reporting”[/FONT]
[FONT=Calibri]        wbs.Range("H30").Value = "- EUR xxx"[/FONT]

[FONT=Calibri]    'Paid Subscription - All Hotels NOT On Same Sub - EUR - Non Discounted Participant[/FONT]
[FONT=Calibri]    ElseIf MS1.Cells(i, 144).Value = "€" And MS1.Cells(i, 379) = "No" And MS1.Cells(i, 41).Value = "N" Then[/FONT]
[FONT=Calibri]        wbs.Range("G30").Value = “Paid Reporting”[/FONT]
[FONT=Calibri]        wbs.Range("H30").Value = "- EUR xxx"[/FONT]

[FONT=Calibri]    'Paid Subscription - All Hotels NOT On Same Sub - GBP - Discounted Participant[/FONT]
[FONT=Calibri]    ElseIf MS1.Cells(i, 144).Value = "£" And MS1.Cells(i, 379) = "No" And MS1.Cells(i, 41).Value = "Y" Then[/FONT]
[FONT=Calibri]        wbs.Range("G30").Value = “Paid Reporting"[/FONT]
[FONT=Calibri]        wbs.Range("H30").Value = "- GBP xxx"[/FONT]

[FONT=Calibri]    'Paid Subscription - All Hotels NOT On Same Sub - GBP - Non Discounted Participant[/FONT]
[FONT=Calibri]   ElseIf MS1.Cells(i, 144).Value = "£" And MS1.Cells(i, 379) = "No" And MS1.Cells(i, 41).Value = "N" Then[/FONT]
[FONT=Calibri]        wbs.Range("G30").Value = " Paid Reporting”[/FONT]
[FONT=Calibri]        wbs.Range("H30").Value = "- GBP xxx"[/FONT]

[FONT=Calibri]    'Paid Subscription - All Hotels NOT On Same Sub - USD - Discounted Participant[/FONT]
[FONT=Calibri]    ElseIf MS1.Cells(i, 144).Value = "$" And MS1.Cells(i, 379) = "No" And MS1.Cells(i, 41).Value = "Y" Then[/FONT]
[FONT=Calibri]        wbs.Range("G30").Value = " Paid Reporting”[/FONT]
[FONT=Calibri]        wbs.Range("H30").Value = "- USD xxx"[/FONT]

[FONT=Calibri]    'Paid Subscription - All Hotels NOT On Same Sub - USD - Non Discounted Participant[/FONT]
[FONT=Calibri]    ElseIf MS1.Cells(i, 144).Value = "$" And MS1.Cells(i, 379) = "No" And MS1.Cells(i, 41).Value = "N" Then[/FONT]
[FONT=Calibri]        wbs.Range("G30").Value = " Paid Reporting”[/FONT]
[FONT=Calibri]        wbs.Range("H30").Value = "- USD xxx"[/FONT]
[FONT=Calibri]    End If[/FONT]

[FONT=Calibri]'Essential Contacts[/FONT]
[FONT=Calibri]            'Principal Contact Details[/FONT]
[FONT=Calibri]    'PC - All Hotels Have Same[/FONT]
[FONT=Calibri]    If MS1.Cells(i, 398).Value = "Yes" Then[/FONT]
[FONT=Calibri]        wbs.Range("G34").Value = MS1.Cells(i, 400)  'PC Name[/FONT]
[FONT=Calibri]        wbs.Range("G36").Value = MS1.Cells(i, 402)  'PC Job Title[/FONT]
[FONT=Calibri]        wbs.Range("G38").Value = MS1.Cells(i, 401)  'PC Email[/FONT]
[FONT=Calibri]        wbs.Range("G40").Value = MS1.Cells(i, 403)  'PC Telephone[/FONT]

[FONT=Calibri]    'PC - All Hotels NOT The Same[/FONT]
[FONT=Calibri]    ElseIf MS1.Cells(i, 398).Value = "No" Then[/FONT]
[FONT=Calibri]        wbs.Range("G34").Value = MS1.Cells(i, 408)  'PC Name[/FONT]
[FONT=Calibri]        wbs.Range("G36").Value = MS1.Cells(i, 410)  'PC Job Title[/FONT]
[FONT=Calibri]        wbs.Range("G38").Value = MS1.Cells(i, 409)  'PC Email[/FONT]
[FONT=Calibri]        wbs.Range("G40").Value = MS1.Cells(i, 411)  'PC Telephone[/FONT]
[FONT=Calibri]    End If[/FONT]

[FONT=Calibri]            'Invoicing Contact Details[/FONT]
[FONT=Calibri]    wbs.Range("G45").Value = MS1.Cells(i, 544)      'IC Name[/FONT]
[FONT=Calibri]    wbs.Range("G47").Value = MS1.Cells(i, 545)      'IC Job Title[/FONT]
[FONT=Calibri]    wbs.Range("G49").Value = MS1.Cells(i, 546)      'IC Email[/FONT]
[FONT=Calibri]    wbs.Range("G51").Value = MS1.Cells(i, 547)      'Legal Entity Name[/FONT]
[FONT=Calibri]    wbs.Range("G53").Value = MS1.Cells(i, 549)      'Billing Address[/FONT]
[FONT=Calibri]    wbs.Range("G55").Value = MS1.Cells(i, 548)      'VAT Number[/FONT]
[FONT=Calibri]    wbs.Range("G57").Value = MS1.Cells(i, 550)      'Other Details[/FONT]

[FONT=Calibri]'Data Input Contact Details[/FONT]
[FONT=Calibri]    'DC - All Hotels Have Same[/FONT]
[FONT=Calibri]    If MS1.Cells(i, 399).Value = "Yes" Then[/FONT]
[FONT=Calibri]        wbs.Range("G62").Value = MS1.Cells(i, 404)  'DC Name[/FONT]
[FONT=Calibri]        wbs.Range("G64").Value = MS1.Cells(i, 406)  'DC Job Title[/FONT]
[FONT=Calibri]        wbs.Range("G66").Value = MS1.Cells(i, 405)  'DC Email[/FONT]
[FONT=Calibri]        wbs.Range("G68").Value = MS1.Cells(i, 407)  'DC Telephone[/FONT]

[FONT=Calibri]    'DC - All Hotels NOT The Same[/FONT]
[FONT=Calibri]    ElseIf MS1.Cells(i, 399).Value = "No" Then[/FONT]
[FONT=Calibri]        wbs.Range("G62").Value = MS1.Cells(i, 476)  'DC Name[/FONT]
[FONT=Calibri]        wbs.Range("G64").Value = MS1.Cells(i, 478)  'DC Job Title[/FONT]
[FONT=Calibri]        wbs.Range("G66").Value = MS1.Cells(i, 477)  'DC Email[/FONT]
[FONT=Calibri]        wbs.Range("G68").Value = MS1.Cells(i, 479)  'DC Telephone[/FONT]
[FONT=Calibri]    End If[/FONT]

[FONT=Calibri]'Distribution List[/FONT]
[FONT=Calibri]    wbs.Range("G73").Value = MS1.Cells(i, 551)[/FONT]

[FONT=Calibri]'Terms & Conditions[/FONT]
[FONT=Calibri]    wbs.Range("B83").Value = MS1.Cells(i, 568)  'Name[/FONT]
[FONT=Calibri]    wbs.Range("J83").Value = MS1.Cells(i, 569)  'Email[/FONT]
[FONT=Calibri]    wbs.Range("J85").Value = MS1.Cells(i, 570)  'Date of Signature[/FONT]

[FONT=Calibri]'Internal Use Only[/FONT]
[FONT=Calibri]    wbs.Range("B92").Value = MS1.Cells(i, 155)  'Date of Room Stock Change[/FONT]
[FONT=Calibri]    wbs.Range("I92").Value = MS1.Cells(i, 156)  'Reason of Room Stock Change[/FONT]
[FONT=Calibri]    wbs.Range("B94").Value = MS1.Cells(i, 156)  'Additional Info re Room Stock Change[/FONT]

[FONT=Calibri]    'Moving to Next Hotel(Worksheet)[/FONT]
[FONT=Calibri]    Set wbs = wb.Sheets("Hotel 2")[/FONT]
 
Last edited by a moderator:

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
Sort by date Sort by votes
Code: 
For i = 3 To lr
U don't code for what "lr" is? You should be looping the hotels with the same code. U may need to re-work how your data is stored. U could load an array and use it for each hotel. U could always make 17 separate procedures… yuck. Good luck. Dave
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question
VBA to run code in different worksheets and run multiple calculations
Replies
1
Views
853
JimCorb
J

Forum statistics

Threads
1,215,182
Messages
6,123,517
Members
449,102
Latest member
admvlad

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top