ExcelBanter - formula in vba

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - formula in vba (https://www.excelbanter.com/excel-programming/334612-formula-vba.html)

how would you enter this formula using vba

=MID(B7,FIND(" ",B7)+1,LEN(B7)-FIND(" ",B7,1)) & "/"&MID(B8,FIND("
",B8)+1,LEN(B8)-FIND(" ",B8,1))

--

Gary

One way:

Range("A1").Formula = "=MID(B7,FIND("" "",B7)+1," & _
"LEN(B7)-FIND("" "",B7,1)) & ""/""&MID(B8," & _
"FIND("" "",B8)+1,LEN(B8)-FIND("" "",B8,1))"

Note that, just as in XL strings, quotation marks need to be doubled.

OTOH, I probably would make the function more efficient:

Range("A2").Formula = "=MID(B7,FIND("" "",B7)+1," & _
"32767) & ""/""&MID(B8," & "FIND("" "",B8)+1,32767)"

where 32767 is just a number large enough to contain the entire
remainder of the string.

In article ,
"Gary Keramidas" wrote:

how would you enter this formula using vba

=MID(B7,FIND(" ",B7)+1,LEN(B7)-FIND(" ",B7,1)) & "/"&MID(B8,FIND("
",B8)+1,LEN(B8)-FIND(" ",B8,1))

i just entered it into a hidden cell for now and referenced that cell in the
code. it works

--

Gary

"Gary Keramidas" wrote in message
...
how would you enter this formula using vba

=MID(B7,FIND(" ",B7)+1,LEN(B7)-FIND(" ",B7,1)) & "/"&MID(B8,FIND("
",B8)+1,LEN(B8)-FIND(" ",B8,1))

--

Gary

thanks, i think that's what was messing me up, the double quotes. i just had
to make the cell references absolute because i sort after i create the
table.

thanks again

--

Gary

"JE McGimpsey" wrote in message
...
One way:

Range("A1").Formula = "=MID(B7,FIND("" "",B7)+1," & _
"LEN(B7)-FIND("" "",B7,1)) & ""/""&MID(B8," & _
"FIND("" "",B8)+1,LEN(B8)-FIND("" "",B8,1))"

Note that, just as in XL strings, quotation marks need to be doubled.

OTOH, I probably would make the function more efficient:

Range("A2").Formula = "=MID(B7,FIND("" "",B7)+1," & _
"32767) & ""/""&MID(B8," & "FIND("" "",B8)+1,32767)"

where 32767 is just a number large enough to contain the entire
remainder of the string.

In article ,
"Gary Keramidas" wrote:

how would you enter this formula using vba

=MID(B7,FIND(" ",B7)+1,LEN(B7)-FIND(" ",B7,1)) & "/"&MID(B8,FIND("
",B8)+1,LEN(B8)-FIND(" ",B8,1))