Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community
SOLVED

Iron Contributor

# 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!

7 Replies

# Re: Macro to include an array formula into an existing do loop

I'd have to see a sample workbook.

# Re: Macro to include an array formula into an existing do loop

@Hans Vogelaar , attaching the workbook for your reference.

# Re: Macro to include an array formula into an existing do loop

I don't understand what that workbook has to do with your question. ws and ws_N are the same sheet - I don't think that's what you intended.

# Re: Macro to include an array formula into an existing do loop

@Hans Vogelaar, how about this revised workbook?

I tried using the macro below and the results populated was #VALUE

``````Set ws = ThisWorkbook.Sheets("Sheet2")

lastRow = ws.Cells(Rows.Count, "A").End(xlUp).Row
Set rng = ws.Range("A2:A" & lastRow)

ws.Range("E2:E" & lastRow) = Evaluate("=trim(left(" & rng.Address & ",min(find({0, 1, 2, 3, 4, 5, 6, 7, 8, 9}, " & rng.Address & " & ""0123456789"")) - 1)")``````

# Re: Macro to include an array formula into an existing do loop

Hi @Hans Vogelaar , would you be able to advise what is wrong with the code?

best response confirmed by hrh_dash (Iron Contributor)
Solution

# Re: Macro to include an array formula into an existing do loop

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``````

# Re: Macro to include an array formula into an existing do loop

, thank you! Much appreciated!
1 best response

Accepted Solutions
best response confirmed by hrh_dash (Iron Contributor)
Solution

# Re: Macro to include an array formula into an existing do loop

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``````