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

CLICK HERE to find out more related problems solutions.

Leave a Comment

Your email address will not be published.

Scroll to Top