Add 'static' value into For Loop

    I'm in need of some help! My goal is to create a price list, I have set values in columns A to E, my problem is that I have an extra cost to add should certain cells by added together. I cannot get this adder to be included, any help would be greatly appreciated.
    Below is the code (with reductions) and attached is the table I'm using, I'm hoping it's self explanatory.



    [VBA]
    Dim a As Long
    Dim b As Long
    Dim c As Long
    Dim d As Long
    Dim e As Long
    Dim i1 As Long
    Dim i2 As Long
    Dim i3 As Long
    Dim i4 As Long
    Dim i5 As Long
    Dim counter As Long


    counter = 1


    a = Range("A1").End(xlDown).Row
    b = Range("B1").End(xlDown).Row
    c = Range("C1").End(xlDown).Row
    d = Range("D1").End(xlDown).Row
    e = Range("E1").End(xlDown).Row


    For i1 = 2 To a
    For i2 = 2 To b
    For i3 = 2 To c
    For i4 = 2 To d
    For i5 = 2 To e
    'Added cost for C10
    If Cells(i2, 2) = Range("B3") And Cells(i3, 3) = Range("C10") + Range("R3") Then GoTo Nexti
    'Added cost for C11
    If Cells(i2, 2) = Range("B3") And Cells(i3, 3) = Range("C11") + Range("U3") Then GoTo Nexti
    'Added cost for C12
    If Cells(i2, 2) = Range("B3") And Cells(i3, 3) = Range("C12") + Range("X3") Then GoTo Nexti


    Cells(counter, 10) = Cells(i1, 1) + Cells(i2, 2) + Cells(i3, 3) + Cells(i4, 4) + Cells(i5, 5)
    counter = counter + 1
    Nexti:
    Next i5
    Next i4
    Next i3
    Next i2
    Next i1



    End Sub
    [/VBA]


    I have removed the repeated lines for adding R4 + B4 all the way to R40 + B40 and so on for the others. The only way I know how is to repeat the same line of code for each adder changing to cells each time. If there is a way to tidy this up I'd very much like to know about it.

    Your post does not comply with our Forum RULES. Use code tags around code.


    Posting code between

    Code

    tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.


    Click on Edit to open your thread, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found in our Forum Rules. Please take a few minutes and refresh yourself.


    This post should not receive any responses until the OP has complied with this request.

    I don't understand, the code has always been inside tags, I take it VBA tags don't do the same thing as CODE tags. I'm not able to edit this post.

    To clarify below is my complete code (hopefully this time the code tags do their job)...



    Attached is my worksheet with completed run through of the code. My issue is (for example), in cell J221 the result is $6.19 (A2($3.26)+B3($0.16)+C10($2.77)+D2($-)+E2($-)). What I want in J221 is in fact $6.60 which comes from (A2($3.26)+B3($0.16)+C10($2.77)+D2($-)+E2($-)+R3($0.41))
    My question is how can the code be written so that it includes value in the cell outside of the loop parameters for certain combinations of cells within the loop?


    A secondary question is how can I group together the cell ranges to reduce the length of the code?


    I sincerely hope this helps with clarification and is within the forum rules.

    I really need help with this!
    To the moderators please could you confirm whether or not this thread is now acceptable and can be replied to.
    To anyone who may have an answer please let me know if I have provided enough information.
    Thanks in advance.

    What exactly is the logic? For example, why are you looping through the cells in column B checking if they match cells in column B? Given that column B appears to be a list of unique values in ascending order, why do you need to check those values against each other?

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

    Quote from rory

    What exactly is the logic? For example, why are you looping through the cells in column B checking if they match cells in column B? Given that column B appears to be a list of unique values in ascending order, why do you need to check those values against each other?


    Rory,


    I have a list of part numbers that each need a separate price value, the part numbers were generated using the same code, the counter used & instead of +, other than that the same. For example the part number in cell J221 for this set was X07-007-501-101 which came from a break down of A=X07-, B=007, C=-501, D=-1 and E=01.
    The total number of part numbers produced for this X07 series was 9750, all of which need prices, some have consistent adders relating to specific areas of the part number, e.g if C=-555 in the PN the price adder would be C=$1.28 and this remains constant throughout. However, should C=-501 this, due to historic pricing, is a random addition in price. The only way I can see to generate these prices is to pull in the calculated adder that does not conform to a rule from outside of the loop.


    Does this make sense? I'm doing this project on the side and as I'm sure you can tell coding is not something I do very often!

    Not really, to be honest.


    I don't see the point of a test like this:


    Code
    If Cells(i2, 2) = Range("B3")


    which is only true when i2 = 3. It looks to me like the loop test only needs to be:


    Code
    If Cells(i3, 3) <> Range("C10") + Cells(i2, "R") _
    And Cells(i3, 3) <> Range("C11") + Cells(i2, "U") _
    And Cells(i3, 3) <> Range("C12") + Cells(i2, "X") Then
        Cells(counter, 10) = Cells(i1, 1) + Cells(i2, 2) + Cells(i3, 3) + Cells(i4, 4) + Cells(i5, 5)
        counter = counter + 1
End If

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

    Try this:

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login