1

I have been trying to put together the code for the following request, I am not an expert and I really need help on this, thanks in advance:

There are 2 sheets 1 "Database", 2 "Scorecard".

  • Loop through the rows in column C Database Sheet, each single value will be copied into the Scorecard sheet Cell B3, this will change the value of the cell C30.
  • The new value for cell C30 will then need to be copied back to the Database Sheet in new column "F", and this will be looped till the last cell. Filling the list.
  • It requires to be correspondent to the the cell, thus the first value in C2 will need the matching value in F2 and so on.
  • The database will change in time so it requires a code that allows to consider new entrances.

I have tried to modify this code I've seen in a different question: Loops in VBA? I want to use a loop to select and copy till last cell but can't make it work...

Thanks so so much!

Sub LoopThroughColumnC()

Dim LastRowInColC As Long, Counter As Long
Dim SourceCell As Range, DestCell As Range
Dim MySheet As Worksheet
'set references up-front
Set MySheet = ThisWorkbook.Worksheets("Dati per calcolo")
Set CopySheet = ThisWorkbook.Worksheets("Scheda costo tessuto e capo")
With MySheet
    LastRowInColC = .Range("C" & .Rows.Count).End(xlUp).Row
    Set DestCell = ThisWorkbook.Worksheets("Scheda costo tessuto e capo").Range("B3")
End With
'loop through column C, copying from cells(counter, 11) to B3
With MySheet
    For Counter = 1 To LastRowInColC
        Set SourceCell = .Range("C" & Counter)
        SourceCell.Copy Destination:=DestCell

    If Target.Address = Range("A1").Address Then
        ' Get the last row on our destination sheet (using Sheet2, col A here)...
        Dim intLastRow As Long
        intLastRow = Sheet2.Cells(Sheet2.Rows.Count, "B").End(xlUp).Row
        ' Add our value to the next row...
        Sheet2.Cells(intLastRow + 1, "A") = Target.Value
    End If

    Next Counter
End With
End Sub

Here's how it looks

cybernetic.nomad
  • 6,100
  • 3
  • 18
  • 31
Joyful J
  • 11
  • 2
  • 5
    Can you [edit](https://stackoverflow.com/posts/52169473/edit) your question and post the code you have tried (with the modifications applied) – cybernetic.nomad Sep 04 '18 at 15:29
  • Thanks so so much!, I hope it's clearer now... sorry, not at all an expert in VBA. – Joyful J Sep 04 '18 at 15:49
  • Assuming the value in `M10` is derived from a formula, this would require formulas to be recalculated for every loop. A slow and clunky process. Are you sure you cannot use a formula in column `F`? – cybernetic.nomad Sep 04 '18 at 16:14
  • You are right, actually there is a formula, let's say I have my "Scorecard" with Excel Formulas which change according to the Number I can put in the B3 Cell. When this happens, the final cost is computed and that M10 cell is the one I need to copy and paste again in the Database. I thought about what you said, but it seemed I would require many columns as I am using lots of nested and matrix formulas in the scorecard... I htought would be easier to use the Macro, do you think it would be better the column then? – Joyful J Sep 04 '18 at 16:19
  • Another option is to do the calculation in VBA. Hard to say what the best way is without seeing your formula(s). – cybernetic.nomad Sep 04 '18 at 17:25
  • I just saw your kind respond thanks, I added a picture, hopefully that helps to clarify... by the way it was C30 instead of M10 as I have said before. I am thinking how to do it with formulas, seems quite challenging to add all those elements. – Joyful J Sep 05 '18 at 07:38

0 Answers0