[SOLVED] Convert formula with string function into a VBA equivalent

Convert formula with string function into a VBA equivalent

I would like to turn Excel formulas with string functions into VBA equivalents.

There are several lines of formulas. I'm going to give two examples.

There is a copy a txt report in column A. Columns B thru O sort through the rows of A, picking out pieces using some of the following formulas:

=IF(MID(A6,60,1)="=",MID(A6,52,15),IF(MID(A6,57,1)="=",MID(A6,53,8),C1))

and

=IF(A2="","-",IF(LEFT(A2,1)="1","pg","-"))

Here's a snapshot of a test report.

Mock Report

I'd like to loop through the formulas and send to Sheet2 starting with A2 (due to headers) not go through the steps of copying then pasting to A1, copying columns B thru O, and then sorting out the "-".

I tried recording a macro and imputing the formulas, but I couldn't make it happen.

Solution

Sub convert()
Dim ws As Worksheet, thisRng As Range
Set ws = Sheet1
Set thisRng = Sheet1.Range("B1")' Select the cell to place data into

'   Break up the formula logically first and it becomes easier to convert
'   =IF (MID(A6,60,1)="=",     MID(A6,52,15),    else if IF(MID(A6,57,1)="=",     MID(A6,53,8),        else    C1))
If Mid(ws.Range("A6").Value, 60, 1) = "=" Then
    thisRng = Mid(ws.Range("A6").Value, 52, 15)
    Else
        If (Mid(ws.Range("A6").Value, 57, 1)) = "=" Then
            thisRng = Mid(ws.Range("A6").Value, 53, 8)
        Else
            thisRng.Value = ws.Range("C1").Value
        End If
End If

End Sub