0

I have a range of data (Sheet1) I need to accomplish the following:

  1. Copy cells from "Sheet1" [K2 & W2] & paste in "Sheet2" [I5 & I3 - respectively]

  2. Copy from "Sheet2" [I6] & Paste (as values) to "Sheet1" [X2]
    *NOTE: "Sheet2" [I6] is derived from a computation of the inputs into [I5 & I3] which takes a 1 or 2 seconds to compute the result due to external data pull given the inputs, and then calculated. So I may need a slight pause in the code

  3. Loop above commands in subsequent rows from "Sheet1" [ie K3 & W3 etc]. Destination of paste [I5 & I3] remains constant.

  4. Perform Loop until no more data left in "Sheet1" (variable) range.

I recorded the script below which runs correctly, but i am unsure of how to incorporate a Loop which will perform my task in the variable Range in "sheet1". I am a beginner VBA user. Any help is much appreciated. -Thank you

Sub Gspread () 
ActiveSheet.Next.Select 
Range("I5").Select 
ActiveCell.FormulaR1C1 = "Sheet1R[-3]C[2]" 
Range("I3").Select 
ActiveCell.FormulaR1C1 = "Sheet1R[-1]C[14]" 
Range("I6").Select 
Selection.Copy 
ActiveSheet.Previous.Select 
Range("X2").Select 
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=clNone, SkipBlanks _    :=False, Transpose:=False
Mike M
  • 1

1 Answers1

1

You could do it like this:

Sub Gspread()

    Dim wsData As Worksheet, wsCalc As Worksheet, c As Range
    
    Set wsData = ThisWorkbook.Sheets("Sheet1")
    Set wsCalc = ThisWorkbook.Sheets("Sheet1")
    
    Set c = wsData.Range("K2") 'first input cell
    Do While Len(c.Value) > 0  'while cell has data...
        wsCalc.Range("I5").Value = c.Value                        'copy input values
        wsCalc.Range("I3").Value = c.EntireRow.Columns("W").Value
        DoEvents  'catch up...
        c.EntireRow.Columns("X").Value = wsCalc.Range("I6").Value 'copy output
        Set c = c.Offset(1)                                       'next row
    Loop
    
End Sub
Tim Williams
  • 154,628
  • 8
  • 97
  • 125
  • There's no way for me to know what your actual sheet names are. Whatever is in your sheet tabs (not the sheet's codename shown in the VB editor) is what you need to use. Watch out for spaces etc - you can copy/paste the name from the tabs... – Tim Williams Jun 26 '23 at 15:38
  • thanks for your reply, Tim. It appears the code is not allowing for ample time to run my calculations. Is there a method to have code pause for 1 or 2 seconds in order for the calculations to run and output in Sheet2 [I6] ? – Mike M Jun 26 '23 at 15:47
  • Some approaches here: https://stackoverflow.com/questions/11277034/wait-until-application-calculate-has-finished – Tim Williams Jun 26 '23 at 15:51