CODEWITHSUNDEEP
excelvba
Matt Taylor
Matt Taylor

Reputation: 671

Formula within vba as a variable

I am trying to simply get the these formulas as a variable in VBA.

Cell C1 is merged until F1. The following returns desired values.

Formulas:

=LEFT(C1,FIND(" ",C1)-1) = FirstWord

=MID(C1,SEARCH(" - ",C1)+3,4) = 123

VBA: 

Dim SUBname, SUBnum As String
SUBname = Formula = "=LEFT(Selection,FIND("" "",Selection)-1)"
SUBnum = Formula = "=MID(Selection,SEARCH("" - "",Selection)+3,4)"
Debug.Print "SubName: "; SUBname
Debug.Print "Sub#: "; SUBnum & vbNewLine

Debug returns False for both.

The prior part of my code selects the merged header. I would like to stick with selection if I can.

What am I doing wrong?

Upvotes: 1

Views: 77

Answers (2)

JvdV
JvdV

Reputation: 75840

An alternative to MID and LEFT, assuming you have strings like:

Text1 - 123
Text2 - 456
Text3 - 789

You can get the first word of such a string in upper case through:

SUBname = Ucase(Split(String, " - ")(0))

The second part (no need for ucase on numbers):

SUBnum = Split(String, " - ")(1)

Upvotes: 1

Scott Craner
Scott Craner

Reputation: 152450

Left and Mid are vba functions use them:

SUBname = LEFT(Selection,INSTR(Selection," ")-1)

And

SUBnum = MID(Selection,INSTR(Selection," - ")+3,4)

The reason you are getting False is the result is asking if Formula is equal to the formula string. Which it is not an thus it returns False to the variable.

Upvotes: 3

Related Questions