subroutine to copy a part naumbers and paste totals 1

[Heeeeelp]

Hi everyone,

I have an excel spreadsheet with a column (P) that lists part numbers. I would like to write a subroutine that would copy the part numbers, one at a time, into cell $B$7. This would lead to two prices being calculated. After they are calculated, I would like the subroutine to copy the prices from cells B44 and F44 to the cells next to its respective part number, in columns Q and R.

I would greatly appreciate it if someone could help me with the code for this.

Kind regards,
Tess

 

[CautionMP]

Heeeeelp,
Typed and untested but it should be pretty close, I think.

Sub Demo()
  Dim wksInput As Worksheet
  Dim rngCalcInput As Range, rngPrice1 As Range, rngPrice2 As Range
  Dim lngInputStart As Long, lngInputStop As Long
  Dim lngInputCurrent As Long
  
  'Set these to match your data
  Set wksInput = ThisWorkbook.Worksheets("Sheet1")
  Set rngCalcInput = wksInput.Range("B7")
  Set rngPrice1 = wksInput.Range("B44")
  Set rngPrice2 = wksInput.Range("F44")
  lngInputStart = 2
  lngInputStop = 100
  'END Set these to match your data
  
  For lngInputCurrent = lngInputStart To lngInputStop
    rngCalcInput.Value = wksInput.Range("P" & lngInputCurrent).Value
    wksInput.Calculate
    wksInput.Range("Q" & lngInputCurrent).Value = rngPrice1.Value
    wksInput.Range("R" & lngInputCurrent).Value = rngPrice2.Value
  Next lngInputCurrent
  
  Set rngPrice1 = Nothing
  Set rngPrice2 = Nothing
  Set rngCalcInput = Nothing
  Set wksInput = Nothing
End Sub

Hope this helps,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[Heeeeelp]

Thank you CMP. This worked perfectly. I truly appreciate your help.

Tess