# what will we do until or while?

## For Each Next Loop Solution

The Code

``````Option Explicit

Sub updateByCriteria()

' Define Constants.

Const wsName As String = "Sheet1"
Const FirstRow As Long = 5
Const srcCol As String = "A"
Const tgtCol As String = "P"
Dim Criteria As Variant
Dim Formulas As Variant
Criteria = Array("ARS", "BRL")       ' add more...
Formulas = Array("=1/245", "=1/246") ' add more...

' Define Source Column Range.

' Define workbook.
Dim wb As Workbook
Set wb = ThisWorkbook
' Define worksheet.
Dim ws As Worksheet
Set ws = wb.Worksheets(wsName)
' Calculate Last Non-Empty Row.
Dim LastRow As Long
LastRow = ws.Cells(ws.Rows.Count, srcCol).End(xlUp).Row
' Define Source Column Range.
Dim rng As Range
Set rng = ws.Range(ws.Cells(FirstRow, srcCol), ws.Cells(LastRow, srcCol))

' Prepare to write to Target Column Range.

' Calculate Column Offset.
Dim ColOffset As Long
ColOffset = ws.Columns(tgtCol).Column - ws.Columns(srcCol).Column

' Declare variables.
Dim CurPos As Variant ' Current Position
Dim cel As Range      ' Current Cell Range

' Write formulas to Target Column Range.

Application.ScreenUpdating = False
' Iterate the cell ranges in Source Range.
For Each cel In rng.Cells
' Check if Current Cell Range in Source Column Range is not empty.
If Not IsEmpty(cel) Then
' Try to find the value in Current Cell Range in Criteria Array
' and write the position to Current Position
CurPos = Application.Match(cel, Criteria, 0)
' Check if value in Current Cell Range has been found
' in Criteria Array.
If Not IsError(CurPos) Then
' Write formula from Formulas Array to current Target Cell
' Range, using Current Position in Criteria Array.
cel.Offset(, ColOffset).Formula = _
Application.Index(Formulas, CurPos)
End If
End If
Next cel
Application.ScreenUpdating = True

' Inform user.

MsgBox "Formulas copied.", vbInformation, "Success"

End Sub
``````