Forum Discussion

hrh_dash's avatar
hrh_dash
Iron Contributor
Oct 31, 2022
Solved

Macro to include an array formula into an existing do loop

Hi all, i would like to insert this array formula into an existing do loop.

 

Excel formula will be as follows:

A2 = TRIM(LEFT(A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456789"))-1))

 

Code as follows:

    If Not rng Is Nothing Then
        adr = rng.Address
        Do
            lastRow = lastRow + 1
            
            ws_N.Range("B" & lastRow).Value = rng.Offset(0, -1).Value
            ws_N.Range("A" & lastRow).Value = Left(rng.Offset(0, -2), WorksheetFunction.Min(Array(0, 1, 2, 3, 4, 5, 6, 7, 8, 9), rng.Offset(0, -2) - 1))
            ws_N.Range("C" & lastRow).Value = Mid(myFile, 1, InStr(1, myFile, ".") - 1) 'How can this be recitfy?
           
            Set rng = ws.UsedRange.FindNext(rng)
            If rng Is Nothing Then Exit Sub
        Loop Until rng.Address = adr
        
    End If

 

thanks for the help in advance!

excel
Formulas and Functions
Macros and VBA
  • HansVogelaar's avatar
    HansVogelaar
    Nov 02, 2022

    hrh_dash 

    You can use Evaluate only for a single cell, not for a multi-cell range. Does this work for you?

     

    Sub testt()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim rng As Range
    Set ws = ThisWorkbook.Sheets("Sheet2")
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    Set rng = ws.Range("A2:A" & lastRow)
    With rng.Offset(0, 4)
        .Formula = "=TRIM(LEFT(" & rng.Address & ",MIN(FIND({0,1,2,3,4,5,6,7,8,9}," & rng.Address & "&""0123456789""))-1))"
        .Value = .Value
    End With
End Sub

     

Resources