Using if InStr statement

[aryanaveen]

Hi All

I have a problem in bulding my macro, please help

I am using below code to find out the line which contains "USD" and this is working fine. Now is it possible to insert one more condition

All I want is the code should execute the formula only if cell starts with numbers 1 to 9 and ends with USD

ex - 52 6/4/2017 16.00 263.00 4,208.00 0.00 0.00 4,208.00USD

My code -

Range("A1").Select
Dim lrow As Long, rng As Range, cell As Range
lrow = Cells(Cells.Rows.Count, "A").End(xlUp).Row


Set rng = Range("A2:A" & lrow)


For Each cell In rng
If InStr(1, cell.Value, "Service Provider:", vbTextCompare) > 0 Then
cell.Offset(0, 2).FormulaR1C1 = "=RIGHT(RC[-2], LEN(RC[-2])-FIND("":"",RC[-2]))"
cell.Offset(0, 2).Copy
cell.Offset(0, 2).PasteSpecial xlPasteValuesAndNumberFormats
Application.CutCopyMode = False

End If


Next cell

 

[Fluff]

Give this a try

Range("A1").Select

Dim lrow As Long, rng As Range, cell As Range
lrow = Cells(Cells.Rows.Count, "A").End(xlUp).Row


Set rng = Range("A2:A" & lrow)


For Each cell In rng
    With cell
        If Left(.Value, 1) >= 1 And Left(.Value, 1) <= 9 And Right(.Value, 3) = "USD" Then
            .Offset(, 2) = Right(.Value, Len(.Value) - InStr(.Value, ":"))
        End If
    End With

Next cell

 

[Rick Rothstein]

Give this a try

Range("A1").Select

Dim lrow As Long, rng As Range, cell As Range
lrow = Cells(Cells.Rows.Count, "A").End(xlUp).Row


Set rng = Range("A2:A" & lrow)


For Each cell In rng
    With cell
        [B][COLOR="#FF0000"]If Left(.Value, 1) >= 1 And Left(.Value, 1) <= 9 And Right(.Value, 3) = "USD" Then[/COLOR][/B]
            .Offset(, 2) = Right(.Value, Len(.Value) - InStr(.Value, ":"))
        End If
    End With

Next cell

Here is another way to write the line I highlighted in red above...

If .Value Like "[1-9]*USD" Then

 

[Fluff]

Thanks for that Rick
A lot neater & easier than mine

 

[aryanaveen]

Thank you Fluff, its working

is their a excel formula which will extract below line in to multiple cells after each space

52 6/4/2017 16.00 263.00 4,208.00 0.00 0.00 4,208.00USD

 

[aryanaveen]

Thank you Rick, its working

is their a excel formula which will extract below line in to multiple cells after each space

52 6/4/2017 16.00 263.00 4,208.00 0.00 0.00 4,208.00USD

 

[Rick Rothstein]

Thank you Rick, its working

is their a excel formula which will extract below line in to multiple cells after each space

52 6/4/2017 16.00 263.00 4,208.00 0.00 0.00 4,208.00USD

Assuming your first value is in cell A1, put this in cell B1 and copy across for as many cells as you think you will ever have data to fill, then copy all of those cells down to the end of your data (or beyond if you think you will be adding more data in the future)...

=TRIM(MID(SUBSTITUTE(" "&$A1," ",REPT(" ",100)),COLUMNS($B:B)*100,100))

 

[aryanaveen]

thank you soo much