VBA code for copying and pasting a value from a specific cell that is changing as the macro runs

Question

I'll try (as best I can) to explain the code I'm using

Essentially I have an excel which outputs a value to cell W151 based on a calculation that depends on cells in the range Q149:Q182.

The first step is to reset all the values in the range Q149:Q182 to their base values by copying and pasting from cells S149:S182.

Based on the base values for the formula, I copy and paste the value outputted to W151 into W99

Next, I change the value in Q149 to "2". This updates the calculation and hence the value in cell W151 which I then copy and paste into W100

Then I change Q150 to "2" and again copy the value from W151, this time into W101 and so on and so forth

My question is, is there a way of setting the cells that i'm changing as an array (picked by the user through a prompt), the output cell W151 as a variable (picked by the user through a prompt) and the destination for the copied values (i.e. currently cells W99:W101) as an array also picked by the user via a prompt. If not is there any way you can think of that might automated this process?

I appreciate that I might have done a poor job explaining what I'm trying to do so please feel free to ask for clarifications (although I warn you my VBA knowledge is very limited)

Many Thanks,

Thomas

Sub Example()

Range("S149:S182").Select
Selection.Copy
Range("Q149").Select
Selection.PasteSpecial Paste:=xlPasteValues
Range("W151").Select
Selection.Copy
Range("W99").Select
Selection.PasteSpecial Paste:=xlPasteValues
Range("Q149").Select
ActiveCell.FormulaR1C1 = "2"
Range("W151").Select
Selection.Copy
Range("W100").Select
Selection.PasteSpecial Paste:=xlPasteValues
Range("Q150").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "2"
Range("W151").Select
Selection.Copy
Range("W101").Select
Selection.PasteSpecial Paste:=xlPasteValues

End Sub

What's the calculation in `W151`? And this is done until all values in `Q149:Q182` are 2 and each calculated value has been pasted into `W99:W132`? — Darren Bartrup-Cook, Oct 31 '18 at 09:32
Your code is needlessly verbose and inefficient. For example, the first 4 lines of code could be replaced simply by `Range("S149:S182").Value = Range("Q149:Q182").Value`. You might benefit from reading [How to avoid using Select in Excel VBA](https://stackoverflow.com/q/10714251/4996248) — John Coleman, Oct 31 '18 at 09:40
The calculation into W151 is massively complicated so I don't think I'd be able to explain it properly but yes that's exactly it, I'm changing the values in Q149:Q182 one by one to 2 and copying and pasting the output for every step in cells W99;W132 — Thomas, Oct 31 '18 at 09:43
Could you give us the formula you're using? It might be massively complicated, but can read out of the formula what it's doing - someone on here might be able to speed it up or think of a better way of doing it. — Darren Bartrup-Cook, Oct 31 '18 at 09:49
The actual formula itself is just a simple average of two cells =AVERAGE(C183:D183) but C183 and D183 a linked up to an entire model that can't be explained without sending you the excel (which I can't do) — Thomas, Oct 31 '18 at 09:55

Error 1004 · Answer 1 · 2018-10-31T09:55:47.163

0

@Thomas first of all welcome!

Make the necessary changes (Sheet name or ranges) and try:

Sub Example()

    With (Sheet1) '<= Change Sheet Name if needed
        .Range("S149:S182").Copy .Range("Q149")
        .Range("W151").Copy .Range("W99")
        .Range("W151").Copy .Range("W100")
        .Range("W151").Copy .Range("W101")
        .Range("Q149").value="2"
        .Range("Q150").value = "2"
    End With

End Sub

edited Oct 31 '18 at 09:55

answered Oct 31 '18 at 09:36

Error 1004

7,877
3
23
46

That would copy the formula from `W151` as well. The OP just needs the value - I'm thinking either extend the formula down so it can be done in one hit, or pull all the values into a VBA array and perform the calculation there before putting it back on the sheet - mutliple copy/paste will be slowing the code down. Depends on the calculation though. – Darren Bartrup-Cook Oct 31 '18 at 09:46
Ideally the array version would be best, I just don't know how to do it – Thomas Oct 31 '18 at 09:54
If i could define Q149:Q182 as an array and then just cycle through each value in the array changing it to 2 and output the value of W151 into another array defined as W99:W132 that would be ideal – Thomas Oct 31 '18 at 10:02

