Forum Discussion
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...
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
I'd have to see a sample workbook.
HansVogelaar , attaching the workbook for your reference.
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.
HansVogelaar, 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)")Hi HansVogelaar , would you be able to advise what is wrong with the code?