Need a Loop for this code Sub EstBatch ( ) 'Declared variables Dim inputSheet As Worksheet Dim outputSheet As Worksheet Dim PPBR As Double Dim EHP As Double ' Set references to input and output sheets Set inputSheet Sheets ( Batch Input ) Set outputSheet Sheets ( Batch Output ) ' Get values from user input PPBR Sheets ( User Form ) Range ( C 2 2 ) Value EHP Sheets ( User Form ) Range ( C 2 3 ) Value 'Copy info from Batch Input to User Form Sheets ( User Form ) Range ( C 6 ) Value inputSheet Range ( A 1 ) Value Sheets ( User Form ) Range ( C 7 ) Value inputSheet Range ( B 1 ) Value Sheets ( User Form ) Range ( C 9 ) Value inputSheet Range ( A 2 ) Value Sheets ( User Form ) Range ( C 1 0 ) Value inputSheet Range ( A 3 ) Value ' Run the single estimate subroutine EstSingle ' Copy results to Batch Output tab outputSheet Range ( A 2 ) Sheets ( User Form ) Range ( C 6 ) outputSheet Range ( B 2 ) Sheets ( User Form ) Range ( C 7 ) outputSheet Range ( C 2 ) Sheets ( User Form ) Range ( C 9 ) outputSheet Range ( D 2 ) Sheets ( User Form ) Range ( C 1 0 ) outputSheet Range ( E 2 ) Sheets ( User Form ) Range ( C 2 2 ) outputSheet Range ( F 2 ) Sheets ( User Form ) Range ( C 2 3 ) outputSheet Range ( G 2 ) Sheets ( User Form ) Range ( C 1 3 ) outputSheet Range ( H 2 ) Sheets ( User Form ) Range ( C 1 4 ) outputSheet Range ( I 2 ) Sheets ( User Form ) Range ( C 1 5 ) outputSheet Range ( J 2 ) Sheets ( User Form ) Range ( C 1 6 ) outputSheet Range ( K 2 ) Sheets ( User Form ) Range ( C 1 7 ) outputSheet Range ( L 2 ) Sheets ( User Form ) Range ( C 1 8 ) 'Copy info from Batch Input to User Form Sheets ( User Form ) Range ( C 6 ) Value inputSheet Range ( A 6 ) Value Sheets ( User Form ) Range ( C 7 ) Value inputSheet Range ( B 6 ) Value Sheets ( User Form ) Range ( C 9 ) Value inputSheet Range ( A 7 ) Value Sheets ( User Form ) Range ( C 1 0 ) Value inputSheet Range ( A 8 ) Value ' Run the single estimate subroutine EstSingle ' Copy results to Batch Output tab outputSheet Range ( A 3 ) Sheets ( User Form ) Range ( C 6 ) outputSheet Range ( B 3 ) Sheets ( User Form ) Range ( C 7 ) outputSheet Range ( C 3 ) Sheets ( User Form ) Range ( C 9 ) outputSheet Range ( D 3 ) Sheets ( User Form ) Range ( C 1 0 ) outputSheet Range ( E 3 ) Sheets ( User Form ) Range ( C 2 2 ) outputSheet Range ( F 3 ) Sheets ( User Form ) Range ( C 2 3 ) outputSheet Range ( G 3 ) Sheets ( User Form ) Range ( C 1 3 ) outputSheet Range ( H 3 ) Sheets ( User Form ) Range ( C 1 4 ) outputSheet Range ( I 3 ) Sheets ( User Form ) Range ( C 1 5 ) outputSheet Range ( J 3 ) Sheets ( User Form ) Range ( C 1 6 ) outputSheet Range ( K 3 ) Sheets ( User Form ) Range ( C 1 7 ) outputSheet Range ( L 3 ) Sheets ( User Form ) Range ( C 1 8 ) 'Copy info from Batch Input to User Form Sheets ( User Form ) Range ( C 6 ) Value inputSheet Range ( A 1 0 ) Value Sheets ( User Form ) Range ( C 7 ) Value inputSheet Range ( B 1 0 ) Value Sheets ( User Form ) Range ( C 9 ) Value inputSheet Range ( A 1 1 ) Value Sheets ( User Form ) Range ( C 1 0 ) Value inputSheet Range ( A 1 2 ) Value ' Run the single estimate subroutine EstSingle ' Copy results to Batch Output tab outputSheet Range ( A 4 ) Sheets ( User Form ) Range ( C 6 ) outputSheet Range ( B 4 ) Sheets ( User Form ) Range ( C 7 ) outputSheet Range ( C 4 ) Sheets ( User Form ) Range ( C 9 ) outputSheet Range ( D 4 ) Sheets ( User Form ) Range ( C 1 0 ) outputSheet Range ( E 4 ) Sheets ( User Form ) Range ( C 2 2 ) outputSheet Range ( F 4 ) Sheets ( User Form ) Range ( C 2 3 ) outputSheet Range ( G 4 ) Sheets ( User Form ) Range ( C 1 3 ) outputSheet Range ( H 4 ) Sheets ( User Form ) Range ( C 1 4 ) outputSheet Range ( I 4 ) Sheets ( User Form ) Range ( C 1 5 ) outputSheet Range ( J 4 ) Sheets ( User Form ) Range ( C 1 6 ) outputSheet Range ( K 4 ) Sheets ( User Form ) Range ( C 1 7 ) outputSheet Range ( L 4 ) Sheets ( User Form ) Range ( C 1 8 ) 'Copy info from Batch Input to User Form Sheets ( User Form ) Range ( C 6 ) Value inputSheet Range ( A 1 6 ) Value Sheets ( User Form ) Range ( C 7 ) Value inputSheet Range ( B 1 6 ) Value Sheets ( User Form ) Range ( C 9 ) Value inputSheet Range ( A 1 7 ) Value Sheets ( User Form ) Range ( C 1 0 ) Value inputSheet Range ( A 1 8 ) Value ' Run the single estimate subroutine EstSingle ' My Code is correct however I need to do a loop with the information I have and I don't know how Could someone help me create a loop for the part in EstBatch ( ) so my code is not so long Its gets the info from the worksheet and the worksheets skips some lines before getting to the next information as you will see from my code Its the VBA 1 3 5 Project but I dont know how to loop my code Sub EstBatch ( ) 'Declared variables Dim inputSheet As Worksheet Dim outputSheet As Worksheet Dim PPBR As Double Dim EHP As Double ' Set references to input and output sheets Set inputSheet Sheets ( Batch Input ) Set outputSheet Sheets ( Batch Output ) ' Get values from user input PPBR Sheets ( User Form ) Range ( C 2 2 ) Value EHP Sheets ( User Form ) Range ( C 2 3 ) Value 'Copy info from Batch Input to User Form Sheets ( User Form ) Range ( C 6 ) Value inputSheet Range ( A 1 ) Value Sheets ( User Form ) Range ( C 7 ) Value inputSheet Range ( B 1 ) Value Sheets ( User Form ) Range ( C 9 ) Value inputSheet Range ( A 2 ) Value Sheets ( User Form ) Range ( C 1 0 ) Value inputSheet Range ( A 3 ) Value ' Run the single estimate subroutine EstSingle ' Copy results to Batch Output tab outputSheet Range ( A 2

The Answer is in the image, click to view ...

Answered step by step

Verified Expert Solution

Link Copied!

Question

1 Approved Answer

Posted on Sep 22, 2024

Need a Loop for this code. Sub EstBatch ( ) 'Declared variables Dim inputSheet As Worksheet Dim outputSheet As Worksheet Dim PPBR As Double Dim

Need a Loop for this code.

Sub EstBatch

()

'Declared variables

Dim inputSheet As Worksheet

Dim outputSheet As Worksheet

Dim PPBR As Double

Dim EHP As Double

'

Set references to input and output sheets

Set inputSheet

=

Sheets

("

Batch Input"

)

Set outputSheet

=

Sheets

("

Batch Output"

)

'

Get values from user input

PPBR

=

Sheets

("

User Form"

) .

Range

("

22 ") .

Value

EHP

=

Sheets

("

User Form"

) .

Range

("

23 ") .

Value

'Copy info from Batch Input to User Form

Sheets

("

User Form"

) .

Range

("

6 ") .

Value

=

inputSheet.Range

("

1 ") .

Value

Sheets

("

User Form"

) .

Range

("

7 ") .

Value

=

inputSheet.Range

("

1 ") .

Value

Sheets

("

User Form"

) .

Range

("

9 ") .

Value

=

inputSheet.Range

("

2 ") .

Value

Sheets

("

User Form"

) .

Range

("

10 ") .

Value

=

inputSheet.Range

("

3 ") .

Value

'

Run the single estimate subroutine

EstSingle

'

Copy results to Batch Output tab

outputSheet.Range

("

2 ") =

Sheets

("

User Form"

) .

Range

("

6 ")

outputSheet.Range

("

2 ") =

Sheets

("

User Form"

) .

Range

("

7 ")

outputSheet.Range

("

2 ") =

Sheets

("

User Form"

) .

Range

("

9 ")

outputSheet.Range

("

2 ") =

Sheets

("

User Form"

) .

Range

("

10 ")

outputSheet.Range

("

2 ") =

Sheets

("

User Form"

) .

Range

("

22 ")

outputSheet.Range

("

2 ") =

Sheets

("

User Form"

) .

Range

("

23 ")

outputSheet.Range

("

2 ") =

Sheets

("

User Form"

) .

Range

("

13 ")

outputSheet.Range

("

2 ") =

Sheets

("

User Form"

) .

Range

("

14 ")

outputSheet.Range

("

2 ") =

Sheets

("

User Form"

) .

Range

("

15 ")

outputSheet.Range

("

2 ") =

Sheets

("

User Form"

) .

Range

("

16 ")

outputSheet.Range

("

2 ") =

Sheets

("

User Form"

) .

Range

("

17 ")

outputSheet.Range

("

2 ") =

Sheets

("

User Form"

) .

Range

("

18 ")

'Copy info from Batch Input to User Form

Sheets

("

User Form"

) .

Range

("

6 ") .

Value

=

inputSheet.Range

("

6 ") .

Value

Sheets

("

User Form"

) .

Range

("

7 ") .

Value

=

inputSheet.Range

("

6 ") .

Value

Sheets

("

User Form"

) .

Range

("

9 ") .

Value

=

inputSheet.Range

("

7 ") .

Value

Sheets

("

User Form"

) .

Range

("

10 ") .

Value

=

inputSheet.Range

("

8 ") .

Value

'

Run the single estimate subroutine

EstSingle

'

Copy results to Batch Output tab

outputSheet.Range

("

3 ") =

Sheets

("

User Form"

) .

Range

("

6 ")

outputSheet.Range

("

3 ") =

Sheets

("

User Form"

) .

Range

("

7 ")

outputSheet.Range

("

3 ") =

Sheets

("

User Form"

) .

Range

("

9 ")

outputSheet.Range

("

3 ") =

Sheets

("

User Form"

) .

Range

("

10 ")

outputSheet.Range

("

3 ") =

Sheets

("

User Form"

) .

Range

("

22 ")

outputSheet.Range

("

3 ") =

Sheets

("

User Form"

) .

Range

("

23 ")

outputSheet.Range

("

3 ") =

Sheets

("

User Form"

) .

Range

("

13 ")

outputSheet.Range

("

3 ") =

Sheets

("

User Form"

) .

Range

("

14 ")

outputSheet.Range

("

3 ") =

Sheets

("

User Form"

) .

Range

("

15 ")

outputSheet.Range

("

3 ") =

Sheets

("

User Form"

) .

Range

("

16 ")

outputSheet.Range

("

3 ") =

Sheets

("

User Form"

) .

Range

("

17 ")

outputSheet.Range

("

3 ") =

Sheets

("

User Form"

) .

Range

("

18 ")

'Copy info from Batch Input to User Form

Sheets

("

User Form"

) .

Range

("

6 ") .

Value

=

inputSheet.Range

("

10 ") .

Value

Sheets

("

User Form"

) .

Range

("

7 ") .

Value

=

inputSheet.Range

("

10 ") .

Value

Sheets

("

User Form"

) .

Range

("

9 ") .

Value

=

inputSheet.Range

("

11 ") .

Value

Sheets

("

User Form"

) .

Range

("

10 ") .

Value

=

inputSheet.Range

("

12 ") .

Value

'

Run the single estimate subroutine

EstSingle

'

Copy results to Batch Output tab

outputSheet.Range

("

4 ") =

Sheets

("

User Form"

) .

Range

("

6 ")

outputSheet.Range

("

4 ") =

Sheets

("

User Form"

) .

Range

("

7 ")

outputSheet.Range

("

4 ") =

Sheets

("

User Form"

) .

Range

("

9 ")

outputSheet.Range

("

4 ") =

Sheets

("

User Form"

) .

Range

("

10 ")

outputSheet.Range

("

4 ") =

Sheets

("

User Form"

) .

Range

("

22 ")

outputSheet.Range

("

4 ") =

Sheets

("

User Form"

) .

Range

("

23 ")

outputSheet.Range

("

4 ") =

Sheets

("

User Form"

) .

Range

("

13 ")

outputSheet.Range

("

4 ") =

Sheets

("

User Form"

) .

Range

("

14 ")

outputSheet.Range

("

4 ") =

Sheets

("

User Form"

) .

Range

("

15 ")

outputSheet.Range

("

4 ") =

Sheets

("

User Form"

) .

Range

("

16 ")

outputSheet.Range

("

4 ") =

Sheets

("

User Form"

) .

Range

("

17 ")

outputSheet.Range

("

4 ") =

Sheets

("

User Form"

) .

Range

("

18 ")

'Copy info from Batch Input to User Form

Sheets

("

User Form"

) .

Range

("

6 ") .

Value

=

inputSheet.Range

("

16 ") .

Value

Sheets

("

User Form"

) .

Range

("

7 ") .

Value

=

inputSheet.Range

("

16 ") .

Value

Sheets

("

User Form"

) .

Range

("

9 ") .

Value

=

inputSheet.Range

("

17 ") .

Value

Sheets

("

User Form"

) .

Range

("

10 ") .

Value

=

inputSheet.Range

("

18 ") .

Value

'

Run the single estimate subroutine

EstSingle

'

My Code is correct however I need to do a loop with the information I have and I don't know how. Could someone help me create a loop for the part in EstBatch

()

so my code is not so long. Its gets the info from the worksheet and the worksheets skips some lines before getting to the next information as you will see from my code. Its the VBA

13.5

Project but I dont know how to loop my code.

Sub EstBatch

()

'Declared variables

Dim inputSheet As Worksheet

Dim outputSheet As Worksheet

Dim PPBR As Double

Dim EHP As Double

'

Set references to input and output sheets

Set inputSheet

=

Sheets

("

Batch Input"

)

Set outputSheet

=

Sheets

("

Batch Output"

)

'

Get values from user input

PPBR

=

Sheets

("

User Form"

) .

Range

("

22 ") .

Value

EHP

=

Sheets

("

User Form"

) .

Range

("

23 ") .

Value

'Copy info from Batch Input to User Form

Sheets

("

User Form"

) .

Range

("

6 ") .

Value

=

inputSheet.Range

("

1 ") .

Value

Sheets

("

User Form"

) .

Range

("

7 ") .

Value

=

inputSheet.Range

("

1 ") .

Value

Sheets

("

User Form"

) .

Range

("

9 ") .

Value

=

inputSheet.Range

("

2 ") .

Value

Sheets

("

User Form"

) .

Range

("

10 ") .

Value

=

inputSheet.Range

("

3 ") .

Value

'

Run the single estimate subroutine

EstSingle

'

Copy results to Batch Output tab

outputSheet.Range

("

2 " Step by Step Solution There are 3 Steps involved in it Step: 1 Get Instant Access to Expert-Tailored Solutions See step-by-step solutions with expert insights and AI powered tools for academic success View Solution Step: 2 Sign Up to view Step: 3 Sign Up to viewAce Your Homework with AI Get the answers you need in no time with our AI-driven, step-by-step assistance Get Started Recommended Textbook for Databases Theory And Applications 33rd Australasian Database Conference Adc 2022 Sydney Nsw Australia September 2 4 2022 Proceedings Lncs 13459 Authors: Wen Hua ,Hua Wang ,Lei Li 1st Edition 3031155114, 978-3031155116 More BooksStudents also viewed these Databases questions Question =+54-1 Identify the physical changes that occur during middle and late adulthood. Answered: 1 week ago Question ★ ★ ★ ★ ★ Determine the error for each of the following forecasts. Compute MAD andMSE. Period Vale Forecast Error 202 191 173 169 202 192 181 174 172 174 179 189 198 211 4 175 182 196 204 219 227 10 Answered: 1 week ago Question ★ ★ ★ ★ ★ Calculate the pH of a 1.0MNH3 solution. Kb for NH3 is 1.8105 Answered: 1 week ago Question ★ ★ ★ ★ ★ Imperial Jewelers manufactures and sells a gold bracelet for $404.00. The company's accounting system says that the unit product cost for this bracelet is $272.00 as shown below: Direct materials... Answered: 1 week ago Question ★ ★ ★ ★ ★ K When firms purchase services from outside suppliers that it used to obtain internally, what is the firm performing? A. Rare vertical integration B. Outsourcing OC. Forward vertical integration D.... Answered: 1 week ago Question ★ ★ ★ ★ ★ Benefit communication is linked to employee----------- associated with benefits. A) Citinzenship B) Creativity C) Satisfaction D) Valence E) compliance PLEASE DO FAST AND CORRECT I WILL RATE ****NEED... Answered: 1 week ago Question ★ ★ ★ ★ ★ True or/ False Evaluations for orders under MAS contracts require the formation of a Source Selection Evaluation Board. Answered: 1 week ago Question ★ ★ ★ ★ ★ Many population projection models assume a constant population growth rate throughout the projection period. What are the potential problems with this assumption? Answered: 1 week ago Question ★ ★ ★ ★ ★ EA3. LO 4.2 Burnham Industries incurs the following costs for the month: Direct materials Direct labor Factory depreciation expense Factory utilities expense CEO's salary A. What is the prime cost?... Answered: 1 week ago Question ★ ★ ★ ★ ★ 4. The next time you read somethinga magazine article, a political blog, a work of nonfiction, a chapter in a textbooktake time to think about the research presented in it. What kinds of research did... Answered: 1 week ago Question ★ ★ ★ ★ ★ 2 Was Viswanathans apology statement appropriate? What kinds of steps could Tivya take to address her mistake? Should she tell her instructor? Should you? Answered: 1 week ago Question ★ ★ ★ ★ ★ 2. Tune in to a few news punditsfor example, Bill OReilly, Rachel Maddow, Randi Rhodes, or Rush Limbaughon the radio or on television. Listen carefully to what they say, and consider how they back up... Answered: 1 week agoPrevious Question Next QuestionView Answer in SolutionInn App Download on the App Store Continue with the mobile websiteServices Sitemap Fun Definitions Become Tutor Study Help Categories Recent Questions Expert Questions Company Info Security Copyrights Privacy Policy Terms & Conditions SolutionInn Fee Scholarship Get In Touch About Us Contact Us Career Jobs FAQ Campus Ambassador Secure Payment Download Our App © 2024 SolutionInn. All Rights Reservedwindow.addEventListener("load",function(){jQuery(document).ready(function(t){t.ajax({type:"POST",url:"/",data:{trackUserActivity:!0,reqUri:document.URL,referer:document.referrer},success:function(t){}})})},!1),window.addEventListener("load",function(){jQuery(document).ready(function(t){t.ajax({type:"POST",url:"/",data:{insertCrawler:!0,reqUri:document.URL,parseTime:"0.056",queryTime:"0.01654768548584",queryCount:"30"},success:function(t){}})})},!1),window.addEventListener("load",function(){jQuery(document).ready(function(){function t(t="",n=!1){var i="itms-apps://itunes.apple.com/app/id6462455425",e="openApp://action?"+t;isAndroid()?(setTimeout(function(){return window.location="market://details?id=com.solutioninn.studyhelp",!1},25),window.location=e):isIOS()?(setTimeout(function(){return window.location=i,!1},25),window.location=e):(i="https://apps.apple.com/in/app/id6462455425",n&&(i="https://play.google.com/store/apps/details?id=com.solutioninn.studyhelp"),window.open("about:blank","_blank").location.href=i)}jQuery("#appModal").modal("show"),jQuery(".download-app-btn").click(function(){t(jQuery(this).attr("question_open_url"))}),jQuery(".redirection").click(function(){var n=jQuery(this).attr("question_open_url"),i=jQuery(this).attr("data-id");void 0!=n?1==i?t(n,!0):t(n,!1):1==i?t("",!0):t("",!1)}),jQuery(".app-notification-close").click(function(){jQuery(".app-notification-section").css("visibility","hidden");var t=new FormData;t.append("hide_notification",!0),jQuery.ajax({type:"POST",url:"/",data:t,cache:!1,contentType:!1,processData:!1,beforeSend:function(){},success:function(t){location.reload()}})})})},!1);