Correcting formula in vba excel

1
votes

I want to create a macro that check all the cells of a column and if the first two characters of a cell is "BB" then i want the macro to extract three characters from the cell and paste it to the next column but a the corresponding row. But my formula after the if clause is not working. this is what i have done since:

Sub test()

Dim lmid As String
Dim srange, SelData, ExtBbFor As String
Dim lastrow As Long
Dim i, icount As Integer

lastrow = ActiveSheet.Range("B30000").End(xlUp).Row
srange = "G1:G" & lastrow
SelData = "A1:G" & lastrow

Range(srange).Formula = "=mid(E1,1,3)"

For i = 1 To lastrow
    If InStr(1, LCase(Range("E" & i)), "bb") <> 0 Then
        Range("G" & i).Formula = "=mid("E & i", 4, 3)"
    End If
Next i

End Sub

thanks in advance

2
vbaexcelexcel-formula

2 Answers

0
votes

Try with below. It will work

Range("G" & i).Value = Mid(Range("E" & i), 4, 3)

If the cell is not limited to 7 then you need as below

Range("G" & i).Value = "=Mid(E" & i & ", 3, " & Len(E & "& i & ") & ")"

It will extract from the 3rd character up to the last character in a cell.

0
votes

Your syntax is wrong where you're trying to concatenate strings, I think you mean to use:

Range("G" & i).Formula = "=MID(E" & i & ",4,3)"

Based on your code I think this will do the exact same thing without having to loop or declare any variables:

Sub test()

With Range("G1:G" & Cells(Rows.Count, 2).End(xlUp).Row)
    .FormulaR1C1 = "=IF(UPPER(LEFT(RC[-2],2))=""BB"",MID(RC[-2],4,3),"""")"
    .Value = .Value
End With

End Sub