score 0 · Answer 2 · answered Oct 31 '18 at 09:48

You have lot of unnecessary things in your code simply use inputbox to get the range and use it as required.

Sub Example()

Dim rng1 As Range
Dim rng2 As Range
Set rng1 = Application.InputBox("Select range1", Type:=8)
Set rng2 = Application.InputBox("Select range2", Type:=8)

With Sheets("Sheet1")
.Range("Q149:Q182").Value = rng1.Value
.Range("W99").Value = rng2.Value
.Range("Q149").FormulaR1C1 = "2"
.Range("W100").Value = rng2.Value
.Range("Q150").FormulaR1C1 = "2"
.Range("W101").Value = rng2.Value
End With

End Sub

Hi Imran, I tried that but the value being pasted in to W99 to W101 is the same (i.e. i think it's being overwritten and outputting the same W151 value into all three cells) — Thomas, Oct 31 '18 at 09:53

Darren Bartrup-Cook · Accepted Answer · 2018-10-31T11:28:58.923

Not quite the way I was hoping to do it as this still relies on putting values on the worksheet.

~~There's one step missing that I didn't understand:~~

Based on the base values for the formula, I copy and paste the value outputted to W151 into W99

This is done before you turn the first value to 2. So is it a case of the base average goes into W99, then you change the first value to 2 and that goes into W100. i.e. If you start with 34 values in column Q you'll end with 35 values copied to column W?

Sub Test()

    Dim CopyRng As Range
    Dim rCell As Range

    With ThisWorkbook.Worksheets("Sheet1")
        Set CopyRng = .Range("Q149:Q182")
        CopyRng.Value = .Range("S149:S182").Value

        .Range("W99").Value = .Range("W151").Value
        For Each rCell In CopyRng
            rCell.Value = 2
            'Q149 Offset by -49 rows and +6 columns = cell W100.
            rCell.Offset(-49, 6).Value = .Range("W151").Value
        Next rCell

    End With

End Sub

Edit:
To ask the user to make the selections you could use the following method.
One problem that hasn't been addressed in this code is if the user presses Cancel, but hopefully the link will point you in the right direction - I liked the answer given by @DirkReichel.

Sub Test()

    Dim CopyRng As Range
    Dim rCalculation As Range
    Dim rDestination As Range
    Dim rCell As Range

    'Creating the base values is a manual operation now.
    'CopyRng.Value = Sheet1.Range("S149:S182").Value

    Set CopyRng = Application.InputBox("Select range to be evaluated.", Type:=8) 'Q149:Q182

    'Calculation must be a single cell.
    Do
        Set rCalculation = Application.InputBox("Select cell containing calculation.", Type:=8) 'W151
    Loop While rCalculation.Cells.Count <> 1

    'First cell in destination must be a single cell.
    Do
        Set rDestination = Application.InputBox("Select first cell to be pasted to.", Type:=8) 'W99
    Loop While rDestination.Cells.Count <> 1

    rDestination.Value = rCalculation.Value
    For Each rCell In CopyRng
        rCell.Value = 2

        rDestination.Offset(rCell.Row - CopyRng.Row + 1).Value = rCalculation.Value
    Next rCell

End Sub

Had a re-read of your question and updated the code to reflect what I didn't understand. First average is pasted to W99, then the updated ones to W100 onwards. — Darren Bartrup-Cook, Oct 31 '18 at 10:33
You've ticked this as accepted, but I've noticed it doesn't actually answer the question you asked: _My question is, is there a way of setting the cells that i'm changing as an array (picked by the user through a prompt)_. — Darren Bartrup-Cook, Oct 31 '18 at 10:41

VBA code for copying and pasting a value from a specific cell that is changing as the macro runs

3 Answers3