Using TRIM in VBA

[JanetW]

Hi All

I am trying to use the TRIM function in VBA but am struggling!

The code which works in excel is:

=TRIM(RIGHT(SUBSTITUTE(N2,"-",REPT(" ",100)),100))

when I try to use the same in VBA, I get a compile error: expected end of statement

I assume that this is because of the " within the code (so VBA thinks code is finished?) so I tried to get round this by creating the formula as a string as follows:

SupplierNoFormula = "=TRIM(RIGHT(SUBSTITUTE(N2," & """ & "-" & """ & ",REPT(" & """ & " " & """ & ",100)),100))" - which also doesn't work!!!

what I basically have is a list in one column of supplier name and number (separated by '-' ie A SUPPLIER-OR12_12345) and I need to extract the supplier number (OR12_12345) to enable me to do a lookup to another report

Any help would be greatly appreciated

Thanks in advance!

Janet

 

[Fluff]

Hi try
Range("A1").Formula = "=TRIM(RIGHT(SUBSTITUTE(N2,""-"",REPT("" "",100)),100))"

 

[steve the fish]

You can use something like this in code:

myCell = Range("N2").Value
myVal = Trim(Mid(myCell, InStrRev(myCell, "-") + 1, Len(myCell)))

 

[Peter_SSs]

You can use something like this in code:

myCell = Range("N2").Value
myVal = Trim(Mid(myCell, InStrRev(myCell, "-") + 1[COLOR="#FF0000"][B], Len(myCell)[/B][/COLOR]))

I think the OP's question is really about how to deal with the quotes in vba but I have a comment on your code anyway.
Since the vba Mid() function does not require the 3rd (length) argument, you could just omit the red part & save that calculation.

 

[JanetW]

Thank you so much for the quick response. That works perfectly!

Love this site!

Hi try
Range("A1").Formula = "=TRIM(RIGHT(SUBSTITUTE(N2,""-"",REPT("" "",100)),100))"

 

[Fluff]

Glad to help & thanks for the feedback

 

[JanetW]

Thank you so much for the quick reply, I have used Fluff's solution but appreciate the response and will keep this for possible future use!

You can use something like this in code:

myCell = Range("N2").Value
myVal = Trim(Mid(myCell, InStrRev(myCell, "-") + 1, Len(myCell)))