SOLVED

Macro to include an array formula into an existing do loop

Regular Contributor

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

@hrh_dash 

I'd have to see a sample workbook.

@Hans Vogelaar , attaching the workbook for your reference.

@hrh_dash 

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.

@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)")

 

 

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

best response confirmed by hrh_dash (Regular Contributor)
Solution

@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

 

, thank you! Much appreciated!