PDA

View Full Version : formula to vba



oleg_v
12-22-2010, 12:55 PM
hi
i need some help
i havethis formula
=RIGHT(B1,LEN(B1)-FIND(":",B1))
how can i right this formula in vba code??
thanks
Oleg

macropod
12-22-2010, 01:01 PM
Hi Oleg,

At it's simplest:
ActiveCell.Value = "=RIGHT(B1,LEN(B1)-FIND("":"",B1))"

oleg_v
12-22-2010, 01:06 PM
hi
it does not work if i out instead of "B1" variable
can i do this with variables?

Tinbendr
12-22-2010, 01:18 PM
Without using any worksheet functions.
ActiveCell.Value = Right(Range("B1"), _
Len(Range("B1")) - InStr(Range("B1"), ":"))

macropod
12-22-2010, 01:28 PM
Hi Oleg,

It is not clear what you want. Perhaps:
Dim i As Integer
For i = 1 To 10
ActiveSheet.Range("B" & i).Value = "=RIGHT(B" & i & ",LEN(B" & i & ")-FIND("":"",B" & i & "))"
Next

oleg_v
12-22-2010, 01:43 PM
what i want that i have a variable "myvar" and it equals $G$291

i need that i cell q1 i will see "G" and in q2 "291"

sorry for explanations i an working online

thanks for all the help you are given me

macropod
12-22-2010, 02:37 PM
Dim myvar As String
myvar = ActiveSheet.Range("Q1").Value & ActiveSheet.Range("Q2").Value
ActiveCell.Value = "=RIGHT(" & myvar & ",LEN(" & myvar & ")-FIND("":""," & myvar & "))"

Zack Barresse
12-23-2010, 12:59 PM
Even though there really is, as far as I know, no difference in the execution, I would recommend using ".Formula" instead of ".Value", purely for the fact of debugging code or performing maintenance on it.