VBA equivelent to Cell Formula ?

Asrampd

Board Regular
Joined
Feb 26, 2012
Messages
247
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.
 

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,910
Something like
Code:
Dim extractedNumeral As String

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

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,218
Office Version
  1. 365
Platform
  1. Windows
Something like this perhaps which puts the result back in the cell.
Code:
Dim rng As Range


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

Asrampd

Board Regular
Joined
Feb 26, 2012
Messages
247
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

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,910
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,218
Office Version
  1. 365
Platform
  1. Windows
If all you want is code for putting the result in B8:
Code:
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,122,672
Messages
5,597,488
Members
414,146
Latest member
marginmakerb

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top