none
For loop with counter RRS feed

  • Question

  • Very new to excel and am trying to make a code that will go count up and copy/paste below along with refer to other cells. This is the code I have so far. There are probably quite a few things wrong with the code so any help I can get will be great. Right now it is currently only populating 1's in the A column with nothing else.

    Sub RunSingle()
        
        Dim Start As Range
        Dim Ending As Range
        Set Start = Range("Start")
        Set Ending = Range("Stop")
        Dim i As Integer
        Dim cntr As Integer
        For i = Start To Ending
            Cells(i + 5, "A").Value = Sheets("Results").Range("a6")
           cntr = Sheets("inputs").Range("c2").Value
            BorrowerID = Sheets("Inputs").Range("C3").Value
            Balance = Sheets("Inputs").Range("C4").Value
            RepayOpt = Sheets("Inputs").Range("C8").Value
            Payment = Sheets("Amort Summary").Range("C10").Value
            Finance = Sheets("Amort Summary").Range("C11").Value
            TotalPaid = Sheets("Amort Summary").Range("C12").Value
            
         
        Range("Results").Calculate
        Range("Results").Offset(cntr, 0) = Range("Results").Value
        cntr = cntr + 1
            Next i
            
            
    End Sub

    Monday, July 1, 2019 6:26 PM

All replies

  • Can't say that I really understand what you are attempting to do but the following might help.

    Are the following variables supposed to be VBA variables or are you trying to use them as named ranges? If named ranges then the syntax is as follows:

        Range ("BorrowerID")
        Range ("Balance")
        Range ("RepayOpt")
        Range ("Payment")
        Range ("Finance")
        Range ("TotalPaid")

    You can't loop from the start of a range to the end of a range with numbers as you are attempting to do. You need to loop through either rows or columns so note the following loops trough rows that are specified after the range variables.

     Sub RunSingle_2()
        
        Dim Start As Range
        Dim Ending As Range
       
        Set Start = Range("Start")
        Set Ending = Range("Stop")
        Dim i As Integer
        Dim cntr As Integer
        For i = Start.Row To Ending.Row
            Cells(i + 5, "A").Value = Sheets("Results").Range("a6")
            cntr = Sheets("inputs").Range("c2").Value
            BorrowerID = Sheets("Inputs").Range("C3").Value
            Balance = Sheets("Inputs").Range("C4").Value
            RepayOpt = Sheets("Inputs").Range("C8").Value
            Payment = Sheets("Amort Summary").Range("C10").Value
            Finance = Sheets("Amort Summary").Range("C11").Value
            TotalPaid = Sheets("Amort Summary").Range("C12").Value
           
            Worksheets("Results").Calculate     'Worksheets NOT Range
           
            'Don't understand. Results appears to be a worksheet.
            'Can't offset a worksheet or get a value of a worksheet
            'Range("Results").Offset(cntr, 0) = Range("Results").Value
            cntr = cntr + 1
        Next i
            
     End Sub

    Suggestion: You will get faster answers at the following Microsoft Community Forum Site. Follow the links for Office -> Excel and then select your  Office Topic,  Office Sub-topic and Office Product from the DropDowns.

    Ask a question is at the top and when you ask a question then ensure you fill out the details at the bottom so the post displays your windows and excel versions.

    https://answers.microsoft.com/en-us/


    Regards, OssieMac

    Thursday, July 4, 2019 6:27 AM