VBA equivelent to Cell Formula ?

[Asrampd]

Can anyoe help me with some code equivalent to the following Formulas - BUT I also need to see the setting up of the variable that will hold the results and the code to place the result into a cell

=MID(A8, 2,3) , so that if the content of A8 was "L096n011_SET_Export.xls" it outputs 096 (without loosing the zero).

Ta.

 

[mikerickson]

Something like

Dim extractedNumeral As String

extractedNumeral = Mid(Range("A8").Text, 2, 3)

 

[Norie]

Something like this perhaps which puts the result back in the cell.

Dim rng As Range


   Set rng = Range("A8")
   rng.NumberFormat = "@"
   rng.Value = Mid(rng.Value, 2,3)

 

[Asrampd]

I can see that both do the job of getting the data out, I need to put the result into Cell B8 please (I will also copy this formula down the sheet do do the same on other rows).

Ta.

 

[mikerickson]

Put the worksheet formula =MID(A8, 2, 3) in B8 and drag down.

Sometimes the best way to use Excel VBA is to use Excel's non-VBA capabilities.

 

[Norie]

If all you want is code for putting the result in B8:

Dim rng As Range 

Set rng = Range("A8")

    With rng.Offset(0,1)
       .NumberFormat = "@"
       .Value = Mid(rng.Value,2,3)
      End With

That isn't a formula though and can't be copied down.

Do you actually want code to enter the formula down column B?