Increasing value in cell and copying results into list a fixed amount of times (VBA issue)

[jspedt]

Hi everyone,

I have looked at the threads and cannot seem to find an answer to my issue despite it likely being a very simple code.

Bear with me for potential clarification, as I have not used VBA at all.

What I am trying to do (I have attached a picture for clarification):
The starting variable is K27. I want to change this in 50 increments and copy and paste the changes in values accordingly.

So starting at a 100 I want to:
K27 Input 100
Copy O36, paste into F48
Copy P47, paste into G48
Copy K27, paste into B48

Return and increase K27 to 150 (i.e. 100+50)
Copy O36, paste into F49 (now moved down 1 row)
Copy P47, paste into G49
Copy K27, paste into B49

And then run this loop x amount of times so I can adjust how much I want to increase K27 with.

The intend is to have a list so that I can see how the K27 input impacts the overall output.

I really hope you can help, as it would save a lot of manual work.

Thanks in advance

 

[Zot]

You should have used XL2BB to select the range of your excel to be copied and paste. Otherwise it will be time consuming to recreate your sheet and test the code. Furthermore, we cannot see any formula to see how the numbers come about.

You said Copy P47, paste into G48. Is it P49?

Anyway, are you familiar with ActiveX button, TextBox etc. and how to attach VBA code to it?

 

[Zot]

Here is the code which I think should work. Not able to fully test.

Here is how it is suppose to run as I tested it
1) Create an ActiveX TextBox on the sheet.
2) Create a Command button, label it as OK and attach the code to it to run upon click

In the TextBox, just key in how many iteration you like. Then click OK. If you do not erase the result, it will keep adding to the list.

Let me know if you encounter problem.

Private Sub CommandButton1_Click()

If TextBox1 = "" Then Exit Sub
rowB = 0

For i = 1 To TextBox1.Value
    If Len(Range("B48")) = 0 Then
        Range("K27") = 100
        Range("B48") = Range("K27")
        Range("F48") = Range("O36")
        Range("G48") = Range("P49")
    Else
        If rowB = 0 Then rowB = Range("B" & Rows.Count).End(xlUp).Row
        rowB = rowB + 1
        Range("K27") = 100 + (i * 50)
        Range("B" & rowB) = Range("K27")
        Range("F" & rowB) = Range("O36")
        Range("G" & rowB) = Range("P49")
    End If
Next

End Sub

 

[jspedt]

Hi Zot,

Thank you for the response.

Sorry for the lack of information, but regardless, the code worked perfectly - thanks a lot!

One other thing, would you know how I could edit the code to paste the values as percentages? Naturally, it is easy to convert manually, but it would be neat to have the code doing that automatically.

Again, thanks for the response!

 

[Zot]

Hi Zot,

Thank you for the response.

Sorry for the lack of information, but regardless, the code worked perfectly - thanks a lot!

One other thing, would you know how I could edit the code to paste the values as percentages? Naturally, it is easy to convert manually, but it would be neat to have the code doing that automatically.

Again, thanks for the response!

You need to change at two places
1) Range("G48") = Range("P49")
2) Range("G" & rowB) = Range("P49")

= Range("P49") change to = Format(Range("P49"), "Percent")