Excel VBA 2016 Passing range cells to another sheet as calculated

0
votes

I am looking for a code to loop through a named range cells values if they are greater than 9 in sheet1. if the Cells value(>9) is found pass that cell value to sheet2 as calculated value

The format is like: Sheet2.Range(name).Cells.value = Sheet1.Range(name).Cells.value

Currently i managed to check those values in sheet1 and it works.

I am failing to assign the value in sheet2 cellTarget looping

code:

Dim rngSource As Range, cellSource As Range
Dim rngTagert As Range, cellTarget As Range

Set rngSource = Range("SourceRange") 'Sheet1
Set rngTagert = Range("TargetRange") 'Sheet2

For Each cellSource In rngSource
    If cellSource.Value >= 9 Then
        For Each cellTarget In rngTagert
            cellTarget.Value = cellSource.Value - 9
        Next cellTarget
    Else
        cellTarget.Value = cellTarget.Value
    End If
Next cellSource

Error says "Invalid Next Control Variable Reference"

Many Thanks

1
vbaexcel
if you will try to indent your code properly, you'll find you have One For Each cellSource In rngSource and Two Next cell – Shai Rado

1 Answers

1
votes

you

  • either susbtitute

    first Next cell with Next cellTarget

    second Next cell with Next cellSource

  • or just substitute each Next cell with Next

what follows is the second option:

Sub main()
    Dim rngSource As Range, cellSource As Range
    Dim rngTagert As Range, cellTarget As Range

    Set rngSource = Range("SourceRange") 'Sheet1
    Set rngTagert = Range("TargetRange") 'Sheet2

    For Each cellSource In rngSource
        If cellSource.value >= 9 Then
            For Each cellTarget In rngTagert
                cellTarget.value = cellSource.value - 9 'Sheet2 Cell.Value = Sheet1 Cell.Value -9
            Next
        Else
            cellSource.value = cellSource.value 'Sheet1 cellTarget.Value = cellTarget.Value 'Sheet2
        End If
    Next
End Sub